Introducción a los permisos de motor de base de datos

Artículo
10/29/2023

Se aplica a:SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Los permisos en el motor de base de datos se administran a nivel de servidor a través de inicios de sesión y roles de servidor, y a nivel de base de datos a través de usuarios de base de datos y roles de base de datos. El modelo para SQL Database expone el mismo sistema dentro de cada base de datos, pero los permisos a nivel de servidor no están disponibles. En este artículo se repasan algunos conceptos básicos de seguridad y, a continuación, se describe una implementación típica de los permisos.

Nota:

Microsoft Entra ID conocido anteriormente como Azure Active Directory (Azure AD).

Entidades de seguridad

Las entidades de seguridad son el nombre oficial de las identidades que utilizan SQL Server y a las que se puede asignar permiso para realizar acciones. Suelen ser personas o grupos de personas, pero pueden ser otras entidades que finjan ser personas. Las entidades de seguridad se pueden crear y administrar mediante la lista Transact-SQL o mediante SQL Server Management Studio.

Inicios de sesión

Los inicios de sesión son cuentas de usuario individuales para iniciar sesión en el motor de base de datos de SQL Server. SQL Server y SQL Database admiten inicios de sesión basados en la autenticación de Windows e inicios de sesión basados en la autenticación de SQL Server. Para obtener más información acerca de los dos tipos de inicio de sesión, consulte Choose an Authentication Mode.

Roles fijos de servidor

En SQL Server, los roles de fijos de servidor son un conjunto de roles preconfigurados que proporcionan un grupo conveniente de permisos a nivel de servidor. Se pueden agregar inicios de sesión a los roles mediante la instrucción ALTER SERVER ROLE ... ADD MEMBER . Para más información, vea ALTER SERVER ROLE (Transact-SQL). SQL Database no admite los roles de servidor fijos, pero tiene dos roles en la base de datos master (dbmanager y loginmanager) que actúan como roles del servidor.

Roles de servidor definidos por el usuario

En SQL Server, puede crear sus propios roles del servidor y asignarles permisos a nivel de servidor. Se pueden agregar inicios de sesión al servidor mediante la instrucción ALTER SERVER ROLE ... ADD MEMBER . Para más información, vea ALTER SERVER ROLE (Transact-SQL). SQL Database no admite los roles de servidor definidos por el usuario.

Usuarios de base de datos

Los inicios de sesión conceden acceso a una base de datos mediante la creación de un usuario de base de datos en una base de datos y la asignación de ese usuario de base de datos para iniciar sesión. Por lo general, el nombre de usuario de la base de datos es el mismo que el nombre de inicio de sesión, aunque no tiene por qué ser el mismo. Cada usuario de base de datos se asigna a un inicio de sesión único. Un inicio de sesión se puede asignar a un único usuario en una base de datos, pero se puede asignar como usuario de base de datos en distintas bases de datos.

También se pueden crear usuarios de base de datos que no tengan un nombre de usuario correspondiente. Estos usuarios se denominan usuarios de bases de datos independientes. Microsoft recomienda el uso de usuarios de bases de datos independientes porque facilita mover la base de datos a otro servidor. Al igual que un inicio de sesión, un usuario de base de datos independiente puede usar la autenticación de Windows o la de SQL Server. Para obtener más información, vea Usuarios de base de datos independiente - Conversión de la base de datos en portátil.

Hay 12 tipos de usuarios con ligeras diferencias en cuanto a cómo se autentican y a quién representan. Para ver una lista de usuarios, consulte CREATE USER(Transact-SQL).

Roles fijos de base de datos

Los roles fijos de base de datos son un conjunto de roles preconfigurados que proporcionan un práctico grupo de permisos de nivel de base de datos. Se pueden agregar usuarios de base de datos y roles de base de datos definidos por el usuario a los roles fijos de base de datos mediante la instrucción ALTER ROLE ... ADD MEMBER. Para obtener más información, vea ALTER ROLE (Transact-SQL).

Roles de base de datos definidos por el usuario

Los usuarios con el permiso CREATE ROLE pueden crear nuevos roles de base de datos definidos por el usuario para representar grupos de usuarios con permisos comunes. Normalmente, los permisos se conceden o deniegan a todo el rol, lo que simplifica la administración y supervisión de permisos. Se pueden agregar usuarios de base de datos a los roles de base de datos mediante la instrucción ALTER ROLE ... ADD MEMBER . Para obtener más información, vea ALTER ROLE (Transact-SQL).

