Séparation du schéma et de l'utilisateur

Mis à jour : 12 décembre 2006

À compter de la version SQL Server 2005, chaque objet appartient à un schéma de base de données. Un schéma de base de données est un espace de noms distinct qui est indépendant d'un utilisateur de base de données. Vous pouvez considérer un schéma comme un conteneur d'objets. Les schémas peuvent être créés et modifiés dans une base de données, et les utilisateurs peuvent se voir accorder l'accès à un schéma. Un schéma peut appartenir à n'importe quel utilisateur, et sa propriété est transférable.

Remarque :
Les schémas de base de données sont différents des schémas XML. Pour plus d'informations sur les schémas XML, consultez Gestion des collections de schémas XML sur le serveur.

Pour plus d'informations sur la création d'un schéma d'objet de base de données, consultez CREATE SCHEMA (Transact-SQL).

Nouveau comportement

Dans les versions antérieures de SQL Server, les schémas et les utilisateurs de base de données constituaient, d'un point de vue conceptuel, un seul et même objet. À compter de la version SQL Server 2005, les utilisateurs et les schémas sont distincts, et les schémas font office de conteneurs d'objets.

La séparation du propriétaire et des schémas présente des implications importantes. Les schémas de base de données offrent un plus grand contrôle sur la sécurité des objets de base de données, comme suit :

• Les autorisations sur les schémas et les sécurisables contenus dans les schémas peuvent être gérées de manière plus précise que dans les versions antérieures. Pour plus d'informations, consultez GRANT - Autorisations sur un schéma (Transact-SQL) et GRANT – octroi d'autorisations d'objet (Transact-SQL).
• La propriété des schémas et des éléments sécurisables sur l'étendue d'un schéma est transférable. Pour plus d'informations, consultez ALTER AUTHORIZATION (Transact-SQL).
• Les objets peuvent être déplacés parmi les schémas. Pour plus d'informations, consultez ALTER SCHEMA (Transact-SQL).
• Un même schéma peut contenir des objets appartenant à plusieurs utilisateurs de base de données.
• Plusieurs utilisateurs de base de données peuvent partager un même schéma par défaut.
• Un schéma peut appartenir à n'importe quelle entité de sécurité de base de données. Cela comprend les rôles et les rôles d'application.
• Il est possible de supprimer un utilisateur de base de données sans supprimer les objets dans un schéma correspondant.

Les schémas de base de données apportent d'autres modifications importantes à la sécurité par rapport aux versions précédentes :

• Le code écrit pour les versions antérieures de SQL Server peut retourner des résultats incorrects si le code suppose que les schémas sont équivalents aux utilisateurs de base de données.
• Les affichages catalogue conçus pour des versions antérieures de SQL Server peuvent retourner des résultats incorrects. Cela comprend sysobjects.
• Les chaînes de propriétés et les changements de contexte utilisateur peuvent désormais se comporter différemment car les utilisateurs peuvent posséder plusieurs schémas. Pour plus d'informations sur les chaînes de propriétés, consultez Chaînes de propriétés et Hiérarchie des autorisations. Pour plus d'informations sur le changement de contexte, consultez Changement de contexte.
• Dans SQL Server 2000, les objets de base de données appartenaient aux utilisateurs. La référence en quatre parties à un objet de base de données dans SQL Server 2000 était [DatabaseServer].[DatabaseName].[ObjectOwner].[DatabaseObject]. À compter de la version SQL Server 2005, la référence en quatre parties à un objet de base de données est [DatabaseServer].[DatabaseName].[DatabaseSchema].[DatabaseObject].

Modifications de la propriété des objets

La propriété d'appartenance des objets suivants fait référence à un schéma, et non à un utilisateur :

• 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

Pour plus d'informations sur les colonnes qui retournent des métadonnées utilisateur par rapport à des métadonnées de schéma, consultez la section « Fonctions et affichages catalogue de schémas » ci-après.

Tables système remplacées par des fonctions et affichages catalogue

