12 Unité et base de données SQL Server 6.5

ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 1
SOMMAIRE
1PRESENTATION DE LA SEQUENCE
2
4
1.1DESCRIPTION DES SAVOIRS
1.2COMPETENCES ATTENDUES
1.3
PREREQUIS
4
4
4
NOTIONS DE BASE
5
2.1
DEFINITION
2.2LES MOTIVATIONS D’UN SGBD
2.3ARCHITECTURE D’UN SGBD
2.4LES FONCTIONS PRINCIPALES
2.4.1Description de la base
2.4.2Manipulation de la base
2.4.3Utilisation de la base
2.5LES DIFFERENTS TYPES D’UTILISATEURS
2.5.1L’administrateur principal de la base : le DBA
2.5.2L’administrateur d’application
2.5.3Le programmeur d’application
5
5
5
5
5
6
6
6
6
6
6
3LES LANGAGES DE DESCRIPTION DES DONNEES
7
3.1.1SGBD basés sur les méthodes d’accès : hiérarchiques et réseaux
3.1.2SGBD basés sur le modèle Relationnel
3.1.3 SGBD avancés
7
7
7
4 LE MARCHE DES SGBD R
8
4.1SGBD R SUR MINI ET GRAND SYSTEME
4.2SGBD R SUR MICRO-ORDINATEURS
4.3SGBD R SERVEURS SUR MICRO-ORDINATEURS
4.4SGBD R CLIENTS SUR MICRO-ORDINATEURS
8
8
8
9
5ALGEBRE RELATIONNELLE
10
5.1
INTRODUCTION
5.2DEFINITIONS ET NOTATIONS
5.2.1Relations n-aires
5.2.2 Notations
5.2.3Clés et super-clés
5.2.4Base de données
5.3OPERATEURS RELATIONNELS
5.3.1 Projection
5.3.2 Jointure
5.3.3 Equi-jointure
5.3.4 Sélection
5.3.5 Division
5.3.6Opérations ensemblistes
5.3.7Exemple d’interrogations
10
10
10
10
10
11
11
11
11
11
11
11
11
12
6COMPOSANTS D’UNE BASE DE DONNEES RELATIONNELLE (RAPPELS)
6.1
TABLE
6.2
INDEX
6.3
VUE
6.4 CLES ET RELATIONS
6.5AUTRES COMPOSANTS DE LA BASE DE DONNEES
6.6LES DIFFERENTS TYPES D’INTEGRITE
6.6.1L’intégrité d’entité
ALSI / S25
SGBD R Support de Cours 2TSIG
13
25
25
25
25
25
26
26
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 2
6.6.2L’intégrité référentielle
6.6.3L’intégrité de domaine
6.6.4L’intégrité définie par l’utilisateur
6.7METTRE EN OEUVRE L’INTEGRITE
6.7.1l’intégrité déclarative
6.7.2l’intégrité procédurale
26
26
26
26
26
26
7LE MODELE CLIENT-SERVEUR (APPROCHE)
28
7.14.1 ORGANISATION DE TYPE MAINFRAME
7.24.2 ORGANISATION EN SERVEUR DE FICHIERS
7.34.3 ORGANISATION EN SERVEUR DE DONNEES
7.4 4.4 LE MIDDLEWARE
7.54.5. BASES DE DONNEES DISTRIBUEES
28
28
28
29
29
8LES OUTILS D’ADMINISTRATION SQL SERVER 6.5
30
9ALLOCATION ET GESTION DE L’ESPACE DE STOCKAGE
33
9.1UNITE DE BASE DE DONNEES
9.2 UNITE D’ALLOCATION
9.3
EXTENSION
9.4
PAGE
9.5 PAGE D’ALLOCATION
33
33
34
34
34
10UNITE ET BASE DE DONNEES SQL SERVER 6.5
34
10.1CREATION D’UNE UNITE DE BASE DE DONNEES
10.2MODIFICATION DE LA TAILLE D’UNE UNITE
10.3SUPPRESSION D’UNE UNITE DE BASE DE DONNEES
10.4CREATION D’UNE BASE DE DONNEES
10.5EXTENSION D’UNE BASE DE DONNEES
10.67.6 REDUCTION D’UNE BASE DE DONNEES
10.7SUPPRESSION D’UNE BASE DE DONNEES
10.8LIRE LES INFOS. D’UNE BASE DE DONNEES
10.9LE JOURNAL DE TRANSACTION
10.10ROLES DES BASES DE DONNEES SYSTEME
10.10.1
44
10.10.2Résumé des tables systèmes de la base master
10.10.3Résumé des tables systèmes de la base model
10.10.4Résumé des tables systèmes de la base msdb
10.11LES OPTIONS D’UNE BASE DE DONNEES
10.12
TABLE
10.12.1Création d’une table
10.12.28.1.2 Modification d’une table
10.12.38.1.3 Types de données pré-définis
10.12.48.1.4 Types de Données Définis par l’Utilisateur (TDDU)
10.12.58.1.5 Les valeurs par défaut
10.12.6Contrainte DEFAULT (déclarative)
10.12.7CREATE DEFAULT (procédurale)
10.12.88.1.6 Les règles de validation
10.12.9Contrainte CHECK (déclarative)
10.12.10Contrainte CHECK (déclarative) suite
10.12.11CREATE RULE (procédurale)
10.12.12Attachement avec la règle (par code)
10.12.15 60
10.12.168.1.7 Les clés primaires et étrangères
10.12.17Les clés primaires
10.12.18Les clés étrangères
10.12.19La contrainte d’unicité
ALSI / S25
SGBD R Support de Cours 2TSIG
37
37
38
38
40
40
41
41
42
42
44
44
46
47
50
50
50
51
52
54
54
54
57
57
58
58
59
61
61
62
64
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 3
10.12.208.2 Vue
10.12.21Utilisation d’une vue
10.13
8.3 INDEX
10.13.1Création d’un index
10.13.2Visualiser les index
10.13.3Supprimer un index
10.148.4 LES PROCEDURES STOCKEES
10.14.1Création d’une procédure stockée
10.14.2UtIlisation d’une procédure stockée
10.158.5 LES DECLENCHEURS OU TRIGGER
65
67
67
67
67
68
68
68
70
70
119. SECURITE ET CONNEXION A UNE BASE SQL SERVER 6.5
76
11.19.1 TERMINOLOGIE DE LA SECURITE
11.29.2 MISE EN ŒUVRE DE LA SECURITE
11.2.19.2.1 Définir les options de sécurité du serveur
11.2.2
84
11.2.3Sécurité mode Standard
11.2.4Sécurité mode Intégré à Windows NT
11.2.5Sécurité mode Mixte
11.2.6Accès par défaut
11.2.7Domaine par défaut
11.2.8Modifier le nom d'hôte en nom d'utilisateur
11.2.9Niveau d'audit
11.2.10
87
11.2.11 Mappage
11.2.12Pour accorder un privilège utilisateur
11.2.13Pour accorder un privilège administrateur système
11.2.14Pour visualiser les détails d'un compte utilisateur
11.2.15Pour visualiser les détails d'un groupe
11.2.16Pour révoquer des privilèges
11.2.17Pour rechercher des informations de compte sur un utilisateur
11.2.18Pour rechercher les noms d'accès SQL Server
11.2.199.2.2 Définir les utilisateurs
11.2.20
97
11.2.21Ajouter un groupe à une base de données
11.2.22Ajouter un utilisateur à une base de données
11.2.23Gérer l'adhésion d'un utilisateur à un groupe
11.2.24Supprimer un utilisateur d'une base de données
11.2.25Supprimer un groupe d'une base de données
11.2.269.2.3 Gérer les permissions objet
11.2.27Accorder ou révoquer des permissions instruction à un utilisateur ou à un groupe
11.2.28 104
11.2.29Modifier le propriétaire d'une base de données
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
76
83
83
84
84
85
85
85
87
87
87
90
90
93
94
94
95
96
96
97
98
100
101
101
101
102
104
1
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 4
PRESENTATION DE LA SEQUENCE
1.1 Description des savoirs
251 Langage de définition de données
252 Langage de manipulation de données
253 Langage de définition des contraintes
254 Langage de définition des droits d’accès
255 Techniques de répartition des données : distribution, réplication
256 Administration de la base de données
1.2 Compétences attendues
 implanter, consulter, interroger, mettre à jour une BdD relationnelle
 administrer une BdD non répartie et en assurer la sécurité
 recenser, évaluer, comparer les solutions commerciales existantes
1.3 Prérequis
Ce module utilise les concepts abordés en 1ère année à savoir :
algèbre relationnelle
langage SQL (Standard et utilisé par ACCESS)
Query By Exemple (MS QUERY d’ACCESS)
L’outil logiciel utilisé pour illustrer ce module est SQL Server 6.5, néanmoins les points présentés sont applicables à n’importe quel SGBD R de type mini ou micro.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
2
Le 16/04/17
page 5
NOTIONS DE BASE
2.1 Définition
Une Base de Données est un ensemble structuré de données représentant une réalité extérieure au système,
partagée par plusieurs applications d’une même entreprise.
Exemple données : liste des inscrits, liste des cours, liste des enseignants
applications : gestion des inscriptions, planning des salles, octroi des bourses…
entreprise : école
Une base de données contient également les relations entre ces données.
Les logiciels qui exploitent ces bases de données sont appelés Système de Gestion de Base de Données.
2.2 Les motivations d’un SGBD
La principale raison d’être d’un SGBD est de fournir une description unique de toutes les données manipulées par l’entreprise. Cette centralisation « logique » des données répond à plusieurs objectifs :
 Réduire les redondances d’informations
 Eliminer les incohérences
 Permettre un partage plus souple entre les applications ; s’adapter aux besoins nouveaux
 Faciliter l’adoption de standard d’implantation
 Permettre une approche unifiée des problèmes de sécurité et d’intégrité
 Arbitrer plus facilement les conflits d’accès.
Le principal avantage de cette description logique unique est d’être indépendante des caractéristiques
d’implantation physiques et des choix d’organisation. Rendre les données indépendantes diminue le coût des évolutions de la base.
2.3 Architecture d’un SGBD
Programme
d’application
Programme
d’application
Programme
d’application
Sous modèle A
Programme
d’application
Sous modèle B
Modèle principal
Archivage
2.4 Les fonctions principales
2.4.1
Description de la base
La description de la base (modèle principal et sous-modèles) s’effectuent à l’aide d’un langage de définition de données : DDL.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 6
2.4.2
Manipulation de la base
La manipulation de la base : accès, mises à jour s’effectuent à l’aide d’un langage de manipulation de données : DML.
2.4.3
Utilisation de la base
L’utilisation de la base peut s’effectuer à l’aide du DML. En fait, le plus souvent, elle s’effectue à l’aide de
procédures stockées, par le biais de commandes.
2.5 Les différents types d’utilisateurs
L’administrateur principal de la base : le DBA
Son rôle est fondamental : c’est lui qui définit le modèle principal. Ses choix conditionnent l’évolution ultérieure de la base.
2.5.1
2.5.2
L’administrateur d’application
C’est lui qui décrit, à partir du modèle principal, les sous-modèles de l’application qu’il gère.
Le programmeur d’application
Son rôle est d’établir des bibliothèques de programme d’utilisation de la base : requètes courantes, mises à
jour interactives… C’est lui qui utilise le DML.
2.5.3
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
3
3.1.1
Le 16/04/17
page 7
LES LANGAGES DE DESCRIPTION DES DONNEES
SGBD basés sur les méthodes d’accès : hiérarchiques et réseaux
Première génération a utilisé les liens entre fichiers (techniques de pointeur), l’objectif est
l’optimisation des entrées/sorties. Les langages de programmation de ces SDBD sont qualifiés « navigationnel » car ils décrivent les chemins d’accès (liaisons) entre les données de la base. Ces SGBD sont encore
utilisés par des entreprises ayant un volume important d’applications développées autour de ce modèle.
3.1.2
SGBD basés sur le modèle Relationnel
Ce modèle a été élaboré par E.F. Codd dans les années 1970, celui-ci posa les bases de l’algèbre relationnelle. La commercialisation des premiers SGBD R date de 1980. Le succès de ce modèle est lié à l’utilisation de
règles précises (règles de Codd) voir plus loin, il offre une facilité de définition et de représentation des données,
par contre les performances d’accès sont jugées trop « moyennes ».
Depuis cette date la plupart des SGBD suivent (plus ou moins complètement) les règles définies par Codd.
3.1.3
SGBD avancés
On constate depuis quelques années une tendance qui vise à prendre en compte de nouveaux besoins en
matière d’applications nouvelles comme la CAO, la Bureautique, ou le génie logiciel. On parle de SGBD de troisième
génération, ils permettent des exploitations dans des «environnements» objets, hétérogènes, répartis ou parallèles.
Ces systèmes doivent encore évoluer avant de s’imposer face au SGBD R qui représente aujourd’hui près
de 80% du total des ventes de SGBD.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
4
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 8
LE MARCHE DES SGBD R
4.1 SGBD R sur mini et grand système
Le tableau ci-dessous résume les produits disponibles et le langage qu’ils utilisent.
Société / Marque
Nom du SGBD
Nom du L4G
SYBASE
SQL / SERVER
Powerbuilder
PROGRESS Software
PROGRESS
PROGRESS 4GL
ASK INGRES
INGRES
Windows 4GL
INFORMIX
INFORMIX ONLINE
I4GL
ORACLE CORPORATION
ORACLE Ver. 7
SQL Forms
HONEYWELL / BULL
MRDS
COMPUTER ASSOCIATES
IBM
CA / universe - DB DATACOM
DB2
SQL/DS
SQL / DBE
4.2 SGBD R sur micro-ordinateurs
Devant le succès de ce type de SGBD, certains produits possèdent des versions micro, mini et grossystème. Exemple Oracle avec :
Personal Oracle 7 pour Windows 3.1 et 95 (micro)
Oracle 7 Workgroup Server (Mini et serveurs départementaux)
Oracle 7 Universal Server (gros-systèmes).
4.3 SGBD R Serveurs sur micro-ordinateurs
Le propre d’un SGBD R Server est d’autoriser l’accès aux bases de données simultanément aux utilisateurs
de l’organisation. Cette possibilité n’est réalisable que si le SGBD R gère correctement les accès concurrents et offre
des mécanismes de contrôle des transactions (validation, annulation, etc.). L’évolution des configurations matérielles
« micro » (processeur, mémoire vive, stockage de masse) permet aujourd’hui la mise en oeuvre de solutions professionnelles jusqu’àlors réservées aux minis et grands systèmes.
De même les systèmes d’exploitation multi-utilisateurs récents comme Windows NT., Netware (Novell) ou
OS/2 (IBM) se prêtent bien aux applications développées autour d’un SGBD R, ils garantissent à la fois la sécurité et
la performance d’accès aux bases de données.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 9
4.4 SGBD R Clients sur micro-ordinateurs
L’offre SGBD R sur micro propose aujourd’hui plusieurs produits qui peuvent fonctionner soit en monoutilisateur soit dans un réseau local de type Workgroup, il ne s’agit pas d’un fonctionnement en mode clientserveur mais plutôt d’un partage de la base de données appelé « système en partage de fichiers ». Un mécanisme de verrouillage prend en charge les conflits d’accès entre utilisateurs et gère les verrous. Chaque poste prend en
charge son traitement. Certains de ces produits peuvent aussi se comporter comme frontal d’applications en mode
client-serveur.
Paradox / Ver. 4.5 à Ver. 7 (Windows 95 ou NT)
dBase / IV V version Windows de dBase V
ACCESS / Ver. 2.0 (Mai 94) Ver. 7.0 pour Windows 95
Windev / Ver.4.0 ou 5.0
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
5
Le 16/04/17
page 10
ALGEBRE RELATIONNELLE
5.1 Introduction
L’algèbre relationnelle est un outil théorique définit par Codd dans le cadre de son modèle relationnel.
Cette algèbre permet de construire des requêtes sur des relations. Le principe fondamental consiste à considérer que
le résultat d’une requête peut s’exprimer sous forme d’une relation. Cette relation est obtenue en appliquant successivement des opérateurs sur des opérandes qui sont des relations.
Ces opérandes sont soit des relations de la base de données soit le résultat d’opérations intermédiaires.
En algèbre relationnelle, on ne manipule que des relations comme données ou comme résultat.
Le langage SQL est un outil d’interrogation d’une BD relationnelle qui reprend, directement ou non, ces
opérateurs relationnels.
5.2 Définitions et notations
5.2.1
Relations n-aires
Soient D1,…Dn des ensembles non nécessairement disjoints. Une relation R définie sur D1,…Dn est un
sous-ensemble du produit cartésien D1x…Dn.
Une relation est donc un ensemble de n-uplets <a1,…an> où, pour chaque i, ai Di.
Comme, en informatique, on s’intéresse à des relations finies, la liste des n-uplets d’une relation donne
bien un tableau.
Il est commode, au lieu de les repérer par leur rang (ai) comme en mathématique, de donner des noms aux
colonnes, ce sont les attributs. L’ensemble des valeurs possibles d’un attribut (le Di correspondant) est appelé domaine.
Un descripteur de relation a la forme suivante :
R(A1 :d(A1),…An :d(An)) où R est le nom de la relation
Les Ai sont les noms des attributs de la relation
Les d(Ai) sont les domaines associés.
Remarque : on peut trouver 2 attributs sur le même domaine.
Par exemple : VOL(n : numéro, départ : ville, arrivée : ville)
5.2.2
Notations
Le plus souvent, les domaines ne nous intéressent pas et nous les omettrons.
Pour désigner :
 les attributs : on utilise les premières lettres de l’alphabet en majuscule
 les relations : on utilise les dernières lettres de l’alphabet en majuscule
 le contenu d’une relation : on utilise le nom de la relation en minuscule
 un n-uplet : x = <d1, d2, d3> ou x = <A1 :d1, A2 :d2, A3 :d3>
5.2.3
Clés et super-clés
Un clé, pour une relation R, est un ensemble d’attributs identifiant une ligne. Plus précisément :
Soit R(X,Y), X est une clé pour R si et seulement si
x, <x,y>  r  il n’existe pas y’  y tq <x, y’>  r.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 11
Une super clé est une clé minimum (il peut y en avoir plusieurs).
5.2.4
Base de données
Une base de données est un triplet (A, R, Y) où
 A est un ensemble d’attributs
 R est un ensemble de relations sur ces attributs
 Y est un ensemble de contraintes d’intégrité
Les contraintes d’intégrités sont spécifiées pour traduire le fait que toutes les valeurs que peuvent prendre
les relations ne représentent pas forcément une réalité du monde extérieur.
Ex : toute personne est de sexe masculin ou féminin, toute personne a une mère au plus,…
5.3 Opérateurs relationnels
5.3.1
Projection
Soit R(X, Y), la projection de R sur Y, notée PROJECTION(R, Y), est définie par
<y>  PROJECTION(r ; Y) ssi  x tq <x, y>  r.
Intuitivement : on supprime les colonnes non retenues dans la projection et on élimine les doubles.
5.3.2
Jointure
Soient S(X, Z) et R(Z,Y), la jointure de S et R, notée JOINTURE(S, R), est définie par
<x, y, z>  JOINTURE(s, r) ssi <x, z>  s.et <z, y>  r.
Intuitivement : pour chaque n-uplet <x,z> de S, on construit autant de n-uplet <x, y, z> qu’il y a de n-uplet
<z, y> dans R.
5.3.3
Equi-jointure
C’est une jointure entre 2 attributs pour lesquels les opérateurs tels que =, , , ,… ont un sens.
L’équi-jointure est utile pour combiner une jointure où les attributs ont des noms différents.
5.3.4
Sélection
Soient R(X) une relation, et B(X) un prédicat applicable à tous les n-uplets de R, la sélection de R par B,
notée SELECTION(R ; B), est définie par
<x >  SELECTION(R ; B) ssi <x>  r.et B(x).
Intuitivement : on balaie la relation en ne conservant que les lignes qui vérifient B.
5.3.5
Division
Soient R(X, A) et S(B,Y) avec A et B compatibles (sur le même domaine), la division de R par S, notée
DIVISION(R, S, A, B) est une relation sur X définie par
<x >  DIVISION(r, s ;A, B) ssi  a  PROJECTION(s ;B) <x, a>  r.
Intuitivement : on se débarasse de Y dans S, ce qui donne un ensemble E de valeurs  B. On cherche alors
dans R l’élément x en relation avec tous les éléments de E.
5.3.6
Opérations ensemblistes
Soient S(X) et R(X). Comme ce sont 2 parties du même ensemble, on peut parler de
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 12
SR
union
SR
intersection
S-R
SxR
différence ensembliste
produit cartésien. Soient R(X, Z) et S(Z,Y), le produit cartésien est défini par
<x, z’, z’’, y>  à S x R ssi <x, z’ >  s et <z’’, y>  r.
5.3.7
Exemple d’interrogations
Q1 : Trouver le numéro des fournisseurs qui me fournissent au moins un article
Q2 : Trouver le numéro des fournisseurs qui ne me fournissent aucun article
Q3 : Trouver le numéro des fournisseurs qui me fournissent P6
Q4 : Trouver le numéro des fournisseurs qui me fournissent autre chose que P6
Q4’ : Trouver le numéro des fournisseurs qui me fournissent quelque chose mais pas P6
Q5 : Trouver le numéro des fournisseurs qui ne fournissent que P6
Q6 : Trouver le numéro des fournisseurs qui me fournissent chacun au moins P4, P5 et P6
Q7 : Trouver le nom et la ville des fournisseurs qui me fournissent tous les produits originaires de Dijon
Q8 : Trouver les numéros des fournisseurs qui me fournissent au moins 2 produits.
Q9 : Trouver les numéros de produits fournis par Bornibus
Q10 : Trouver les noms de produits fournis par Bornibus
Q11 : Trouver les noms de fournisseurs qui fournissent au moins un produit vert
Q12 : Trouver les noms de fournisseurs qui fournissent au moins un produit originaire de leur ville
Q13 : Trouver les noms et villes des fournisseurs qui ne fournissent que des produits verts
Q14 : Trouver les numéros des fournisseurs qui fournissent au moins un produit vert et un produit blanc
Q15 : Trouver les numéros des fournisseurs qui fournissent tous les produits verts
Q16 : Trouver les noms et villes des fournisseurs qui font une remise supérieure à celle de Bornibus
Q17 : Trouver les numéros de fournisseurs qui ne fournissent que des produits originaires de leur ville
Q18 : Trouver les numéros de fournisseurs qui fournissent tous les produits originaires de leur ville
Q19 : Trouver les numéros de fournisseurs dont tous les produits fournis sont verts.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
6
Le 16/04/17
page 13
NORMALISATION
Une BD ne peut pas être construite autour d’une relation unique (relation universelle) car ceci pose des
problèmes de redondance, d’anomalies lors des mises à jour…
Il faut donc décomposer la relation en
 Préservant son contenu,
 Préservant les dépendances,
 Trouvant les meilleures qualités possibles pour les relations : les formes normales.
