Solucionando problemas de visibilidade de metadados

Para permitir que usuários exibam metadados

Para permitir que usuários com privilégios mínimos vejam todos os metadados, execute uma das instruções seguintes:

• GRANT VIEW ANY DEFINITION TO public;

  Esta instrução substituirá as limitações de visibilidade de metadados em nível de instância. Todos os metadados na instância serão visíveis ao público.

• GRANT VIEW DEFINITION TO public;

  Esta instrução substituirá as limitações de visibilidade de metadados em nível de banco de dados. Todos os metadados no banco de dados serão visíveis ao público.

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

  Esta instrução substituirá as limitações de visibilidade de metadados em nível de esquema. Todos os metadados no esquema serão visíveis ao público.

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

  Esta instrução substituirá as limitações de visibilidade de metadados em nível de objeto. Todos os metadados para o objeto serão visíveis ao público. Se o objeto for uma tabela, todas as colunas, índices, estatísticas e restrições da tabela serão visíveis ao público. Este comportamento também se aplica a GRANT VIEW DEFINITION ON ASSEMBLY e outras instruções GRANT semelhantes.

Para permitir que um usuário específico com privilégios mínimos ou uma função veja todos os metadados, use um usuário específico ou nome de função como usuário autorizado em vez de público.

Para permitir que usuários vejam um ao outro

Por padrão, usuários que têm privilégios mínimos não podem ver outros usuários nas exibições do catálogo sys.database_principals e sys.server_principals. Isto significa que um usuário com privilégios mínimos que possui uma tabela não pode ver outros usuários para quem ele desejaria conceder permissões. Para permitir que usuário user_X com privilégios mínimos veja outro usuário, user_Y, você pode emitir a instrução GRANT seguinte:

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

Você terá que executar esta instrução para cada usuário. Você pode automatizar o processo criando um gatilho DDL semelhante para:

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

Para permitir que funções de aplicativo vejam metadados de nível de servidor

Uma função de aplicativo não pode acessar metadados fora de seu próprio banco de dados porque funções de aplicativo não são associadas com uma entidade de nível de servidor. Os métodos a seguir podem ser usados para permitir que funções de aplicativo vejam metadados de nível de servidor.

Definir um sinalizador de rastreamento

Para permitir que funções de aplicativo acessem metadados em nível de servidor, defina sinalizador global 4616. Para obter informações sobre a configuração de sinalizadores de rastreamento, consulte DBCC TRACEON (Transact-SQL). Para obter informações sobre sinalizadores de rastreamento 4616, consulte Sinalizadores de rastreamento (Transact-SQL).

Use um procedimento armazenado assinado por certificado

É recomendável usar procedimentos assinados por certificado para acessar tabelas do sistema no nível de servidor. Procedimentos assinados por certificado oferecem os seguintes benefícios:

• Você não precisa usar um sinalizador de rastreamento.

• Poucas informações do nível de servidor podem ser divulgadas. Aplicativos com base em função de aplicativo devem usar procedimentos armazenados em vez de consultas gerais. Procedimentos armazenados são mais prováveis de retornar somente os dados específicos que são requeridos pelo aplicativo.

• O exemplo a seguir cria um procedimento armazenado assinado por certificado e demonstra como uma função de aplicativo pode usar o procedimento para exibir metadados do nível de servidor.

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

Consulte também

Tarefas

Conceitos