元数据可见性配置Metadata Visibility Configuration

03/17/2017

适用对象：SQL Server Azure SQL 数据库 Azure Synapse Analytics (SQL DW) 并行数据仓库 APPLIES TO: yes SQL Server yes Azure SQL Database yes Azure Synapse Analytics (SQL DW) yes Parallel 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 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 system tables
数据库维护计划系统表Database maintenance plan system tables	备份系统表Backup system tables
复制系统表Replication system tables	复制及 SQL ServerSQL Server 代理 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 Engine sp_help 存储过程可能只返回行子集或 NULL。The 数据库引擎Database 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. 有关详细信息，请参阅数据库引擎服务启动选项和跟踪标志 (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.servers 的 provider_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. 有关范围的详细信息，请参阅权限层次结构（数据库引擎）。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