6.1 Exemple
Soit la relation GEO (BAR, PAYS, CONTINENT).
Les contraintes d’intégrité sont les suivantes :
 Un bar n’appartient qu’à un seul pays
 Un pays n’est situé que dans un seul continent.
Exemple de contenu :
GEO BAR
PAYS
1
France
3
USA
2
Allemagne
4
France
5
USA
CONTINENT
Europe
Amérique
Europe
Europe
Amérique
6.2 Définitions
6.2.1
Dépendances fonctionnelles
Le monde représenté dans la base de données est soumis à certaines règles : les pays n’appartiennent qu’à
un seul continent…Les données stockées dans la base doivent respecter ces règles : ce sont les contraintes
d’intégrités.
La plupart des contraintes d’intégrité peuvent être modélisées à l’aide de dépendances fonctionnelles (DF).
Ex : les pays n’appartiennent qu’à un seul continent s’exprime par la DF PAYSCONTINENT.
Il existe une dépendance fonctionnelle entre X et Y, notée XY si et seulement si
 (x, y)

R et  (x, y’)  R alors y = y’.
En français : quand on connaît x, on connaît y.
6.2.2
Schéma relationnel
Les DF expriment des contraintes qui doivent être vérifiées quelque soit le contenu des relations.
On appelle schéma relationnel d’une relation R, notée R(U, F),
l’ensemble U des attributs de R et
l’ensemble F des dépendances fonctionnelles applicables à R.
6.3 Préserver le contenu
On décompose la relation unique en plusieurs relations. Le contenu de chaque relation « fille » est obtenu
en projetant la relation « mère » sur les attributs de la relation « fille ».
En recomposant par jointure les relations « filles », on doit obtenir la relation mère.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 14
Théorème de décomposition sans perte :
Soit une relation R(X, Y, Z) qui possède une dépendance fonctionnelle XY.
R est décomposable sans perte en
R1 = PROJECTION(R ; X, Y) et
R2 = PROJECTION(R ; X, Z) .
C’est à dire R = JOINTURE(R1, R2 ; R1.x = R2.x)
6.4 Préserver toutes les dépendances
Quand on décompose une relation R en R1, R2…, les contenus des projections doivent aussi respecter les
dépendances fonctionnelles.
Une décomposition préserve les dépendances fonctionnelles si on peut retrouver les dépendances fonctionnelles et les dépendances fonctionnelles logiquement impliquées.
Les dépendances fonctionnelles logiquement impliquées sont les nouvelles dépendances fonctionnelles obtenues en appliquant les axiomes d’Armstrong à l’ensemble des dépendances fonctionnelles F. Ce nouvel ensemble
est noté F+.
Les axiomes d’Armstrong :
Réflexivité
Y  X  U  XY
Augmentation
XY et Z  U  XZYZ
Transitivité
XY et YZ  XZ
Choisir une décomposition qui préserve les DF permet de limiter les contrôles d’intégrité à l’intérieur de
chaque relation de la décomposition.
Si les DF applicables aux relations sont toutes de la forme cléY, tous les contrôles sont pris en charge
par le SGBD sinon il faut contrôler toutes les mises à jour de données et donc effectuer les jointures nécessaires pour
vérifier l’intégrité des données à chaque mise à jour.
6.5 Les formes normales
6.5.1
Définitions
Un attribut A d’une relation R est primitif s’il appartient à au moins une clé minimum de R.
Un attribut A d’une relation R est non primitif s’il n’appartient à aucune clé minimum de R.
Soit XA, une DF de F+.
L’attribut A est pleinement dépendant de X
s’il n’existe pas X’ partie de X telle que X’A soit une DF de F+.
Un attribut A est transitivement dépendant d’un ensemble d’attributs X
s’il existe Y ne contenant pas A tel que XY et YA.
Un attribut A est directement dépendant d’un ensemble d’attributs X
s’il n’est pas transitivement dépendant de X.
6.5.2
Première forme normale : 1NF
Une relation est en première forme normale (1NF) si aucun attribut qui la compose n’est lui-même une relation (Toute valeur d’attribut est atomique).
6.5.3
Deuxième forme normale : 2NF
Une relation est en deuxième forme normale (2NF) si elle est en première forme normale (1NF) et tous les
attributs non primitifs sont pleinement dépendants d’une clé minimum.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 15
6.5.4
Troisième forme normale : 3NF
Une relation est en troisième forme normale (3NF) si elle est en deuxième forme normale (2NF) et tous les
attributs non primitifs sont directement dépendants d’une clé minimum.
Définition équivalente :
Une relation R est en 3NF
 si elle est en 1NF et
 pour toute DF XA s’appliquant à R (X n’inclut pas A), X est une clé minimum de R ou
A est primitif.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
7
Le 16/04/17
page 16
LE LANGAGE SQL AVANCE
7.1 Le standard SQL
SQL (Structured Query Langage) a une triple fonction :
 Interrogation et modification des données d’une base de donnée relationnelle
 Définition et modification du schéma d’une base de donnée relationnelle
 Contrôle de la sécurité et de l’intégrité de la base.
La norme définit 2 langages de SQL :
 SQL-LDD : utilisé pour modifier le schéma de la BD (création de table, ajout de colonne dans une
table, création d’index…) et contrôler l’accès aux données (ajout ou suppression des droits d’accès
des utilisateurs sur un objet de la BD). Ces fonctions de gestion d’accès aux données peuvent être regroupées en tant que Langage de contrôle des données : SQL-LCD.
 SQL-LMD : utilisé pour interroger et modifier les données de la base. On peut distinguer les fonctions d’interrogation en tant que Langage d’interrogation des données : SQL-LID.
