中繼資料可見性組態Metadata Visibility Configuration

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

中繼資料的可見性會限制在使用者所擁有的安全性實體,或已授與使用者某些權限的安全性實體。The visibility of metadata is limited to securables that a user either owns or on which the user has been granted some permission. 例如,如果授與使用者資料表 myTable的 SELECT 或 INSERT 權限,則下列查詢會傳回一個資料列。For example, the following query returns a row if the user has been granted a permission such as SELECT or INSERT on the table myTable.

SELECT name, object_id  
FROM sys.tables  
WHERE name = N'myTable';  
GO  

但是,如果使用者對 myTable沒有任何權限,則查詢會傳回空的結果集。However, if the user does not have any permission on myTable, the query returns an empty result set.

中繼資料可見性組態的範圍和影響Scope and Impact of Metadata Visibility Configuration

中繼資料可見性組態僅適用於下列安全性實體。Metadata visibility configuration only applies to the following securables.

目錄檢視Catalog views Database EngineDatabase Engine sp_help 預存程序sp_help stored procedures
公開內建函數的中繼資料Metadata exposing built-in functions 資訊結構描述檢視Information schema views
相容性檢視Compatibility views 擴充屬性Extended properties

中繼資料可見性組態不適用於下列安全性實體。Metadata visibility configuration does not apply to the following securables.

「記錄傳送」系統資料表Log shipping system tables SQL ServerSQL Server Agent 系統資料表Agent system tables
資料庫維護計畫系統資料表Database maintenance plan system tables 「備份」系統資料表Backup system tables
「複寫」系統資料表Replication system tables 複寫和 SQL ServerSQL Server Agent sp_help 預存程序Replication and SQL ServerSQL Server Agent sp_help stored procedures

中繼資料存取範圍有限制的意義如下:Limited metadata accessibility means the following:

  • 假設以 public 來存取中繼資料的應用程式會中斷。Applications that assume public metadata access will break.

  • 對系統檢視查詢可能只會傳回資料列的子集,或有時候傳回空的結果集。Queries on system views might only return a subset of rows, or sometimes an empty result set.

  • 發出中繼資料的內建函數 (例如 OBJECTPROPERTYEX) 可能傳回 NULL。Metadata-emitting, built-in functions such as OBJECTPROPERTYEX may return NULL.

  • Database EngineDatabase Engine sp_help 預存程序可能只會傳回資料列的子集,或傳回 NULL。The Database EngineDatabase Engine sp_help stored procedures might return only a subset of rows, or NULL.

SQL 模組 (例如預存程序和觸發程序) 會在呼叫者的安全性內容下執行,因此在中繼資料的存取上受到限制。SQL modules, such as stored procedures and triggers, run under the security context of the caller and, therefore, have limited metadata accessibility. 例如,在下列的程式碼中,當預存程序嘗試存取資料表 myTable 的中繼資料而呼叫者對此資料表沒有任何權限時,將會傳回空的結果集。For example, in the following code, when the stored procedure tries to access metadata for the table myTable on which the caller has no rights, an empty result set is returned. 若在舊版的 SQL ServerSQL Server,則會傳回一個資料列。In earlier releases of SQL ServerSQL Server, a row is returned.

CREATE PROCEDURE assumes_caller_can_access_metadata  
BEGIN  
SELECT name, object_id   
FROM sys.objects   
WHERE name = N'myTable';  
END;  
GO  

若要讓呼叫者能夠檢視中繼資料,您可以在適當範圍 (例如,物件層級、資料庫層級或伺服器層級) 授與呼叫者 VIEW DEFINITION 權限。To allow callers to view metadata, you can grant the callers VIEW DEFINITION permission at an appropriate scope: object level, database level or server level. 因此,在上個範例中,如果呼叫者具有 myTable 的 VIEW DEFINITION 權限,預存程序就會傳回一個資料列。Therefore, in the previous example, if the caller has VIEW DEFINITION permission on myTable, the stored procedure returns a row. 如需詳細資訊,請參閱 GRANT (Transact-SQL)授與資料庫權限 (Transact-SQL)For more information, see GRANT (Transact-SQL) and GRANT Database Permissions (Transact-SQL).