SQL Server 2005 propose plus de 250 nouveaux affichages catalogue, certains traitant des objets schéma et utilisateur de base de données, qui remplacent les tables système de SQL Server 2000. Il est vivement conseillé d'utiliser ces affichages pour accéder aux métadonnées. Pour plus d'informations, consultez Affichages catalogue (Transact-SQL).

Le tableau suivant présente la correspondance entre les tables système dans SQL Server 2000 et les affichages catalogue équivalents dans SQL Server 2005 :

Schémas par défaut

Pour résoudre les noms des éléments sécurisables qui ne sont pas complets, SQL Server 2000 utilisait la résolution de noms pour vérifier le schéma dont le propriétaire est l'utilisateur de base de données appelant, et le schéma dont dbo est le propriétaire.

Dans SQL Server 2005, un schéma par défaut peut être affecté à chaque utilisateur. Ce schéma par défaut peut être défini et modifié à l'aide de l'option DEFAULT_SCHEMA des instructions CREATE USER ou ALTER USER. Si l'option DEFAULT_SCHEMA n'est pas définie, SQL Server 2005 partira du principe que le schéma dbo est le schéma par défaut.

Remarque :
Les utilisateurs qui se connectent via un groupe authentifié par Windows ne disposent pas d'association de schéma par défaut. Si un utilisateur de ce type crée un objet qui n'est pas qualifié avec un schéma, un nouveau schéma est créé, son nom est défini au nom de l'utilisateur actif, et l'objet table est créé dans ce nouvel espace de noms nommé par l'utilisateur.

Les nouvelles instructions DDL (Data Definition Language) peuvent introduire des éléments de complexité dans les métadonnées système qui ne sont pas reflétées avec exactitude dans les tables système antérieures telles que sysobjects. Dans cet exemple, l'ID utilisateur et le nom du schéma retournés par sysobjects ne sont pas synchronisés, et ils reflètent la distinction entre utilisateur et schéma introduite dans 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

Attention :
Vous devez utiliser les nouveaux affichages catalogue dans les bases de données dans lesquelles les instructions DLL suivantes ont été utilisées : CREATE/ALTER/DROP SCHEMA; CREATE/ALTER/DROP USER; CREATE/ALTER/DROP ROLE; CREATE/ALTER/DROP APPROLE; ALTER AUTHORIZATION.

Fonctions et affichages catalogue de schémas

Depuis la version SQL Server 2005, les schémas constituent des entités explicites reflétées dans les métadonnées. En conséquence, les schémas ne peuvent avoir qu'un seul propriétaire, mais un même utilisateur peut être propriétaire d'un ou de nombreux schémas. Cette relation complexe ne se retrouve pas dans les tables système de SQL Server 2000, aussi SQL Server 2005 présente de nouveaux affichages catalogue qui reflètent fidèlement les nouvelles métadonnées.

Le tableau suivant présente les affichages catalogue, les métadonnées et les fonctions des schémas dans SQL Server 2005 :

Exemples

A. Création d'un schéma et affectation de sa propriété à un utilisateur

L'exemple suivant ajoute une connexion SQL Server et un utilisateur appelé Marjorie ainsi qu'un nouveau schéma appelé Auditing dans la base de données AdventureWorks. Marjorie est définie comme propriétaire du schéma Auditing.

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

B. Attribution à un utilisateur des droits sur un autre schéma

L'exemple suivant accorde à un utilisateur appelé Marjorie l'autorisation SELECT sur le schéma Purchasing dans la base de données AdventureWorks.

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

C. Modification de la propriété d'un schéma

Dans l'exemple suivant, un nouvel utilisateur, Jon, est créé dans la base de données AdventureWorks. Jon se voit accorder la propriété du schéma Auditing dans la base de données AdventureWorks. Ensuite, l'utilisateur appelé Marjorie est supprimé de la base de données 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

D. Affichage de la propriété d'un schéma

L'exemple suivant affiche le propriétaire du schéma Auditing dans la base de données 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

Voir aussi

Concepts

Hiérarchie des autorisations
Entités de sécurité

Autres ressources

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)
Modification du schéma dans les bases de données de publication

Aide et Informations

Assistance sur SQL Server 2005

Historique des modifications