Separación de esquemas de usuario

Actualizado: 12 de diciembre de 2006

Desde SQL Server 2005, cada objeto pertenece a un esquema de base de datos. Un esquema de base de datos es un espacio de nombres separado de un usuario de base de datos. Un esquema se puede considerar como un contenedor de objetos. Los esquemas se pueden crear y modificar en una base de datos, y a los usuarios se les puede conceder acceso a un esquema. Un esquema puede ser propiedad de cualquier usuario y esta propiedad es transferible.

[!NOTA] Los esquemas de base de datos son diferentes de los esquemas XML. Para obtener más información acerca de los esquemas XML, vea Administrar colecciones de esquemas XML en el servidor.

Para obtener más información acerca de cómo crear un esquema de objeto de base de datos, vea CREATE SCHEMA (Transact-SQL).

Comportamiento nuevo

En las versiones anteriores de SQL Server, los usuarios y los esquemas de base de datos eran conceptualmente el mismo objeto. Desde SQL Server 2005, los usuarios y los esquemas están separados, y estos últimos se utilizan como contenedores de objetos.

La separación de propiedad de los esquemas tiene consecuencias importantes. Los esquemas de base de datos ofrecen un mayor control sobre la seguridad de los objetos de la base de datos porque:

• Se pueden administrar los permisos sobre esquemas y sobre elementos que se pueden proteger con mayor precisión que en las versiones anteriores. Para obtener más información, vea GRANT (permisos de esquema de Transact-SQL) y GRANT (permisos de objeto de Transact-SQL).
• La propiedad de los esquemas y de los elementos que se pueden proteger con ámbito de esquema es transferible. Para obtener más información, vea ALTER AUTHORIZATION (Transact-SQL).
• Es posible mover objetos entre esquemas. Para obtener más información, vea ALTER SCHEMA (Transact-SQL).
• Un mismo esquema puede contener objetos que sean propiedad de varios usuarios de base de datos.
• Varios usuarios de base de datos pueden compartir un mismo esquema predeterminado.
• Cualquier entidad de seguridad de base de datos puede ser propietaria de un esquema. Esto incluye funciones y funciones de aplicación.
• Es posible eliminar un usuario de base de datos sin necesidad de eliminar objetos en un esquema correspondiente.

Los esquemas de base de datos aportan otros cambios importantes de seguridad en relación con las versiones anteriores:

• El código escrito para las versiones anteriores de SQL Server puede producir resultados incorrectos si el código considera que los esquemas son equivalentes a los usuarios de base de datos.
• Las vistas de catálogo diseñadas para las versiones anteriores de SQL Server pueden producir resultados incorrectos. Esto incluye a sysobjects.
• Las cadenas de propiedad y el cambio de contexto de usuario pueden ahora comportarse de manera distinta porque los usuarios pueden ser propietarios de más de un esquema. Para obtener más información acerca de las cadenas de propiedad, vea Cadenas de propiedad y Jerarquía de permisos. Para obtener más información acerca del cambio de contexto, vea Cambio de contexto.
• En SQL Server 2000, los objetos de base de datos eran propiedad de los usuarios. La referencia de cuatro partes a un objeto de base de datos en SQL Server 2000 era [DatabaseServer].[DatabaseName].[ObjectOwner].[DatabaseObject]. A partir de SQL Server 2005, la referencia de cuatro partes a un objeto de base de datos es [DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject].

Cambios en la propiedad de objetos

La propiedad de los siguientes objetos se refiere a un esquema, no a un usuario:

• CREATE TABLE
• ALTER TABLE
• CREATE VIEW
• ALTER VIEW
• CREATE INDEX
• ALTER INDEX
• CREATE FUNCTION
• ALTER FUNCTION
• DROP FUNCTION
• VIEW_TABLE_USAGE
• VIEW_COLUMN_USAGE
• TABLE_CONSTRAINTS
• REFERENTIAL_CONSTRAINTS
• KEY_COLUMN_USAGE
• CONSTRAINT_TABLE_USAGE
• CONSTRAINT_COLUMN_USAGE
• CHECK_CONSTRAINTS
• COLUMN_DOMAIN_USAGE
• COLUMNS
• DOMAIN_CONSTRAINTS
• ROUTINE_COLUMNS

Para obtener más información acerca de las columnas que devuelven metadatos de usuarios y las que devuelven metadatos de esquema, vea la sección "Vistas y funciones de los catálogos de esquemas" más adelante.

Tablas de sistema reemplazadas por vistas y funciones de catálogos

SQL Server 2005 incluye más de 250 vistas de catálogo nuevas. Algunas tratan sobre objetos de esquema y usuarios de base de datos, que reemplazan a las tablas de sistema de SQL Server 2000. Se recomienda que utilice las nuevas vistas de catálogo para tener acceso a los metadatos. Para obtener más información, vea Vistas de catálogo (Transact-SQL).

En la tabla siguiente se muestra la asignación entre las tablas de sistema de SQL Server 2000 y las vistas de catálogo de SQL Server 2005 equivalentes:

Esquemas predeterminados

Para resolver los nombres no completos de asegurables, SQL Server 2000 utilizaba la resolución de nombres para comprobar el esquema propiedad del usuario de base de datos que realiza la llamada, seguido del esquema propiedad de dbo.

En SQL Server 2005, a cada usuario se le puede asignar un esquema predeterminado. Se puede establecer y cambiar el esquema predeterminado mediante la opción DEFAULT_SCHEMA de CREATE USER o ALTER USER. Si DEFAULT_SCHEMA no está definido, SQL Server 2005 dará por sentado que el esquema dbo es el esquema predeterminado.

