Dépannage de la visibilité des métadonnées
Utilisez cette rubrique pour résoudre les problèmes liés à l'affichage des métadonnées.
Un utilisateur peut voir uniquement les métadonnées qui lui appartiennent ou sur lesquelles il dispose de certaines autorisations. Cette stratégie empêche les utilisateurs dotés de droits minimaux d'afficher les métadonnées de tous les objets dans une instance de SQL Server. Pour plus d'informations sur la visibilité des métadonnées, consultez Configuration de la visibilité des métadonnées.
Pour permettre aux utilisateurs d'afficher les métadonnées
Pour permettre aux utilisateurs dotés de droits minimaux de voir toutes les métadonnées, exécutez l'une des instructions suivantes :
GRANT VIEW ANY DEFINITION TO public;
Cette instruction annulera les limitations de visibilité des métadonnées à l'échelle de l'instance. Toutes les métadonnées de l'instance seront visibles par le rôle public.
GRANT VIEW DEFINITION TO public;
Cette instruction annulera les limitations de visibilité des métadonnées à l'échelle de la base de données. Toutes les métadonnées de la base de données seront visibles par le rôle public.
GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;
Cette instruction annulera les limitations de visibilité des métadonnées à l'échelle du schéma. Toutes les métadonnées du schéma seront visibles par le rôle public.
GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;
Cette instruction annulera les limitations de visibilité des métadonnées à l'échelle de l'objet. Toutes les métadonnées de l'objet seront visibles par le rôle public. Si l'objet est une table, l'ensemble des colonnes, index, statistiques et contraintes de la table seront visibles par le rôle public. Ce comportement s'applique également à l'instruction GRANT VIEW DEFINITION ON ASSEMBLY et aux instructions GRANT similaires.
Pour permettre à un utilisateur spécifique qui dispose de droits minimaux de voir toutes les métadonnées, accordez l'autorisation requise à un nom d'utilisateur ou à un nom de rôle spécifique au lieu de l'accorder au rôle public.
Pour permettre aux utilisateurs de voir les autres utilisateurs
Par défaut, les utilisateurs dotés de droits minimaux ne peuvent pas voir les autres utilisateurs dans les affichages catalogue sys.database_principals et sys.server_principals. Ceci signifie qu'un utilisateur doté de droits minimaux qui possède une table ne peut pas voir les autres utilisateurs auxquels il pourrait vouloir accorder des autorisations. Pour permettre à l'utilisateur user_X doté de droits minimaux de voir l'utilisateur user_Y, vous pouvez exécuter l'instruction GRANT suivante :
- GRANT VIEW DEFINITION ON USER :: <user_Y> TO <user_X>
Vous devez exécuter cette instruction pour chaque utilisateur. Vous pouvez automatiser le processus en créant un déclencheur DDL similaire à celui-ci :
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
Pour permettre aux rôles d'application de voir les métadonnées au niveau serveur
Un rôle d'application ne peut pas accéder à des métadonnées situées en dehors de sa propre base de données car les rôles d'application ne sont pas associés à une entité de sécurité au niveau serveur. Les méthodes suivantes peuvent être utilisées pour permettre aux rôles d'application de voir les métadonnées de niveau serveur.
Définir un indicateur de trace
Pour permettre aux rôles d'application d'accéder aux métadonnées au niveau du serveur, définissez l'indicateur global 4616. Pour plus d'informations sur la définition d'indicateurs de trace, consultez DBCC TRACEON (Transact-SQL). Pour plus d'informations sur l'indicateur de trace 4616, consultez Indicateurs de trace (Transact-SQL).
Utiliser une procédure stockée signée par un certificat
Nous vous recommandons d'utiliser des procédures signées par un certificat pour accéder aux tables système de niveau serveur. Les procédures signées par un certificat offrent les avantages suivants :
Vous n'êtes pas obligé d'utiliser un indicateur de trace.
La quantité d'informations de niveau serveur pouvant être divulguées est limitée. Les applications basées sur les rôles d'application doivent utiliser des procédures stockées au lieu de requêtes générales. Les procédures stockées sont plus susceptibles de renvoyer uniquement les données spécifiques requises par l'application.
L'exemple suivant crée une procédure stockée signée par un certificat et montre comment un rôle d'application peut utiliser la procédure pour afficher les métadonnées de niveau serveur.
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.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