Principales fonctions du langage SQL :
LMD
LID
SELECT
INSERT
UPDATE
DELETE
LDD
LCD
GRANT
REVOKE
CREATE
ALTER
TRUNCATE
DROP
Chaque SGBD R utilise une syntaxe un peu différente. On utilise ici la syntaxe Transact-SQL du SGBD relationnel SQL Server.
7.2 SQL : Langage de manipulation de données
7.2.1
Interrogation des données
L’interrogation des données se fait par l’ordre SELECT dont la syntaxe est la suivante :
SELECT [ALL | DISTINCT] {* | liste_expressions}
[FROM liste_objet]
[WHERE condition]
[GROUP BY liste_expression]
[HAVING condition]
[ORDER BY liste expression]
7.2.2
Sélection des colonnes, projection
L’ordre SELECT permet d’introduire une liste de colonnes à extraire des tables ou toutes les colonnes avec
le signe *.
Les colonnes apparaissent dans l’ordre spécifié par le SELECT ou dans l’ordre de création dans le cas
d’une *.
En cas d’ambiguïté dans les noms de colonnes, on peut utiliser la forme : table.colonne.
La colonne résultante porte le même nom que la colonne de la table sauf si on utilise un alias de colonne.
Par défaut, les doublons apparaissent dans le résultat. Pour faire une projection et éviter les doublons, on
utilise SELECT DISTINCT.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 17
Exemple : Projection des villes des clients.
SELECT DISTINCT vilcli Ville
FROM client
Ville
-------------------------------------------------Brest
Quimper
7.2.3
Sélection (des lignes), restriction
La restriction consiste à n’extraire qu’un certain nombre de lignes répondant à une condition. La clause
WHERE condition permet la mise en œuvre des restrictions.
Les conditions sont des expressions booléennes composées de nom de colonnes, de constantes, de fonctions, d’opérateurs de comparaisons (=, <, >…) et d’opérateurs logiques (AND, OR…).
Exemple : Numéros et noms des clients habitant Brest.
SELECT numcli, nomcli
FROM client
WHERE vilcli = ‘Brest’
numcli
----------1
3
nomcli
-------------------------------------------------joujou
nanar
L’opérateur de comparaison LIKE permet de filtrer une chaîne de caractères suivant un masque spécifié.
Le masque peut être composé de
un caractère quelconque
%
N caractères quelconques
[abc] un caractère dans la liste abc
[0-9] un caractère dans l’intervalle 0-9
[^abc] un caractère en dehors de la liste abc. On peut aussi utiliser la négation pour les intervalles.
Pour utiliser -, %, ^ ou [ en tant que caractère de recherche, il faut les encadrer par [ ].
Exemple : Numéros et noms des clients habitant une impasse.
SELECT numcli, nomcli
FROM client
WHERE voie LIKE ‘%impasse%’
numcli
nomcli
----------- -------------------------------------------------5
toto
La valeur null ne peut pas être testée par un opérateur de comparaison. On utilise la fonction IS NULL.
Exemple : Numéros et noms des agences sans directeur.
SELECT numage, nomage
FROM agence
WHERE numdir IS NULL
numage
nomage
----------- -------------------------------------------------2904
quimper centre
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 18
7.2.4
Extension
L’extension consiste à afficher une valeur n’appartenant pas à la table mais calculée à partir des colonnes
de la table. On insère alors au niveau du SELECT l’expression de calcul de cette valeur.
Le calcul est réalisé par les opérateurs arithmétiques (+, -, *, /, % et parenthèses), les opérateurs logiques
(AND, OR, NOT) ou les fonctions de manipulation de chaque type de données fournies par le SGBD :
Fonctions mathématiques :
ABS(exp)
Valeur absolue de exp
CEILING(exp)
Plus petit entier supérieur ou égal à exp
FLOOR(exp)
Plus grand entier inférieur ou égal à exp
trigonométriques : SIN pour le SINUS, COS pour le COSINUS…
logarithmiques : LOG pour logarithme népérien, EXP pour exponentielle…
Fonctions sur les dates :
Les fonctions sur les dates manipulent des expressions de type DATETIME et utilisent des formats représentant la partie de date à gérer. Ces formats sont :
Format
Year
Quarter
Month
Day of year
Day
WeekDay
Abréviation
Yy
Qq
Mm
Dy
dd
Dw
Hour
Minute
Seconds
Milliseconds
Hh
Mi
ss
ms
Signification
Année de 1753 à 9999
Trimestre (1 à 4)
Mois (1 à 12)
Jour dans l’année (1 à 366)
Jour dans le mois (1 à 31)
Jour dans la semaine (1 Lundi à 7
Dimanche)
Heure (0 à 23)
Minute (0 à 59)
Seconde (0 à 59)
Milliseconde (0 à 999)
GETDATE()
DATENAME(fmt, date)
DATEPART(fmt, date)
DATEDIFF(fmt, date1, date2)
DATEADD(fmt, n, date)
Renvoie la date et l’heure système
Renvoie la date sous forme de texte au format fmt.
Renvoie la valeur de date au format fmt.
Différence selon fmt entre les 2 dates.
Ex : Différence exprimée en jours si fmt = dd
Ajoute n fmt à la 2 dates. Ex : ajoute n jours si fmt = dd
Fonctions sur les chaînes de caractères :
ASCII(exp)
CHAR(exp)
LTRIM(exp) et RTRIM(exp)
Les fonctions usuelles :
Valeur du code ASCII du 1er caractère de exp
Caractère correspondant au code ASCII exp
Supprime les espaces non significatifs respectivement à
gauche ou à droite de exp.
STR(exp, lg, nbd)
Convertit le nombre exp en chaine de longueur totale lg
avec nbd décimales.
SPACE(n)
Renvoie n espaces
REPLICATE(exp, n)
Renvoie n fois exp
LOWER(exp) et UPPER(exp) Convertit exp en minuscule ou en majuscule (respectivement).
SUBSTRING (exp, dp, lg)
Renvoie lg caractères de exp à partir de dp.
Fonctions système : Il existe une liste de fonctions permettant d’avoir le nom de la base, de la station de
travail, de l’utilisateur…
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 19
Exemple : Numéros et noms des clients concaténés (sans blancs après le numéro de client).
SELECT ltrim(str(numcli)) + ' ' + nomcli Client
FROM client
Client
------------------------------------------------------------1 joujou
3 nanar
2 robi
5 toto
L’expression de calcul apparaît donc aussi en entête de colonne et on utilise le principe de renommage de
colonne par un alias pour obtenir un libellé plus clair.
Calculs d’agrégats
Les calculs statistiques portant sur des partitionnements de lignes ou sur toute une sélection sont faits en
utilisant les fonctions statistiques (COUNT, SUM, AVG, MIN et MAX) et éventuellement la clause GROUP BY. La
clause HAVING permet de tester une condition pour limiter les groupes résultats.
7.2.5
Exemple : Numéro de client et montants mini, moyen et maxi des opérations de chaque client
SELECT numcli, mini=MIN(montant), moyen=AVG(montant), maxi=MAX(montant)
FROM opere
GROUP BY numcli
numcli
----------1
2
3
mini
----------223
32
1000
moyen
----------691
560
4500
maxi
----------1000
1560
9500
7.2.6
Produit cartésien
Le produit cartésien permet d’extraire des données de plusieurs tables en associant chaque ligne de chaque
table citée. Les tables concernées doivent être séparées par des virgules dans la clause FROM.
Si on veut citer le même nom de colonne venant de 2 tables différentes, celui-ci doit être précédé du nom
de la table ou du nom d’alias afin qu’il n’y ait pas d’ambiguïté.
Exemple : Produit cartésien des employés et des agences
SELECT A. numage, E. numage, nomage, nomemp
FROM agence A, employe E
numage
----------2901
2901
2901
2901
2901
2901
2902
2902
2902
2902
2902
2902
2903
2903
2903
2903
2903
2903
2904
2904
2904
2904
2904
2904
ALSI / S25
numage
----------2901
2901
2901
2902
2902
2902
2901
2901
2901
2902
2902
2902
2901
2901
2901
2902
2902
2902
2901
2901
2901
2902
2902
2902
nomage
-------------------------------------------------brest centre
brest centre
brest centre
brest centre
brest centre
brest centre
quimper gare
quimper gare
quimper gare
quimper gare
quimper gare
quimper gare
quimper meable
quimper meable
quimper meable
quimper meable
quimper meable
quimper meable
quimper centre
quimper centre
quimper centre
quimper centre
quimper centre
quimper centre
SGBD R Support de Cours 2TSIG
nomemp
-------------requin
nigaud
glandu
lebon
labrute
letruand
requin
nigaud
glandu
lebon
labrute
letruand
requin
nigaud
glandu
lebon
labrute
letruand
requin
nigaud
glandu
lebon
labrute
letruand
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 20
7.2.7
Jointure
La jointure est la combinaison d’un produit cartésien et d’une restriction. Elle permet d’associer logiquement des lignes de tables différentes. Les jointures sont utilisées pour faire correspondre les données d’une ligne
comportant une clé étrangère avec les données de la ligne comportant la clé primaire (jointure naturelle).
Exemple : Liste des employés par agences
SELECT A. numage, nomage, nomemp
FROM agence A, employe E
WHERE A.numage = E.numage
numage
----------2901
2901
2901
2902
2902
2902
nomage
-------------------------------------------------brest centre
brest centre
brest centre
quimper gare
quimper gare
quimper gare
nomemp
----------------requin
nigaud
glandu
lebon
labrute
letruand
7.2.8
Jointure externe
Dans une jointure naturelle, aucune ligne n’apparaît dans le résultat lorsque la condition n’est pas satisfaite.
Les jointures externes permettent d’extraire les lignes d’une des 2 tables concernées même si la condition
est fausse. Dans ce cas, les données de la 2ème table ont la valeur NULL.
La syntaxe utilisée pour la condition est
Nom_table1.col *= Nom_table2.col ou Nom_table1.col =* Nom_table2.col
Selon que l’on veut voir les lignes de la 1ère table (*=) ou de la 2ème table (=*).
Exemple : Liste de tous les clients et des agences où ils se rendent éventuellement
SELECT client.numcli, nomcli, numage
FROM client, va
WHERE client.numcli *= va.numcli
numcli
----------1
3
2
2
5
nomcli
-------------------------------------------------joujou
nanar
robi
robi
toto
numage
----------2901
2902
2901
2902
(null)
7.2.9
AutoJointure
Il est possible d’associer des lignes d’une table à d’autres lignes de la même table en réalisant une autojointure. L’utilisation des alias de table est alors obligatoire pour éviter les ambiguïtés de syntaxe.
Exemple : Liste des employés et de leur directeur d’agence
SELECT E.numemp, substring(E.nomemp, 1, 10) nomemp, E.numage,
D.numemp numdir, D.nomemp nomdir
FROM employe E, agence A, employe D
WHERE E.numage = A.numage
AND A.numdir = D.numemp
AND E.numemp != D.numemp
numemp
----------2
4
11
12
ALSI / S25
nomemp
---------requin
nigaud
lebon
labrute
numage
----------2901
2901
2902
2902
SGBD R Support de Cours 2TSIG
numdir
----------7
7
13
13
nomdir
--------------------------------glandu
glandu
letruand
letruand
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 21
7.2.10
Union
L’opérateur UNION permet d’obtenir un ensemble de lignes provenant de plusieurs requêtes. Toutes les
requêtes doivent fournir le même nombre de colonnes et les colonnes doivent être de même type.
Exemple : Liste des employés et des clients
SELECT numemp num, substring(nomemp, 1, 10) nom
FROM employe E
UNION
SELECT numcli num, nomcli nom
FROM client
num
----------1
2
2
3
4
5
7
11
12
13
nom
-------------------------------------------------joujou
requin
robi
nanar
nigaud
toto
glandu
lebon
labrute
letruand
7.2.11
Division
L’opérateur relationnel de division n’existe pas en SQL. En effet, la division algèbrique concerne la prise
en compte du quantificateur universel Quelquesoit qui n’existe pas en SQL. SQL ne connaît que le quantificateur
existentiel Il existe.
1ère solution
Pour exprimer Quelquesoit x, P(x) est vrai , on utilise la double forme négative :
Il n’existe pas x tel que P(x) est faux
Soit 2 tables R1(A,B) et R2(A)
La division de R1 par R2 donne la table R3(B) telle que :
R3(B) = {b appartenant à R1(B) tel que
Quelquesoit a2 appartenant à R2
Il existe (a1, b) appartenant à R1 tel que a1 = a2}
Autrement dit : b appartient à R3 si et seulement si
Il n’existe pas de a2 appartenant à R2 tel qu’
Il n’existe pas de (a1, b) appartenant à R1 tel que a1 = a2.
En conclusion, la division relationnelle se traduit par la succession de 2 opérateurs NOT EXISTS.
Exemple : Liste des comptes et des clients qui possèdent un compte de chaque intitulé
SELECT P3.numcli, P3.numcom, intitule
FROM possede P3, compte R3
WHERE P3.numcom = R3.numcom
and not exists (SELECT intitule from compte R2
Where not exists
(SELECT P1.numcli, P1.numcom, R1.intitule
FROM possede P1, compte R1
WHERE P1.numcom = R1.numcom
AND R2.intitule = R1.intitule
AND P3.numcli = P1.numcli) )
numcli
----------1
1
1
ALSI / S25
numcom
----------1
2
3
SGBD R Support de Cours 2TSIG
intitule
-------------------------------------------------compte cheque
livret d'epargne
plan epargne logement
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 22
2ème solution
Une autre façon de traduire la division est basée sur l’utilisation de la fonction de comptage COUNT.
Soit 2 tables R1(A,B) et R2(A)
On compte de nombre d’éléments a dans R2.
On compte le nombre de a différents pour chaque b de R1.
On compare les 2. En cas d’égalité, on retient b dans R3.
Exemple : Liste des clients qui possèdent un compte de chaque intitulé
SELECT P3.numcli
FROM possede P3, compte R3
WHERE P3.numcom = R3.numcom
GROUP BY P3.numcli HAVING COUNT(DISTINCT P3.numcom) =
(SELECT COUNT(distinct intitule) FROM compte)
numcli
----------1
7.2.12
Sous interrogation ramenant 1 ligne
Elle permet de comparer une valeur à la valeur retournée par un ordre SELECT.
Décomposition de la sous-interrogation :
1. La sous-interrogation est indépendante de l’interrogation principale
2. Le noyau évalue la sous-interrogation puis l’interrogation principale avec le résultat trouvé.
Exemple : Liste des opérations dont le montant est supérieur au plus petit solde de compte positif
SELECT numcli, numcom, montant
FROM opere
WHERE montant > (SELECT min(solde) FROM possede WHERE solde > 0)
numcli
----------1
1
1
3
3
3
1
2
numcom
----------1
1
1
10
10
12
1
5
montant
----------500
1000
800
9500
1000
3000
936
1560
7.2.13
Sous interrogation ramenant n lignes
Dans ce cas, il faut utiliser un opérateur permettant de comparer une valeur à une liste de valeurs : IN.
L’opérateur IN peut être nié par NOT.
Exemple : Numéro, nom et villes des agences qui n’ont pas d’employé
SELECT numage, nomage, vilage
FROM agence
WHERE numage NOT IN (SELECT numage FROM employe)
numage
----------2903
2904
nomage
-------------------------------------------------quimper meable
quimper centre
vilage
-------------------------quimper
quimper
7.2.14
Sous interrogation synchronisée
La sous-interrogation fait référence à une colonne de l’interrogation principale. Ceci nécessite la réévaluation de la sous-interrogation pour chaque ligne principale. Dans certains cas, il est nécessaire d’utiliser des alias pour
renommer une des tables.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 23
Exemple : Liste des directeurs d’agence qui ne travaillent pas dans leur agence
SELECT numdir
FROM agence A
WHERE A.numage != (SELECT numage FROM employe E
WHERE A.numdir = E.numemp)
numdir
----------4
Pour chaque directeur, la sous-interrogation est réévaluée.
Sous interrogation testant l’existence
Une des formes particulières de sous-interrogation synchronisée est celle testant l’existence de lignes de
valeurs répondant à une condition.
Le mot-clé EXIST est placé à cet effet devant une sous-interrogation.
Cet opérateur renvoie le résultat VRAI si la sous-interrogation ramène au moins 1 ligne de valeurs, faux sinon. Dans ce cas, l’interrogation principale s’exécute.
L’opérateur EXIST peut être nié par NOT.
7.2.15
Exemple : Liste des agences qui ont des comptes
SELECT numage, nomage
FROM agence A
WHERE EXISTS (SELECT numage FROM compte
WHERE A.numage = compte.numage)
numage
----------2901
2902
7.2.16
nomage
-------------------------------------------------brest centre
quimper gare
Requètes de création de tables
Il est possible de créer une nouvelle table à partir d’une requête en utilisant la syntaxe :
SELECT … INTO nom_table FROM …
La nouvelle table aura le schéma correspondant aux colonnes extraites ; dans le cas de colonnes calculées,
il faut donner un alias de colonne.
Si le nom de la table est précédé de #, la table devient temporaire locale. Elle n’est accessible que par la
session qui l’a créé et disparaît à la déconnexion.
Si le nom de la table est précédé de ##, la table devient temporaire globale. Elle est accessible par toutes
les sessions et elle est supprimée à la fin de la dernière session qui l’a utilisée.
Les tables temporaires sont stockées dans la base tempdb.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 24
7.3 SQL : Langage de définition de données
7.4 SQL : Langage de contrôle de données
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
8
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 25
COMPOSANTS D’UNE BASE DE DONNEES RELATIONNELLE (RAPPELS)
8.1 Table
Les données sont stockées sous la forme de tables composée de lignes et de colonnes, chaque colonne représente un attribut de la relation, chaque ligne une occurrence ou enregistrement (n-uplet). Le nombre de ligne n’est
limité que par la capacité du disque.
8.2 Index
Ce sont des tables dont le rôle est d’accélérer les recherches de données. Le moteur SQL utilise ces tables
selon les besoins de la requête. Le recours à un nombre d’index trop important dégrade les performances car il faut
mettre à jour ces tables lors des opérations d’insertion ou de suppression, il faut donc trouver le bon compromis.
Il existe plusieurs type de gestion des index :
 index ISAM (Indexed Sequential Access Method = Accès Seq. Indexé)
 index HASH (Hash Coding = Index calculé)
 index B-TREE (Balanced Tree = arbre équilibré)
SQL Server utilise une technique d’index de type B-TREE
8.3 Vue
Une vue est une table virtuelle définie grâce à une requête SQL utilisant les données d’une table (réelle),
elle n’occupe donc pas de place sur disque, elle est souvent utilisée pour limiter l’affichage des données aux utilisateurs.
8.4 Clés et relations
Les règles de Codd imposent l’utilisation de clés primaires et étrangères ainsi que des contraintes sur
ces clés. (Voir parag. 3.6)
La clé primaire assure l’intégrité d’entité c.a.d. que chaque ligne de la table est identifiée de manière
unique, la valeur de la clé est donc unique et toujours définie (NULL non autorisé)
La clé étrangère quant à elle peut accepter une valeur indéfinie (NULL autorisé), grâce à cette clé la contrainte d’intégrité référentielle est assurée. Une clé étrangère est reliée soit à une clé primaire soit à une clé secondaire
8.5 Autres composants de la base de données
D’autres objets viennent s’ajouter à la base de données :
 les procédures stockées
 les Types de Données Définis par l’Utilisateur (TDDU)
 les règles et valeurs par défaut
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 26
8.6 Les différents types d’intégrité
La cohérence des informations enregistrées dans la base repose sur des règles d’ingrégrité qui sont mis en
oeuvre par le moteur SQL.
Quatre types d’intégrité sont gérés :
L’intégrité d’entité
Vérifie l’unicité de chaque ligne de la table, une ou plusieurs colonnes contiennent des valeurs uniques
donc identifiantes (clé primaire).
8.6.1
L’intégrité référentielle
Elle concerne les liaisons entre les tables, lors des opérations de mise à jour, d’insertion ou de suppression
de ligne. Le mécanisme de référence est assurée en comparant les clés primaires et étrangères.
8.6.2
Cela évite :
 de supprimer des lignes d’une table parent alors qu’il existe des lignes associées dans la table enfant.
(ACCESS autorise la suppression en cascade). Exemple : supprimer une ligne de Client alors qu’il
reste des lignes Commande pour ce client.
 d’insérer des lignes dans une table enfant qui ne posséderait pas de lignes associées dans la table parent. Exemple : insérer des lignes Commande sans relation avec un Client existant.
 de modifier des colonnes dans la table parent ce qui générerait des « orphelins » dans la table enfant.
Exemple : modifier la valeur d’un N°Client qui possède des Commandes en cours.
8.6.3
L’intégrité de domaine
Son rôle est de contrôler les valeurs saisies dans les colonnes de table, plusieurs mécanismes coopèrent :
 le type de donnée, prédéfini, une colonne de type entier ne peut contenir une chaîne de caractères,
 la valeur par défaut, en l’absence d’une saisie, la colonne prendra une valeur définie afin d’éviter
que celle-ci reste indéfinie,
 la règle de validation, exemple une colonne doit appartenir à l’intervalle 5 à 25%,
 la clé étrangère, la valeur de la colonne est contrôlée automatiquement en faisant appel à la table
référencée.
L’intégrité définie par l’utilisateur
Elle concerne les contrôles mis en place dans une organisation pour vérifier qu’une donnée est cohérente
par rapport à une autre ou à une règle de gestion. Exemple : une entreprise n’acceptera une nouvelle commande d’un
client si le compte de celui-ci possède un solde débiteur > 15 ooo frs.
8.6.4
8.7 Mettre en oeuvre l’intégrité
Deux possibilités existent : l’intégrité déclarative et l’intégrité procédurale
8.7.1
l’intégrité déclarative
La « déclaration » est mis en place lors de la création ou de la modification de structure de la table.
l’intégrité procédurale
Certains objets externes aux tables sont chargés de contrôles divers, un même objet peut bien sûr, être mis
en oeuvre sur plusieurs tables.
8.7.2
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
SQL Server et l’intégrité
Intégrité déclarative
Intégrité de domaine
Type de données, contrainte DEFAULT,
contrainte CHECK
Intégrité d’entité
contrainte PRIMARY
KEY, contrainte
UNIQUE
intégrité référentielle
Contrainte REFERENCE, FOREIGN
KEY
Intégrité utilisateur
impossible
ALSI / S25
SGBD R Support de Cours 2TSIG
Le 16/04/17
page 27
intégrité procédurale
Règle, valeur par défaut
Index unique, déclencheurs
Déclencheurs
Déclencheurs, procédures
stockées, vues
D:\582659791.doc
9
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 28
LE MODELE CLIENT-SERVEUR (APPROCHE)
Il se distingue des applications fonctionnant en réseau ou en multi-utilisateur par le fait que les clients ne
gèrent que la partie logique d’affichage, ils s’adressent au serveur pour effectuer les traitements.
9.1 4.1 Organisation de type Mainframe
Organisation des années 70, où l’ordinateur central assurait à la fois la totalité des traitements et le stockage des données. Les utilisateurs avaient à leur disposition des terminaux passifs (consoles clavier-écran) relié au
Mainframe par un réseau local. Plus le nombre de terminaux est important moins les performances sont bonnes. Cette
architecture est assez coûteuse (coût de fonctionnement et de maintenance). Par contre les systèmes d’exploitation
propriétaires sont fiables et puissants.
9.2 4.2 Organisation en serveur de fichiers
L’arrivée des micro-ordinateurs (1980) a modifié cette organisation. La possibilité d’utiliser les micro en
émulation de terminaux (moins chers) et comme machine autonome pour la Bureautique a séduit les entreprises. Avec
eux sont apparus les réseaux locaux et la nécessité de partager et faire circuler l’information.
Les systèmes d’exploitation pour réseaux locaux comme Netware de Novel, Lantastic, Windows Workgroups étaient le support privilégié pour les produits dBase, FoxPro, Access, Paradox.
Avec cette architecture en serveur de fichiers, les données sont stockées sur le serveur, les applications sur
chaque poste client ; dès qu’une requête est émise au serveur, toutes les données sont transmises au poste client afin
d’y être traitées. Le réseau est donc encombré inutilement. Aussi pour éviter cet inconvénient, les éditeurs de SGBD
ont cherché à transposer leurs moteurs SGBD vers ces serveur qui devenaient serveurs de données.
9.3 4.3 Organisation en serveur de données
Les serveurs de données s’inspirent donc de ces deux architectures et les traitements de données sont répartis entre le client et le serveur. Ainsi une requête émise par un client sur une table de 10 000 lignes située sur le serveur, est traitée sur le serveur qui ne renvoit au client que le résultat de cette requête. Ce dernier prenant à sa charge
les traitements d’affichage de mise en forme ou de calculs complémentaires.
Parmi les produits « micro » seul SQL Server permet cette architecture client-server, les produits comme
FoxPro, dBase, Access se comportent en serveur de fichiers lorsqu’ils sont utilisés seuls (car il n’y a pas de système
de traitement sur le serveur). Par contre ces produits offrent la possibilité de travailler avec SQL Server grâce aux
logiciels appelés middleware c.a.d. médiateur entre le client et le serveur.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 29
9.4 4.4 Le Middleware
C’est la couche logicielle qui assure la communication entre le client et le serveur.
Plusieurs objectifs lui sont assignés :
 Envoyer les requêtes au serveur et transporter les résultats jusqu’au client,
 Transformer les types de données si nécessaire (convertir les jeux de caractères),
 Gérer les transactions (unité de base des traitements sur la BdD),
 Gérer les caches mémoire clients et serveur,
 Gérer les connexions et les contrôles d’accès utilisateurs.
Aujourd’hui les pilotes ODBC (Open Data Base Connectivity) permettent de développer des applications
clientes sans connaître le serveur SQL qui sera utilisé. Ces pilotes s’améliorent et offriront la possibilité de gérer les
bases de données réparties sur plusieurs serveurs.
9.5 4.5. Bases de données distribuées
L’idée d’utiliser plusieurs machines pour répartir les données et réduire les risques en cas de panne est à
l’origine des architectures distribuées. Microsoft propose depuis 1997 un produit nommé Microsoft Transaction Server, celui-ci permet de gérer des transactions sur plusieurs serveurs possédant donc une base de données répartie sur
des serveurs (qui peuvent être très éloignés grâce aux réseaux étendus).
Une nouvelle fois, les produits « grands systèmes » comme CICS ou Tuxedo qui sont des moniteurs transactionnels ont inspiré l’offre micro. Dans SQL Server 6.5, le service MS DTC assure la gestion des transactions
distribuées.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 30
10 LES OUTILS D’ADMINISTRATION SQL SERVER 6.5
Ci-dessous l’interface graphique Entreprise Manager :
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 31
Cette version prend en compte le concept de gestion centralisée de serveurs, cela permet d’administrer tous
les serveurs de l’entreprise y compris ceux qui sont à l’extérieur (réseaux étendus), les composants de SQL Server 6.5
sont organisés selon le schéma ci-dessous :
SQL Entreprise Manager
OLE Automation
SQL Server Distributed Management Objects (DMO)
Moteur
SQL Server
SQL Executive
Gestionnaire de réplication
Gestionnaire de tâches
Gestionnaire d’événements
Gestionnaire d’alerte
SQL Server et Windows NT uilisent des outils communs : l’observateur d’événements, l’analyseur de performances, le gestionnaire de services, le registre, etc.
SQL Server est multithread, chaque processus en cours sur le serveur possède des zones de mémoire et des
priorités indépendantes des autres threads.
Structure DMF Distributed Management Framework est un ensemble de services et objets nécessaires à
l’administration de SQL Server qui se décompose en :
SQL Entreprise Manager (SQLEW.EXE) Interface graphique d’administration
DMO Distributed Management Objects interface d’accès au moteur = SQL DMO et OLE Automation.
Transact-SQL qui est une extension à SQL permet d’administrer complètement les serveurs, il regroupe :
le langage de définition des données (DDL) : définir les bases, tables, index, vues, ...
le langage de manipulation des données (DML) : sélection, mise à jour, ...
le langage de contrôle des données (DCL) : gestion des droits d’accès à l’information,
le langage de programmation : écriture des procédures stockées et déclencheurs.
 Services SQL Executive (réplication, tâches, événements, alertes)
 Gestionnaire de Services SQL (SQLMGR.EXE) :interface « feu rouge » pour le démarrage, l’arrêt, la reprise du
serveur
 Gestionnaire de sécurité SQL (SQSECMGR.EXE) : gestion des comptes utilisateurs pour la sécurité intégrée à
N.T.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 32
 ISQL_W : interface graphique permettant de lancer des requêtes et d’afficher les résultats. On peut lancer tous les
types de requêtes (DDL, DML, DCL) et procédures stockées et analyser les requêtes.
 Analyseur de performances SQL (PERFMON.EXE de Win. N.T.) : mesure l’activité et les performances du
serveur.
 SQL Trace (SQLTRACE.EXE) : permet de suivre l’activité du serveur en temps réel
 MS Query (MSQRY32.EXE) : interface graphique de gestion des requêtes à travers ODBC
 Assistant Web SQL (WEBWIZ.EXE) : outil de création de pages HTLM
 Titres en ligne SQL Server (INFOVIEW.EXE) : documentation en ligne
 Pilotes Microsoft ODBC et fichier d’aide pour SQL Server
 Objets de gestion distribuée SQL Server : aide sur SQL DMO
Fichiers exécutables depuis la ligne de commande :
BCP.exe : transfert de données entre fichiers systèmes et SQL Server
ISQL.exe : exécution de scripts en Transact-SQL
SQLSERVR.exe : exécutable de MSSQLSERVER
SQLMAINT.exe : programme pour la maintenance de bases de données
MAKEPIPE.exe, READPIPE.exe : verifie le fonctionnement des cannaux nommés
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 33
11 ALLOCATION ET GESTION DE L’ESPACE DE STOCKAGE
La première étape à réaliser lors de la mise en place d’une base de données consiste à réserver un espace
disque destiné au stockage de toutes les données de la base.
Avec SQL Server, il est nécessaire de pré-allouer l’espace de stockage c’est à dire qu’il faut calculer et
réserver un espace disque suffisant pour y stocker la base. SQL Server utilise l’architecture suivante :
Unité de Base de Données
Base de données
Segment
Unité d’allocation (512 Ko)
Extension (16 Ko)
Page (2 Ko)
11.1 Unité de Base de Données
C’est l’entité de base créée par le système d’exploitation (instruction Disk Init).Un fichier physique est
alors créé, il possède un nom logique d’unité, un nom physique (de fichier), une taille et un numéro. Une unité peut
contenir :
 une ou plusieurs bases de données,
 un ou plusieurs journaux de transactions.
A l’installation, SQL Server génère trois unités de base : MASTER.DAT, MSDBDATA.DAT,
MSDBLOG.DAT
11.2 Segment
Un segment est un ensemble nommé de parties de disque. Il s'agit d'un sous-ensemble d'une ou de plusieurs
unités de base de données mises à la disposition d'une base de données particulière.
Par défaut, un segment prend toute la place allouée à la base de donnée sur l’unité de base de donnée. Le
même segment peut occuper plusieurs unités de base de données.
Les informations sur les segments sont obtenues par la fonction sp_helpegment «NomSegment ».
Lors de la création d'une base de données, les segments suivants sont créés automatiquement pour cette
base de données :
Nom de segment
Fonction
SYSTEM
Stocke les tables système.
LOGSEGMENT
Stocke le journal des transactions.
DEFAULT
Stocke tous les autres objets de base de données, sauf si vous créez d'autres segments. Si vous ne spécifiez pas de segment lors de la création de la table ou en utilisant la procédure système sp_placeobject, les objets de base de données seront
stockés sur ce segment.
11.3 Unité d’allocation
Lorsque la base à été placée dans une unité de base, elle est découpée en unités d’allocation valant chacune
512Ko.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 34
11.4 Extension
Il existe 32 extensions par unité d’allocation, une extension vaut donc 1/32 ème de 512 K, soit 16 Ko.
Chaque table ou index est affecté d’une extension lors de sa création : l’extension initiale. A mesure que la
table augmente de volume, le système lui attribue de nouvelles extensions : les extensions supplémentaires.
C’est l’unité de stockage minimum d’une table ou d’un index. Il ne peut y avoir qu’une table ou un index
par extension.
11.5 Page
Chaque extension contient 8 pages de données, c’est dans cet espace que sont stockées les lignes des
tables et index. La taille d’une page est de 2048 octets (dont 2016 octets utilisables), cependant la longueur maxi
d’un enregistrement (hors champs text et image) est 1962 octets. Il est important de bien calculer la longueur des
enregistrements lors de la conception.
Exemple : si un enregistrement occupe 1040 octets, cette longueur ne permet pas de stocker 2 enregistrements par page (2*1040 = 2080), de ce fait chaque enregistrement occupera une page, ce qui représente un gaspillage de près de 50% de l’espace
La procédure sp_estspace permet de calculer la taille d’une table en fonction de sa structure et du nombre
d’enregistrements estimés.
11.6 Décomposition d’une unité d’allocation
Le tableau ci-dessous présente l’organisation d’une unité d’allocation
0
8 9
240
248
1
241
249
2
3
4
5
6
7
10
11
12
13
14
15
242
250
243
251
244
252
245
253
246
254
247
255
extension 0 (pages 0 à 7)
extension 1 (pages 8 à 15)
extension 30 (pages 240 à 247)
extension 31 (pages 248 à 257)
11.7 Estimation de la taille d’une table
11.7.1
Remarques préliminaires
Les calculs diffèrent un peu selon que les champs sont de longueurs fixes (longueur fixe et défini avec
l’option NOT NULL) ou de longueur variable (longueur variable ou longueur fixe et défini avec l'option NULL).
Dans le second cas, il faut faire les calculs avec la longueur moyenne du champ et la formule de calcul tient compte
du nombre de champs de longueur variable dans une ligne.
On simplifie ici, en prenant la taille maxi de chaque colonne.
On prend le cas particulier d’un index ordonné : les feuilles d’un index ordonné sont les pages de données
elles-même. Dans le cas d’un index non ordonné, il y a un niveau d’indexation supplémentaire et le nombre d’octets
réservé pour les servitudes logicielles diffère un peu.
11.7.2
Etape 1 : Calcul de la taille d’une ligne de données
2 + (Somme des octets de toutes les colonnes) = Sous-total
Sous-total + ((Sous-total / 256) + 1) + (Nombre de colonnes de longueur variable + 1) + 2 =
Taille de la ligne de données
La valeur 1 et 2 correspondent aux servitudes logicielles que SQL Server mobilise pour le stockage interne.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
11.7.3
Le 16/04/17
page 35
Etape 2 : Calcul du nombre de pages de données
Pour calculer le nombre de pages de données:
2 016 / (Taille de la ligne de données) = Nombre de lignes de données par page
(Nombre de lignes) / (Nombre de lignes de données par page) = Nombre de pages de données
requises.
11.7.4 Etape 3 : Calcul de la taille des lignes d'un index ordonné
5 + (Somme des octets des clés d'index )) = Sous-total
(Sous-total) + ((Sous-total / 256) + 1) + (Nombre de colonnes de longueur variable + 1) + 2 =
Taille d'une ligne d'index ordonné
Les valeurs 1, 2 et 5 correspondent aux servitudes logicielles que SQL Server mobilise pour le stockage interne.
11.7.5 Etape 4 : Calcul du nombre de pages d'un index ordonné
Après avoir calculé la taille d'une ligne de l'index ordonné, vous pouvez calculer le nombre de pages requises par celui-ci.
(2 016 / Taille d'une ligne de l'index ordonné)  2 = Nombre de lignes d'index ordonné par page
(Nombre de pages de données) / (Nombre de lignes d'index ordonné par page)
= Nombre de pages d'index au niveau 0
Si le résultat obtenu est supérieur à 1, répétez les étapes de division en utilisant chaque fois le quotient obtenu précédemment comme nouveau dividende, jusqu'à ce que vous obteniez un résultat égal à 1. Dès que vous aurez
obtenu un quotient égal à 1, vous aurez atteint le niveau racine de l'index.
(Nombre de pages d'index au dernier niveau) / (Nombre de lignes d'index ordonné par page) =
Nombre de pages d'index au niveau suivant
11.7.6 Etape 5 : Calcul du nombre total de pages
Pour calculer le nombre total de pages de données:
(Nombre de pages de données) + (Nombre de pages d'index au niveau 0) + (Nombre de pages
d'index au niveau suivant) + (Nombre de pages d'index au niveau suivant) [...] = Nombre total de pages de
données
11.8 Calcul de la taille d'une table de 9 millions de lignes dotée d'un index ordonné
L'exemple suivant illustre comment calculer la taille (en nombre de pages de 2 Ko) des données et de
l'index ordonné d'une table présentant les caractéristiques suivantes:
 9 millions de lignes
 Somme des octets des colonnes de longueur fixe = 100
 Somme des octets de deux colonnes de longueur variable = 50
 Clé d'index ordonné de longueur fixe = 4 octets
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 36
Procédez comme suit:
1. Calculez la taille d'une ligne de données:
4
(Servitudes logicielles)
+ 100
Somme des octets de toutes les colonnes de longueur fixe
+ 50
Somme des octets de toutes les colonnes de longueur variable
154
Sous-total
154
+ 1
+ 3
+ 2
160
Sous-total
(Sous-total / 256) + 1 (Servitudes logicielles)
Nombre de colonnes de longueur variable + 1
(Servitudes logicielles)
Taille d'une ligne de données
2. Calculez le nombre de pages de données:
2 016 / 160 = 12 lignes de données par page
9 000 000 / 12 = 750 000 pages de données
3. Calculez la taille des lignes de l'index ordonné:
5
Servitudes logicielles
+4
Somme des octets des clés d'index de longueur fixe
9
Taille des lignes de l'index ordonné
4. Calculez le nombre de pages de l'index ordonné:
(2 016 / 9) - 2 = 222 lignes d'index ordonné par page
750 000 / 222 = 3 378 pages d'index (Niveau 0)
3 378 / 222 = 15 pages d'index (Niveau 1)
15 / 222 = 1 page d'index (Niveau 2)
5. Calculez le nombre total de pages:
Totaux:
Niveau 2 (racine)
Niveau 1
Niveau 0
Données
Nombre total de pages de 2 Ko
ALSI / S25
SGBD R Support de Cours 2TSIG
Pages
1
15
3 378
750 000
753 394
Lignes
15
3 378
750 000
9 000 000
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 37
12 UNITE ET BASE DE DONNEES SQL SERVER 6.5
12.1 Création d’une unité de base de données
Cette opération peut se réaliser avec l’interface Entreprise Manager ou avec du code Transact-SQL. Avec
E. M. cliquez sur le dossier Unité de base de données, choisissez Créez une nouvelle unité (bouton droit de la souris).
La fenêtre Nouvelle unité de base de données apparaît :
La taille de l’unité est ajustée avec le curseur d’accroissement
Avec Transct-SQL, exemple :
DISK INIT
Name = ‘’FFMC’’
Physname = ‘’C:\MSSQL\DATA\CASMOTO.DAT’’
Vdevno = 10,
Size = 51200
12.2 Modification de la taille d’une unité
Avec E. M. cliquez sur le dossier Unité de base de données, choisissez Edition (bouton droit de la souris).
La fenêtre Edition unités de base de données apparaît. Le bouton d’accroissement permet la modification, la zone
centrale montre l’utilisation de l’espace de l’unité.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 38
Avec Transct-SQL, exemple :
DISK RESIZE
Name = ‘’FFMC’’
Physname = ‘’C:\MSSQL\DATA\CASMOTO.DAT’’
Size = 52224
12.3 Suppression d’une unité de base de données
La suppression d’une unité entraîne la suppression des bases et des journaux de transactions qu’elle contient. Avec E. M. il faut supprimer ‘à la main’ le fichier physique correspondant.
Avec Transct-SQL, exemple : sp_dropdevice ‘’FFMC’’, DELFILE
12.4 Création d’une base de données
Cette opération peut se réaliser avec l’interface E. M. ou avec du code Transact-SQL. Avec Entreprise
Manager, cliquez sur le dossier Base de données, choisissez Nouvelle Base (bouton droit de la souris). La fenêtre
Nouvelle base de données apparaît. Le premier cadre indique le nom de l’unité de données et le nom d’unité de jour-
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 39
nal de transaction ainsi que leurs tailles respectives. La partie inférieure indique les unités existantes et la place disponible sur chacune d’elles.
Avec Transact-SQL. Exemple : CREATE DATABASE MOTO on FFMC = 10
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 40
12.5 Extension d’une base de données
L’extension de la base est possible, cette opération peut se faire dans l’unité initiale ou sur d’autres unités.
Le terme désigné en SQL Server est ‘développement de base de données’ Double-cliquez sur le nom de la base de
données, puis sur le bouton Développement, la fenêtre ci-dessous apparaît :
Avec Transact-SQL. Exemple : ALTER DATABASE MOTO on FFMC = 5
12.6 7.6 Réduction d’une base de données
Même opération que pour l’extension, choisir le bouton Réduire, une fenêtre indique la taille minimale en
dessous de laquelle on ne peut descendre.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 41
Avec Transact-SQL. Exemple : DBCC SHRINKDB (MOTO, 496)
12.7 Suppression d’une base de données
Les bases de données utilisateurs (et non les bases système master, model, tempdb) peuvent être supprimées. Avec l’interface E. M. cliquez sur le nom de la base, choisissez Supprimer (bouton droit de la souris). Cette
opération supprime à la fois la base désignée mais aussi tous les objets qu’elle contient (pas de récup. possible !!!).
Avec Transact-SQL. Exemple : DROP DATABASE MOTO
12.8 Lire les infos. d’une base de données
Avec Entreprise Manager, double-cliquez sur le nom de la base, la fenêtre Edition informe sur la date de
création, le propriétaire, la taille des données et du journal de transactions et l’espace disponible.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 42
12.9 Le journal de transaction
Il occupe une place importante dans le système de sécurité car il contient la trace de toutes les modifications apportées à la base. Chaque base possède son journal de transaction, celui-ci est repésenté par la table syslogs.
Le mécanisme de la transaction est le suivant :
 une transaction (une ou plusieurs opérations SQL) est effectuée par un utilisateur
 les lignes qui doivent être modifiées sont chargées en MEV (cache de donnée), elles restent en MEV,
 la transaction est ensuite validée (ordre COMMIT) et placée dans le journal de transaction sur disque.
Si un incident survenait à ce moment la MEV (cache mémoire) serait effacée mais la transaction enregistrée dans le journal permettrait de réappliquer les modifications grâce au mécanisme de restauration automatique.
12.10 Rôles des bases de données système
Quatre bases ‘système’ sont implantées lors de l’installation de SQL Server
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 43
 master, base indispensable au fonctionnement du moteur SQL,
 model, base exemple servant de modèle aux nouvelles bases créées,
 tempdb, base de stockage temporaire,
 msdb, base utilisée par SQL Executive
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 44
La base système master assure la sécurité d’accès, le suivi des connexions, le suivi des bases et unités,
la gestion des verrous. Master occupe 17 Mo et comporte 13 tables systèmes.
12.10.1
12.10.2 Résumé des tables systèmes de la base master
Nom de la table système
Description
syscharsets
jeux de caractères, ordres de tri sur le serveur
sysconfigures
informations de configuration modifiables
syscurconfigs
copie de sysconfigures
sysdatabases
références de toutes les bases
sysdevices
références de toutes les unités de bases
syslanguages
indications des langues installées / serveur
syslocks
références des verrous actifs
syslogins
table des noms d’accès au serveur
sysmessages
messages erreurs et d’avertissement de SQL
Server
sysprocesses
références des connexions avec SQL Server
sysremotelogins
table des noms d’accès distants au serveur
sysservers
références des serveurs distants accessibles
sysusages
unités d’allocation affectées / base de données
La base système model joue le rôle de base exemple pour les nouvelles bases créées. Elle comporte 18
tables système ainsi que tous les objets (tables, TDDU, règles, valeurs par défaut, procédures stockées...) définis par
l’utilisateur. Ces éléments seront recopiés lors de la création d’une base de données. Sa taille est de 1Mo.
12.10.3 Résumé des tables systèmes de la base model
Nom de la table système
sysalternates
contient les noms d’allias
sysarticles
informations des articles à publier (réplication)
syscolumns
références de toutes les colonnes(tables, vues,
ALSI / S25
Description
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 45
etc.)
syscomments
code Transact-SQL des vues, règles, valeurs
par déf. déclencheurs et procédures stockées
sysconstraints
références des contraintes de type PK, FK,
CHECK, DEFAULT, UNIQUE et utilisation dans les
tables.
sysdepends
dépendances fonctionnelles entre objets.
sysindexes
une ligne par index (cluster ou non)
syslogs
c’est le journal des transactions
sysobjects
contient les références des tables de la BdD
arbre d’exécution des vues,valeurs par déf.,
sysprocedures
règles, ...
sysprotects
droits d’utilisation des objets
syspublications
contient les publications pour la réplication
sysreferences
une ligne par contrainte de type FK
syssegments
références des segments existants sur la base
syssubscriptions
systypes
sysusers
ALSI / S25
références des publications et articles (abonnement du serveur)
contient les types système et TDDU
références des groupes et utilisateurs (lien
avec la table syslogins
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 46
La base système msdb est utilisée par le service SQL Executive pour la gestion des alertes, le suivi des
sauvegardes et restaurations.
12.10.4 Résumé des tables systèmes de la base msdb
Nom de la table système
sysalerts
Description
références des alertes
syshistory
historique des tâches déclenchées et des alertes
sysnotifications
liaisons entre les alertes et les opérateurs
sysoperators
liste des opérateurs devant être prévenus
systasks
liste des tâches automatisées
sysservermessages
complément des messages erreurs propre au serveur
sysibackupdetail
références des sauvegardes réalisées par unité
sysbackuphistory
une ligne par opération de sauvegarde
sysrestorehistory
une ligne par opération de restauration
sysrestoredetail
références des sauvegardes réalisées par unité
La base système
tions suivantes :
tempb est une base temporaire utilisée pour le stockage des données lors des opéra-
 tris (order by)
 sous-requêtes imbriquées ou corrélées
 regroupement (group by, having)
 calculs et fonctions (AVG, MIN, SUM, ...)
 création de procédures stockées (ODBC, ...)
Par défaut la taille de tempdb est fixée à 2 Mo, cette taille peut s’avérer insuffisante et avoir des répercussions sur les performances de la base. ll convient alors d’augmenter la taille de celle-ci.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 47
12.11 Les options d’une base de données
L’onglet Options de la fenêtre Edition de la base de données fait apparaître une série d’options (cases à cocher) que l’on peut activer ou non
Select into / bulkcopy : Cette option activée permet de ne pas écrire dans le journal de transaction, certaines opérations comme bcp ou SELECT INTO. Par défaut elle est désactivée.
Colonnes NULL par défaut : Avec cette option, les colonnes ne spécifiant pas NULL ou NOT
NULL (lors des opérations CREATE ou ALTER) acceptent la valeur NULL.
Aucune synchronisation lors de la récupération : Si cette option est activée aucun enregistrement de synchronisation (entre des serveurs secondaires et le serveur primaire) n’est ajouté. L’option doit être activée pour permettre la restauration des journaux de transactions en provenance du serveur primaire.
Lecture seule : Permet de déclarer la base en lecture seule, donc pas d’écriture possible.
Utilisateur unique : Avec cette option, un seul utilisateur (n’importe lequel) peut avoir accès à
la base, cette option est à distiguer de « Réservé au DBO »
Réservé au DBO
Le DBO est le propriétaire de la base, avec cette option, seul le DBO peut accéder à la base.
Tronquer le journal lors de la synchronisation
Lorsque cette option est activée, le journal des transactions est validé au moment des points de
synchronisation (chaque minute) est vidé. Cela représente un risque de ne pouvoir reconstruire les données perdues.
Options activées par la procédure sp_dboption
Offline : utilisée pour les bases se trouvant sur un support amovible comme le CD-ROM.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 48
Published : permet à une base d’être ‘éditeur’ (lors d’une réplication).
Subscribed : permet à une base d’être abonné’ d’une autre base.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 49
8. Création des objets de la base SQL Server 6.5
Comme pour les opérations précédentes, la création des objets peut se faire via l’interface graphique (Entreprise Manager) ou bien grâce au code Transact-SQL. Nous utiliserons de préférence le mode graphique (plus rapide et agréable), néanmoins il est possible de générer les scripts correspondants afin de les étudier ou de les rejouer
lorsque cela est nécessaire.
Le groupe Objets de la fenêtre précédente comprend six sous-groupes :






Tables
Vues
Procédures stockées
Règles
Défauts
Type de données utilisateur
L’interface grafique permet (clic droit de la souris) de créer, modifier, supprimer facilement les objets de la
base.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 50
12.12 Table
12.12.1 Création d’une table
La fenêtre de création de table comprend une zone destinée à la définition des champs de la table (Clé,
identité, Nom de colonne, Type de donnée, Taille, Valeurs Null, Par défaut).
La seconde zone comprend quatre onglets destinés à la définition des clés primaires et étrangères ainsi que
les contraintes d’unicité et de validation.
Une table peut contenir 250 colonnes au maximum, le nombre de lignes n’est limité que par la capacité du
serveur ; une base peut contenir 2 milliards de tables.
12.12.2 8.1.2 Modification d’une table

il est aisé de créer une table par l’interface Entreprise Manager, par contre il faut penser à enregistrer
le script de création afin de le rejouer ou d’apporter des modifications.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 51
12.12.3 8.1.3 Types de données pré-définis
Il existe 19 types de données fournis pas le système
Les types de données système sont:
Binaire
binary[(n)]
varbinary[(n)]
Caractère
char[(n)]
varchar[(n)]
Date et heure
datetime
smalldatetime
Numérique exact
decimal[(p[, s])]
numeric[(p[, s])]
Numérique approché
float[(n)]
real
Entier
int
smallint
tinyint
Monétaire
money
smallmoney
Spécial
bit
timestamp
Texte et image
text
image
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 52
12.12.4 8.1.4 Types de Données Définis par l’Utilisateur (TDDU)
Comme son nom l’indique, cette possibilité permet d’affiner la définition de certaines colonnes, un TDDU
est lié à une base de données. Comme pour tous les objets d’une base de données un TDDU sera défini soit par
l’interface E.M. comme ci-dessous, soit par code.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
ALSI / S25
SGBD R Support de Cours 2TSIG
Le 16/04/17
page 53
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 54
TDDU défini par code comme ci-dessous.
Exemple 1 : un type Code-Règlement (entier compris entre 1 et 5)
Sp_addtype CodRglType, smallint NOT NULL
Exemple 2° : un type Code-Postal (sur 5 caractères)
Sp_addtype CodPosType, Char(5) NOT NULL
Il n’est pas possible de modifier un TDDU, il faut le supprimer, puis le recréer d’où l’intérêt de rejouer le
script apès suppression de l’objet. Sp-droptype CodPost

pour bénéficier de ce TDDU dans toutes vos bases de données, il suffit de le créer dans la base model.
De plus un TDDU peut posséder une valeur par défaut et une règle de validation.
12.12.5
8.1.5 Les valeurs par défaut
Cette possibilité permet de ne pas saisir certaines valeurs de champ en proposant à l’utilisateur une valeur
convenue à l’avance. Ces valeurs peuvent être définies soit sur la colonne de la table, soit sur le type de la colonne.
Selon les options utilisées la mise en œuvre de l’intégrité des données se fera de manière déclarative ou procédurale.
12.12.6 Contrainte DEFAULT (déclarative)
Une colonne ne peut posséder qu’une contrainte DEFAULT c’est à dire une constante texte ou numérique,
une fonction niladique (ne possédant pas d’arguments) ou une valeur nulle.
Exemple : CREATE TABLE CLIENT
(
NumCli int NOT NULL,
DateCde DateTime DEFAULT (GETDATE(),
Montant Double,
CodeRglt smallint,
CONSTRAINT DefMontant DEFAULT 0 FOR Montant ) )
On remarque que la première contrainte défaut est déclarée directement sur la colonne DateCde , la seconde contrainte est définie après le mot clé CONSTRAINT. Cette possibilité permet d’ajouter une contrainte même
si la table est déjà constituée exemple :
ALTER TABLE CLIENT
ADD CONSTRAINT DefCodeRglt DEFAULT 1 FOR CodeRglt
12.12.7 CREATE DEFAULT (procédurale)
La valeur par défaut est créée sans être attachée immédiatement à une colonne, cet objet est alors considéré
comme les autres tables ou index. Ce type de valeur par défaut sera attaché soit à une colonne, soit à un TDDU. Dans
ce cas la colonne créée avec ce type hérite de la valeur par défaut.
La fenêtre suivante présente le mécanisme CREATE DEFAULT :
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 55
création d’une valeur par défault,
association de colonnes
ou de types de données

la contrainte DEFAULT se trouvant dans la structure de table est vérifiée en premier. L’intérêt des va-
leurs par défaut réside dans la possibilité de les associer aux TDDU.
Création d’une valeur par défaut avec E.M.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 56
Association d’une valeur par défaut à une colonne avec E.M.
Association d’une valeur par défaut à TDDU avec E.M.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
12.12.8
Le 16/04/17
page 57
8.1.6 Les règles de validation
Elles assurent l’intégrité de domaine et permettent des contrôles :


en limitant les valeurs saisies dans un intervalle de valeurs
en iimposant un format de saisie
Deux possibilités de création sont offertes (code ou E.M.) pour chacun des deux contrôles : la contrainte de
type CHECK ou la règle de validation RULE.
12.12.9 Contrainte CHECK (déclarative)
La création peut se faire par code
Exemple codé 1°:
(
CREATE TABLE CLIENT
NumCli int NOT NULL,
CodPost char(5),
CONSTRAINT CSCodPo
CHECK (CodPost LIKE ‘ [0-9] [0-9] [0-9] [0-9] [0-9] ‘ )
)
Exemple codé 2°
ALTER TABLE JOUR
ADD CONSTRAINT CSJour CHECK ( Jour IN ‘Lundi’, ‘Mardi’, …))
Exemple codé 3°
ALTER TABLE CLIENT
ADD CONSTRAINT CSRemis CHECK ( Remise <= 30)
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 58
12.12.10 Contrainte CHECK (déclarative) suite
La création peut se faire aussi par l’interface E.M.
12.12.11 CREATE RULE (procédurale)
Comme pour les valeurs par défaut, il est possible de créer des règles de validation indépendantes des colonnes. La syntaxe de la condition est identique à celle d’une condition WHERE (excepté les SELECT imbriqués).
Les exemples suivants présentent quelques règles réalisées par code ou avec l’interface E.M.
Exemple codé 1°:
CREATE RULE CodPostRule
AS @codpo LIKE ‘ [0-9] [0-9] [0-9] [0-9] [0-9] ‘ )
L’utilisation de la variable @codpo permettra le passage du nom de la colonne ou du TDDU lors de la liaison avec la règle.
Exemple codé 2°:
CREATE RULE RemisRule
AS @Taux >=0 AND @Taux <=40
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 59
12.12.12 Attachement avec la règle (par code)
liaison de la règle CodPostRule à la colonne CodPost
Sp-bindrule CodPostRule, ‘MaBase.CodPost’
liaison de la règle CodPostRule au TDDU CodPostType
Sp-bindrule CodPostRule, CodPosType

CHECK et RULE visent le même objectif, quelques différences existent cependant :

la contrainte CHECK fait partie de la table, elle la suit lors d’une procédure d’export ou de de réplication.

la contrainte CHECK peut référencer plusieurs colonnes et permet de comparer des colonnes d’une
même table.,

une contrainte CHECK et une règle peuvent être associées à une même colonne (la contrainte est vérifiée en premier).

la règle RULE est associable à un TDDU, ce qui permet de définir des types génériques utilisables par
tous les développeurs d’une même équipe.
Comme pour les TDDU, la création de
celles-ci automatiquement dans toute nouvelle base créée.
règles
dans
la
base
model
intégrera
Définition d’une règle par l’interface E. M.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 60
Association d’une règle à une colonne par l’interface E. M.
12.12.13
Association d’une règle à un type de donnée par l’interface E. M.
12.12.14
12.12.15
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
12.12.16
Le 16/04/17
page 61
8.1.7 Les clés primaires et étrangères
Elles jouent un rôle essentiel dans la définition :


de l’intégrité d’entité (vérification que chaque ligne est identifiée de manière unique)
de l’intégrité référentielle (vérification que la clé étrangère possède une corresondance avec la clé primaire de la table référencée)
Les clés primaires et étrangères sont référencées dans la table système syskeys qui gère la structure de la
base de données. Dans la version 6.5, ces clés sont considérées comme des contraintes de la structure des tables.
12.12.17
Les clés primaires
La contrainte PRIMARY KEY indique que la table possède une ou plusieurs colonnes permettant
l’identification unique de chacune des lignes qui la composent. La contrainte UNIQUE permet également cette contrainte.
Avec l’interface E.M. / fenêtre Gestion de tables / onglet Clé primaire
La création d’une clé primaire implique la création d’un index unique. La colonne ou le groupe de colonne
ne doit pas avoir « Valeurs Null » coché.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 62
Avec le code Transact-SQL
Exemple codé 1°:
12.12.17.1.1.1
CREATE TABLE PROF
NumProf int
PRIMARY KEY
Une clé primaire est créée son nom est généré par la système
Exemple codé 2°:
CREATE TABLE PROF
NumProf int
CONSTRAINT PKNumProf
PRIMARY KEY CLUSTERED
12.12.17.1.1.2 Une clé primaire est créée son nom est PKNumProf, l’index associé est de
type CLUSTERED c.a.d. trié selon la table.
12.12.17.1.1.3
Exemple codé 3°:
12.12.17.1.1.4
CREATE TABLE PROF
(
NomProf CHAR(25),
PréProf CHAR(20),
CONSTRAINT PKProf
PRIMARY KEY (NomProf, PréProf)
)
Une clé primaire composée est créée son nom est PKProf
12.12.18
Les clés étrangères
La vérification de l’intégrité référentielle passe par la mise en œuvre des clés étrangères, cela évite
l’utilisation de déclencheurs. (sauf s’il s’agit de mise à jour et suppression en cascade). Comme pour la clé étrangère
les deux modes (codé ou interface E.M.) sont disponibles, il faut cependant vérifier l’existence d’une PRIMARY
KEY ou une contrainte UNIQUE avant de créer une clé étrangère, un index UNIQUE dans la table parent ne suffit
pas.
Avec le code Transact-SQL
Exemple codé 1°:
12.12.18.1.1.1
ALSI / S25
CREATE TABLE ENFANT-SALARIE
NumSal int
REFERENCE SALARIE(NumSal)
Une clé étrangère est créée à partir de la colonne NumSal de la table SALARIE
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Exemple codé 2°:
12.12.18.1.1.2
Le 16/04/17
page 63
CREATE TABLE ENFANT-SALARIE
NomSal CHAR(25),
PréSal CHAR(20),
CONSTRAINT CléEtrEnfant
FOREING KEY (NomSal ,PréSal)
REFERENCE SALARIE(Nom ,Prénom)
Une clé étrangère composite est créée à partir des colonnes Nom et Prénom de la table SALARIE
Exemple de clé étrangère définié avec l’interface E.M.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 64
La contrainte d’unicité
La clé primaire possède par défaut la propriété UNIQUE, pour les autres clés (secondaires) ou attributs
auxquels on souhaite affecter la contrainte unique on utilise la contrainte UNIQUE.
12.12.19
Exemple codé :
ALTER TABLE Produits ADD CONSTRAINT CUDésign_Prix
UNIQUE CLUSTERED (Désignation, PUHT)
Avec l’interface E.M. onglet Contraintes d’unicité :
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 65
12.12.20 8.2 Vue
Une vue est une table virtuelle (c.a.d. sans existence physique sur disque) issue d’une ou de plusieurs
tables, son intérêt réside dans la possibilité de présenter sous une forme adaptée (après sélection et projection) certaines informations au niveau des postes clients.
Exemple une vue nommée VUE-CDE-CLI est créée à partir des tablesCLIENT, PRODUIT, COMMANDE :
Table CIENT
N
RaiSoc
Ville
umCli
4
1025
Dupont & dupond
4
1203
Compagnie
des
Indes
4
Valenciennes
Nante
s
Chez Gustave
Per-
1852
pignan
efProd
1045
4512
3078
Table PRODUIT
R
Désignation
2
UC 486
DX 33
2
EC 17P
Sony
2
UC
P166 MMX
VUE-CDE-CLI
Dési-
Raisonsociale
EC 17P
Chez Gustave
Sony
UC 486
Compagnie
Indes
UC
P166 MMX
pond
umCde
72041
73042
86014
des
Dupont & du-
Table COMMANDE
N
D
ateCde
TEi
9
1
2/02/97
9
0
8/03/97
5
9
1
9/07/98
Q
éfProd
5
4512
1
1045
6
3078
3
520
3
120
4
500
T
OTAL HT
15
600
52
800
27
000
gnation
DX 33
P
rix
R
N
umCli
2
4
1852
2
4
1203
2
4
1025
Avec le code Transact-SQL
12.12.20.1.1.1.1
CREATE VIEW VUE-CDE-CLI AS
SELECT Désignation, RaiSoc, ‘TOTAL HT’ = QTE * Prix
FROM CLIENT, COMMANDE, PRODUIT
WHERE COMMANDE.NumCli = CLIENT.NumCli
AND COMMANDE.RéfProd = PRODUIT.RéfProd
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5

Le 16/04/17
page 66
certaines instructions ne sont pas autorisées lors de la création d’une vue :
COMPUTE, COMPUTE BY, ORDER BY, SELECT INTO, UNION.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 67
12.12.21 Utilisation d’une vue
Une vue s’utilise comme une table mais avec quelques restrictions :

Les instructions UPDATE, INSERT, DELETE ne s’appliquent qu’à une seule table sous-jacente à la vue.

Il est impossible de mettre à jour une colonne calculée (comme le TOTAL HT).

Il est impossible d’ajouter des enregistrements dans une vue contenant des champs calculés.

L’insertion d’un enregistrement dans une vue respecte les mêmes règles que l’insertion d’un enregistrement
dans une table (contrainte, valeurs NULL).
12.13 8.3 Index
Les index permettent un accès rapide aux colonnes d'une table en fonction des valeurs d'une ou de plusieurs colonnes. Les index accélèrent considérablement l'exécution des instructions SQL grâce aux critères de sélection qui font référence aux colonnes indexées.
L'utilisation d'index est appropriée pour les colonnes qui sont fréquemment utilisées avec des critères de
sélection. Elle s'impose également dans des tables pour lesquelles les requêtes sont plus fréquentes que les insertions
et les mises à jour.
SQL Server offre deux types d'index : les index ordonnés et non ordonnés.
 Un index ordonné (CLUSTERED) est un index dans lequel l'ordre logique des valeurs clé est identique à l'ordre
physique des lignes correspondantes dans la table principale. Une table ne peut contenir qu'un seul index ordonné.
 Un index non ordonné (NONCLUSTER) est un index dans lequel l'ordre logique de l'index ne correspond pas à
l'ordre physique des lignes sur le disque. Les index ordonnés permettent généralement un accès plus rapide aux
données que les index non ordonnés.
SQL Server crée automatiquement un index pour les contraintes PRIMARY KEY et UNIQUE. Si vous désirez d'autres index, vous devez les créer.
12.13.1 Création d’un index
 Dans la fenêtre du Gestionnaire de serveur de SQL Enterprise Manager, sélectionnez la table appropriée, appuyez
sur le bouton droit de la souris et choisissez ensuite «Index».
Ou
 Utilisez l'instruction CREATE INDEX.
Par exemple:
CREATE UNIQUE CLUSTERED INDEX UPKCL_auidind
ON auteurs (id_auteur)
crée un index sur la colonne id_auteur de la table auteurs pour assurer l'unicité des valeurs introduites
dans cette colonne.
12.13.2 Visualiser les index
Pour visualiser qui existent sur une table, utilisez la procédure système sp_helpindex. Un maximum de
huit index seront affichés. La table système sysindexes contient des informations sur tous les index.
Voici un exemple de rapport à propos de la table auteurs :
sp_helpindex auteurs
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 68
index_name index_description index_keys
----------------------------------------------------------UPKCL_auidind
clustered, unique, primary key
located on default
id_auteur
aunmind
nonclustered located on default
nom_auteur, pn_auteur
(1 ligne(s) affectée(s))
Lorsqu'elle est utilisée avec le nom d'une table, la procédure système sp_help établit également un rapport
sur les index d'une table spécifique.
Lorsque vous n'avez plus besoin d'un index, vous pouvez l'enlever d'une base de données et récupérer la
place réservée à son stockage en le supprimant. Par exemple, envisagez de supprimer un index si vous ne l'utilisez
pas pour la plupart de vos requêtes ou pour toutes vos requêtes.
Remarque Seul le propriétaire de l'index peut le supprimer. Le propriétaire ne peut pas transmettre une
permission de suppression à d'autres utilisateurs.
12.13.3 Supprimer un index
 Dans la fenêtre du Gestionnaire de serveur de SQL Enterprise Manager, sélectionnez la table appropriée, appuyez
sur le bouton droit de la souris, choisissez «Index» dans la liste déroulante; choisissez ensuite le bouton «Supprimer».
Ou
 Utilisez l'instruction DROP INDEX.
Par exemple:
DROP INDEX auteurs.UPKCL_auidind
supprime l'index UPKCL_auidind de la table auteurs.
12.14 8.4 Les procédures stockées
Une procédure stockée est un programme qui à pour objet un traitement itératif ou ensembliste (SQL standard), ce traitement agit sur les données de la base. Le langage utilisé est Transact SQL. Ces programmes peuvent
recevoir des paramètres, renvoyer des valeurs, être exécutés à distance et posséder des droits d’accès.
Une procédure stockée peut être lancée depuis un client, ou depuis une autre procédure stockée ou être
exécutée automatiquement par le serveur. Contrairement à une requête SQL classique, elle est pré-compilée et optimisée pour en améliorer les performances.
ODBC utilise nombre de procédures stockées, SQL Server dispose quand à lui de centaines de procédures
systèmes (préfixe sp_)
12.14.1 Création d’une procédure stockée
L’interface Gestion de procédures stockées propose une feuille de requête pour la création ou la modification du code. Tout comme une vue ou un déclencheur une procédure ne peut se modifier directement, il faut la supprimer puis la recréer. On trouve en tête de l’instruction DROP destinée à la suppression. L’ensemble des instructions
SQL est accessible à condition de ne pas dépasser 65000 caractères par procédure.
Exemple 1 : création d’une procédure stockée de suppression d’un article
CREATE PROCEDURE Suppres_Article (@RéfArt INT) AS
IF NOT EXISTS (SELECT * FROM ARTICLE WHERE Référence = @RéfArt )
BEGIN
PRINT « Cet Article n’existe pas dans la Base ! »
RETURN
END
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 69
IF EXISTS (SELECT * FROM COMMANDE WHERE Référence = @RéfArt )
BEGIN
PRINT « Attention cet Article est concerné par des commandes !! »
RETURN
END
DELETE FROM ARTICLE WHERE Référence = @RéfArt
GO
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 70
Exemple 2 : utilisation d’une procédure stockée de suppression d’un article
EXECUTE Suppres_Article 41245
Syntaxe générale de l’instruction de création de procédure :
CREATE PROCedure nom_procédure [ ;numéro]
[(paramètre1 [,paramètre2] … paramètre255] ) }
[{ FOR REPLICATION } | { WITH RECOMPILE } ]
[ WITH ENCRYPTION ] AS instruction_SQL
nom_procédure
nom unique dans la base, précédé d’un signe #, la procédure sera
locale et temporaire, avec deux ## elle sera globale et temporaire
numéro
numéro d’ordre pour les procédures ayant le même nom
Paramètre
de la forme @nom type [ = valeur ] [OUTPUT], pouvant être passé à la procédure. OUTPUT permet d’indiquer un paramètre retorné par la procédure.
FOR
REPLICATION
Permet de préciser que la procédure sera utilisée lors
de la réplication.
WITH
RECOMPILE
La procédure est recompilée à chaque exécution
WITH
ENCRYPTION
Le code sera crypté dans les tables système.
12.14.2 UtIlisation d’une procédure stockée
Les procédures stockées sont utilisées dans les cas suivants :





Mise en œuvre des règles d’entreprise
Traitements des mises à jour ou suppression en cascade
Sécurité d’exécution
Enchaînement d’instructions
Manipulation des données système
12.15 8.5 Les déclencheurs ou TRIGGER
Un déclencheur est un type particulier de procédure stockée qui entre en action lorsque vous modifiez des
données dans une table spécifique. Les déclencheurs sont habituellement créés pour assurer la cohérence des règles
de gestion des données liées logiquement dans différentes tables.
L'avantage des déclencheurs est qu'ils s'exécutent automatiquement (ils fonctionnent quelle que soit la raison de la modification des données, des données entrées par un employé ou l'action d'une application).
Chaque déclencheur est spécifique à une ou plusieurs opérations de modification des données: UPDATE,
INSERT ou DELETE. Un déclencheur ne s'exécute qu'une fois par instruction SQL, il s'exécute immédiatement après
l'achèvement des instructions de modification de données. Le déclencheur et l'instruction qui le déclenche sont traités
comme une seule transaction qui peut être annulée à partir du déclencheur. Si une erreur grave est détectée, toute la
transaction est automatiquement annulée.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 71
Les déclencheurs sont utiles dans les cas suivants:

 Pour effectuer des changements en cascade dans des tables liées de la base de données.
Par exemple, un déclencheur DELETE associé à la colonne id_titre de la table titres provoque une suppression équivalente de lignes correspondantes dans d'autres tables, en utilisant la colonne titre_id comme clé
unique pour localiser des lignes dans les tables titreauteur, ventes et droits_prévus.

 Pour empêcher ou annuler les changements qui violent l'intégrité référentielle de la base, en annulant la tentative
de modification des données en question.
Un déclencheur de ce type peut entrer en action si vous essayez de modifier une clé étrangère et que la
nouvelle valeur ne correspond pas à sa clé primaire. Par exemple, vous pouvez créer un déclencheur INSERT sur
titreauteur.id_titre, qui annule toute insertion si la nouvelle valeur ne correspond pas à une valeur dans
titres.id_titre. (Cependant, l'intégrité référentielle déclarative (DRI) est souvent employée dans ce but). Quand cela est possible, il est préférable de laisser agir la DRI.

 Pour assurer l'application de restrictions plus complexes que celles définies à l'aide de contraintes CHECK.
Contrairement aux contraintes CHECK, les déclencheurs peuvent faire référence à des colonnes dans
d'autres tables. Par exemple, un déclencheur peut annuler des mises à jour visant à augmenter le prix d'un livre de
plus d'un pour-cent de son avance sur droits d'auteur. Il peut être contrôler au moyen d'une instruction SELECT
portant sur une autre table.

 Pour trouver la différence entre l'état d'une table avant et après une modification des données, et accomplir une ou
plusieurs actions en fonction de cette différence.
Remarque A l'exception du déclencheur deltitle, les déclencheurs traités dans ce chapitre ne font pas partie de la base de données pubs. Pour utiliser les exemples, vous devez créer chaque déclencheur avec l'instruction
CREATE TRIGGER.
Attention : Chaque nouveau déclencheur associé à une table ou une colonne pour le même type d'opération (INSERT, UPDATE, ou DELETE) écrase le précédent sans avertissement.
Les déclencheurs étudiés jusqu'à présent considéraient chaque instruction de modification des données
comme un tout: si une seule ligne d'une insertion de quatre lignes n'était pas acceptable, c'est l'insertion entière qui
n'était pas acceptée, et toute la transaction était annulée. C'est la manière dont fonctionne un déclencheur. Il rejette ou
accepte chaque transaction de modification de données en entier.
Cependant, vous ne devez pas annuler toutes vos modifications de données parce que certaines ne sont pas
valides. En utilisant une sous-requête subordonnée dans un déclencheur, vous pouvez forcer le déclencheur à analyser
les lignes modifiées une à une.
L'exemple suivant suppose l'existence d'une table nommée nouvelles_ventes. Voici l'instruction CREATE
pour créer cette table:
CREATE TABLE nouvelles_ventes
(id_mag char(4),
num_cmd varchar(20),
date datetime,
qt smallint,
modepaiements varchar(12),
id_titre tid)
Comment analyser chacun des enregistrements que vous essayez d'insérer ? Le déclencheur ins_condition
les analyse ligne par ligne, et ensuite supprime les lignes qui n'ont pas de id_titre dans la table titres:
CREATE TRIGGER ins_condition
ON ventes
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 72
FOR INSERT AS
IF
(SELECT COUNT(*) FROM titres, inserted
WHERE titres.id_titre = inserted.id_titre) <> @@rowcount
BEGIN
DELETE ventes FROM ventes, inserted
WHERE ventes.id_titre = inserted.id_titre AND
inserted.id_titre NOT IN
(SELECT id_titre
FROM titres)
PRINT 'Ne sont ajoutées que les ventes dont les id_titre
correspondent.'
END
Le test de déclencheur est identique à celui utilisé dans l'exemple de déclencheur INSERT (déclench_ins)
présenté plus haut, mais la transaction n'est pas annulée. Au lieu de cela, le déclencheur supprime les lignes rejetées.
Cette capacité de supprimer des lignes qui ont été insérées repose sur l'ordre dans lequel le traitement s'effectue lorsque les déclencheurs s'exécutent. Les premières lignes sont insérées dans la table ventes et dans la table inserted, et le
déclencheur s'exécute ensuite.
Pour tester le déclencheur conditionnel, insérez quatre lignes dans la table nouvelles_ventes. Les id_titre
de deux lignes de cette table ne correspondent pas aux valeurs spécifiées dans la table titres. Les données à insérer
sont les suivantes:
nouvelles_ventes
id_mag
-------
num_cmd
--------
date qt
modepaiements
--------------------
id_titre
--------
-------
7066
7066
7067
7131
QA7442.3
Jul 25 1995 8:35AM
QA7442.3
Jul 24 1995 8:35AM
D4482 Jul 27 1995 12:00AM
10
N914008
Jul 27 1995 12:00AM
75
Net 30
PS1372
75
Net 60
BU7832
Net 30
PSxxxx
20
Net 30
PSyyyy
Ensuite, insérez des données de la table nouvelles_ventes dans la table ventes. L'instruction est la suivante:
INSERT ventes
SELECT * FROM nouvelles_vents
Les lignes id_titre PSxxxx et PSyyyy ne correspondent à aucune ligne de la table titres, et le déclencheur
ins_condition supprime ces deux lignes des tables ventes et inserted.
Deux tables spéciales sont utilisées dans les instructions de déclencheurs: la table deleted et la table inserted. Ces tables temporaires s'utilisent pour les tests de déclencheurs. Elles servent à tester les effets de certaines
modifications de données et à fixer les conditions pour les actions des déclencheurs. Vous ne pouvez pas modifier
directement les données contenues dans les tables test d'un déclencheur, mais vous pouvez employer ces tables dans
des instructions SELECT pour détecter les effets d'une instruction INSERT, UPDATE, ou DELETE.

 La table deleted stocke des copies de lignes concernées par les instructions DELETE ou UPDATE. Pendant l'exécution d'une instruction DELETE ou UPDATE, certaines lignes sont supprimées de la table du déclencheur et déplacées vers la table deleted. Cette table et la table du déclencheur n'ont habituellement pas de ligne en commun.

 La table inserted stocke des copies de lignes concernées par les instructions INSERT et UPDATE. Pendant une
transaction INSERT ou UPDATE, de nouvelles lignes sont ajoutées simultanément dans la table inserted et dans
la table du déclencheur. Les lignes de la table inserted sont des copies des nouvelles lignes de la table du déclencheur.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 73
 D'un point de vue théorique, une transaction UPDATE est une opération de suppression suivie d'une opération
d'insertion; les anciennes lignes sont d'abord copiées dans la table deleted, et les nouvelles lignes sont ensuite copiées dans la table du déclencheur et dans la table inserted.
Lorsque vous fixez les conditions d'un déclencheur, utilisez les tables test du déclencheur appropriées pour
la modification de données. Vous pouvez faire référence à la table deleted pendant le test d'un déclencheur INSERT,
ou à la table inserted pendant le test d'un déclencheur DELETE, mais dans de tels cas, les tables test ne contiendront
pas de ligne.
Remarque Un déclencheur donné ne s'exécute qu'une fois par instruction. Si l'action d'un déclencheur dépend du nombre de lignes affectées par une modification de données, vous devriez tester les modifications de données multilignes (effectuées au moyen d'une instruction INSERT, DELETE ou UPDATE basée sur une instruction
SELECT) à l'aide d'un test comme @@ROWCOUNT, et définir les actions appropriées.
Les exemples de déclencheurs présentés dans les sections suivantes contiennent des modifications de données multilignes lorsque c'est nécessaire. La variable @@ROWCOUNT, qui stocke le nombre de lignes affectées par
la dernière modification de données, recherche toute insertion, mise à jour ou suppression de plusieurs lignes. Si une
instruction SELECT précède le test sur @@ROWCOUNT dans le déclencheur, vous devriez utiliser des variables
locales pour stocker la valeur afin de l'analyser par la suite. (Toutes les instructions Transact-SQL qui ne renvoient
pas de valeur remettent @@ROWCOUNT à 0).
Lorsque des déclencheurs contenant des instructions ROLLBACK TRANSACTION sont exécutés à partir
d'un lot d'instructions, ils annulent le lot entier. Dans l'exemple suivant, si l'instruction INSERT active un déclencheur
contenant une instruction ROLLBACK TRANSACTION, l'instruction DELETE ne s'exécute pas parce que le lot est
annulé:
INSERT employé VALUES ('XYZ12345M', 'Nouveau', 'M', 'Employé', 1, 1,
'9952', '6/1/95')
DELETE employé WHERE id_employé = 'PMA42628M'
Si des déclencheurs contenant des instructions ROLLBACK TRANSACTION sont exécutés à partir d'une
transaction définie par l'utilisateur, l'instruction ROLLBACK TRANSACTION annule toute la transaction. Dans
l'exemple suivant, si l'instruction INSERT active un déclencheur contenant une instruction ROLLBACK TRANSACTION, l'instruction UPDATE est également annulée:
BEGIN TRAN
UPDATE employé SET date_embauche = '7/1/94' WHERE id_employé =
'VPA30890F'
INSERT employé VALUES ('XYZ12345M', 'Nouveau', 'M', 'Employé', 1, 1,
'9952', '6/1/95')
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 74
Lorsque des déclencheurs contenant des instructions ROLLBACK TRANSACTION sont exécutés à partir
d'un lot d'instructions, ils annulent le lot entier. Dans l'exemple suivant, si l'instruction INSERT active un déclencheur
contenant une instruction ROLLBACK TRANSACTION, l'instruction DELETE ne s'exécute pas parce que le lot est
annulé:
INSERT employé VALUES ('XYZ12345M', 'Nouveau', 'M', 'Employé', 1, 1,
'9952', '6/1/95')
DELETE employé WHERE id_employé = 'PMA42628M'
Si des déclencheurs contenant des instructions ROLLBACK TRANSACTION sont exécutés à partir d'une
transaction définie par l'utilisateur, l'instruction ROLLBACK TRANSACTION annule toute la transaction. Dans
l'exemple suivant, si l'instruction INSERT active un déclencheur contenant une instruction ROLLBACK TRANSACTION, l'instruction UPDATE est également annulée:
BEGIN TRAN
UPDATE employé SET date_embauche = '7/1/94' WHERE id_employé =
'VPA30890F'
INSERT employé VALUES ('XYZ12345M', 'Nouveau', 'M', 'Employé', 1, 1,
'9952', '6/1/95')
Comme les déclencheurs sont des objets de base de données, ils sont classés par nom dans la table système
sysobjects. La colonne type de la table sysobjects identifie les déclencheurs à l'aide de l'abréviation TR. Les plans
d'exécution des déclencheurs sont stockés dans la table sysprocedures.
La requête suivante extrait les déclencheurs d'une base de données:
SELECT *
FROM sysobjects
WHERE type = 'TR'
name
----------------employé_insmaj
déclench_suppr
schema
-----0
0
refdate
------------------août 9 1995 10:04
août 21 1995 16:07
deltrig
------688005482
416004513
ALSI / S25
id
---------832005995
1392007990
instrig
------0
0
type
---TR
TR
userstat
-------0
0
crdate
------------------août 9 1995 10:04
août 21 1995 16:07
updtrig
------0
0
SGBD R Support de Cours 2TSIG
uid
--1
1
seltrig
------0
0
indexdel
-------0
0
version
------0
0
category
-------0
0
cache
----0
0
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 75
Pour afficher des informations sur un déclencheur
 Exécutez la procédure système sp_help.
Par exemple, pour obtenir des informations sur le déclencheur déclench_suppr:
sp_help déclench_suppr
Name
---déclench_suppr
Owner
----dbo
Type
------trigger
When_created
-----------------août 21 1995 16:07
Data_located_on_segment
----------------------not applicable
L'instruction CREATE TRIGGER de chaque déclencheur est stockée dans la table système syscomments.
Vous pouvez afficher la définition d'un déclencheur en utilisant la procédure système sp_helptext:
sp_helptext déclench_suppr
text
------------------------------------------create trigger déclench_suppr
on ventes
for delete
as
...
Vous pouvez éliminer un déclencheur en le supprimant ou en supprimant sa table. Lorsqu'une table est
supprimée, tous les déclencheurs qui lui sont associés sont également supprimés. La permission DROP TRIGGER
revient par défaut au propriétaire du déclencheur et n'est pas transmissible.
Pour supprimer un déclencheur
 Utilisez Microsoft SQL Enterprise Manager.
Ou
 Utilisez l'instruction DROP TRIGGER.
Par exemple:
DROP TRIGGER déclench_suppr
supprime le déclencheur déclench_suppr de la table ventes.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 76
13 9. SECURITE ET CONNEXION A UNE BASE SQL SERVER 6.5
La sécurité SQL Server 6.5 comprend plusieurs éléments qui, ensemble, vous permettent de définir :
 les utilisateurs autorisés à se connecter à un SQL Server;
 les utilisateurs autorisés à accèder à chaque base de données sur SQL Server;
 les objets de base de données (tables, vues, procédures stockées) auxquels chaque utilisateur est autorisé à accéder et/ou qu'il peut modifier;
 les instructions Transact-SQL que chaque utilisateur est autorisé à effectuer pour chaque base de données à laquelle il a accès.
La sécurité de SQL Server protège un serveur et les données stockées sur ce serveur. Elle détermine :




les personnes qui peuvent se connecter au serveur,
les tâches administratives que les utilisateurs peuvent exécuter,
les bases de données,
les objets de base de données et les données auxquelles les utilisateurs peuvent avoir accès, etc.
13.1 9.1 Terminologie de la sécurité
13.1.1.1.1.1.1.1 L'administrateur système
L'administrateur système est responsable des travaux administratifs et des fonctions opérationnelles qui
ne sont pas liés à une application particulière. Il s'agira probablement d'une personne qui possède une vue d'ensemble complète de SQL Server et de l'ensemble de ses applications.












L'administration de SQL Server consiste généralement à :
installer SQL Server,
créer des serveurs et des clients,
gérer et surveiller l'utilisation de l'espace disque,
la mémoire et les connexions,
créer des unités et des bases de données,
ouvrir des comptes SQL Server à des utilisateurs et leur accorder des permissions, transférer des données vers
et depuis les bases de données SQL Server,
sauvegarder et restaurer des bases de données,
procéder à l'application et à la maintenance de la réplication,
programmer des opérations sous surveillance indirecte,
surveiller et régler SQL Server pour optimiser ses performances,
diagnostiquer les problèmes relatifs au système.
L'administrateur système peut également conseiller les créateurs d'applications à propos des données
qui existent déjà sur le système SQL Server, faire des recommandations sur la standardisation des définitions des
données dans les applications, etc.
L'administrateur système opère en dehors du système de sécurité. Autrement dit, SQL Server ne vérifie
jamais les permissions de l'utilisateur reconnu comme administrateur système. Celui-ci est également considéré
comme le propriétaire de toute base de données qu'il utilise. Toute personne connaissant le mot de passe de l'administrateur système peut se connecter en tant qu'administrateur et jouir de toutes les prérogatives attachées à cette
fonction (sauf si le serveur tourne en mode de sécurité de connexion approuvée).
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 77
13.1.1.1.1.1.1.2 Alias
Nom d'utilisateur de base de données partagé par plusieurs noms d'accès. Un alias de base de données
vous permet de traiter plusieurs personnes comme un seul utilisateur à l'intérieur d'une base de données, en accordant à toutes les mêmes permissions. N'importe quel nom d'utilisateur dans une base de données peut également
servir d'alias.
Les alias s'emploient souvent pour autoriser plusieurs utilisateurs à jouer le rôle de propriétaire de base
de données.
13.1.1.1.1.1.1.3 Connexions approuvées
La sécurité intégrée exige des protocoles de réseau qui gèrent les connexions authentifiées entre les
clients et les serveurs. Ces connexions sont désignées sous le nom de connexions approuvées. Les protocoles multiprotocole et canal de communication nommé offrent des connexions approuvées.
Notez que les connexions non-approuvées (connexions via d'autres protocoles de réseau qui ne gèrent
pas les connexions authentifiées) doivent être gérées à l'aide de la sécurité standard de SQL Server.
13.1.1.1.1.1.1.4 Domaine
Dans la sécurité de Windows NT, un domaine est un ensemble d'ordinateurs qui sont regroupés pour
des raisons de visualisation et d'administration et qui partagent une base de données de sécurité commune.
Groupe
Dans SQL Server, un groupe de base de données est un ensemble d'utilisateurs de base de données. Les
utilisateurs reçoivent les permissions sur les bases de données accordées au groupe. L'utilisation de groupes simplifie la gestion d'un grand nombre d'utilisateurs de base de données, car les groupes facilitent l'accord et la révocation des permissions pour plusieurs utilisateurs en même temps.
Dans Windows NT, un groupe est un ensemble d'utilisateurs de Windows NT. Les utilisateurs ont reçu
les droits et les permissions Windows NT accordées au groupe. Ceux-ci offrent un moyen pratique de gérer les
possibilités offertes à un grand nombre d'utilisateurs qui ont les mêmes besoins, et ce dans le contexte de sécurité
d'un domaine ou d'un ordinateur.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 78
Mode de sécurité d'accès
Le mode de sécurité d'accès détermine le mode de validation d'une demande d'accès par SQL Server. Il
existe trois types de sécurité d'accès : intégrée, standard et mixte.
Nom d'accès
Un nom d'accès est le nom sous lequel SQL Server connaît un utilisateur. Le terme accès remplace parfois le terme nom d'accès.
Pour se connecter à un serveur SQL qui utilise la sécurité standard, un utilisateur doit fournir un nom
d'accès et un mot de passe valides.
Nom d'utilisateur
Dans SQL Server, un nom d'utilisateur de base de données est un nom attribué à un nom d'accès en vue
de permettre à un utilisateur d'avoir accès à une base de données. Les possibilités dont dispose un utilisateur à
l'intérieur d'une base de données dépendent des permissions accordées au nom d'utilisateur (et à des groupes dont
le nom d'utilisateur fait partie).
Dans Windows NT, un nom d'utilisateur est le nom sous lequel le domaine ou un ordinateur Windows
NT individuel connaît l'utilisateur. Un utilisateur se connecte à un domaine ou un ordinateur en fournissant un
nom d'utilisateur et un mot de passe valides. Les possibilités dont dispose l'utilisateur dépendent des droits et des
permissions Windows NT accordées à ce nom d'utilisateur et aux groupes Windows NT dont le nom d'utilisateur
fait partie.
Permissions
Microsoft SQL Server utilise les permissions pour appliquer la sécurité de la base de données. Le système de permissions de SQL Server spécifie quels sont les utilisateurs qui sont autorisés à utiliser telles ou telles
instructions Transact-SQL, des vues et des procédures stockées. La faculté d'accorder des permissions est déterminée par le statut de chaque utilisateur (administrateur système, propriétaire de base de données ou propriétaire
d'objet de base de données).
Il en existe deux types : les permissions d'objet et les permissions d'instruction.
Permissions d'instruction
Les permissions d'instruction accordent le privilège d'utiliser certaines instructions Transact-SQL. Les
permissions d'instruction ne sont liées à aucun objet spécifique. Ces permissions ne peuvent être accordées que par
l'administrateur système (SA) ou le propriétaire de base de données.
Les permissions d'instruction s'appliquent aux instructions suivantes : CREATE DATABASE,
CREATE DEFAULT, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, CREATE VIEW, DUMP
DATABASE et DUMP TRANSACTION.
Les permissions d'instruction sont également appelées permissions commande.
Permissions d'objet
Les permissions d'objet réglementent l'utilisation de certaines instructions sur certains objets de base de
données déterminés. Elles sont accordées et révoquées par le propriétaire de l'objet.
Propriétaire de base de données
Le propriétaire d'une base de données (DBO, Database Owner) est l'utilisateur qui la crée. Il n'y a qu'un
seul DBO. Il dispose de toute latitude d'action dans sa base de données et détermine l'accès et les possibilités dont
disposent les autres utilisateurs.
Dans sa propre base de données, l'utilisateur est reconnu comme DBO; dans les autres bases de données, le propriétaire de base de données est identifié par son nom d'utilisateur de base de données.
Le statut de DBO peut être réaffecté à un autre utilisateur. Un seul nom d'accès peut être DBO, même si
d'autres noms d'accès peuvent utiliser DBO comme alias.
Propriétaire d'objet de base de données
On appelle objets de base de données les tables, les index, les vues, les valeurs par défaut, les déclencheurs, les règles et les procédures. L'utilisateur qui crée un objet de base de données devient le propriétaire de
l'objet de base de données et reçoit automatiquement toutes les permissions applicables à son objet. Le proprié-
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 79
taire d'objet de base de données peut accorder une permission à d'autres utilisateurs pour qu'ils utilisent cet objet.
La propriété d'objet de base de données n'est pas transmissible.
Sécurité intégrée
La sécurité intégrée permet à un serveur SQL d'utiliser les mécanismes d'authentification de Windows
NT pour valider les noms d'accès pour toutes les connexions. Seules les connexions approuvées (multiprotocole
ou canaux de communication nommés) sont autorisées.
Sécurité mixte
La sécurité mixte permet de valider les demandes de connexion à l'aide de la sécurité intégrée ou de la
sécurité standard. Il est possible d'établir des connexions approuvées (telles qu'elles sont utilisées par la sécurité
intégrée) et des connexions non approuvées (telles qu'elles sont employées par la sécurité standard).
Sécurité standard
La sécurité standard utilise le processus de validation de noms d'accès de SQL Server pour toutes les
connexions. Pour se connecter sur un serveur SQL, chaque utilisateur doit fournir un nom d'accès et un mot de
passe valides.
Chaque serveur SQL doit être configuré pour l'un des trois modes de sécurité disponibles.
 La sécurité intégrée permet à un serveur SQL d'utiliser les mécanismes d'authentification Windows NT
pour valider les noms d'accès SQL Server pour toutes les connexions. Seules les connexions approuvées
(multiprotocole ou canal de communication nommé) sont autorisées.
 La sécurité standard utilise le propre processus de validation de nom d'accès de SQL Server pour toutes
les connexions.
 La sécurité mixte permet la validation des demandes de connexion de SQL Server à l'aide des méthodes
de sécurité intégrée ou standard. Les connexions approuvées (telles qu'elles sont utilisées par la sécurité
intégrée) et les connexions non approuvées (telles qu'elles sont employées par la sécurité standard) sont
supportées.
En outre, quel que soit le mode de sécurité de connexion du serveur, les applications clientes ODBC et
DB-library peuvent être configurées de façon à toujours demander une connexion approuvée depuis le serveur.
La sécurité intégrée assure l'intégration de la sécurité d'accès SQL Server dans la sécurité Windows NT.
Cette intégration permet à un utilisateur du réseau de se connecter à un serveur SQL sans fournir un nom d'accès ou
un mot de passe séparés. Les utilisateurs peuvent avoir un même nom d'accès et mot de passe pour Windows NT et
pour SQL Server.
La sécurité intégrée permet aux applications SQL Server de bénéficier des fonctions de sécurité de Windows NT, à savoir les mots de passe codés, l'expiration du mot de passe, les comptes utilisateur au niveau du domaine et l'administration des utilisateurs sous Windows. (Pour plus d'informations sur ces fonctions, consultez votre
documentation sur Windows NT.)
SQL Server réalise l'intégration de la sécurité d'accès dans Windows NT en utilisant les attributs de sécurité Windows NT d'un utilisateur de réseau pour contrôler l'accès à SQL Server. Les attributs de sécurité de réseau d'un
utilisateur sont définis lors de la connexion sur le réseau et sont validés par Windows NT au moyen d'un mécanisme
de codage de mot de passe sophistiqué. Quand un utilisateur de réseau essaie de se connecter à SQL Server, ce dernier utilise les ressources de Windows NT pour déterminer avec certitude le nom d'utilisateur de réseau validé du
client. SQL Server autorise ou refuse ensuite l'accès basé sur ce nom d'utilisateur de réseau seul, sans exiger l'envoi
d'un nom d'accès et d'un mot de passe séparés dans la demande de connexion du client.
L'intégration de la sécurité d'accès fonctionne au moyen de protocoles de réseau qui gèrent les connexions
authentifiées entre les clients et les serveurs. Ces connexions sont désignées sous le nom de connexions approuvées.
Les connexions approuvées comprennent les sessions multiprotocole et canal de communication nommé
provenant d'autres stations de travail tournant sous Windows NT, de clients tournant sous Windows pour Workgroups, de clients Windows 95 et de clients Microsoft LAN Manager tournant sous les systèmes d'exploitation Microsoft Windows ou MS-DOS. L'utilisation de la Net-Library multiprotocole permet de faire les connexions approuvées via le protocole NWLink IPX/SPX à partir de clients Windows 3.1 qui utilisent le logiciel Novell. (Dans ce cas,
l'utilisateur sera invité à fournir un nom d'utilisateur et un mot de passe Windows NT lors de la connexion avec SQL
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 80
Server.) Les autres protocoles de réseau ne gèrent pas les connexions authentifiées. Les clients qui utilisent ces protocoles devront donc être gérés suivant la sécurité standard de SQL Server.
Quand un utilisateur de la sécurité intégrée se connecte à SQL Server via une connexion approuvée, l'utilisateur est mappé sur un nom d'accès SQL Server, sur un nom d'accès SQL Server par défaut (généralement appelé
invité), ou, si l'utilisateur possède des privilèges administratifs, sur l'administrateur système.
Une fois que le processus de connexion est terminé, tous les autres contrôles de sécurité SQL Server sont
gérés suivant la procédure habituelle. L'accès aux tables SQL Server individuelles, par exemple, est géré au moyen
des permissions accordées à l'intérieur d'une base de données SQL Server.
Si plusieurs ordinateurs SQL Server participent à un domaine géré par un serveur Windows NT, une seule connexion
réseau pour chaque utilisateur suffit pour permettre l'accès à tous les serveurs SQL. Pour les utilisateurs définis sur
d'autres domaines,
La sécurité standard utilise le processus de validation d'accès SQL Server pour toutes les connexions.
Pour se connecter à un serveur SQL, chaque utilisateur doit fournir un nom d'accès et un mot de passe valides.
Lorsque le mode de sécurité d'accès d'un serveur est configuré sur standard et qu'un utilisateur essaie de se
connecter au serveur, SQL Server cherche le nom d'accès et le mot de passe de l'utilisateur dans la table syslogins. Si
le nom d'accès et le mot de passe indiqués sont valides, l'utilisateur est connecté au serveur SQL.
Pour obtenir des instructions sur la configuration de la sécurité standard (en commençant par un aperçu de
chaque tâche nécessaire pour procéder à la configuration), consultez le chapitre 9, Gestion de la sécurité.
La sécurité mixte permet de valider les demandes de connexion SQL Server en utilisant les méthodes de
sécurité intégrée ou de sécurité standard. Les connexions approuvées (telles qu'elles sont utilisées par la sécurité intégrée) et les connexions non approuvées (telles qu'elles sont employées par la sécurité standard) sont supportées.
Lorsque le mode de sécurité d'accès d'un serveur est configuré sur mixte et qu'un utilisateur essaie de se
connecter au serveur via une connexion approuvée (multiprotocole ou canal de communication nommé), SQL Server
vérifie le nom d'accès.

 Si ce nom d'accès correspond au nom d'utilisateur de réseau de l'utilisateur, ou si le nom d'accès est vierge ou contient des espaces, SQL Server utilise les règles de connexion approuvées Windows NT (comme pour la sécurité
intégrée).
 Si le nom d'accès demandé représente toute autre valeur, l'utilisateur doit fournir le mot de passe SQL Server correct et SQL Server utilise son propre processus de validation de connexion (comme pour la sécurité standard).
