Compartir por

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

Aplica a:SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsSistema de Plataforma de Analítica (PDW)Base de datos SQL en Microsoft Fabric

En este artículo se revisan algunos conceptos básicos de seguridad y, a continuación, se describe una implementación típica de permisos. Los permisos del motor de base de datos se administran en el nivel de servidor a través de inicios de sesión y roles de servidor, y en el nivel de base de datos a través de usuarios de base de datos y roles de base de datos.

SQL Database y SQL Database en Microsoft Fabric proporcionan las mismas opciones dentro de cada base de datos, pero los permisos de nivel de servidor no están disponibles.

Note

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

Entidades de seguridad

Una entidad de seguridad es la identidad que SQL Server utiliza y a la que se le pueden asignar permisos para realizar acciones. Las entidades de seguridad suelen ser personas o grupos de personas, pero pueden ser otras entidades que pretenden ser personas. Las entidades de seguridad se pueden crear y administrar mediante los ejemplos de Transact-SQL que se muestran en este artículo o mediante SQL Server Management Studio.

Logins

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 información sobre los dos tipos de inicios de sesión, consulte Elegir un modo de autenticación.

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, vea ALTER SERVER ROLE. SQL Database no admite los roles fijos de servidor, pero tiene dos roles en la master base de datos (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, vea ALTER SERVER ROLE. SQL Database no admite los roles de servidor definidos por el usuario.

Usuarios de base de datos

Para conceder acceso a un inicio de sesión en una base de datos, cree un usuario de base de datos en esa base de datos y asigne el usuario de base de datos a un inicio de sesión. El nombre de usuario de la base de datos suele ser el mismo que el nombre de inicio de sesión por convención, aunque no tiene que ser el mismo. Cada usuario de base de datos se asigna a un inicio de sesión único. Un inicio de sesión solo se puede asignar a un usuario de una base de datos, pero se puede asignar como usuario de base de datos en varias bases de datos diferentes.

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 fomenta el uso de usuarios de bases de datos independientes, ya que facilita el traslado de 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, consulte Conversión de la base de datos en portátil con bases de datos independientes.

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.

Roles fijos de base de datos

Los roles fijos de base de datos son un conjunto de roles preconfigurados que proporcionan un grupo práctico 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.

Roles de base de datos definidos por el usuario

Los usuarios con el permiso pueden crear nuevos CREATE ROLE 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.

Otros principales

Otras entidades de seguridad que no se describen aquí incluyen roles de aplicación e 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 Creación de un usuario de base de datos.

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

  1. Cree un usuario 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 el usuario se conectará a muchas 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 inicios de sesión de autenticación de 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, puede tener un rol de analista financiero y un rol de 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 el usuario se conectará solo a una 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 aquí un usuario de base de datos contenido con autenticación de SQL Server).

  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, puede tener un rol de analista financiero y un rol de 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 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

La mayoría de las instrucciones de permiso tienen el siguiente formato:

<authorization> <permission> ON <securable>::<name> TO <principal>;

  • <authorization> debe ser GRANT, REVOKEo DENY.

  • <permission> establece la acción que permite o prohíbe. El número exacto de permisos difiere entre SQL Server y Azure SQL Database. Para obtener información sobre los permisos, vea Permisos (motor de base de datos) y consulte el gráfico más adelante en este artículo.

  • 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 <securable>::<name> porque es inequívoco o inadecuado en el contexto. Por ejemplo, el permiso CREATE TABLE no requiere la cláusula <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 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;

En la siguiente instrucción de ejemplo, se concede el permiso UPDATE para el esquema Production y por extensión, 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.

Caution

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 puede ser un escenario común. En el ejemplo siguiente se muestra el principio.

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 Sales rol. 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 GRANT del rol Sales.

  • 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 DENY SELECT ON OBJECT::OrderStatus TO Sales; incorrectamente, a Jae, como miembro del rol Sales, se le niega el permiso SELECT porque el DENY a Sales prevalece sobre su GRANT individual.

Note

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 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. Normalmente, el permiso CONTROL en un objeto te da todos los demás permisos en el 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, vamos a tomar una tabla (Region), en un esquema (Customers), en una base de datos (SalesDB).

  • CONTROL el permiso en la tabla Region incluye todos los demás permisos de la tabla Region, incluidos ALTER, SELECT, INSERT, UPDATE, DELETE, y otros permisos.

  • SELECT en el esquema Customers que posee la tabla Region incluye el permiso SELECT en la tabla Region.

Así que el permiso SELECT en la tabla Region 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 enumerado anteriormente (GRANT SELECT ON OBJECT::Region TO Jae;) es el más granular. Esa instrucción es el menor permiso posible que concede SELECT. No incluye permisos para los objetos subordinados. Es un buen principio conceder siempre el menor permiso posible, pero debe considerar la posibilidad de conceder a niveles superiores, con el fin de simplificar el sistema de concesión.

Por tanto, si Jae necesita permiso 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 funciona mejor cuando la base de datos está diseñada para que los objetos que necesiten permisos idénticos se incluyan en un único esquema.

Tip

Al diseñar una base de datos y sus objetos, planee desde el principio cómo las aplicaciones y los usuarios acceden a esos objetos. Use esta información para controlar el acceso a tablas, vistas, funciones y procedimientos almacenados mediante esquemas. Los esquemas permiten agrupar tipos de acceso más fácilmente.

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.

Captura de pantalla del PDF de permisos del motor de base de datos.

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 los roles fijos de base de datos son similares, pero no exactamente iguales que los permisos granulares. 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.

Sin embargo, conceder el permiso CONTROL SERVER no convierte a un login en miembro del rol fijo de servidor sysadmin y agregar un login al rol fijo de servidor sysadmin no concede explícitamente al login el permiso CONTROL SERVER. A veces, un procedimiento almacenado comprueba los permisos comprobando el rol fijo y no comprobando el permiso granular.

Por ejemplo, la separación de una base de datos requiere ser miembro del 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 usar el sistema de permisos más reciente y granular en lugar de roles fijos siempre que sea posible.

Supervisión de permisos

Las vistas siguientes devuelven información de seguridad. Para ver todas las vistas relacionadas con la seguridad, consulte Vistas de catálogo de seguridad (Transact-SQL).

View Description
sys.server_principals 1 Inicios de sesión y roles de servidor definidos por el usuario en un servidor
sys.database_principals Usuarios y roles definidos por el usuario en una base de datos
sys.server_permissions 1 Permisos concedidos a inicios de sesión y roles fijos de servidor definidos por el usuario
sys.database_permissions Permisos concedidos a usuarios y roles fijos de base de datos definidos por el usuario
sys.database_role_members Pertenencia a rol de base de datos
sys.server_role_members 1 Pertenencia a rol de servidor

1 Esta vista no está disponible en SQL Database.

Examples

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 siguiente instrucción Transact-SQL 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
     INNER JOIN sys.database_principals AS dp
         ON perms.grantee_principal_id = dp.principal_id
     INNER 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 siguiente sentencia en la base de datos.

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

Contenido relacionado

  • Last updated on