Leer en inglés

Compartir a través de

Propiedad y separación de esquemas de usuario en SQL Server

  • Artículo

Se aplica a: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW) Base de datos SQL de Microsoft Fabric

Un concepto básico en la seguridad de SQL Server es que los propietarios de los objetos disponen de permisos irrevocables para administrarlos. No puede quitar privilegios de un propietario del objeto y no puede eliminar usuarios de una base de datos si en ella existen objetos que les pertenezcan.

Separación de esquemas de usuario

La separación del esquema de usuario permite disponer de más flexibilidad en la administración de los permisos de objeto de base de datos. Un esquema es un contenedor con nombre para objetos de base de datos, que permite agrupar objetos en espacios de nombres independientes. Por ejemplo, la base de datos de ejemplo de AdventureWorks contiene esquemas para Production, Sales y HumanResources.

La sintaxis de asignación de nombres de cuatro partes para hacer referencia a los objetos especifica el nombre de esquema.

SQL 
Server.Database.DatabaseSchema.DatabaseObject

Propietarios y permisos de esquemas

Los esquemas pueden pertenecer a cualquier entidad de seguridad de base de datos y una entidad de seguridad puede ser propietaria de varios esquemas. Puede aplicar reglas de seguridad a un esquema, que heredan todos los objetos incluidos en él. Después de configurar los permisos de acceso de un esquema, estos permisos se aplican automáticamente a medida que se agregan nuevos objetos al esquema. Se puede asignar un esquema predeterminado a los usuarios y varios usuarios de base de datos pueden compartir el mismo esquema.

De forma predeterminada, cuando los programadores crean objetos en un esquema, éstos pertenecen a la entidad de seguridad a la que pertenece el esquema y no al programador. La propiedad del objeto se puede transferir con la instrucción ALTER AUTHORIZATION de Transact-SQL. Un esquema también puede contener objetos que pertenecen a diferentes usuarios y disponer de permisos más granulares que los asignados al esquema, aunque esto no sea recomendable ya que agrega complejidad a la administración de permisos. Los objetos se pueden mover entre los esquemas y la propiedad del esquema se puede transferir entre entidades de seguridad. Se pueden quitar usuarios de base de datos sin que esto afecte a los esquemas.

Esquemas integrados para la compatibilidad con versiones anteriores

SQL Server incluye nueve esquemas predefinidos que tienen los mismos nombres que los usuarios y roles de base de datos integrados: db_accessadmin, db_backupoperator, db_datareader, db_datawriter, db_ddladmin, db_denydatareader, db_denydatawriter, db_owner y db_securityadmin. Se han creado para la compatibilidad con versiones anteriores. La recomendación es que no se usen para objetos de usuario. Puedes quitar los esquemas que tienen los mismos nombres que los roles fijos de base de datos, a menos que ya estén en uso, en cuyo caso el comando drop simplemente devolverá un error y bloqueará la eliminación del esquema usado.

SQL 
IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_accessadmin')
DROP SCHEMA [db_accessadmin]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_backupoperator')
DROP SCHEMA [db_backupoperator]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_datareader')
DROP SCHEMA [db_datareader]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_datawriter')
DROP SCHEMA [db_datawriter]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_ddladmin')
DROP SCHEMA [db_ddladmin]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_denydatareader')
DROP SCHEMA [db_denydatareader]
GO

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_denydatawriter')
DROP SCHEMA [db_denydatawriter]
GO

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_owner')
DROP SCHEMA [db_owner]
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'db_securityadmin')
DROP SCHEMA [db_securityadmin]
GO

Si quitas estos esquemas de la base de datos de model, no aparecerán en las bases de datos nuevas. Los esquemas que contienen objetos no se pueden quitar.

Los esquemas siguientes no se pueden quitar:

  • dbo
  • guest
  • sys
  • INFORMATION_SCHEMA

Nota

Los esquemas sys y INFORMATION_SCHEMA están reservados para los objetos del sistema. No puede crear objetos en ellos ni quitarlos.

Esquema dbo

El esquema dbo es el predeterminado de cada base de datos. De forma predeterminada, los usuarios creados con el comando CREATE USER de Transact-SQL usan dbo como esquema predeterminado. El esquema dbo pertenece a la cuenta de usuario dbo.

Los usuarios a los que se les asigna dbo como esquema predeterminado no heredan los permisos de la cuenta de usuario dbo. Los usuarios no heredan ningún permiso de un esquema; los permisos de esquema se heredan en los objetos de base de datos incluidos en el esquema. El esquema predeterminado de un usuario se utiliza únicamente para la referencia a objetos en caso de que el usuario omita el esquema al consultar objetos.

Nota

Cuando se hace referencia a objetos de base de datos con un nombre de una sola parte, SQL Server busca en primer lugar en el esquema predeterminado del usuario. Si no se encuentra el objeto, SQL Server busca a continuación en el esquema dbo. Si el objeto tampoco se encuentra en el esquema dbo, se muestra un error.

Contenido relacionado