Determinar los permisos efectivos del motor de base de datos

  • Artigo

Se aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsAnalytics Platform System (PDW)

En este artículo se describe cómo determinar quién tiene permisos para varios objetos en el motor de base de datos de SQL Server. SQL Server implementa dos sistemas de permisos para el motor de base de datos. Un sistema anterior de roles fijos tiene permisos preconfigurados. A partir de SQL Server 2005 (9.x) se dispone de un sistema más flexible y preciso.

Nota:

La información de este artículo se aplica a SQL Server 2005 (9.x) y versiones posteriores. Ciertos tipos de permisos no están disponibles en algunas versiones de SQL Server.

Siempre debe tener en cuenta los siguientes puntos:

  • Los permisos efectivos son la suma de ambos sistemas de permiso.
  • Una denegación de permisos invalida una concesión de permisos.
  • Si un usuario es miembro del rol fijo de servidor de administrador del sistema los permisos no se comprueban más, por lo que las denegaciones no se aplicarán.
  • El sistema antiguo y el nuevo sistema tienen similitudes. Por ejemplo, la pertenencia al rol fijo de servidor sysadmin es similar a tener el permiso CONTROL SERVER. Pero los sistemas no son idénticos. Por ejemplo, si un inicio de sesión solo tiene el permiso CONTROL SERVER y un procedimiento almacenado comprueba la pertenencia en el rol fijo de servidor sysadmin, se producirá un error en la comprobación del permiso. El proceso inverso también es cierto.

Resumen

  • El permiso de nivel de servidor puede proceder de la pertenencia a roles fijos de servidor o roles de servidor definidos por el usuario. Todos pertenecen al rol fijo de servidor public y reciben cualquier permiso asignado allí.
  • Los permisos de nivel de servidor pueden proceder de concesiones de permisos a inicios de sesión o roles de servidor definidos por el usuario.
  • Los permisos de nivel de base de datos pueden proceder de la pertenencia a los roles de base de datos fijos o a roles de base de datos definidos por el usuario en cada base de datos. Todos pertenecen al rol fijo de base de datos public y reciben cualquier permiso asignado allí.
  • Los permisos de nivel de base de datos pueden proceder de concesiones de permisos a usuarios o a roles de base de datos definidos por el usuario en cada base de datos.
  • Los permisos se pueden recibir desde el inicio de sesión guest o el usuario de base de datos guest si están habilitados. El inicio de sesión y los usuarios guest están deshabilitados de forma predeterminada.
  • Los usuarios de Windows pueden ser miembros de grupos de Windows que pueden tener inicios de sesión. SQL Server detecta la pertenencia a grupos de Windows cuando un usuario de Windows se conecta y presenta un token de Windows con el identificador de seguridad de un grupo de Windows. Como SQL Server no administra ni recibe actualizaciones automáticas sobre la pertenencia a grupos de Windows, SQL Server no puede informar de forma confiable de los permisos de usuarios de Windows que se reciben de la pertenencia a grupos de Windows.
  • Los permisos se pueden adquirir cambiando a un rol de aplicación y proporcionando la contraseña.
  • Los permisos se pueden adquirir mediante la ejecución de un procedimiento almacenado que incluya la cláusula EXECUTE AS.
  • Los permisos se pueden adquirir por los inicios de sesión o los usuarios con el permiso IMPERSONATE.
  • Los miembros del grupo de administradores del equipo local siempre pueden elevar sus privilegios a sysadmin. (No se aplica a la base de datos SQL).
  • Los miembros del rol fijo de servidor securityadmin pueden elevar muchos de sus privilegios y en algunos casos pueden elevar los privilegios a sysadmin. (No se aplica a la base de datos SQL).
  • Los administradores de SQL Server pueden ver información sobre todos los inicios de sesión y usuarios. Los usuarios con menos privilegios normalmente solo ven información sobre sus propias identidades.

Sistema anterior de permisos con rol fijos

