• Статья

Устранение неполадок, связанных с видимостью метаданных

В этом разделе приведена информация об устранении проблем при просмотре метаданных.

Пользователь может просматривать только те метаданные, которыми он владеет или на которые ему были предоставлены разрешения. Эта политика предотвращает возможность просмотра метаданных всех объектов экземпляра SQL Server пользователями с низкими правами доступа. Дополнительные сведения о видимости метаданных см. в разделе Настройка видимости метаданных.

Разрешение пользователям просматривать метаданные

Чтобы разрешить пользователям с низкими правами доступа просмотр всех метаданных, выполните одну из следующих инструкций.

  • GRANT VIEW ANY DEFINITION TO public;

    Эта инструкция снимает все ограничения на видимость метаданных на уровне экземпляра. Все метаданные экземпляра будут видимы для группы public.

  • GRANT VIEW DEFINITION TO public;

    Эта инструкция снимает все ограничения на видимость метаданных на уровне базы данных. Все метаданные базы данных будут видимы для группы public.

  • GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;

    Эта инструкция снимает все ограничения на видимость метаданных на уровне схемы. Все метаданные схемы будут видимы для группы public.

  • GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;

    Эта инструкция снимает все ограничения на видимость метаданных на уровне объекта. Все метаданные данного объекта будут видимы для группы public. Если этот объект является таблицей, то все столбцы, индексы, статистики и ограничения этой таблицы будут видимы для группы public. Такое поведение также относится к инструкции GRANT VIEW DEFINITION ON ASSEMBLY и другим аналогичным инструкциям GRANT.

Чтобы все метаданные могли быть просмотрены определенным пользователем или ролью с низкими правами доступа, укажите имя пользователя или роли в качестве получателя разрешений вместо группы public.

Разрешение пользователям видеть друг друга

По умолчанию пользователи с низкими правами доступа не могут видеть других пользователей в представлениях каталога sys.database_principals и sys.server_principals. Это означает, что пользователь с низкими правами доступа, которому принадлежит некоторая таблица, не может видеть других пользователей, которым он может захотеть предоставить разрешения. Чтобы пользователь user_X с низкими правами доступа мог видеть другого пользователя user_Y, можно использовать следующую инструкцию GRANT.

  • GRANT VIEW DEFINITION ON USER :: <user_Y> TO <user_X>

Необходимо выполнить эту инструкцию для каждого пользователя. Можно автоматизировать этот процесс, создав примерно такой триггер DDL.

CREATE TRIGGER grant_view_definition_on_principal ON DATABASE
FOR CREATE_USER, CREATE_ROLE
AS
    DECLARE @event_type sysname, @principal_name sysname, @sql nvarchar(max);
    SELECT @event_type     = eventdata().value('(/EVENT_INSTANCE/EventType) [1]','sysname');
    SELECT @principal_name = eventdata().value('(/EVENT_INSTANCE/ObjectName)[1]','sysname');
    IF (@event_type = 'CREATE_USER')
        SELECT @sql = 'GRANT VIEW DEFINITION ON USER :: ' + @principal_name + ' TO PUBLIC ' ;
    ELSE
        SELECT @sql = 'GRANT VIEW DEFINITION ON ROLE :: ' + @principal_name + ' TO PUBLIC ' ;
    EXEC (@sql) ;
GO

Разрешение ролям приложений видеть метаданные уровня сервера

Роль приложения не связана с участником на уровне сервера данных, поэтому она не может обращаться к метаданным за пределами своей собственной базы данных. Чтобы разрешить ролям приложений видеть метаданные уровня сервера, можно использовать следующие методы.

Установка флага трассировки

Чтобы разрешить ролям приложений обращаться к метаданным уровня сервера, установите глобальный флаг 4616. Дополнительные сведения об установке флагов трассировки см. в разделе DBCC TRACEON (Transact-SQL). Дополнительные сведения о флаге трассировки 4616 см. в разделе Флаги трассировки (Transact-SQL).

Использование хранимой процедуры, подписанной сертификатом

Для доступа к системным таблицам уровня сервера рекомендуется использовать хранимые процедуры, подписанные сертификатом. Процедуры, подписанные сертификатом, дают следующие преимущества.

  • Не нужно использовать флаг трассировки.

  • Разглашается меньше сведений уровня сервера. Приложения, основанные на ролях приложений, вместо общих запросов должны использовать хранимые процедуры. Хранимые процедуры гораздо вероятнее возвратят только те определенные данные, которые нужны приложению.

  • В следующем примере создается хранимая процедура, подписанная сертификатом, и демонстрируется, как роль приложения использует эту процедуру для просмотра метаданных уровня сервера.

