APSQL

Qu'est ce que les schémas

Dans SQL Server 2008, le schéma est un ensemble qui permet de définir un regroupement logique pour les objets. Un schéma, comme tout objet de la base de données est créé par un utilisateur mais ce n'est l'une des composantes de l'utilisateur. Ainsi le schéma peut être transférer à un autre utilisateur, mais également la suppression de l'utilisateur qui a créé le schéma n'entraine pas la suppression du ou des schémas qu'il a créé.

Dans une base de données l'utilisation des schémas présente plusieurs avantages:

- Regroupement logique : le schéma possède un nom significatif et il permet de réaliser un regroupement logique des tables, vues, fonctions, procédures, …. Si cette notion d'organisation peut être absente des petites bases ce n'est pas le même cas pour les bases plus importantes. La base d'exemple AdventureWorks fournis un bon exemple d'utilisation des schémas.

- Entité de sécurité : l'autre aspect important des schémas est qu'ils constituent une entité de sécurité. Il est ainsi possible de délivrer des privilèges directement au niveau du schéma et donc concerne tous les objets présents dans le schéma.

Comment créer un schéma?

Le schéma va être défini à l'aide de l'instruction CREATE SCHEMA. Il est possible de définir le schéma puis de créer les objets par la suite et d'accorder les autorisations de façon séparés ou bien de cumuler ces opérations directement dans le CREATE SCHEMA. Lors de la création du schéma il faut également spécifier le compte d'utilisateur de base de données qui est propriétaire du schéma.

Exemple 1 Création d'un schéma simple

Le schéma compta est défini et son propriétaire est l'utilisateur de base de données dbo.

create schema compta AUTHORIZATION dbo;
			

Exemple 2 : Créer une table sur le schéma

Il suffit pour cela de donner le nom table en précisant nomSchéma.nomTable

create table compta.comptes(
numero numeric(8) constraint pk_comptes primary key,
libelle nvarchar(80));

Exemple 3 Créer un schéma avec une table et accorder des autorisations sur le schéma

Dans l'exemple suivant le schéma catalogue est défini et la table des produits est créé en même temps. De plus le compte de base de données guest (invité) reçoit le privilège d'exécuter l'instruction SELECT au niveau du schémas c'est à dire être capable de voir le contenu de toutes les tables présentent dans ce schéma.

CREATE SCHEMA catalogue AUTHORIZATION dbo
  CREATE TABLE produits(
                reference nvarchar(13) constraint pk_produits primary key,
                libelle nvarchar(80),
                PUHT numeric(8,2))
                GRANT SELECT TO guest;

Comment déplacer une table vers un schéma particulier?

Le schéma étant un ensemble logique, il est possible de transférer des tables, vues, procédures, fonctions, … facilement car cela n'entraine pas de modification physique. Ce transfère est réalisé avec l'instruction ALTER SCHEMA. Il faut alors préciser le nom du nouveau schéma et le nom complet de l'objet à transférer (nomSchéma. NomObjet)

Dans l'exemple suivant la table des clients est transféré du schéma dbo vers le schéma compta.

ALTER SCHEMA compta TRANSFER dbo.clients;

Comment modifier le propriétaire d'un schéma?

C'est l'instruction ALTER autorisation qui permet de modifier le propriétaire d'un schéma. Cette opération n'est bien sur pas possible sur les schémas sys, dbo et INFORMATION_SCHEMA.

L'exemple suivant montre comment transférer le schéma compta vers le rôle db_owner.

ALTER AUTHORIZATION ON Schema::compta TO db_owner;