Le Langage de Contr

1
Le Langage de Contrôlede DonnéesLCD
2
Contrôle des Données

• Notion de SousSchéma
• Restriction de la vision
• Restriction des actions
• Privilèges
• Systèmes
• Objets
• Rôles
• Regroupement de privilèges
• Contraintes évènementielles Trigger (plus loin)
• Contrôles avant une modification
• Mises à jour automatique

3
Restreindre les accès à une BD
Tout le monde ne peut pas VOIR et FAIRE nimporte
quoi
de la vision
VIEW
RESTRICTION
GRANT
des actions
4
Restriction des accèsSous-schéma ou schéma
externe
DBA
Utilisateur2
BD
Utilisateur1
5
Lobjet VUE

• Une VUE est une table virtuelle aucune
  implémentation physique de ses données
• La définition de la vue est enregistrée dans le
  DD
• A chaque appel dune vue le SGBD réactive sa
  construction à partir du DD
• Vue mono-table crée à partir dune table
• Modifications possibles ? modifications dans la
  table
• Jointure en forme procédurale autorisée
• Vue multi-table
• Crée par une jointure en forme relationnelle
• Aucune modification autorisée

6
Utilisation dune VUE

• Simplification de requêtes pour des non
  spécialistes
• Création de résultats intermédiaires pour des
  requêtes complexes
• Présentation différente de la base de données
  schéma externe
• Mise en place de la confidentialité (VOIR)
• Une vue mono-table pourra être mise à jour avec
  contraintes

7
Création et suppression dune VUE

• Création dune vue
• Suppression dune vue
• Pas de modification dune vue

CREATE ( OR REPLACE ) VIEW nom_vue ( liste des
colonnes de la vue ) AS SELECT WITH CHECK
OPTION CONSTRAINT nom_cont
Sélection de lignes et colonnes
DROP VIEW nom_vue
8
Exemples de création de VUES (1)

• Vue mono-table avec restriction horizontale
• Vue mono-table avec restriction verticale

CREATE VIEW enseignant_info AS SELECT FROM
enseignant WHERE idDip IN (SELECT idDip FROM
diplome WHERE UPPER(nomDiplome) LIKE 'INFO')
1 Table
CREATE VIEW etudiant_scol AS SELECT
idEtu,nomEtu,adrEtu,idDip FROM etudiant
9
Exemples de création de VUES (2)

• Vue mono-table avec restriction mixte

CREATE VIEW etudiant_info (numEtudiant,nomEtudiant
,adrEtudiant,dip) AS SELECT idEtu,nomEtu,adrEtu,id
Dip FROM etudiant WHERE idDip IN (SELECT idDip
FROM diplome WHERE UPPER(nomDiplome) LIKE
'INFO')
Nouveaux noms
10
Exemples de création de VUES (3)

• Vue mono-table avec colonnes virtuelles
• Pas de modification sur les colonnes virtuelles
• Modifications autorisées sur les colonnes de base
  ? mise à jour instantanée !

CREATE VIEW employe_salaire (ne,nome,mensuel,annue
l,journalier) AS SELECT idEmp,nomEmp,sal,sal12,sa
l/22 FROM employe
11
Exemples de création de VUES (4)

• Vue mono-table avec groupage
• Utilisation de la vue ? reconstruction

CREATE VIEW emp_service (ns,nombreEmp,moyenneSal)
AS SELECT idService,COUNT(),AVG(sal)FROM
employe GROUP BY idService
SELECT FROM emp_service WHERE nombreEmpgt5
SELECT idService AS ns,COUNT() AS
nombreEmp, AVG(sal) AS moyenneSal FROM
employe GROUP BY ns HAVING COUNT() gt 5
12
Vues multi-tables

• Simplification de requêtes
• Pas de modifications possibles dans ce type de
  vue ( voir trigger instead of)
• Tables temporaires virtuelles de travail
• Transformation de la présentation des données
  ?Schéma externe

CREATE VIEW emp_ser(nom_service, nom_employe) AS
SELECT s.noms,e.nome FROM emp e,service s WHERE
e.idSers.idSer
2 Tables
13
Exemples de vues multi-tables

• Reconstitution des clients (UNION)
• Reconstitution des étudiants (JOINTURE)

CREATE VIEW clients(idCli,nom,.,secteur)
AS SELECT ct.,T FROM clients_toulouse
ct UNION SELECT cb.,B FROM clients_bordeaux
cb UNION SELECT cm.,M FROM clients_montpellier
cm
CREATE VIEW etudiants(idEtu,nom,adresse, nomstage,
entrstage) AS SELECT e.id,e.nom,e.adr,s.nomS,s.ent
rS FROM etudiant e,stage s WHERE e.ids.id
14
Vues avec ContraintesWITH CHECK OPTION

• Principe le prédicat de sélection de lignes se
  transforme en contrainte
• Mise en place de contraintes spécifiques

Table T
Vue VT
Contraintes génériques
Contraintes spécifiques
15
Exemple de vue avec Contrainte

• Les employés informaticiens ont des contraintes
  avantageuses
• On ne pourra pas insérer un employé informaticien
  qui ne correspond pas aux contraintes du prédicat

CREATE VIEW emp_info AS SELECT FROM emp WHERE
idSer IN (SELECT idSer FROM service WHERE
UPPER(nomSer) LIKE 'INFO') AND sal gt 3500 AND
prime BETWEEN 500 AND 1000 WITH CHECK OPTION
CONSTRAINT cko_emp_info
16
Restriction des Actions Les privilèges