USE master;
GO 
CREATE DATABASE approle_db; 
GO 
CREATE LOGIN some_login WITH PASSWORD = '<enterStrongPasswordHere>'; 
GO 
USE approle_db; 
GO 
CREATE USER some_user FOR LOGIN some_login; 
GO
EXEC sp_addapprole 'an_approle', '<enterStrongPasswordHere>'; 
GO
--------------------------------------------------------------------- 
-- This section shows how to use a certificate to authenticate 
-- a signed procedure.
--------------------------------------------------------------------- 
CREATE LOGIN execute_as_login WITH PASSWORD = '<enterStrongPasswordHere>'; 
GO 
USE master; 
GO 
GRANT VIEW ANY DEFINITION TO execute_as_login; 
GRANT VIEW SERVER STATE TO execute_as_login; 
GO 
USE approle_db;
GO 
CREATE USER execute_as_user FOR LOGIN execute_as_login; 
GO 
--
-- You must use EXECUTE AS 'authenticator' here because the application role 
-- does not have a server identity. Therefore, the application role cannot use 
-- the certificate permissions on the server. Therefore, you 
-- need a new execution context to which you can grant 
-- the needed VIEW* permissions. 
-- 
CREATE PROC access_server_system_tables 
    WITH EXECUTE AS 'execute_as_user' 
    AS 
    SELECT sid, status, name, dbname, hasaccess, loginname 
        FROM master.dbo.syslogins; 
    SELECT spid, kpid, lastwaittype, waitresource, dbid 
        FROM master.dbo.sysprocesses; 
GO 
GRANT EXECUTE ON access_server_system_tables TO an_approle; 
GO 
CREATE CERTIFICATE signing_cert 
    ENCRYPTION BY PASSWORD = '<enterStrongPasswordHere>' 
    WITH SUBJECT = 'Signing Cert'; 
GO 
BACKUP CERTIFICATE signing_cert TO FILE = 'signing_cert.cer'; 
GO 
ADD SIGNATURE TO access_server_system_tables
    BY CERTIFICATE signing_cert WITH PASSWORD = '<enterStrongPasswordHere>';
GO
--------------------------------------------------------------------- 
-- Create a copy of the signing certificate in the target 
-- database. In this case, the target database is the master database. 
-- This copy of the signing certificate vouches for the execution context
-- that enters this database from the signed procedure. 
--------------------------------------------------------------------- 
USE master; 
GO 
CREATE CERTIFICATE signing_cert FROM FILE = 'signing_cert.cer'; 
GO 
--------------------------------------------------------------------- 
-- Because the VIEW permissions in question are server-level permissions,
-- we need to grant AUTHENTICATE SERVER permission on a login-mapped certificate. 
--------------------------------------------------------------------- 

CREATE LOGIN signing_cert_login FROM CERTIFICATE signing_cert; 
GO 
GRANT AUTHENTICATE SERVER TO signing_cert_login 
GO 
--------------------------------------------------------------------- 
-- Now you can open a new connection as "some_login" and 
-- set the application role. Then, call the "access_server_system_tables"
-- procedure, and obtain verification that you can access server-level information 
-- when the application role-based application runs. 
-- For an example, see the Demo usage.sql code below.
--------------------------------------------------------------------- 

--------------------------------------------------------------------- 
-- Clean up. 
-- The following statements remove the objects created above.
--------------------------------------------------------------------- 
USE master 
GO 
DROP DATABASE approle_db; 

DROP LOGIN some_login; 
GO 
DROP LOGIN execute_as_login; 
GO 
DROP LOGIN signing_cert_login; 
GO 
DROP CERTIFICATE signing_cert; 
GO 
-- 
-- Delete the certificate file. 
-- 
EXEC sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
EXEC sp_configure 'xp_cmdshell', 1; 
GO 
RECONFIGURE; 
GO 
EXEC xp_cmdshell 'del "C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\signing_cert.cer"'; 
GO 
EXEC sp_configure 'xp_cmdshell', 0; 
GO 
RECONFIGURE; 
GO 

-- ============================================================================
-- - Application role access to server information - Demo usage.sql
--
--  This code is companion code that shows an example of application role access
--  to server information by using a certificate-signed procedure.
--
-- ============================================================================
--  -------------------------------------------------- 
-- Connect as some_login first.
-- ------------------------------------------------ 
USE approle_db;
GO
EXEC sp_setapprole 'an_approle', '<enterStrongPasswordHere>';
GO
-- Display the server-level information the application role can currently view. 
SELECT sid, status, name, dbname, hasaccess, loginname 
FROM master.dbo.syslogins; 
SELECT spid, kpid, lastwaittype, waitresource, dbid 
FROM master.dbo.sysprocesses; 
GO 
-- Display the server-level information the application role
-- can view by running the certificate-signed stored procedure.
EXEC access_server_system_tables;
GO