Otras entidades de seguridad

Otras entidades de seguridad que no se tratan aquí son los roles de aplicación y los inicios de sesión y usuarios basados en certificados o claves asimétricas.

Para ver un gráfico que muestra las relaciones entre usuarios de Windows, grupos de Windows, inicios de sesión y usuarios de base de datos, consulte Create a Database User.

Escenario típico

En el ejemplo siguiente se muestra un método común y recomendado para la configuración de permisos.

En Windows Active Directory o Microsoft Entra ID

Cree un usuario para cada persona.
Cree grupos de Windows que representen las unidades y las funciones de trabajo.
Agregue los usuarios de Windows a los grupos de Windows.

Si la persona que se conecta lo va a hacer a diversas bases de datos

Cree un inicio de sesión para los grupos de Windows. (Si usa autenticación de SQL Server, omita los pasos de Active Directory y cree aquí los inicios de sesión de autenticación de SQL Server).
En la base de datos de usuario, cree un usuario de base de datos para el inicio de sesión que representa los grupos de Windows.
En la base de datos de usuario, cree uno o varios roles de base de datos definidos por el usuario, cada uno para representar una función similar. Por ejemplo, analista financiero y analista de ventas.
Agregue los usuarios de base de datos a uno o varios roles de base de datos definidos por el usuario.
Conceda permisos a los roles de base de datos definidos por el usuario.

Si la persona que se conecta lo va a hacer a una única base de datos