• Privilèges Système et objet
• Contrôler laccès à la base de données
• Sécurité système  couvre l'accès à la base de
  données et son utilisation au niveau du système
  (nom de l'utilisateur et mot de passe, espace
  disque alloué aux utilisateurs et opérations
  système autorisées par l'utilisateur)
• Sécurité données  couvre l'accès aux objets de
  la base de données et leur utilisation, ainsi que
  les actions exécutées sur ces objets par les
  utilisateurs

17
Privilèges système

• Plus de 100 privilèges système
• Création dutilisateurs (CREATE USER)
• Suppression de table (DROP ANY TABLE)
• Création despace disque (CREATE TABLESPACE)
• Sauvegarde des tables (BACKUP ANY TABLE)
• ..
• Les privilèges peuvent être regroupés dans des
  rôles (voir plus loin)

18
Exemples de privilèges systèmes
Privilèges ALTER CREATE DROP
PROCEDURE ? ?
ANY PROCEDURE ? ?
TABLE ?
ANY TABLE ? ? ?
SESSION ? ?
TABLESPACE ? ? ?
USER ? ? ?
VIEW ?

19
Délégation et suppressionde privilèges

• Délégation GRANT
• Suppression REVOKE

GRANT privilège_système rôle ,privilège2
rôle2... TO utilisateur1 rôle PUBLIC
,utilisateur2 ... WITH ADMIN OPTION 
REVOKE privilège_système rôle ,privilège2
rôle2... FROM utilisateur1 rôle PUBLIC
,utilisateur2 ...
20
Exemple de délégation et de suppressionde
privilèges Système
GRANT CREATE SESSION, CREATE TABLE,  DROP ANY
TABLE TO michel
Attention suppression dautres tables
REVOKE DROP ANY TABLE FROM michel
On supprime que ce privilège
21
Privilèges Objet

• Contrôle les actions sur les objets
• Objets tables, vues, séquences, .
• Actions update, insert, execute, .
• Le propriétaire (owner) peut donner ces
  privilèges sur ses propres objets
• Les privilèges peuvent être donnés avec loption
  de délégation

22
Privilèges ObjetDélégation et suppression

• Délégation GRANT
• Suppression REVOKE

GRANT privilège1 ,privilège2 ... (colonne
,colonne2.) ON schéma.objet TO utilisateur1
rôle PUBLIC ,utilisateur2 ... WITH
GRANT OPTION 
REVOKE privilège1 ,privilège2 ... (colonne
,colonne2...) ON schéma.objet FROM
utilisateur1 rôle PUBLIC ,utilisateur2
... CASCADE CONSTRAINTS
23
Privilèges ObjetExemples
GRANT INSERT,UPDATE (adr,tel) ON etud_info TO
Martine, Nicole
GRANT DELETE, UPDATE , INSERT ON etud_info TO
Michel WITH GRANT OPTION
REVOKE UPDATE (tel) ON etud_info FROM Nicole
24
Les privilèges Objet objets et actions possibles
Table Vue Séquence Procédure Fonction Package Snapshot
ALTER ?
DELETE ? ?
EXECUTE ?
INDEX ?
INSERT ? ?
REFERENCES ?
SELECT ? ? ? ?
UPDATE ? ?
Objets
Actions
25
Les Rôles

• Regroupement de privilèges pour des familles
  dutilisateur
• Facilitent la gestion des autorisations des
  privilèges objet en évitant les ordres GRANT
• Un rôle par défaut est donné à un utilisateur
• Un utilisateur peut posséder plusieurs rôles mais
  nest connecté quavec un seul à la fois
• On peut donner un mot de passe pour certains rôles

26
Les Rôles évitent le produit cartésien
Privilège1
ROLE1
Privilège2
Privilège3
ROLE2
Privilège4
ROLE3
Privilège5
Privilège6
27
Manipulation des rôles Ordres

• Création / Modification dun rôle
• Remplissage et attribution dun rôle
• Rôle par défaut ou activation
• Suppression / Révocation dun rôle

CREATEALTER ROLE nom_rôle NOT IDENTIFIED
IDENTIFIED BY mot_de_passe EXTERNALLY
GRANT privilège1 rôle1 TO nom_rôle GRANT
privilège2 rôle2 TO nom_rôle GRANT ROLE
nom_role TO user
SET ROLE nom_rôle IDENTIFIED BY mot_de_passe
REVOKE ROLE nom_rôle FROM user
DROP ROLE nom_rôle
28
Manipulation des rôles Exemples
CREATE ROLE secretariat_info
GRANT SELECT,UPDATE (adr,tel) ON ens_info TO
secretariat_info GRANT SELECT,INSERT,UPDATE ON
etud_info TO secretariat_info GRANT
SELECT,INSERT ON cours_info TO secretariat_info
GRANT secretariat_info TO laurent, thomas,
corinne
29
Rôles prédéfinis

• DBA
• Tous les privilèges système et objet
• RESOURCE
• Création de tous les objets classiques
• Propriétaire des données (owner)
• CONNECT
• Connexion à la base
• Attente de privilèges objet
• EXP_FULL_DATABASE
• Exportation de tous les objets
• IMP_FULL_DATABASE
• Importation dobjets

30
Dictionnaire de données

• ROLE_SYS_PRIVS
• privilèges systèmes accordés aux rôles
• ROLE_TAB_PRIVS
• privilèges objets accordés aux rôles
• USER_ROLE_PRIVS
• rôles accessibles par lutilisateur