您也可以修改預存程序,使其在擁有者的認證下執行。You can also modify the stored procedure so that it executes under the credentials of the owner. 若程序擁有者和資料表擁有者是相同的擁有者,就會套用擁有權鏈結,且程序擁有者的安全性內容就可以存取 myTable的中繼資料。When the procedure owner and the table owner are the same owner, ownership chaining applies, and the security context of the procedure owner enables access to the metadata for myTable. 在這種狀況下,下列程式碼會將中繼資料的資料列傳回給呼叫者。Under this scenario, the following code returns a row of metadata to the caller.

注意

下列範例使用 sys.objects 目錄檢視,而非 sys.sysobjects 相容性檢視。The following example uses the sys.objects catalog view instead of the sys.sysobjects compatibility view.

CREATE PROCEDURE does_not_assume_caller_can_access_metadata  
WITH EXECUTE AS OWNER  
AS  
BEGIN  
SELECT name, object_id  
FROM sys.objects   
WHERE name = N'myTable'   
END;  
GO  

注意

您可以使用 EXECUTE AS,暫時切換到呼叫者的安全性內容。You can use EXECUTE AS to temporarily switch to the security context of the caller. 如需詳細資訊,請參閱 EXECUTE AS (Transact-SQL)For more information, see EXECUTE AS (Transact-SQL).

中繼資料可見性組態的優點和限制Benefits and Limits of Metadata Visibility Configuration

中繼資料可見性組態在整體安全性計畫中扮演著重要的角色。Metadata visibility configuration can play an important role in your overall security plan. 但在某些情況中,技術純熟又執意操作的使用者還是能夠強制洩漏某些中繼資料。However, there are cases in which a skilled and determined user can force the disclosure of some metadata. 我們建議您將中繼資料權限部署為全面防禦中的一環。We recommend that you deploy metadata permissions as one of many defenses-in-depth.

強制發出錯誤訊息中的中繼資料,理論上是可行的,做法是在查詢中操縱述詞評估的順序。It is theoretically possible to force the emission of metadata in error messages by manipulating the order of predicate evaluation in queries. 這種「嘗試與錯誤攻擊」 的可能性不是 SQL ServerSQL Server 所特有。The possibility of such trial-and-error attacks is not specific to SQL ServerSQL Server. 它由關聯式代數所允許的關聯式和交換式轉換所暗示。It is implied by the associative and commutative transformations permitted in relational algebra. 您可以限制錯誤訊息所傳回的資訊來減輕此風險。You can mitigate this risk by limiting the information returned in error messages. 若要以此方式進一步限制中繼資料的可見性,您可以用追蹤旗標 3625 來啟動伺服器。To further restrict the visibility of metadata in this way, you can start the server with trace flag 3625. 此追蹤旗標限制錯誤訊息所顯示的資訊量。This trace flag limits the amount of information shown in error messages. 而這有助於防止強制洩漏。In turn, this helps to prevent forced disclosures. 代價是錯誤訊息會簡單一些,用於偵錯時可能會比較困難。The tradeoff is that error messages will be terse and might be difficult to use for debugging purposes. 如需詳細資訊,請參閱 Database Engine 服務啟動選項追蹤旗標 (Transact-SQL)For more information, see Database Engine Service Startup Options and Trace Flags (Transact-SQL).