En la base de datos de usuario, cree un usuario de base de datos independiente para el grupo de Windows. (Si usa autenticación de SQL Server, omita los pasos de Active Directory y cree aquí la autenticación de SQL Server del usuario de la base de datos independiente.
En la base de datos de usuario, cree uno o varios roles de base de datos definidos por el usuario, cada uno para representar una función similar. Por ejemplo, analista financiero y analista de ventas.
Agregue los usuarios de base de datos a uno o varios roles de base de datos definidos por el usuario.
Conceda permisos a los roles de base de datos definidos por el usuario.

Normalmente, el resultado en este punto es que un usuario de Windows es miembro de un grupo de Windows. El grupo Windows tiene un inicio de sesión en SQL Server o SQL Database. El inicio de sesión se asigna a una identidad de usuario en la base de datos de usuario. El usuario es miembro de un rol de base de datos. Ahora debe agregar permisos al rol.

Asignar permisos

El formato de la mayoría de instrucciones de permiso es:

AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;

AUTHORIZATION debe ser GRANT, REVOKE o DENY.
PERMISSION establece las acciones permitidas o prohibidas. El número exacto de permisos difiere entre SQL Server y SQL Database. Los permisos se enumeran en el artículo Permisos (motor de base de datos) y en el gráfico al que se hace referencia a continuación.
ON SECURABLE::NAME es el tipo de elemento protegible (servidor, objeto de servidor, base de datos u objeto de base de datos) y su nombre. Algunos permisos no requieren ON SECURABLE::NAME porque este es inequívoco o bien no procede en el contexto. Por ejemplo, el permiso CREATE TABLE no requiere la cláusula ON SECURABLE::NAME (GRANT CREATE TABLE TO Mary; permite a Mary crear tablas).
PRINCIPAL es la entidad de seguridad (inicio de sesión, usuario o rol) que recibe o pierde el permiso. Conceda permisos a los roles siempre que sea posible.

La siguiente instrucción de concesión de ejemplo concede el permiso UPDATE en la tabla o vista Parts contenida en el esquema Production al nombre de rol PartsTeam:

GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam;

La siguiente instrucción de concesión de ejemplo concede el permiso UPDATE en el esquema Production y, por extensión, en cualquier tabla o vista contenida en este esquema al nombre de rol ProductionTeam, que es un enfoque más eficaz y comercial para asignar permisos que a nivel de objeto individual:

GRANT UPDATE ON SCHEMA::Production TO ProductionTeam;

Los permisos se conceden a las entidades de seguridad (inicios de sesión, usuarios y roles) mediante la instrucción GRANT . Los permisos se deniegan explícitamente mediante el comando DENY . Para eliminar un permiso concedido o denegado anteriormente, se usa la instrucción REVOKE . Los permisos son acumulativos, de modo que el usuario recibe todos los permisos concedidos al usuario, al inicio de sesión y a cualquier pertenencia a algún grupo. Sin embargo, las denegaciones de permisos invalidan todas las concesiones.

Sugerencia

Un error común es intentar eliminar un elemento GRANT mediante DENY en lugar de REVOKE. Esto puede causar problemas cuando un usuario recibe permisos de varios orígenes, lo que es bastante común. En el ejemplo siguiente se muestra la entidad de seguridad.

El grupo Ventas recibe permisos SELECT en la tabla OrderStatus mediante la instrucción GRANT SELECT ON OBJECT::OrderStatus TO Sales;. El usuario Jae es miembro del rol Ventas. A Jae también se le concedió permiso SELECT a la tabla OrderStatus bajo su propio nombre de usuario mediante la instrucción GRANT SELECT ON OBJECT::OrderStatus TO Jae;. Supongamos que el administrador desea quitar el elemento GRANT al rol Ventas.

Si el administrador ejecuta correctamente REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, Jae conservará el acceso SELECT a la tabla OrderStatus a través de la instrucción GRANT individual.
Si el administrador ejecuta incorrectamente DENY SELECT ON OBJECT::OrderStatus TO Sales; se denegará el permiso SELECT a Jae, como miembro del rol de Ventas, porque DENY a Ventas anula su GRANT individual.

Nota:

Los permisos se pueden configurar mediante Management Studio. Encuentre el elemento protegible en el Explorador de objetos, haga clic con el botón derecho del ratón en él y seleccione Propiedades. Seleccione la página Permisos . Para obtener ayuda sobre el uso de la página de permisos, consulte Permissions or Securables Page.

Jerarquía de permiso

Los permisos tienen una jerarquía de elementos principales y secundarios. Es decir, si se concede el permiso SELECT en una base de datos, dicho permiso incluirá el permiso SELECT en todos los esquemas (secundarios) de la base de datos. Si se concede el permiso SELECT en un esquema, incluirá el permiso SELECT en todas las tablas y vistas (secundarias) del esquema. Los permisos son transitivos, es decir, si se concede el permiso SELECT en una base de datos, incluirá el permiso SELECT en todos los esquemas (secundarios) y todas las tablas y vistas (descendientes de elementos secundarios).

Los permisos también tienen permisos de cobertura. El permiso CONTROL en un objeto normalmente concede todos los otros permisos del objeto.

Puesto que tanto la jerarquía de elementos principales y secundarios como la jerarquía de cobertura pueden actuar en el mismo permiso, el sistema de permisos puede resultar complicado. Por ejemplo, consideremos una tabla (Región) de en un esquema (Clientes) en una base de datos (SalesDB).

CONTROL en la tabla Región incluye todos los otros permisos de la tabla Región, incluidos los permisos ALTER, SELECT, INSERT, UPDATE, DELETEy algunos otros permisos.
SELECT en el esquema Clientes que posee la tabla Región incluye el permiso SELECT en la tabla Región.

Así que el permiso SELECT en la tabla Región se puede obtener mediante cualquiera de estas seis instrucciones:

GRANT SELECT ON OBJECT::Region TO Jae;

GRANT CONTROL ON OBJECT::Region TO Jae;

GRANT SELECT ON SCHEMA::Customers TO Jae;

GRANT CONTROL ON SCHEMA::Customers TO Jae;

GRANT SELECT ON DATABASE::SalesDB TO Jae;

GRANT CONTROL ON DATABASE::SalesDB TO Jae;

Conceder el permiso mínimo

El primer permiso mencionado antes (GRANT SELECT ON OBJECT::Region TO Jae;) es el más pormenorizado, es decir, esa instrucción es el permiso mínimo posible que concede SELECT. No incluye permisos para los objetos subordinados. Es un buen principio conceder siempre el permiso mínimo posible (puede leer más sobre el principio de privilegios mínimos), pero al mismo tiempo (lo que supone una contradicción) intente realizar la concesión en niveles superiores para simplificar el sistema de concesión. Por tanto, si Jae necesita permisos para todo el esquema, conceda SELECT una vez en el nivel de esquema, en lugar de conceder SELECT en el nivel de tabla o vista varias veces. El diseño de la base de datos puede afectar drásticamente la eficacia de esta estrategia. Esta estrategia funcionará mejor si la base de datos está diseñada de forma que los objetos que necesiten permisos idénticos se incluyan en un único esquema.

Sugerencia

Al diseñar una base de datos y sus objetos, planee desde el principio quién o qué aplicaciones accederán a qué objetos y, en función de ello, coloque los objetos, es decir, las tablas, pero también las vistas, las funciones y los procedimientos almacenados, en los esquemas según los cubos de tipo de acceso, en la medida de lo posible.

Diagrama de permisos

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.

Para ver un gráfico que muestra las relaciones entre las entidades de seguridad del motor de base de datos y los objetos del servidor y de la base de datos, vea Jerarquía de permisos (motor de base de datos).

Permisos y roles fijos de servidor y base de datos

Los permisos de los roles fijos de servidor y roles fijos de base de datos son similares a los permisos granulares, pero no son exactamente iguales. Por ejemplo, los miembros del rol fijo de servidor sysadmin tienen todos los permisos sobre la instancia de SQL Server, al igual que los inicios de sesión con el permiso CONTROL SERVER. Pero la concesión del permiso CONTROL SERVER no convierte un inicio de sesión en miembro del rol fijo de servidor de administrador del sistema, y agregar un inicio de sesión al rol fijo de servidor sysadmin no concede explícitamente el permiso CONTROL SERVER al inicio de sesión. A veces, un procedimiento almacenado comprobará los permisos comprobando el rol fijo sin comprobar el permiso granular. Por ejemplo, para desasociar una base de datos es necesaria la pertenencia al rol fijo de base de datos db_owner . El permiso equivalente CONTROL DATABASE no es suficiente. Estos dos sistemas operan en paralelo, pero rara vez interactúan entre sí. Microsoft recomienda utilizar el sistema de permisos granulares más reciente en lugar de los roles fijos siempre que sea posible.

Supervisión de permisos

Las vistas siguientes devuelven información de seguridad.

Los inicios de sesión y los roles de servidor definidos por el usuario en un servidor se pueden examinar con la vista sys.server_principals . Esta vista no está disponible en SQL Database.
Los usuarios y los roles definidos por el usuario en una base de datos se pueden examinar con la vista sys.database_principals .
Los permisos concedidos a los inicios de sesión y los roles fijos de servidor definidos por el usuario se pueden examinar con la vista sys.server_permissions . Esta vista no está disponible en SQL Database.
Los permisos concedidos a los usuarios y los roles fijos de base de datos definidos por el usuario se pueden examinar con la vista sys.database_permissions .
La pertenencia al rol de base de datos se puede examinar con la vista sys.database_role_members .
La pertenencia al rol de servidor se puede examinar con la vista sys.server_role_members . Esta vista no está disponible en SQL Database.
Para obtener vistas adicionales relacionadas con la seguridad, vea Vistas de catálogo de seguridad (Transact-SQL) .

Ejemplos

Las instrucciones siguientes devuelven información útil acerca de los permisos.

A Lista de permisos de base de datos para cada usuario

Para entregar los permisos explícitos concedidos o denegados en una base de datos (SQL Server y SQL Database), ejecute la siguiente instrucción en la base de datos.

SELECT
    perms.state_desc AS State,
    permission_name AS [Permission],
    obj.name AS [on Object],
    dp.name AS [to User Name]
FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dp
    ON perms.grantee_principal_id = dp.principal_id
JOIN sys.objects AS obj
    ON perms.major_id = obj.object_id;

B. Enumeración de miembros de rol de servidor

Para entregar los miembros de los roles del servidor (solo SQL Server), ejecute la instrucción siguiente.

SELECT roles.principal_id AS RolePrincipalID,
    roles.name AS RolePrincipalName,
    server_role_members.member_principal_id AS MemberPrincipalID,
    members.name AS MemberPrincipalName
FROM sys.server_role_members AS server_role_members
INNER JOIN sys.server_principals AS roles
    ON server_role_members.role_principal_id = roles.principal_id
LEFT JOIN sys.server_principals AS members
    ON server_role_members.member_principal_id = members.principal_id;

C. Enumeración de todas las entidades de seguridad de base de datos que son miembros de un rol en el nivel de base de datos

Para entregar los miembros de los roles de base de datos (SQL Server y SQL Database), ejecute la instrucción siguiente en la base de datos.

SELECT dRole.name AS [Database Role Name], dp.name AS [Members]
FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dp
    ON dRo.member_principal_id = dp.principal_id
JOIN sys.database_principals AS dRole
    ON dRo.role_principal_id = dRole.principal_id;