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

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

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

• 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

См. также

Задания

Основные понятия