下列的中繼資料不會被強制洩漏:The following metadata is not subject to forced disclosure:

  • sys.serversprovider_string資料行中儲存的值。The value stored in the provider_string column of sys.servers. 沒有 ALTER ANY LINKED SERVER 權限的使用者在資料行中只會看見 NULL 值。A user that does not have ALTER ANY LINKED SERVER permission will see a NULL value in this column.

  • 使用者自訂物件 (例如預存程序或觸發程序) 的來源定義。Source definition of a user-defined object such as a stored procedure or trigger. 只有下列任一狀況屬實時,才能看見原始程式碼:The source code is visible only when one of the following is true:

    • 使用者具有物件的 VIEW DEFINITION 權限。The user has VIEW DEFINITION permission on the object.

    • 使用者對該物件的 VIEW DEFINITION 權限尚未被拒絕,且使用者具有該物件的 CONTROL、ALTER 或 TAKE OWNERSHIP 權限。The user has not been denied VIEW DEFINITION permission on the object and has CONTROL, ALTER, or TAKE OWNERSHIP permission on the object. 其他所有使用者都只會看到 NULL。All other users will see NULL.

  • 下列目錄檢視中的定義資料行:The definition columns found in the following catalog views:

    sys.all_sql_modulessys.all_sql_modules sys.sql_modulessys.sql_modules
    sys.server_sql_modulessys.server_sql_modules sys.check_constraintssys.check_constraints
    sys.default_constraintssys.default_constraints sys.computed_columnssys.computed_columns
    sys.numbered_proceduressys.numbered_procedures
  • syscomments 相容性檢視中的 ctext 資料行。The ctext column in the syscomments compatibility view.

  • sp_helptext 程序的輸出。The output of the sp_helptext procedure.

  • 資訊結構描述檢視中的下列資料行:The following columns in the information schema views:

    INFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSEINFORMATION_SCHEMA.CHECK_CONSTRAINTS.CHECK_CLAUSE INFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULTINFORMATION_SCHEMA.COLUMNS.COLUMN_DEFAULT
    INFORMATION_SCHEMA.DOMAINS.DOMAIN_DEFAULTINFORMATION_SCHEMA.DOMAINS.DOMAIN_DEFAULT INFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_DEFAULTINFORMATION_SCHEMA.ROUTINE_COLUMNS.COLUMN_DEFAULT
    INFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITIONINFORMATION_SCHEMA.ROUTINES.ROUTINE_DEFINITION INFORMATION_SCHEMA.VIEWS.VIEW_DEFINITIONINFORMATION_SCHEMA.VIEWS.VIEW_DEFINITION
  • OBJECT_DEFINITION() 函數OBJECT_DEFINITION() function

  • sys.sql_logins的 password_hash 資料行中儲存的值。The value stored in the password_hash column of sys.sql_logins. 沒有 CONTROL SERVER 權限的使用者將在此資料行中看到 NULL 值。A user that does not have CONTROL SERVER permission will see a NULL value in this column.

注意

內建系統程序和函數的 SQL 定義,可經由 sys.system_sql_modules 目錄檢視、 sp_helptext 預存程序和 OBJECT_DEFINITION() 函數公開檢視。The SQL definitions of built-in system procedures and functions are publicly visible through the sys.system_sql_modules catalog view, the sp_helptext stored procedure, and the OBJECT_DEFINITION() function.

中繼資料可見性的一般原則General Principles of Metadata Visibility

下列是幾個關於中繼資料可見性的一般考量原則:The following are some general principles to consider regarding metadata visibility:

  • 固定角色的隱含權限Fixed roles implicit permissions

  • 權限範圍Scope of permissions

  • DENY 的優先順序Precedence of DENY

  • 子元件中繼資料的可見性Visibility of subcomponent metadata

固定角色和隱含權限Fixed Roles and Implicit Permissions

固定角色可以存取的中繼資料取決於其對應的隱含權限。Metadata that can be accessed by fixed roles depends upon their corresponding implicit permissions.

權限範圍Scope of Permissions

在某個範圍的權限意味著,查看該範圍及所有包含範圍之中繼資料的能力。Permissions at one scope imply the ability to see metadata at that scope and at all enclosed scopes. 例如,結構描述的 SELECT 權限表示被授與者對該結構描述包含的所有安全性實體具有 SELECT 權限。For example, SELECT permission on a schema implies that the grantee has SELECT permission on all securables that are contained by that schema. 因此,授與結構描述的 SELECT 權限,可讓使用者查看結構描述的中繼資料,以及其中所有資料表、檢視、函數、程序、佇列、同義字、類型和 XML 結構描述集合。The granting of SELECT permission on a schema therefore enables a user to see the metadata of the schema and also all tables, views, functions, procedures, queues, synonyms, types, and XML schema collections within it. 如需範圍的詳細資訊,請參閱權限階層 (Database Engine)For more information about scopes, see Permissions Hierarchy (Database Engine).

DENY 的優先順序Precedence of DENY