[!NOTA] Los usuarios que se conectan a través de un grupo con autenticación de Windows no tendrán una asociación de esquema predeterminada. Si uno de estos usuarios crea un objeto que no está calificado con un esquema, se creará un nuevo esquema, su nombre se establecerá en el nombre del usuario actual y el objeto de tabla se creará en este nuevo espacio de nombre al que da nombre el usuario.

Las nuevas instrucciones de Lenguaje de definición de datos (DDL) pueden introducir complejidades en los metadatos del sistema que no se hayan reflejado con precisión en las tablas de sistema anteriores, como sysobjects. En este ejemplo, el Id. de usuario y el nombre de esquema devueltos por sysobjects no están sincronizados y reflejan la distinción entre el usuario y el esquema introducido en SQL Server 2005.

USE tempdb
GO
CREATE LOGIN u1 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'
CREATE USER u1 WITH DEFAULT_SCHEMA = u1
GO
GRANT CREATE TABLE TO u1 
GO
CREATE SCHEMA sch1
GO
CREATE SCHEMA u1 AUTHORIZATION u1
GO
EXECUTE AS USER = 'u1'
GO
CREATE TABLE t1(c1 int)
GO
REVERT
GO
SELECT user_name(uid) , * FROM sysobjects WHERE name = 't1'
GO

Advertencia:
Es necesario utilizar las nuevas vistas de catálogo en cualquier base de datos en la que se hayan utilizado alguna vez alguna de las instrucciones DDL siguientes: CREATE/ALTER/DROP SCHEMA; CREATE/ALTER/DROP USER; CREATE/ALTER/DROP ROLE; CREATE/ALTER/DROP APPROLE; ALTER AUTHORIZATION.

Funciones y vistas de catálogo de esquemas

A partir de SQL Server 2005, los esquemas son entidades explícitas reflejadas en los metadatos. En consecuencia, los esquemas sólo pueden tener un propietario, pero un solo usuario puede tener uno o varios esquemas. Esta compleja relación no se refleja en las tablas del sistema de SQL Server 2000, por lo que SQL Server 2005 introduce nuevas vistas de catálogo que reflejan los nuevos metadatos con precisión.

La tabla siguiente muestra las vistas de catálogo, los metadatos y las funciones para los esquemas en SQL Server 2005:

Ejemplos

A. Crear un esquema y asignar la propiedad a un usuario

El ejemplo siguiente agrega un inicio de sesión de SQL Server y un usuario denominado Marjorie y un esquema nuevo denominado Auditing a la base de datos AdventureWorks. Marjorie se asigna como el propietario del esquema Auditing.

CREATE LOGIN Marjorie
    WITH PASSWORD = '8fdKJl3$nlNv3049jsKK';
USE AdventureWorks;
CREATE USER Marjorie FOR LOGIN Marjorie
GO
CREATE SCHEMA Auditing AUTHORIZATION Marjorie;
GO

B. Conceder a un usuario derechos para otro esquema

El ejemplo siguiente concede a un usuario denominado Marjorie el permiso SELECT en el esquema Purchasing en la base de datos AdventureWorks.

USE AdventureWorks;
GO
GRANT SELECT ON SCHEMA::Purchasing TO Marjorie;
GO

C. Cambiar la propiedad de un esquema

En el ejemplo siguiente, un usuario nuevo, Jon, se crea en la base de datos AdventureWorks. A Jon se le concede la propiedad del esquema Auditing en la base de datos AdventureWorks. A continuación, el usuario Marjorie se elimina de la base de datos AdventureWorks.

USE AdventureWorks;
GO
/* Create a new user in the database */
CREATE LOGIN Jon
    WITH PASSWORD = '1fdKJl3$nlNv3049jsBB';
USE AdventureWorks;
CREATE USER Jon FOR LOGIN Jon
GO
ALTER AUTHORIZATION ON SCHEMA::Auditing TO Jon;
GO
/* Removes the user from the system */
DROP LOGIN Marjorie;
GO
DROP USER Marjorie;
GO

C. Mostrar la propiedad de un esquema

En el ejemplo siguiente se muestra el propietario del esquema Auditing en la base de datos AdventureWorks.

USE AdventureWorks;
GO
/* This method uses the INFORMATION_SCHEMA views */
SELECT * 
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'Auditing';
GO

/* This method uses the sys.schemas catalog and links
   the names of the database users and server logins */
SELECT s.name AS 'Schema Name'
, db.name AS 'Database User Name'
, svr.name AS 'SQL Server Login Name'
FROM sys.schemas s
/* Obtains the name of the database user */
   INNER JOIN sys.database_principals db
      ON s.principal_id = db.principal_id
/* Obtains the name of the server login */
      INNER JOIN sys.server_principals svr
         ON db.sid = svr.sid
WHERE s.name = 'Auditing'
ORDER BY s.name

Vea también

Conceptos

Jerarquía de permisos
Entidades de seguridad

Otros recursos

CREATE SCHEMA (Transact-SQL)
ALTER SCHEMA (Transact-SQL)
ALTER AUTHORIZATION (Transact-SQL)
DROP SCHEMA (Transact-SQL)
sys.schemas (Transact-SQL)
CREATE USER (Transact-SQL)
ALTER USER (Transact-SQL)
Realizar cambios de esquema en las bases de datos de publicaciones

Ayuda e información

Obtener ayuda sobre SQL Server 2005

Historial de cambios