Los roles fijos de servidor y los roles fijos de base de datos tienen permisos preconfigurados que no se pueden cambiar. Para determinar quién es miembro del rol fijo de servidor, ejecute la siguiente consulta:

Nota:

No se aplica a SQL Database ni a Azure Synapse Analytics, donde el permiso de nivel de servidor no está disponible. La columna is_fixed_role de sys.server_principals se agregó en SQL Server 2012 (11.x). No es necesaria para las versiones anteriores de SQL Server.

SELECT SP1.name AS ServerRoleName,
    ISNULL(SP2.name, 'No members') AS LoginName
FROM sys.server_role_members AS SRM
RIGHT JOIN sys.server_principals AS SP1
    ON SRM.role_principal_id = SP1.principal_id
LEFT JOIN sys.server_principals AS SP2
    ON SRM.member_principal_id = SP2.principal_id
WHERE SP1.is_fixed_role = 1 -- Remove for SQL Server 2008
ORDER BY SP1.name;

Nota:

Todos los inicios de sesión son miembros del rol público y no se pueden quitar. La consulta comprueba las tablas de la base de datos master, pero puede ejecutarse en cualquier base de datos del producto local.

Para determinar quién es miembro de un rol fijo de base de datos, ejecute la consulta siguiente en todas las bases de datos.