Si la tentative d'accès ne se fait pas via une connexion approuvée, l'utilisateur doit fournir le nom d'accès et
le mot de passe corrects pour établir la connexion, et SQL Server utilise son propre processus de validation de connexion (comme pour la sécurité standard).
Quel que soit le mode de sécurité d'accès du serveur, il est possible de configurer les applications clientes
ODBC et DB-Library de façon à ce qu'elles demandent toujours une connexion approuvée (sécurité intégrée) au serveur. Cette fonction permet à SQL Executive de se connecter à des serveurs distants tant qu'il tourne sur un compte
d'utilisateur Windows NT correctement configuré, un compte qui bénéficie de l'accès SA au serveur SQL.
Cette fonction assure une connexion approuvée, mais les niveaux de privilège d'utilisateur appropriés doivent exister pour garantir la réussite de l'accès. Si vous le souhaitez, vous pouvez empêcher la connexion via des
connexions approuvées demandées par des clients en utilisant la procédure suivante :
1. utilisez l'utilitaire Gestionnaire d'utilisateurs Windows NT pour créer un groupe d'utilisateurs Windows NT
local (par exemple SQLAdmins) qui ne contient pas d'utilisateur;
2. utilisez le Gestionnaire de sécurité SQL pour accorder le privilège d'administrateur système à ce groupe vide;
3. utilisez le Gestionnaire de sécurité SQL pour retirer le privilège d'administrateur système au groupe local
Administrateurs.
La sécurité étant attribuée par utilisateur plutôt que par ressource dans Windows NT, un utilisateur ne doit
se souvenir que d'un seul nom de compte utilisateur et d'un seul mot de passe Windows NT pour se connecter et utiliser les ressources locales et les ressources du réseau. Avec la sécurité Windows NT, l'ensemble des ressources et des
actions sont protégées par un contrôle d'accès discrétionnaire. Autrement dit, certains utilisateurs peuvent avoir accès à une ressource ou exécuter une action, tandis que d'autres se voient interdire l'accès à la même ressource ou à la
même action. La sécurité est très souple, par exemple, différentes permissions pour différents utilisateurs peuvent être
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 81
définies sur différents fichiers d'un répertoire. La sécurité Windows NT s'applique à la fois aux utilisateurs qui travaillent sur l'ordinateur où se trouvent les ressources et aux utilisateurs qui ont accès aux ressources via le réseau.
Pour Windows NT, l'unité de base pour la sécurité et l'administration centralisée est le domaine. Un domaine est un ensemble d'ordinateurs qui reconnaissent une base de données de comptes de sécurité commune. Quatre
types d'ordinateurs participent à la sécurité des domaines : les contrôleurs de domaine primaires (PDC, Primary Domain Controller), les contrôleurs de domaine de sauvegarde (BDC, Backup Domain Controller), les serveurs et les
stations de travail.
Chaque domaine possède un PDC qui tient à jour la base de données des comptes de sécurité pour le domaine. Un domaine peut posséder n'importe quel nombre de BDC. Chaque BDC reçoit une copie de la base de données de comptes de sécurité du domaine et assiste le PDC en partageant la charge de l'authentification des connexions
de domaine. Les ordinateurs PDC et BDC exécutent toujours le système d'exploitation Windows NT Server.
D'autres ordinateurs peuvent être membres du domaine. Un ordinateur membre participe à la sécurité du
domaine. Il tient à jour sa propre base de données locale de comptes de sécurité et peut authentifier les accès locaux,
mais il peut aussi accepter des authentifications de connexion d'utilisateurs du domaine qui ont été exécutées par le
PDC et les BDC du domaine. Un ordinateur qui exécute le système d'exploitation Windows NT Server peut être un
serveur membre du domaine. Un ordinateur qui exécute le système d'exploitation Windows NT Workstation peut être
une station de travail membre du domaine.
D'autres types d'ordinateurs, par exemple les ordinateurs tournant sous Microsoft MS-DOS, ne stockent
pas les comptes d'utilisateur et ne participent pas à la sécurité du domaine. Toutefois, de tels ordinateurs possèdent en
général un domaine par défaut déclaré pour permettre de parcourir les listes d'ordinateurs.
Quand SQL Server est configuré pour l'intégration de la sécurité sous Windows NT, il exploite les possibilités de Windows NT en matière de sécurité. Les utilisateurs Windows NT peuvent être autorisés à se connecter à
SQL Server. Les comptes utilisateur Windows NT peuvent être mappés sur les noms d'accès. Ceci permet à un utilisateur de se connecter à SQL Server sans fournir un nom d'accès et un mot de passe séparés. Le nom d'accès d'origine
de l'utilisateur pour accéder à Windows NT suffit pour permettre (ou, le cas échéant, pour refuser) l'accès à SQL Server. Avec l'intégration de la sécurité, les utilisateurs conservent un même nom d'accès et mot de passe pour Windows
NT et pour SQL Server.
Grâce à l'intégration de la sécurité, la sécurité au niveau des bases de données est toujours gérée par le
propriétaire de base de données. Les groupes et les utilisateurs de bases de données sont créés de la même façon que
n'importe quelle installation SQL Server, et les permissions d'objet et d'instruction sont accordées normalement.
L'illustration ci-dessous montre que des éléments de sécurité séparés mais étroitement liés existent au niveau du domaine, de l'ordinateur, de SQL Server et des bases de données.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 82
Il peut y avoir plusieurs domaines Windows NT sur un réseau ou dans une entreprise. Les relations d'approbation sont des accords de sécurité passés entre les domaines. Ces accords permettent une émulation de l'authentification de l'accès d'utilisateur. Les relations d'approbation et l'émulation d'authentification permettent à un utilisateur
de n'avoir qu'un seul compte utilisateur dans un domaine tout en ayant accès à l'ensemble du réseau.
Quand deux domaines sont configurés pour s'approuver, un utilisateur d'un domaine peut se voir accorder
(ou refuser) l'accès aux ressources et aux actions pour les ordinateurs de l'autre domaine. Le domaine d'approbation
accepte comme les authentifications d'accès qu'il reçoit du domaine approuvéde la même manière qu'un ordinateur
membre d'un domaine accepte comme valides les authentifications d'accès qu'il reçoit des PDC et des BDC de son
propre domaine.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 83
13.2 9.2 Mise en œuvre de la sécurité
La gestion de la sécurité de SQL Server consiste à :

