Introducción a los permisos de los motores de bases de datos

Se aplica a: síSQL Server (todas las versiones admitidas) SíAzure SQL Database SíInstancia administrada de Azure SQL síAzure Synapse Analytics síAlmacenamiento de datos paralelos

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

Entidades de seguridad

"Entidad de seguridad" es el nombre oficial de las identidades que utilizan SQL Server y a las que se pueden conceder permisos 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 el Transact-SQL indicado 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 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 fijos de servidor son un conjunto de roles preconfigurados que proporcionan un grupo práctico de permisos de nivel de servidor. Se pueden agregar inicios de sesión a los roles mediante la instrucción ALTER SERVER ROLE ... ADD MEMBER . Para obtener más información, consulte ALTER SERVER ROLE (Transact-SQL). SQL Database no admite los roles fijos de servidor, pero tiene dos roles en la base de datos maestra (dbmanager y loginmanager) que actúan como roles de servidor.

Roles de servidor definidos por el usuario

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

Usuarios de bases de datos

Para conceder acceso a una base de datos a los inicios de sesión, se crea un usuario de base de datos en una base de datos y se asigna ese usuario de base de datos al inicio de sesión. Normalmente, el nombre de usuario de base de datos es el mismo que el nombre de inicio de sesión, aunque no tiene por qué ser así en todos los casos. 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 el correspondiente inicio de sesión. Estos usuarios se denominan usuarios de base de datos independiente. Microsoft recomienda el uso de los usuarios de base de datos independiente porque facilita el traslado de la base de datos a otro servidor. Igual que un inicio de sesión, un usuario de base de datos independiente puede utilizar tanto la autenticación de Windows como la autenticación de SQL Server . Para obtener más información, vea Usuarios de base de datos independiente: hacer que la base de datos sea 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 Active Directory o Azure Active Directory:

  1. Cree un usuario de Windows para cada persona.

  2. Cree grupos de Windows que representen las unidades y las funciones de trabajo.

  3. 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

  1. Cree un inicio de sesión para los grupos de Windows. (Si usa la autenticación de SQL Server , omita los pasos de Active Directory y cree los inicios de sesión de la autenticación SQL Server aquí).

  2. 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.

  3. 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.

  4. Agregue los usuarios de base de datos a uno o varios roles de base de datos definidos por el usuario.

  5. 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

  1. En la base de datos de usuario, cree un usuario de base de datos independiente para el grupo de Windows. (Si usa la autenticación de SQL Server , omita los pasos de Active Directory y cree la autenticación de SQL Server del usuario de base de datos independiente aquí).

  2. 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.

  3. Agregue los usuarios de base de datos a uno o varios roles de base de datos definidos por el usuario.

  4. 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 de 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.

Asignación de 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 muestran en el tema Permissions (Database Engine) (Permisos [motor de base de datos]) y en el gráfico al que se hace referencia más abajo.

  • 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. (Por ejemplo, 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.

En la siguiente instrucción GRANT de ejemplo, se concede el permiso UPDATE de la tabla o vista Parts incluida en el esquema Production al rol denominado PartsTeam:

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

En la siguiente instrucción de concesión de ejemplo, se concede el permiso UPDATE para el esquema Production y, en cualquier tabla o vista contenida en este esquema, al rol denominado ProductionTeam, que es un enfoque más eficaz y rentable para asignar permisos que en el 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 Ted es miembro del rol Ventas. También se ha concedido a Ted el permiso SELECT para la tabla OrderStatus bajo su propio nombre de usuario mediante la instrucción GRANT SELECT ON OBJECT::OrderStatus TO Ted;. Supongamos que el administrador desea quitar el elemento GRANT al rol Ventas.

  • Si el administrador ejecuta correctamente REVOKE SELECT ON OBJECT::OrderStatus TO Sales;, Ted conservará el acceso SELECT a la tabla OrderStatus mediante su instrucción GRANT individual.

  • Si el administrador no ejecuta correctamente DENY SELECT ON OBJECT::OrderStatus TO Sales; , a Ted, como miembro del rol de ventas, le será denegado el permiso SELECT porque DENY a Ventas invalida su GRANTindividual.

Nota

Los permisos se pueden configurar mediante Management Studio. Busque el elemento protegible en el Explorador de objetos, haga clic con el botón derecho en el elemento protegible y, luego, haga clic en 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 permisos

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 Ted;   
  
GRANT CONTROL ON OBJECT::Region TO Ted;   
  
GRANT SELECT ON SCHEMA::Customers TO Ted;   
  
GRANT CONTROL ON SCHEMA::Customers TO Ted;   
  
GRANT SELECT ON DATABASE::SalesDB TO Ted;   
  
GRANT CONTROL ON DATABASE::SalesDB TO Ted;  

Conceder el permiso mínimo

El primer permiso mencionado antes (GRANT SELECT ON OBJECT::Region TO Ted;) 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 Ted 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 ejerce un impacto importante sobre el éxito que puede tener 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. Puede leer más sobre este enfoque en esta entrada de blog de Andreas Wolter Diseño de esquemas para SQL Server: recomendaciones para el diseño de esquemas teniendo en cuenta la seguridad.

Diagrama de permisos

El gráfico siguiente muestra los permisos y las relaciones entre ellos. 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. Haga clic en la imagen para descargar el póster de los permisos de los motores de bases de datos en formato pdf.

Permisos de los motores de bases de datos

Para ver un gráfico que muestra las relaciones entre las entidades de seguridad de Motor de base de datos y los objetos de servidor y base de datos, consulte Permissions Hierarchy (Database Engine) (Jerarquía de permisos [motor de base de datos]).

Permisos y roles fijos de servidor y de 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 en la instancia de SQL Server, 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 sysadmin, 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. 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 devolver los permisos explícitos concedidos o denegados en una base de datos (SQL Server y SQL Database), ejecute la instrucción siguiente en la base de datos.

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

B. Enumeración de miembros de rol de servidor

Para devolver los miembros de los roles de 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 OUTER 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 devolver 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], dPrinc.name AS [Members]  
FROM sys.database_role_members AS dRo  
JOIN sys.database_principals AS dPrinc  
    ON dRo.member_principal_id = dPrinc.principal_id  
JOIN sys.database_principals AS dRole  
    ON dRo.role_principal_id = dRole.principal_id;  

Pasos siguientes

Para obtener más temas para comenzar, consulte:

Consulte también

Centro de seguridad para el motor de base de datos SQL Server y la base de datos SQL Azure
Funciones de seguridad (Transact-SQL)
Funciones y vistas de administración dinámica relacionadas con la seguridad (Transact-SQL)
Vistas de catálogo de seguridad (Transact-SQL)
sys.fn_builtin_permissions (Transact-SQL)
Determinar los permisos efectivos del motor de base de datos