Solucionar problemas de visibilidad de los metadatos
Use este tema para solucionar los problemas que surjan al ver los metadatos.
Un usuario sólo puede ver los metadatos que posea o para los que se le haya concedido algún permiso. Esta directiva impide que los usuarios con privilegios mínimos puedan ver los metadatos de todos los objetos de una instancia de SQL Server. Para obtener más información acerca de la visibilidad de los metadatos, vea Configuración de visibilidad de los metadatos.
Para permitir a los usuarios ver los metadatos
Para que los usuarios con privilegios mínimos puedan ver todos los metadatos, ejecute una de las instrucciones siguientes:
GRANT VIEW ANY DEFINITION TO public;
Esta instrucción anulará las limitaciones de visibilidad de los metadatos en las instancias. Todos los metadatos de la instancia serán visibles en public.
GRANT VIEW DEFINITION TO public;
Esta instrucción anulará las limitaciones de visibilidad de los metadatos en las bases de datos. Todos los metadatos de la base de datos serán visibles en public.
GRANT VIEW DEFINITION ON SCHEMA :: <schema_name> TO public;
Esta instrucción anulará las limitaciones de visibilidad de los metadatos en los esquemas. Todos los metadatos del esquema serán visibles en public.
GRANT VIEW DEFINITION ON OBJECT :: <object_name> TO public;
Esta instrucción anulará las limitaciones de visibilidad de los metadatos en los objetos. Todos los metadatos del objeto serán visibles en public. Si el objeto es una tabla, todas las columnas, índices, estadísticas y limitaciones de la tabla serán visibles en public. Este comportamiento también se aplica a GRANT VIEW DEFINITION ON ASSEMBLY y otras instrucciones GRANT similares.
Para que un usuario o una función concretos con privilegios mínimos puedan ver todos los metadatos, utilice un nombre de usuario o función específico como receptor en lugar de public.
Para que los usuarios puedan verse entre sí
De forma predeterminada, los usuarios con privilegios mínimos no pueden ver a los demás usuarios de las vistas de catálogo sys.database_principals y sys.server_principals. Esto significa que un usuario con privilegios mínimos que sea propietario de una tabla no puede ver a otros usuarios a los que quizás desee conceder permisos. Para que el usuario con privilegios mínimos user_X pueda ver a otro usuario, user_Y, puede emitir la siguiente instrucción GRANT:
- GRANT VIEW DEFINITION ON USER :: <user_Y> TO <user_X>
Deberá ejecutar la instrucción para cada usuario. Puede automatizar el proceso creando un desencadenador DDL similar al siguiente:
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 las funciones de aplicación vean metadatos del servidor
Una función de aplicación no puede tener acceso a los metadatos que se encuentran fuera de su propia base de datos porque las funciones de aplicación no están asociadas a una entidad de seguridad de servidor. Para que las funciones de aplicación vean los metadatos del servidor se pueden usar los métodos siguientes.
Establecer una marca de traza
Para permitir que las funciones de aplicación tengan acceso a los metadatos de nivel de servidor, establezca el marcador global 4616. Para obtener más información acerca de cómo establecer las marcas de seguimiento, vea DBCC TRACEON (Transact-SQL). Para obtener información acerca de la marca de seguimiento 4616, vea Marcadores de seguimiento (Transact-SQL).
Usar un procedimiento almacenado firmado con certificado
Se recomienda utilizar procedimientos almacenados firmados con certificado para tener acceso a las tablas del sistema de los servidores. Los procedimientos firmados con certificado ofrecen las ventajas siguientes:
No tiene que usar una marca de traza.
Puede revelarse menos información de los servidores. Las aplicaciones basadas en funciones de aplicación deben usar procedimientos almacenados en lugar de consultas generales. Es más probable que los procedimientos almacenados devuelvan únicamente los datos concretos que requiere la aplicación.
En el ejemplo siguiente se crea un procedimiento almacenado firmado con certificado y se demuestra cómo puede usarlo una función de aplicación para ver metadatos de servidores.
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