SELECT DP1.name AS DatabaseRoleName,
    ISNULL(DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT JOIN sys.database_principals AS DP1
    ON DRM.role_principal_id = DP1.principal_id
LEFT JOIN sys.database_principals AS DP2
    ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.is_fixed_role = 1
ORDER BY DP1.name;

Para conocer los permisos que se conceden a cada rol, consulte las descripciones de los roles en las ilustraciones de Books Online (roles a nivel de servidor y roles a nivel de base de datos).

Sistema de permisos granulares más reciente

Este sistema es flexible, lo que significa que puede ser complicado si los usuarios que lo configuran quieren que sea preciso. Para simplificar las cosas, ayuda a crear roles, asignar permisos a roles y, después, agregar los grupos de usuarios a los roles. Y es más fácil si el equipo de desarrollo de base de datos separa la actividad por esquemas y, después, concede permisos de rol a un esquema completo en lugar de a procedimientos o tablas individuales. Las situaciones reales son complejos y las necesidades empresariales pueden dar lugar a requisitos de seguridad inesperados.

La siguiente imagen muestra los permisos y sus relaciones entre sí. Algunos de los permisos de nivel superior (como CONTROL SERVER) se muestran varias veces. En este artículo, el póster es demasiado pequeño para leerlo. Puede descargar el Póster de permisos del motor de base de datos a tamaño completo en formato PDF.

A screenshot from the Database Engine permissions PDF.

Clases de seguridad

Los permisos pueden concederse a nivel de servidor, de base de datos, de esquema, de objeto, etc. Existen 26 niveles (denominados clases). La lista completa de clases en orden alfabético es: APPLICATION ROLE, ASSEMBLY, ASYMMETRIC KEY, AVAILABILITY GROUP, CERTIFICATE, CONTRACT, DATABASE, DATABASESCOPED CREDENTIAL, ENDPOINT, FULLTEXT CATALOG, FULLTEXT STOPLIST, LOGIN, MESSAGE TYPE, OBJECT, REMOTE SERVICE BINDING, ROLE, ROUTE, SCHEMA, SEARCH PROPERTY LIST, SERVER, SERVER ROLE, SERVICE, SYMMETRIC KEY, TYPE, USER, XML SCHEMA COLLECTION. (Algunas clases no están disponibles en algunos tipos de SQL Server.) Para proporcionar información completa sobre cada clase se requiere una consulta diferente.

Principals

Los permisos se conceden a entidades de seguridad. Las entidades de seguridad pueden ser roles de servidor, inicios de sesión, roles de base de datos o usuarios. Los inicios de sesión pueden representar grupos de Windows que incluyen muchos usuarios de Windows. Como SQL Server no mantiene los grupos de Windows, no siempre sabe quién es miembro de un grupo de Windows. Cuando un usuario de Windows se conecta a SQL Server, el paquete de inicio de sesión contiene los tokens de pertenencia a grupos de Windows para el usuario.

Cuando un usuario de Windows se conecta con un inicio de sesión basado en un grupo de Windows, es posible que algunas actividades requieran SQL Server para crear un inicio de sesión o un usuario para representar al usuario concreto de Windows. Por ejemplo, un grupo de Windows (Ingenieros) contiene usuarios (María, Luis, Juan) y el grupo Ingenieros tiene una cuenta de usuario de base de datos. Si María tiene permiso y crea una tabla, es posible que se cree un usuario (María) para que sea el propietario de la tabla. O bien, si se deniega a Luis un permiso que el resto del grupo Ingenieros tiene, entonces debe crearse el usuario Luis para realizar un seguimiento de la denegación del permiso.

Recuerde que un usuario de Windows puede ser miembro de más de un grupo de Windows (por ejemplo, tanto Ingenieros como Administradores). Los permisos concedidos o denegados al inicio de sesión de Ingenieros, al inicio de sesión de Administradores, concedidos o denegados al usuario de forma individual y concedidos o denegados a los roles de los que el usuario es miembro, se agregan y se evalúan para los permisos efectivos. La función HAS_PERMS_BY_NAME puede mostrar si un usuario o un inicio de sesión tiene un permiso concreto. Pero no hay ninguna manera obvia de determinar el origen de la concesión o denegación del permiso. Estudie la lista de permisos y, si procede, experimente mediante prueba y error.

Consultas útiles

Permisos de servidor

La consulta siguiente devuelve una lista de los permisos que se han concedido o denegado en el nivel de servidor. Esta consulta debe ejecutarse en la base de datos master.

Nota:

Los permisos de nivel de servidor se no se pueden conceder ni consultar en la base de datos SQL o Azure Synapse Analytics.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.server_principals AS pr
LEFT JOIN sys.server_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE is_fixed_role = 0 -- Remove for SQL Server 2008
ORDER BY pr.name,
    type_desc;

Permisos de base de datos

La consulta siguiente devuelve una lista de los permisos que se han concedido o denegado en el nivel de base de datos. Esta consulta debe ejecutarse en todas las bases de datos.

SELECT pr.type_desc,
    pr.name,
    ISNULL(pe.state_desc, 'No permission statements') AS state_desc,
    ISNULL(pe.permission_name, 'No permission statements') AS permission_name
FROM sys.database_principals AS pr
LEFT JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
WHERE pr.is_fixed_role = 0
ORDER BY pr.name,
    type_desc;

Cada clase de permiso en la tabla de permisos puede combinarse con otras vistas del sistema que proporcionan información relacionada con esa clase de elemento protegible. Por ejemplo, la consulta siguiente proporciona el nombre del objeto de base de datos que se ve afectado por el permiso.

SELECT pr.type_desc,
    pr.name,
    pe.state_desc,
    pe.permission_name,
    s.name + '.' + oj.name AS OBJECT,
    major_id
FROM sys.database_principals AS pr
INNER JOIN sys.database_permissions AS pe
    ON pr.principal_id = pe.grantee_principal_id
INNER JOIN sys.objects AS oj
    ON oj.object_id = pe.major_id
INNER JOIN sys.schemas AS s
    ON oj.schema_id = s.schema_id
WHERE class_desc = 'OBJECT_OR_COLUMN';

Use la función HAS_PERMS_BY_NAME para determinar si un usuario determinado (en este caso TestUser) tiene un permiso. Por ejemplo:

EXECUTE AS USER = 'TestUser';
SELECT HAS_PERMS_BY_NAME ('dbo.T1', 'OBJECT', 'SELECT');
REVERT;

Para obtener detalles de la sintaxis, vea HAS_PERMS_BY_NAME.

Pasos siguientes