DENY 的優先順序通常高於其他權限。DENY typically takes precedence over other permissions. 例如,如果授與資料庫使用者結構描述的 EXECUTE 權限,但是對該結構描述中某個預存程序的 EXECUTE 權限遭到拒絕,則使用者就不能檢視該預存程序的中繼資料。For example, if a database user is granted EXECUTE permission on a schema but has been denied EXECUTE permission on a stored procedure in that schema, the user cannot view the metadata for that stored procedure.

此外,如果某個使用者對結構描述的 EXECUTE 權限被拒,但是曾被授與該結構描述中某個預存程序的 EXECUTE 權限,則使用者還是不能檢視該預存程序的中繼資料。Additionally, if a user is denied EXECUTE permission on a schema but has been granted EXECUTE permission on a stored procedure in that schema, the user cannot view the metadata for that stored procedure.

以其他例子來說,如果授與使用者預存程序的 EXECUTE 權限而又被拒絕 (使用不同角色成員資格可能會造成這種情形),則會優先執行 DENY,而使用者將不能檢視該預存程序的中繼資料。For another example, if a user has been granted and denied EXECUTE permission on a stored procedure, which is possible through your various role memberships, DENY takes precedence and the user cannot view the metadata of the stored procedure.

子元件中繼資料的可見性Visibility of Subcomponent Metadata

子元件 (例如索引、檢查條件約束和觸發程序) 的可見性是由其父元件上的權限來決定。The visibility of subcomponents, such as indexes, check constraints, and triggers is determined by permissions on the parent. 這些子元件不具有可授與的權限。These subcomponents do not have grantable permissions. 例如,如果某個使用者被授與了某個資料表的部分權限,該使用者就能檢視資料表、資料行、索引、檢查條件約束、觸發程序和其他子元件的中繼資料。For example, if a user has been granted some permission on a table, the user can view the metadata for the tables, columns, indexes, check constraints, triggers, and other such subcomponents.

所有資料庫使用者都可以存取的中繼資料Metadata That Is Accessible to All Database Users

在特定資料庫中,某些中繼資料必須可由所有使用者存取。Some metadata must be accessible to all users in a specific database. 例如,檔案群組不具可授與的權限,所以使用者無法被授與權限來檢視檔案群組的中繼資料。For example, filegroups do not have conferrable permissions; therefore, a user cannot be granted permission to view the metadata of a filegroup. 但是,任何可以建立資料表的使用者,必定能夠存取檔案群組中繼資料,以使用 CREATE TABLE 陳述式中的 ON filegroup 或 TEXTIMAGE_ON filegroup 子句。However, any user that can create a table must be able to access filegroup metadata to use the ON filegroup or TEXTIMAGE_ON filegroup clauses of the CREATE TABLE statement.

DB_ID() 和 DB_NAME() 函數傳回的中繼資料,所有使用者都可以看到。The metadata that is returned by the DB_ID() and DB_NAME() functions is visible to all users.

下表列出 public 角色可以看見的目錄檢視。The following table lists the catalog views that are visible to the public role.

sys.partition_functionssys.partition_functions sys.partition_range_valuessys.partition_range_values
sys.partition_schemessys.partition_schemes sys.data_spacessys.data_spaces
sys.filegroupssys.filegroups sys.destination_data_spacessys.destination_data_spaces
sys.database_filessys.database_files sys.allocation_unitssys.allocation_units
sys.partitionssys.partitions sys.messagessys.messages
sys.schemassys.schemas sys.configurationssys.configurations
sys.sql_dependenciessys.sql_dependencies sys.type_assembly_usagessys.type_assembly_usages
sys.parameter_type_usagessys.parameter_type_usages sys.column_type_usagessys.column_type_usages

另請參閱See Also

GRANT (Transact-SQL) GRANT (Transact-SQL)
DENY (Transact-SQL) DENY (Transact-SQL)
REVOKE (Transact-SQL) REVOKE (Transact-SQL)
EXECUTE AS 子句 (Transact-SQL) EXECUTE AS Clause (Transact-SQL)
目錄檢視 (Transact-SQL) Catalog Views (Transact-SQL)
相容性檢視 (Transact-SQL)Compatibility Views (Transact-SQL)