définir le mode de sécurité d'accès au serveur,

autoriser l'accès des utilisateurs Windows NT à SQL Server,

gérer les noms d'accès SQL Server,

gérer les utilisateurs de bases de données,

gérer les permissions objet et instruction,

modifier les propriétaires de bases de données,

manipuler les outils de gestion de la sécurité fournis par SQL Server
Les options de sécurité permettent de déterminer le mode sécurisé d'accès au serveur, le niveau d'audit des
connexions, et le mappage des caractères des noms d'utilisateurs de Windows NT sur les caractères disponibles sous
SQL Server. Vous pouvez utiliser le programme d'installation de SQL Server ou SQL Enterprise Manager pour définir ou modifier ces options de sécurité. Les procédures décrites dans cette section supposent que vous utilisiez SQL
Enterprise Manager.
13.2.1
9.2.1 Définir les options de sécurité du serveur
1.
Lancez SQL Enterprise Manager à partir du groupe de programmes de Microsoft SQL Server 6.5
2.
Ouvrez un groupe de serveurs dans la fenêtre Gestionnaire de serveurs, puis sélectionnez le
serveur à administrer.
3.
Sélectionnez l'option «Configurations» dans le menu Serveur.
4.
Choisissez l'onglet Options de sécurité dans la boîte de dialogue Configuration/Options serveur.
La fenêtre Options de sécurité affiche la configuration courante des options.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 84
5.
Visualisez et modifiez les options selon vos besoins.
Une explication de chaque option vous est proposée à la suite de cette procédure.
6.
Après avoir configuré les options de sécurité, choisissez «OK».
SQL Server 6.5 vous permet de choisir parmi trois modes sécurisés d'accès. Votre choix aura une influence
non négligeable sur la façon dont le serveur gérera la sécurité. Les informations qui suivent se bornent à résumer
chaque mode de sécurité d'accès (standard, intégré, ou mixte).
13.2.2
13.2.3
Sécurité mode Standard
Le mode Standard est la valeur par défaut. En mode Standard, SQL Server gère son propre processus de
validation des connexions pour toutes les connexions (sauf pour les applications clientes qui requièrent explicitement
la sécurité intégrée sur les connexions approuvées).
Remarque
Le serveur peut être configuré de façon à refuser les connexions approuvées requises par le
client, mais alors, les applications et les fonctions qui utilisent des connexions approuvées forcées (telles que SQL
Enterprise Manager et la réplication)connaîtront des restrictions. Pour plus de détails sur le sujet, consultez Refus de
connexions approuvées demandées par un client.
13.2.4
Sécurité mode Intégré à Windows NT
Le mode Intégré utilise les mécanismes d'authentification de Windows NT pour toutes les connexions.
SQL Server n'admet que les connexions approuvées. Il ignore toujours le nom d'accès et le mot de passe SQL Server
soumis dans la requête de connexion à partir d'une application cliente DB-Library ou Open Database Connectivity
(ODBC). Les utilisateurs de réseau bénéficiant de privilèges SQL Server de niveau utilisateur peuvent se connecter à
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 85
l'aide de leur nom d'utilisateur réseau ou du nom d'accès par défaut (si leur nom d'utilisateur réseau ne se trouve pas
dans la table syslogins). Les noms d'utilisateurs réseau auxquels est affecté un privilège de niveau administrateur
système se connectent en tant que SA.
Avec cette option, seuls les clients de type multiprotocole et canaux nommés sont supportés. Cette option
n'est pas disponible si vous avez installé des Net-Libraries. De plus, si vous utilisez cette option, vous ne pouvez pas
installer d'autres Net-Libraries de serveur.
13.2.5
Sécurité mode Mixte
Le mode Mixte, combinaison des modes Intégré et Standard, vous permet d'établir des connexions approuvées et non-approuvées. Pour les connexions approuvées (multiprotocole ou canaux de communication nommés),
SQL Server examine le nom d'accès requis tel qu'il est spécifié par l'application cliente DB-Library ou ODBC. Si ce
nom d'accès correspond au nom d'utilisateur de réseau de l'utilisateur, ou s'il est nul ou constitué d'espaces, SQL Server tentera tout d'abord d'appliquer les règles de connexion intégrées à Windows NT comme décrit ci-dessus. Si cette
opération échoue, SQL Server utilisera les règles standard. Si le nom d'accès requis a une autre valeur, l'utilisateur
devra fournir le mot de passe SQL Server correct, et SQL Server traitera la connexion à l'aide des règles standard
décrites ci-dessus. Toutes les demandes de connexion issues de connexions non-approuvées sont traitées selon les
règles standard.
Le mode mixte offre aux utilisateurs l'avantage très commode d'offrir la sécurité intégrée sans forcer pour
autant tous les clients et les applications à employer ce mécanisme. Par exemple, les applications existantes qui renferment un mot de passe et un nom d'accès spécifiques pour tous les utilisateurs, continuent de fonctionner comme
avant. Les clients qui n'appartiennent pas au monde PC (comme les stations de travail Apple® Macintosh® et UNIX®)
peuvent également accéder à un serveur SQL configuré en mode mixte. Les utilisateurs qui se connectent à SQL Server via des connexions approuvées peuvent éviter une validation du mot de passe propre à SQL Server en introduisant leur nom d'utilisateur de réseau ou en introduisant des blancs dans leur requête de connexion.
Dans tous les modes de sécurité d'accès, les communications de serveur-à-serveur (procédures stockées
distantes) sont gérées à l'aide des mécanismes standard de SQL Server.
SQL Server vous laisse également le soin de spécifier les paramètres suivants :
13.2.6
Accès par défaut
Nom d'accès SQL Server, utilisé par un utilisateur autorisé sur les connexions approuvées lorsque le nom
d'utilisateur réseau de cet utilisateur n'apparaît pas dans la table syslogins. Si cette zone de saisie reste vierge, les
utilisateurs dont le nom d'utilisateur réseau ne figure pas dans la table syslogins se verront refuser l'accès au serveur,
même si des privilèges utilisateur leur ont été accordés comme décrit dans les pages suivantes.
Remarquez que l'administrateur système (SA) doit créer un nom d'accès correspondant au nom spécifié
comme défaut. Vous ne créerez pas automatiquement le nom d'accès en introduisant ce nom dans la zone «Accès par
défaut».
Ce paramètre n'est pas utilisé en mode standard, sauf pour les applications clientes qui requièrent explicitement une connexion approuvée.
13.2.7
Domaine par défaut
Nom du domaine Windows NT, spécifié comme valeur par défaut pour établir une correspondance entre
les noms d'utilisateur réseau et les noms d'accès SQL Server. Du fait que le même nom d'utilisateur réseau peut être
défini dans deux domaines différents pour deux utilisateurs distincts, et que tous deux peuvent bénéficier d'un droit
d'accès à SQL Server, ce dernier doit pouvoir faire la distinction entre les deux noms dans le processus de connexion
d'une connexion approuvée. Pour les noms d'utilisateur réseau définis dans des domaines différents du domaine par
défaut spécifié, SQL Server ajoute le nom du domaine et un caractère de mappage, comme le signe souligné ( _ ), au
nom d'utilisateur réseau avant de consulter la table syslogins.
Supposons par exemple que le domaine MARKETING est défini comme valeur par défaut de SQL Server,
et que le nom d'utilisateur réseau jacques est utilisé par deux utilisateurs différents (l'un dans le domaine MARKETING et l'autre dans le domaine INGÉNIEURS. Si Jacques dans Marketing utilise une connexion approuvée, il accèdera à SQL Server en utilisant le nom d'accès jacques (à condition bien sûr que cette entrée existe dans syslogins).
Jacques dans Ingénieurs accèdera au même serveur SQL en utilisant le nom d'accès INGÉNIEURS_ Jacques. Si la
majorité de vos utilisateurs appartient à un domaine bien particulier, définissez celui-ci comme Domaine par Défaut.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 86
Si votre ordinateur serveur ne fait pas partie d'un domaine, donnez au Domaine par Défaut le nom de l'ordinateur
serveur.
Ce paramètre n'est pas utilisé en mode standard, sauf pour les applications clientes qui requièrent explicitement une connexion approuvée.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 87
13.2.8
Modifier le nom d'hôte en nom d'utilisateur
Détermine si le nom d'hôte de l'enregistrement de la connexion cliente est remplacé par le nom d'utilisateur
réseau Windows NT pour les utilisateurs sous connexions approuvées. Si cette option est sélectionnée, le nom d'utilisateur réseau apparaîtra, par exemple, dans la sortie de la procédure système sp_who. Par défaut, cette option ne fait
pas la substitution.
13.2.9
Niveau d'audit
Les tentatives d'accès effectuées par les utilisateurs peuvent être enregistrées avec les autres informations
du journal de SQL Server. L'audit peut être activé pour les trois modes de sécurité; il permet d'enregistrer des informations à la fois sur les connexions approuvées et non-approuvées.
Vous pouvez choisir de ne pas effectuer l'audit (valeur par défaut), ou d'enregistrer les tentatives d'accès
réussies, les tentatives d'accès infructueuses, ou même les deux à la fois. L'enregistrement de ces événements apparaît
dans le journal des événements de Windows NT ou dans le journal des erreurs de SQL Server, ou dans ces deux programmes, selon la façon dont vous avez configuré la consignation de ces événements pour votre serveur SQL. Pour
plus d'informations sur la configuration des opérations de consignation d'événements dans SQL Server, consultez le
chapitre 3, Configuration des serveurs.
13.2.10
13.2.11 Mappage
Les noms d'utilisateur de Windows NT comprennent certains caractères qui ne sont pas valides dans les
noms d'accès SQL Server (comme par exemple, les tirets, les espaces et le point). Lorsque vous utilisez des options
de sécurité intégrées ou les connexions approuvées requises par le client, les mappages vous permettent d'indiquer
comment traiter ces caractères dans SQL Server. Par exemple, le nom d'utilisateur Windows NT t-Jacques, qui ne
serait pas valide par SQL Server, peut être mappé sur t_Jacques en mappant le signe souligné (_) de SQL Server sur
le tiret (-) de Windows NT. Les caractères de mappage admis sont le séparateur de domaine (\), l'espace ( ), le tiret (), le point (.), le guillemet simple ('), le point d'exclamation (!), l'arrobas (@), le signe de pourcentage (%), le signe
d'insertion (^), le et commercial (&), et la valeur «inutilisé» (qui indique qu'il n'y a pas de mappage).
SQL Server vous laisse la possibilité de mapper des caractères spéciaux sur le signe souligné (_), le signe
dièse (#), ou le signe dollar ($), de la façon suivante :
Map _
Détermine quel caractère de Windows NT sera mappé sur le caractère souligné (_) valide dans SQL Server. La valeur par défaut est le séparateur de domaine (\).
Map #
Détermine quel caractère de Windows NT sera mappé sur le caractère dièse (#) valide dans SQL Server.
La valeur par défaut est le tiret (-).
Map $
Détermine quel caractère de Windows NT sera mappé sur le caractère dollar ($) valide dans SQL Server.
La valeur par défaut est l'espace ( ).
Lorsqu'un serveur est configuré de façon à fonctionner en mode sécurité intégrée ou mixte, après avoir défini ses options de sécurité, vous créerez les groupes et utilisateurs Windows NT qui seront autorisés à accéder à ce
serveur SQL, puis leur affecterez les droits d'accès appropriés à ce serveur.
Pour gérer les groupes et utilisateurs Windows NT, utilisez le Gestionnaire des utilisateurs Windows NT.
Affectez-leur des droits d'accès à SQL Server en utilisant le Gestionnaire de sécurité SQL.
Pour bénéficier de la sécurité mixte ou intégrée dans SQL Server, vous devez d'abord créer les groupes et
utilisateurs Windows NT qui seront autorisés à se connecter à SQL Server. Le Gestionnaire des utilisateurs, inclus
dans le logiciel du système d'exploitation Windows NT, est l'outil qui vous permet de réaliser cette opération.
Lancez le Gestionnaire des utilisateurs en sélectionnant son icône dans le groupe de programmes Outils
d'administration. Pour plus de précisions sur l'utilisation de cet outil, consultez l'Aide en ligne du gestionnaire des
utilisateurs. Si vous n'avez pas accès à ce gestionnaire, demandez à un Administrateur Windows NT de le faire à votre
place.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 88
Un groupe d'Administrateurs est créé dès l'installation du système d'exploitation Windows NT. Quand SQL
Server est installé, tous les utilisateurs de ce groupe d'Administrateurs reçoivent des privilèges d'administrateurs système (SA) sur le serveur SQL.
La procédure recommandée pour établir la sécurité intégrée de SQL Server consiste à créer deux groupes
définis localement pour accéder à SQL Server. Vous avez, par exemple, la possibilité d'utiliser le Gestionnaire des
utilisateurs pour créer un groupe local que vous nommez UtilisateursSQL, et auquel vous accordez des privilèges de
niveau utilisateur, ainsi qu'un second groupe que vous nommez AdminSQL et auquel vous accordez des privilèges
d'administrateur système. Vous pouvez ensuite affecter de nouveaux utilisateurs individuels à ces groupes.
Les membres des groupes locaux peuvent être des utilisateurs définis localement, des utilisateurs définis
dans le domaine, ou des groupes globaux définis dans le domaine. Si vous utilisez la sécurité appliquée au domaine,
vous souhaiterez certainement ajouter les noms d'utilisateurs définis dans le domaine, afin d'éviter de devoir gérer un
jeu séparé de comptes Windows NT sur l'ordinateur Windows NT qui exécute SQL Server.
Les indications suivantes vous permettront de configurer des comptes à l'aide du Gestionnaire des utilisateurs Windows NT, sans que cela ne pose de problèmes. Ces comptes pourront ensuite être mappés sur les noms
d'accès SQL Server :


Lorsque vous créez des noms pour les groupes et utilisateurs Windows NT, il est conseillé de n'utiliser que des identificateurs valides dans SQL Server. N'employez ni l'espace, ni le point, ni tout autre caractère que
Windows NT accepte mais que SQL Server ne reconnaît pas, et limitez la taille du nom de groupe à 30 caractères
maximum. De plus, n'introduisez pas le souligné (_), le signe dollar ($), ou le signe dièse (#) dans les noms de groupe
ou d'utilisateur car SQL Server utilise ces caractères comme caractères spéciaux de mappage.

Evitez de placer les utilisateurs Windows NT dans plus d'un groupe Windows NT ayant un droit
d'accès à SQL Server, car SQL Server ne permet pas aux utilisateurs d'appartenir à des groupes qui se chevauchent au
sein des bases de données. En veillant à maintenir vos utilisateurs Windows NT dans un groupe unique bénéficiant du
privilège d'accès à SQL Server, vous pourrez utiliser le Gestionnaire de sécurité SQL pour conserver la cohérence
entre les groupes d'utilisateurs de base de données et les groupes Windows NT.


Créez un groupe de niveau administrateur et un ou plusieurs groupe(s) de niveau utilisateur pour les
comptes qui auront accès à SQL Server. Les groupes doivent correspondre aux niveaux de sécurité que vous voulez
accorder à vos bases de données SQL Server (par exemple, SaisieCommandes et CadresSaisieCommandes). Si vous
désirez que vos administrateurs Windows NT soient les mêmes que vos administrateurs système dans SQL Server,
vous ne devrez pas modifier les permissions par défaut créées par le programme d'installation. Cependant, vous pouvez créer un groupe administrateurs indépendant de SQL Server et lui accorder des privilèges administratifs à la fois
sur Windows NT et sur SQL Server. Après avoir créé un groupe distinct d'administrateurs pour les administrateurs
système de SQL Server, vous pourrez révoquer les privilèges au groupe d'administrateurs Windows NT.
Le Gestionnaire de sécurité SQL présente une interface graphique vous permettant de mapper les groupes
et utilisateurs Windows NT sur les noms d'accès de SQL Server pour la sécurité mixte ou intégrée. (Si vous utilisez la
sécurité standard, employez SQL Enterprise Manager pour configurer les noms d'accès.)
Le Gestionnaire de sécurité SQL vous permet également d'accorder ou de révoquer des privilèges SQL
Server aux groupes Windows NT, en définissant des mots de passe et des noms d'accès SQL Server pour le groupe ou
en ajoutant des utilisateurs aux bases de données SQL Server, par exemple. En outre, vous pouvez utiliser le Gestionnaire de sécurité SQL pour rechercher les permissions d'accès SQL Server accordées à un compte Windows NT donné.
Avant de pouvoir gérer les comptes à sécurité intégrée, vous devez lancer le Gestionnaire de sécurité SQL
et vous connecter à un serveur SQL.
Pour lancer le Gestionnaire de sécurité SQL et vous connecter à un serveur SQL
1.
Server 6.0.
ALSI / S25
Choisissez l'icône Gestionnaire de sécurité SQL dans le groupe de programmes de Microsoft SQL
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 89
Le Gestionnaire de sécurité SQL démarre et la boîte de dialogue Connexion à un serveur apparaît.
2.
Entrez ou sélectionnez le nom du serveur dont vous voulez gérer la sécurité intégrée dans la zone
«Serveur».
Cette zone «Serveur» contient la liste des cinq derniers serveurs ayant fait l'objet d'une connexion. Pour
consulter la liste des serveurs du réseau, choisissez le bouton «Liste de serveurs».
3.
Entrez votre nom d'accès dans la zone «Nom d'accès». Saisissez ensuite votre mot de passe dans la
zone «Mot de passe».
Pour les connexions avec sécurité intégrée, vous n'êtes pas obligé de fournir un nom d'accès ou un mot de
passe.
4.
Sélectionnez le bouton «Connecter».
La fenêtre du Gestionnaire de sécurité SQL s'ouvre et le gestionnaire de sécurité se connecte au serveur.
Lorsque vous accordez des privilèges à un groupe Windows NT, vous autorisez l'accès à SQL Server à
chaque membre de ce groupe. Vous pouvez accorder soit un privilège utilisateur, soit un privilège administrateur
système.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 90
13.2.12 Pour accorder un privilège utilisateur
1.
Choisissez le bouton «Privilège utilisateur». Ou sélectionnez l'option «Privilège utilisateur» dans le
menu Affichage .
2.
Sélectionnez Accorder nouveau dans le menu Sécurité.
La boîte de dialogue Accord du privilège utilisateur apparaît.
3.
Dans la zone «Accord de privilège», sélectionnez le groupe qui aura accès à SQL Server.
Choisissez «Groupes locaux» pour visualiser tous les groupes locaux sur l'ordinateur Windows NT, et
«Groupes du domaine par défaut» pour visualiser tous les groupes du domaine par défaut.
La zone «Ajout d'un nom d'accès pour les membres du groupe» est activée, pour spécifier que vous voulez
créer un nom d'accès à SQL Server pour chaque utilisateur de ce groupe.
4.
Si vous ne souhaitez pas affecter un nom d'accès SQL Server individuel à chaque membre du
groupe, désactivez la case à cocher «Ajout d'un nom d'accès pour les membres du groupe». Si vous n'attribuez pas un
nom d'accès individuel à chaque utilisateur, ils ne pourront accéder à SQL Server qu'en utilisant le nom d'accès par
défaut (habituellement invités). Sil s'agit de la configuration de sécurité pour laquelle vous avez opté, assurez-vous de
bien avoir spécifié un nom d'accès par défaut lorsque vous avez défini les options de sécurité à l'aide de SQL Enterprise Manager.
5.
Pour ajouter les utilisateurs du groupe à une base de données, et en faire leur base de données par
défaut lorsqu'ils se connectent à SQL Server, activez la case «Ajout d'utilisateurs à la base de données», puis choisissez la base de données dans la liste proposée.
Le Gestionnaire de sécurité SQL tentera de créer, dans la base de données, un groupe SQL Server qui corresponde au nom du groupe Windows NT et il y inclura tous les nouveaux utilisateurs de la base de données.
Il est recommandé d'affecter les utilisateurs à une base de données par défaut autre que master, afin de les
empêcher de créer des objets de base de données dans master. Si vous n'associez pas les utilisateurs à une base de
données par défaut, master deviendra leur base de données par défaut.
6.
Choisissez le bouton «Accorder».
13.2.13 Pour accorder un privilège administrateur système
1.
Choisissez le bouton «Privilège SA» ou sélectionnez Privilège administrateur système dans le menu
Affichage.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 91
2.
Sélectionnez Accorder nouveau dans le menu Sécurité,
La boîte de dialogue Accord du privilège d'administrateur système (sa) apparaît.
3.
Sélectionnez le groupe qui aura accès à SQL Server dans la zone «Accord de privilège»,
Choisissez «Groupes locaux» pour visualiser tous les groupes locaux sur l'ordinateur Windows NT, et
«Groupes du domaine par défaut» pour visualiser tous les groupes du domaine par défaut.
Remarquez que lorsque vous accordez un privilège administrateur système, les zones «Ajout d'un nom
d'accès pour les membres du groupe» et «Ajout d'utilisateurs à la base de données» apparaissent comme estompées.
La raison en est simple : quand vous accordez un privilège d'accès administrateur système à SQL Server à un groupe,
les utilisateurs de ce groupe sont automatiquement mappés sur le nom d'accès SA, et master est définie comme base
de données par défaut.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
4.
Le 16/04/17
page 92
Choisissez le bouton «Accorder».
Remarque Si vous accordez une permission au groupe Utilisateurs de domaine sur le domaine par défaut,
vous verrez s'afficher, en guise de membres du domaine, tous les comptes d'ordinateur Windows NT (se terminant par
un $), ainsi que tous les comptes utilisateur définis pour le domaine.
Comme les comptes d'ordinateur n'ont pas besoin de permissions SQL Server pour se connecter, il est préférable de créer un groupe séparé dans le domaine, contenant seulement les comptes utilisateur valides, et de lui accorder ensuite une permission.
Lorsque vous ouvrez le Gestionnaire de sécurité SQL, une arborescence des groupes ajoutés à SQL Server
s'affiche. Vous pouvez visualiser la liste des utilisateurs jouissant d'un privilège utilisateur.
Pour visualiser les utilisateurs dotés d'un privilège utilisateur
1.
Choisissez le bouton «Privilège utilisateur» ou sélectionnez Privilège utilisateur dans le menu Affichage.
2.
Les groupes bénéficiant d'un privilège utilisateur s'affichent.
Pour visualiser la liste des utilisateurs appartenant à un groupe, cliquez deux fois sur le nom du groupe.
3.
Cliquez à nouveau deux fois sur le nom du groupe pour faire disparaître une liste de noms d'utilisa-
teur.
Vous pouvez obtenir des détails sur un groupe ou un utilisateur inscrit dans un groupe. Pour un utilisateur
individuel, vous pouvez ajouter un nom d'accès qui correspond à son nom d'utilisateur mappé, ou supprimer ce nom
d'accès correspondant s'il en existe déjà un. Vous pouvez également ajouter ou supprimer un utilisateur d'une base de
données ou modifier la base de données par défaut. Pour un compte de groupe donné, ces mêmes actions s'appliquent
à tous les membres du groupe. Par exemple, vous pouvez mettre à jour les noms d'accès d'un groupe pour ajouter des
noms d'accès correspondant aux noms d'utilisateur mappés des membres du groupe, à condition que ce nom d'accès
n'existe pas encore.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 93
13.2.14 Pour visualiser les détails d'un compte utilisateur
1.
Choisissez le bouton «Privilège utilisateur» ou sélectionnez Privilège utilisateur dans le menu Affichage.
2.
Cliquez deux fois sur le nom du groupe qui contient l'utilisateur à propos duquel vous voulez obtenir des informations.
La liste des utilisateurs appartenant à ce groupe s'affiche à l'écran.
3.
Sélectionnez l'utilisateur de votre choix, puis choisissez Détail de compte dans le menu Sécurité. Ou
bien cliquez deux fois sur le nom de l'utilisateur.
La boîte de dialogue Détail de compte apparaît. Le nom de compte Windows NT de l'utilisateur s'affiche,
ainsi que le nom d'utilisateur mappé de celui-ci.
4.
5.
ALSI / S25
Modifiez les informations de compte, selon vos besoins.
Choisissez le bouton «Fermer».
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 94
13.2.15 Pour visualiser les détails d'un groupe
1.
Choisissez le bouton «Privilège utilisateur» ou sélectionnez Privilège utilisateur dans le menu Affichage.
Les groupes jouissant d'un privilège utilisateur s'affichent.
2.
Sélectionnez un groupe, puis choisissez Détail de compte dans le menu Sécurité. Ou bien appuyez
sur la touche CTRL et cliquez deux fois sur le nom du groupe .
La boîte de dialogue Détail de compte apparaît. Les informations de compte Windows NT du groupe s'affichent à l'écran, ainsi que le nom mappé du Serveur SQL du groupe.
3.
4.
Modifiez les informations de compte selon vos besoins.
Choisissez le bouton «Fermer».
Lorsque vous révoquez des privilèges, tous les utilisateurs du groupe auquel vous révoquez les permissions
sont supprimés des bases de données SQL Server, et leurs noms d'accès sont éliminés de SQL Server.
13.2.16 Pour révoquer des privilèges
1.
Choisissez le bouton «Privilège utilisateur» ou le bouton «Privilège SA». Ou bien choisissez Privilège utilisateur ou Privilège administrateur système dans le menu Affichage.
Tous les groupes dotés de ce privilège s'affichent à l'écran.
2.
Choisissez le nom du groupe dont vous voulez révoquer le privilège.
3.
Choisissez Révoquer dans le menu Sécurité.
Un message apparaît, vous demandant de confirmer.
4.
Choisissez «Oui».
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 95
Si vous voulez refuser un accès SQL Server à un membre d'un groupe bénéficiant d'un privilège SQL Server, utilisez le Gestionnaire des utilisateurs pour supprimer cet utilisateur du groupe. Le nom d'accès SQL Server et
tous les noms d'utilisateur de base de données associés à ce compte Windows NT seront conservés dans SQL Server
mais deviendront inaccessibles à cet utilisateur. Vous pouvez, périodiquement, éliminer ces noms d'accès et d'utilisateur en faisant appel à l'option «Trouver les noms d'accès SQL orphelins» dans la boîte de dialogue Recherche
d'informations sur un compte.
Il vous est possible d'afficher des informations sur les comptes de chaque utilisateur individuel. Ces informations comprennent les chemins de permission d'un compte utilisateur donné.
Un chemin de permission indique comment un compte utilisateur est autorisé à accéder à SQL Server (par
exemple, en faisant partie d'un groupe local à qui le privilège utilisateur a été accordé). Un compte utilisateur donné
peut appartenir à plusieurs groupes auxquels un privilège utilisateur ou SA a été accordé. Quand un utilisateur qui
présente ces caractéristiques se connecte, SQL Server permet au compte d'accéder au serveur via un chemin de permission qui lui donne le privilège d'accès maximal. Si vous souhaitez supprimer un compte utilisateur Windows NT
donné de tous les groupes qui ont accès à SQL Server, vous pouvez faire appel à l'option qui permet de répertorier
tous les chemins de permission de l'utilisateur en question. Exécutez ensuite l'application Gestionnaire des utilisateurs
pour supprimer le compte utilisateur de tous ces groupes.
Vous pouvez également visualiser les noms d'accès SQL Server des utilisateurs qui existent dans SQL Server mais qui n'existent plus dans aucun groupe Windows NT doté d'une permission d'accès.
13.2.17 Pour rechercher des informations de compte sur un utilisateur
1.
Choisissez Rechercher dans le menu Sécurité.
La boîte de dialogue Recherche d'informations sur un compte apparaît.
2.
Entrez le nom d'un utilisateur dans la zone «Compte».
3.
Choisissez le type de recherche approprié :
Activez la case «Vérifier les permissions du compte» pour connaître le chemin de permission
maximal affecté à ce compte.
Activez la case «Trouver toutes les permissions du compte» pour connaître tous les chemins de
permissions que ce compte utilise pour accéder à SQL Server.
4.
ALSI / S25
Choisissez le bouton «Rechercher».
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 96
Les résultats de cette opération s'affichent à l'écran en indiquant le nom complet du compte, la permission
SQL Server maximale accordée à l'utilisateur, et les chemins de permissions par lesquels l'utilisateur peut accéder à
SQL Server.
5.
Choisissez le bouton «Annuler».
13.2.18 Pour rechercher les noms d'accès SQL Server
qui n'ont pas de comptes Windows NT correspondants
1.
Choisissez Rechercher dans le menu Sécurité.
La boîte de dialogue Recherche d'informations sur un compte apparaît.
2.
Activez la case «Trouver les noms d'accès SQL orphelins».
3.
Choisissez le bouton «Rechercher».
Les résultats de la recherche s'affichent à l'écran.
4.
Choisissez le bouton «Supprimer» pour éliminer les noms d'accès SQL Server (autres que sa, probe,
repl_publisher, ou repl_subscriber) qui ne correspondent à aucun utilisateur Windows NT dotés de permissions valides.
5.
Choisissez le bouton «Annuler».
13.2.19 9.2.2 Définir les utilisateurs
Avant qu'un utilisateur puisse exploiter une base de données, il vous faudra, dans cette base de données, attribuer un nom d'utilisateur à son nom d'accès. Le nom d'utilisateur peut être le nom d'accès de l'utilisateur, ou le nom
d'utilisateur d'un autre utilisateur de la base de données (ou alias). Si vous affectez un utilisateur à une base de données à l'aide d'un alias, il ne pourra utiliser aucun autre nom d'utilisateur pour accéder à la base de données.
Lorsque vous ajoutez un utilisateur à une base de données, vous pouvez également spécifier le nom de son
groupe. Par défaut, les nouveaux utilisateurs sont placés dans le groupe intégré public. Les utilisateurs peuvent être
ajoutés à un autre groupe.
Les utilisateurs autorisés à se connecter à une base de données doivent ensuite obtenir la permission d'y effectuer des opérations. Pour plus d'informations concernant la configuration des permissions, consultez Gestion des
permissions objet et Gestion des permissions instruction.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 97
Un groupe de base de données est un ensemble d'utilisateurs de base de données. La formation de groupes
vous permet de simplifier la gestion d'un grand nombre d'utilisateurs de base de données. Elle constitue un moyen très
pratique d'accorder ou de révoquer des permissions à tout un ensemble d'utilisateurs en même temps. Tout nouvel
utilisateur est ajouté par défaut au groupe public intégré. Chaque utilisateur peut être membre d'un autre groupe en
plus du groupe public.
Vous pouvez créer des groupes à tout moment. Créez des groupes avant d'ajouter des utilisateurs à une
base de données.
Si vous utilisez la sécurité intégrée et si vous avez créé des groupes à l'aide de Windows NT, ces
groupes ne seront pas automatiquement liés aux groupes de base de données. Si vous voulez que tous les
membres d'un groupe Windows NT soient affectés à un groupe de base de données, vous devrez tout d'abord attribuer
un nom d'accès explicite à chaque utilisateur du groupe, puis ajouter tous ces noms d'accès en tant qu'utilisateurs dans
la base de données; enfin, vous devrez affecter ces utilisateurs au groupe de base de données que vous créez. Le Gestionnaire de sécurité SQL peut créer automatiquement des noms d'accès et un groupe SQL Server pour un groupe
Windows NT sélectionné.
13.2.20
13.2.21 Ajouter un groupe à une base de données
1.
A l'aide de SQL Enterprise Manager, sélectionnez un serveur dans la fenêtre Gestionnaire de serveurs, puis ouvrez le dossier Bases de données de ce serveur.
La liste des bases de données de ce serveur s'affiche.
2.
Sélectionnez une base de données, puis choisissez Groupes dans le menu Gérer.
Ou bien, ouvrez l'arborescence de cette base de données (choisissez le bouton «» ), utilisez le bouton droit
de la souris pour choisir le dossier Groupes/utilisateurs, et choisissez Nouveau Groupe dans le menu contextuel qui
apparaît.
La boîte de dialogue Gestion de groupes apparaît pour la base de données sélectionnée.
3.
Entrez le nom du groupe dans la zone «Groupe».
Si la zone «Groupe» comporte déjà un nom, choisissez «Nouveau Groupe» dans la liste, puis entrez le nom
du groupe.
Le nom de groupe peut comporter un maximum de 30 caractères et doit être unique dans la base de données. Les caractères peuvent être alphanumériques, mais le premier caractère doit obligatoirement être une lettre, le
signe dièse (#) ou le signe (_) (par exemple, DATAOPS ou OPERS8).
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 98
4.
En option, sélectionnez l'utilisateur à ajouter au groupe dans la liste Utilisateurs, puis choisissez le
bouton «Ajouter» dans le cadre Utilisateurs de base de données.
Le nom d'utilisateur passe dans la liste Membres.
Répétez l'opération pour chaque utilisateur que vous souhaitez ajouter au groupe.
5.
Choisissez le bouton «Ajouter» à droite de la zone «Groupe».
Le groupe est ainsi ajouté à la base de données.
Avant qu'un utilisateur puisse utiliser une base de données, vous devez définir un nom d'utilisateur pour le
nom d'accès de cet utilisateur dans la base de données. Les possibilités offertes aux utilisateurs de la base de données
dépendent des permissions accordées au nom d'utilisateur (et à tous les groupes dont l'utilisateur fait partie).
Vous n'aurez peut-être pas besoin de recourir à la procédure suivante pour ajouter des utilisateurs à la base
de données. En effet, lorsque vous ajoutez un nom d'accès à SQL Server, vous disposez d'une option vous permettant
de choisir d'ajouter, pour chaque nom d'accès, un nom d'utilisateur dans chaque base de données.
Vous pouvez également employer la procédure qui suit pour ajouter l'utilisateur invités à une base de données, ou pour ajouter un utilisateur au nom d'accès par défaut.
13.2.22 Ajouter un utilisateur à une base de données
1.
A l'aide de SQL Enterprise Manager, sélectionnez un serveur dans la fenêtre Gestionnaire de serveurs, puis ouvrez le dossier Bases de données de ce serveur.
La liste des bases de données du serveur s'affiche à l'écran.
2.
Sélectionnez une base de données, puis choisissez Utilisateurs dans le menu Gérer.
Ou bien, ouvrez l'arborescence de cette base de données (choisissez le bouton «» ), puis choisissez le dossier Groupes/Utilisateurs avec le bouton droit de la souris; choisissez enfin Nouvel Utilisateur
dans le menu contextuel qui apparaît.
La boîte de dialogue Gestion des utilisateurs apparaît.
3.
Entrez un nom d'utilisateur dans la zone «Nom d'utilisateur». Il peut être identique au nom d'accès.
Si la zone «Nom d'utilisateur» n'est pas vide, choisissez <Nouvel utilisateur> dans la liste, puis saisissez le
nom d'utilisateur.
Le nom d'utilisateur peut comporter un maximum de 30 caractères et doit être unique dans la
base de données. Les caractères peuvent être alphanumériques, mais le premier caractère doit obligatoirement être une lettre, le signe dièse (#) ou le signe (_) (par exemple, CEDRIC ou UTILISATEUR8). Si
vous ne saisissez aucun nom d'utilisateur, le nom d'utilisateur sera identique au nom d'accès.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 99
4.
Dans la liste de la zone «Connexion», sélectionnez le nom d'accès auquel vous voulez affecter un
nom d'utilisateur de base de données.
5.
En option, pour ajouter l'utilisateur à un groupe, sélectionnez un groupe dans la liste de la zone
«Groupe».
Tous les utilisateurs font automatiquement partie du groupe public. Si vous le souhaitez, vous pouvez ajouter l'utilisateur à un deuxième groupe.
6.
Choisissez le bouton «Ajouter».
L'utilisateur s'ajoute à la base de données et le bouton «Ajouter» est remplacé par le bouton «Modifier».
7.
Il vous est possible, en option, de créer un alias pour l'utilisateur. A cet effet, sélectionnez un alias
dans la liste Connexions disponibles, choisissez «Ajouter», puis «Modifier».
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 100
13.2.23 Gérer l'adhésion d'un utilisateur à un groupe
Après avoir créé un groupe, vous pouvez, à tout moment, y ajouter de nouveaux utilisateurs. Dans chaque
base de données, chaque utilisateur ne peut appartenir qu'à un seul groupe en plus du groupe public.
Vous pouvez supprimer les utilisateurs de n'importe quel groupe (sauf du groupe public) à tout moment.
Vous ne pouvez supprimer aucun utilisateur du groupe public.
Pour gérer l'adhésion d'un utilisateur à un groupe
1.
Sélectionnez un serveur dans la fenêtre Gestionnaire de serveurs, puis ouvrez le dossier Bases de
données.
La liste des bases de données de ce serveur s'affiche à l'écran.
2.
Sélectionnez une base de données et ouvrez le dossier Groupes/Utilisateurs de cette base de don-
nées.
Les groupes et utilisateurs de cette base de données s'affichent.
3.
Cliquez deux fois sur un groupe.
Ou bien, utilisez le bouton droit de la souris pour choisir le nom du groupe, puis choisissez Edition dans le
menu contextuel qui apparaît.
La boîte de dialogue Gestion de Groupes apparaît; elle contient des informations sur ce groupe.
4.
Pour ajouter un utilisateur à ce groupe, sélectionnez le nom d'utilisateur à ajouter dans la liste Utilisateurs, puis choisissez le bouton «Ajouter».
Le nom d'utilisateur s'ajoute à la liste Membres.
5.
Pour supprimer un utilisateur du groupe, sélectionnez le nom d'utilisateur à supprimer dans la liste
Membres, puis choisissez le bouton «Supprimer».
Le nom d'utilisateur passe ainsi dans la liste Utilisateurs.
6.
ALSI / S25
Choisissez le bouton «Modifier».
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 101
13.2.24 Supprimer un utilisateur d'une base de données
En supprimant un utilisateur vous l'éliminez de la base de données concernée. Vous ne pouvez supprimer
les utilisateurs qui possèdent des objets. Etant donné qu'il n'existe pas de commande permettant de transférer la propriété des objets, vous devez supprimer les objets avant de pouvoir supprimer le nom d'utilisateur. Pour refuser l'accès à un utilisateur propriétaire d'objets, vous pouvez changer son mot de passe SQL Server.
Pour supprimer un utilisateur d'une base de données
1.
Sélectionnez un serveur dans la fenêtre Gestionnaire de serveurs, puis ouvrez le dossier Bases de
données de ce serveur.
La liste des bases de données de ce serveur s'affiche à l'écran.
2.
Sélectionnez une base de données et ouvrez le dossier Groupes/Utilisateurs.
La liste des groupes et utilisateurs de cette base de données s'affiche.
3.
Sélectionnez un nom d'utilisateur, et appuyez sur la touche SUPPR.
Vous pouvez également utiliser le bouton droit de la souris pour choisir le nom d'utilisateur, puis choisir
Supprimer dans le menu contextuel qui apparaît.
Le nom d'utilisateur est ainsi éliminé de la liste et l'utilisateur ne peut plus avoir accès à la base de données.
13.2.25 Supprimer un groupe d'une base de données
Lorsque vous supprimez un groupe, tous les utilisateurs de ce groupe sont automatiquement éliminés de ce
groupe. Les utilisateurs appartenant à ce groupe supprimé restent cependant utilisateurs de la base de données et demeurent membres du groupe public.
Pour supprimer un groupe d'une base de données
1.
Sélectionnez un serveur dans la fenêtre Gestionnaire de serveurs, puis ouvrez le dossier Bases de
données de ce serveur.
La liste des bases de données de ce serveur s'affiche à l'écran.
2.
Sélectionnez une base de données et ouvrez le dossier Groupes/Utilisateurs de cette base de don-
nées.
La liste des groupes et utilisateurs de cette base de données s'affiche.
3.
Sélectionnez un groupe, et appuyez sur la touche SUPPR.
Vous pouvez également utiliser le bouton droit de la souris pour choisir le nom du groupe, puis choisir
Supprimer dans le menu contextuel qui apparaît.
Le groupe est ainsi supprimé de la liste.
13.2.26 9.2.3 Gérer les permissions objet
Les permissions objet contrôlent l'utilisation de certaines instructions sur certains objets de base de données. C'est le propriétaire de l'objet qui les accorde et les révoque. Les permissions objet s'appliquent aux instructions
et objets suivants :
Instruction
SELECT
UPDATE
INSERT
DELETE
REFERENCE
EXECUTE
Objet
Table, vue, colonnes
Table, vue, colonnes
Table, vue
Table, vue
Table
Procédure stockée
Dans SQL Server 6.0, la permission REFERENCE assure la compatibilité ANSI pour les contraintes référentielles. Elle permet de créer des références à une table donnée sans avoir de permission SELECT sur cette table.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 102
Cette permission est vérifiée lors de la création de la référence ainsi qu'au moment de l'exécution. Lorsque la référence est vérifiée, l'utilisateur qui crée la table doit avoir soit la permission SELECT, soit la permission REFERENCE
sur toutes les tables référencées. (Créer une référence à une table suppose que la table référencée dispose de permissions qui permet de vérifier toutes les références renvoyées à la table dans l'instruction CREATE). Dans l'interface du
gestionnaire SQL Enterprise Manager, la permission REFERENCE est mentionnée sous le terme «DRI».
SQL Enterprise Manager vous permet d'administrer les permissions objet par utilisateur (c'est à dire les
permissions objet accordées à un utilisateur ou à un groupe), ou par objet (autrement dit, les utilisateurs ou les
groupes disposant de permissions pour cet objet). Seul l'administrateur système (SA) ou le propriétaire d'une base de
données (DBO) peut administrer les permissions objet.
L'administrateur système ou le propriétaire d'une base de données peut accorder des permissions instruction permettant aux groupes ou à des utilisateurs individuels d'effectuer les tâches suivantes sur des bases de données
:

création d'une base de données ;

création de règles et de valeurs par défaut sur une base de données ;

création de tables et de vues sur une base de données ;

création de procédures stockées ;

sauvegarde de la base de données ;
Chaque base de données possède son propre système de permissions indépendant. Les autres bases de
données ne seront nullement influencées par l'accord d'une permission d'utiliser telle instruction dans telle base de
données. Seul un administrateur système (SA) ou un propriétaire de base de données (DBO) est habilité à gérer des
permissions instruction.
Pour gérez les permissions instruction, sélectionnez ou désélectionnez les cases à cocher correspondantes.
Indicateur de case à cocher
Signification
Vide
Pas de changement à la permission déjà acquise.
Marque bleue Permission accordée.
Marque rouge Permission révoquée (annule un accord donné).
Marque verte Accord en attente. (La permission est accordée si vous choisissez le bouton «OK».)
Cercle rouge barré
Révocation en attente. (La permission est révoquée si vous choisissez le bouton
«OK».)
13.2.27 Accorder ou révoquer des permissions instruction à un utilisateur ou à un groupe
1.
Ouvrez la boîte de dialogue Edition de la base de données pour la base de données qui fera l'objet
d'une gestion de permissions instruction.
Pour ce faire, sélectionnez le nom du serveur dans la fenêtre Gestionnaire de serveurs, ouvrez le dossier
Bases de données et cliquez deux fois sur la base de données concernée.
La boîte de dialogue Edition de la base de données apparaît.
2.
Choisissez l'onglet Permissions.
La fenêtre Permissions apparaît. Les marques bleues apparaissant dans les cases à cocher indiquent les
permissions existantes.
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 103
3.
Pour accorder une permission, activez la case concernée.
Une marque verte apparaît dans la case à cocher dès qu'une permission est sélectionnée,
Remarque
Si vous accordez une permission à un groupe, vous n'avez pas besoin de l'accorder individuellement à chaque utilisateur membre de ce groupe.
4.
Pour supprimer une permission, désactivez la case à cocher concernée.
Lorsque vous sélectionnez la révocation d'une permission, un cercle rouge barré apparaît sur la case à cocher concernée.
5.
Choisissez «OK».
Les permissions ne seront réellement accordées ou révoquées que lorsque vous aurez choisi «OK».
Pour obtenir une description de chaque permission instruction, consultez la section Synthèse des permissions instruction.
Vous trouverez ci-après une brève description de chaque permission instruction. Elles ne sont pas spécifiques aux objets. Elles ne peuvent être accordées que par un administrateur système (SA) ou un propriétaire de base
de données (DBO)
13.2.27.1.1.1.1.1.1 Créer une BdD
L'utilisateur peut créer une base de données. Cette permission ne peut être accordée que par un administrateur système (SA), et uniquement aux utilisateurs de la base de données master.
13.2.27.1.1.1.1.1.2 Créer une table
L'utilisateur peut créer une table.
13.2.27.1.1.1.1.1.3 Créer une vue
L'utilisateur peut créer une vue.
13.2.27.1.1.1.1.1.4 Créer une PS
L'utilisateur peut créer une procédure stockée pour la base de données.
13.2.27.1.1.1.1.1.5 Créer un défaut
L'utilisateur peut créer une valeur par défaut. (II s'agit d'une valeur que SQL Server insère dans une colonne si l'utilisateur n'en saisit pas une explicitement.)
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc
ALSI S25
SQL Avancé et administration d’un SGBD Relationnel
STS2 IG
Application à SQL Server 6.5
Le 16/04/17
page 104
13.2.27.1.1.1.1.1.6 Créer une règle
L'utilisateur peut créer une règle. (Une règle détermine ce qu'un utilisateur peut entrer dans une colonne
spécifiée, ou dans toute colonne dotée d'un certain type de données défini par l'utilisateur).
13.2.27.1.1.1.1.1.7 Sauvegarder une BdD
L'utilisateur peut sauvegarder la base de données.
13.2.27.1.1.1.1.1.8 Sauvegarder une transaction
L'utilisateur peut sauvegarder le journal des transactions.
Vous pouvez modifier le propriétaire d'une base de données. Vous supprimerez ainsi les alias des utilisateurs disposant des mêmes prérogatives que l'ancien propriétaire (DBO) sur la base de données. Vous pouvez envisager de changer le propriétaire de la base de données,par exemple, à la création d'une nouvelle base de données dont
vous voulez affecter les droits de propriété à un autre utilisateur.
Le nouveau propriétaire ne doit pas encore être reconnu en tant qu'utilisateur dans la base de données, ni
posséder un alias. Il sera peut être nécessaire de supprimer un utilisateur d'une base de données ou d'un alias avant de
modifier le propriétaire d'une base de données.
Vous devez vous placer dans la base de données dont vous voulez changer le propriétaire.
13.2.28
13.2.29 Modifier le propriétaire d'une base de données

sp_changedbowner nom_d'accès [, true]
nom_d'accès
Nom du nouveau propriétaire de la base de données courante. Le nouveau propriétaire ne doit pas encore
être reconnu en tant qu'utilisateur dans la base de données, ni posséder un alias dans cette base de données.
true (vrai)
Transfère les alias et leurs permissions au nouveau propriétaire de la base de données.
Par exemple, pour que maried devienne propriétaire de la base de données courante, et pour supprimer les
alias des utilisateurs disposant des mêmes prérogatives que l'ancien propriétaire (DBO) sur la base de données, entrez
:
sp_changedbowner maried
Vous ne pouvez pas modifier le propriétaire de la base de données master.
Pour plus d'informations sur la façon d'utiliser la procédure système sp_changedbowner, consultez le Manuel de référence Transact-SQL .
ALSI / S25
SGBD R Support de Cours 2TSIG
D:\582659791.doc