Administration des bases de données Mehdi Louizi 1

Administration des bases de
données
Mehdi Louizi
1
Administration des bases de données
Pourquoi PL/SQL
2
Administration des bases de données
Pourquoi PL/SQL ?
PL/SQL = PROCEDURAL LANGUAGE/SQL
SQL est un langage non procédural
Les traitements complexes sont parfois difficiles à écrire
si on ne peut utiliser des variables et les structures de
programmation comme les boucles et les alternatives
On ressent vite le besoin d’un langage procédural pour
lier plusieurs requêtes SQL avec des variables et dans les
structures de programmation habituelles




3
Administration des bases de données
Principales caractéristiques de PL/SQL
Extension de SQL : des requêtes SQL cohabitent avec les
structures de contrôle habituelles de la programmation
structurée (blocs, alternatives, boucles)
La syntaxe ressemble au langage Ada ou Pascal
Un programme est constitué de procédures et de
fonctions
Des variables permettent l’échange d’information entre
les requêtes SQL et le reste du programme




4
Administration des bases de données
Utilisation de PL/SQL
PL/SQL peut être utilisé pour l’écriture des procédures
stockées et des triggers


Oracle accepte aussi le langage Java
Il convient aussi pour écrire des fonctions utilisateurs qui
peuvent être utilisées dans les requêtes SQL (en plus des
fonctions prédéfinies)
Il est aussi utilisé dans des outils Oracle



5
Ex : Forms et Report
Administration des bases de données
Normalisation du language
Langage spécifique à Oracle
Tous les SQBG ont un langage procédural





TransacSQL pour SQLServer,
PL/pgSQL pour Postgresql
Procédures stockées pour MySQL depuis 5.0
Tous les langages L4G des différents SGBDs se
ressemblent

6
Administration des bases de données
Utilisation de PL/SQL
Le PL/SQL peut être utilisé sous 3 formes




7
Un bloc de code, exécuté comme une unique commande SQL,
via un interpréteur standard (SQL+ ou iSQL*PLus)
un fichier de commande PL/SQL
un programme stocké(procédure, fonction, package ou trigger)
Administration des bases de données
Le langage PL/SQL
8
Administration des bases de données
Blocs
Un programme est structuré en blocs d’instructions de 3
types :




Procédures anonymes
Procédures nommées
Fonctions nommées
Un bloc peut contenir d’autres blocs

9
Administration des bases de données
Structure d’un programme PL/SQL
DECLARE
-- définition des variables
BEGIN
-- code du programme
EXCEPTION
-- code de gestion des erreurs
END;
10
Administration des bases de données
Déclaration, initialisation des variables

Identificateurs Oracle :






30 caractères au plus
commence par une lettre
Peut contenir lettres, chiffres, _, $ et #
pas sensible à la casse
Portée habituelle des langages à blocs
Doivent être déclarés avant d’être utilisés
11
Administration des bases de données
Déclaration, initialisation des variables




Déclaration et initialisation
Nom_variable
type_variable := valeur;
Initialisation
Nom_variable
:= valeur;
Déclaration multiple interdite
Exemples:




12
age integer;
nom varchar(30);
dateNaissance date;
ok boolean:= true;
Administration des bases de données
Initialisation de variables


Plusieurs façons de donner une valeur à une variable :
Opérateur d’affectation


n :=
Directive INTO de la requête SELECT

Exemples :




13
dateNaissance:= ’10/10/2004’;
SELECT nome INTO nom
FROM emp
WHERE matr= 509;
Administration des bases de données
SELECT … INTO …



select expr1, expr2,…into var1, var2,…met des valeurs
de la BD dans une ou plusieurs variables expr1, expr2, …
Le select ne doit renvoyer qu’une seule ligne
Avec Oracle il n’est pas possible d’inclure un select sans
«into» dans une procédure : pour ramener plusieurs
lignes  les curseurs.
14
Administration des bases de données
Le type de variables


VARCHAR2

Longueur maximale : 32767 octets

Syntaxe: Nom_variable VARCHAR2(30);

Exemple:

name VARCHAR2(30); name VARCHAR2(30) := ’farid’;
NUMBER(long,dec)

Long : longueur maximale

Dec : longueur de la partie décimale
15

Exemple:

num_tel number(10); farid number(5,2)=142.12;
Administration des bases de données
Le type de variables (2)

DATE


Nom_variable
DATE;
Par défaut DD-MON-YY (18-DEC-02)
Fonction TO_DATE

Exemple :
start_date := to_date(’29-SEP-2003’,’DD-MON-YYYY’);
start_date := to_date(’29-SEP-2003:13:01’,’DD-MONYYYY:HH24:MI’);

BOOLEAN

16
TRUE, FALSE ou NULL
Administration des bases de données
Déclaration %TYPE et %ROWTYPE

On peut déclarer qu’une variable est du même type
qu’une colonne d’une table ou (ou qu’une autre variable) :

Exemple :


nom emp.nome.%TYPE;
Une variable peut contenir toutes les colonnes d’une
ligne d’une table

Exemple :


17
employe emp%ROWTYPE;
déclare que la variable employe contiendra une ligne de la table emp
Administration des bases de données
Exemple d’utilisation
DECLARE
employe emp%ROWTYPE;
nom emp.nome.%TYPE;
BEGIN
SELECT * INTO employe
FROM emp
WHERE matr= 900;
nom := employe.nome;
employe.dept:= 20;
…
INSERT into emp VALUES employe;
END
18
Administration des bases de données
Commentaires

--Pour une fin de ligne

/* Pour plusieurs
lignes */
19
Administration des bases de données
PL /SQL : les principales
commandes
20
Administration des bases de données
Test conditionnel

IF-THEN


IF l_date > ’11-APR-03’ THEN
l_salaire := l_salaire * 1.15;
END IF;
IF-THEN-ELSE

21
IF l_date > ’11-APR-03’ THEN
l_salaire := l_salaire * 1.15;
ELSE l_salaire := l_salaire * 1.05;
END IF;
Administration des bases de données
Test conditionnel

IF-THEN-ELSIF


IF l_nom = MOHAMED’ THEN
l_salaire := l_salaire * 1.15;
ELSIF l_nom = ‘AHMED’ THEN
l_salaire := l_salaire * 1.05;
END IF;
CASE

22
CASE sélecteur
WHEN expression1 THEN résultat1
WHEN expression2 THEN résultat2
ELSE résultat3
END;
Administration des bases de données
Test conditionnel
Exemple :
val := CASE os
WHEN ‘WINDOWS’ THEN ‘MICROSOFT’
WHEN ‘LEOPARD’ THEN ‘APPLE’
ELSE ‘Unix’
END;
23
Administration des bases de données
Exercices

Soit la table départements avec les champs suivants :




Soit la séquence « dep » suivante :



id_departement : clé primaire, entier non nul
nom : varchar2(40)
id_region : toujours nul
Commence à 1, pas de 1, pas de valeur minimale, valeur maximale
égale à 100
Créez un bloc PL/SQL pour insérer un nouveau département
dans la table départements
Utilisez la séquence dep pour générer un numéro de
département. Créez un paramètre pour le nom du
département.
24
Administration des bases de données
Exercices

Soit la table commande avec les champs suivants :





id_commande : clé primaire
id_employe : clé étrangère
nom : varchar2(40)
total : montant de la commande : entier, trois chiffres après la virgule
Soit la table employés
id_employe : clé_primaire
 commission : entier 3 chiffres
Un employé a plusieurs commandes, une commande a un et un seul
employé




Créez un paramètre qui reçoit un numéro d’employé
Trouvez la somme totale de toutes les commandes traitées
par cet employé
Mettre à jour le pourcentage de commission de cet employé :
25
Administration des bases de données
Exercices





Si la somme < 100,000 passer la commission à 10
Si la somme est comprise entre 100,000 et 1,000,000
inclus passer la commission à 15
Si la somme excède 1,000,000 passer la commission à 20
Si aucune commande n’existe pour cet employé, mettre la
commission à 0
Valider la modification
26
Administration des bases de données
Les boucles

LOOP

instructions exécutables;

END LOOP;

Obligation d’utiliser la commande EXIT

WHILE condition LOOP


instructions exécutables;
END LOOP;
27
Administration des bases de données
Les boucles

FOR variable IN debut..fin

LOOP


instructions;
END LOOP;
28
Administration des bases de données
Affichage

Activer le retour écran


set serveroutput on size 10000
Affichage


29
dbms_output.put_line(chaîne);
Utilise || pour faire une concaténation
Administration des bases de données
Exemple
set serveroutput on --sous SQLPLUS
DECLARE
i number(2);
BEGIN
FOR i IN 1..5 LOOP
dbms_output.put_line(‘Nombre: ’|| i );
END LOOP;
END;
30
Administration des bases de données
Exemple
DECLARE
nb integer;
BEGIN
delete from emp where matr in (600, 610);
nb := sql%rowcount; --curseur sql
dbms_output.put_line('nb = ' || nb);
END;
31
Administration des bases de données
Exemple
DECLARE
compteur number(3);
i number(3);
BEGIN
select count(*) into compteur from clients;
FOR i IN 1..compteur LOOP
dbms_output.put_line('Nombre : ' || i );
END LOOP;
END;
32
Administration des bases de données
Les curseurs
33
Administration des bases de données
Les curseurs




Toutes les requêtes SQL sont associées à un curseur
Ce curseur représente la zone mémoire utilisée pour
parser(analyser) et exécuter la requête
Le curseur peut être implicite (pas déclaré par
l’utilisateur) ou explicite
Les curseurs explicites servent à retourner plusieurs
lignes avec un select
34
Administration des bases de données
Les curseurs





Tous les curseurs ont des attributs que l’utilisateur peut
utiliser
%ROWCOUNT: nombre de lignes traitées par le
curseur
%FOUND: vrai si au moins une ligne a été traitée par la
requête ou le dernier fetch
%NOTFOUND: vrai si aucune ligne n’a été traitée par
la requête ou le dernier fetch
%ISOPEN: vrai si le curseur est ouvert (utile seulement
pour les curseurs explicites)
35
Administration des bases de données
Les curseurs

Les curseurs implicites
 Les curseurs implicites sont tous nommés SQL

Exemple :
DECLARE
nb_lignes integer;
BEGIN
delete from emp where dept= 10;
nb_lignes:= SQL%ROWCOUNT;
…
36
Administration des bases de données
Les curseurs

Les curseurs explicites

Pour traiter les select qui renvoient plusieurs lignes
Ils doivent être déclarés
On les utilise dans une boucle FOR

Utilisation implicite des instructions OPEN, FETCH et CLOSE


37
Administration des bases de données
Les curseurs

Les curseurs explicites
DECLARE
nom varchar2(30);
CURSOR c_nom_clients IS
SELECT nom,adresse FROM clients;
BEGIN
FOR le_client IN c_nom_clients
LOOP
dbms_output.put_line('Employé: ' ||
UPPER(le_client.nom) ||' Ville : '|| le_client.adresse);
END LOOP;
END;
38
Administration des bases de données
Les curseurs

Curseurs paramétrés


39
Un curseur paramétré peut servir plusieurs fois avec des
valeurs des paramètres différentes
On doit fermer le curseur entre chaque utilisation de
paramètres différents (sauf si on utilise «for »qui ferme
automatiquement le curseur)
Administration des bases de données
Les curseurs

Curseurs paramétrés
DECLARE
CURSOR c(p_dept integer) is
select dept, nome from emp where dept= p_dept;
BEGIN
FOR employe in c(10)LOOP
dbms_output.put_line(employe.nome);
END LOOP;
FOR employe in c(20) LOOP
dbms_output.put_line(employe.nome);
END LOOP;
END;
40
Administration des bases de données
Les exceptions
41
Administration des bases de données
Les exceptions


Une exception est une erreur qui survient durant une
exécution
2 types d’exception :


42
prédéfinie par Oracle
définie par le programmeur
Administration des bases de données
Saisir les exceptions

Une exception ne provoque pas nécessairement l’arrêt du
programme si elle est saisie par un bloc (dans la partie
«EXCEPTION »)
43
Administration des bases de données
Les exceptions prédéfinies

NO_DATA_FOUND


TOO_MANY_ROWS


Erreur numérique
ZERO_DIVIDE


Quand Select into retourne plusieurs lignes
VALUE_ERROR


Quand Select into ne retourne aucune ligne
Division par zéro
OTHERS

44
Toutes erreurs non interceptées
Administration des bases de données
Traitement des exceptions
BEGIN
…
EXCEPTION
WHEN NO_DATA_FOUND THEN
...
WHEN TOO_MANY_ROWS THEN
...
WHEN OTHERS THEN--optionnel
...
END;
45
Administration des bases de données
Les exceptions Utilisateur


Elles doivent être déclarées avec le type EXCEPTION
On les lève avec l’instruction RAISE
46
Administration des bases de données
Exemple d’exception utilisateur
DECLARE
salaire numeric(8,2);
salaire_trop_bas EXCEPTION;
BEGIN
select sal into salaire from emp where matr= 50;
if salaire < 300 then
RAISE salaire_trop_bas;
end if;
EXCEPTION
WHEN salaire_trop_bas THEN
dbms_output.put_line(‘Salaire trop bas’);
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
END;
47
Administration des bases de données
Procédures et Fonctions
48
Administration des bases de données
Bloc anonyme ou nommé



Un bloc anonyme PL/SQL est un bloc «DECLARE –
BEGIN –END »comme dans les exemples précédents
Dans SQL*PLUS on peut exécuter directement un bloc
PL/SQL anonyme en tapant sa définition
Le plus souvent, on crée plutôt une procédure ou une
fonction nommée pour réutiliser le code
49
Administration des bases de données
Procédures sans paramètres
create or replace procedure list_nom_clients
IS
BEGIN
DECLARE
nom varchar2(30);
CURSOR c_nom_clients IS select nom,adresse from clients;
BEGIN
FOR le_client IN c_nom_clients LOOP
dbms_output.put_line('Employé: '
|| UPPER(le_client.nom)
||' Ville : ‘
|| le_client.adresse);
END LOOP;
END;
END;
50
Administration des bases de données
Procédures avec paramètre
create or replace procedure list_nom_clients
(ville IN varchar2,
IN : en lecture seule
result OUT number)
OUT : en écriture seule
IN OUT : en lecture/écriture
IS
BEGIN
DECLARE
CURSOR c_nb_clients IS
select count(*) from clients where adresse=ville;
BEGIN
open c_nb_clients;
fetch c_nb_clients INTO result;
END;
END;
51
Administration des bases de données
Récupération des résultats sous SQL+

Déclarer une variable


SQL> execute list_nom_clients('paris',:nb)
Visualisation du résultat


Une variable globale s’utilise avec le
préfixe :
Exécuter la fonction


SQL> variable nb number;
SQL> print
Description des paramètres

52
SQL> desc nom_procedure
Administration des bases de données
Fonctions sans paramètres
create or replace function nombre_clients
return number
Déclaration du type de retour de la
IS
fonction
BEGIN
DECLARE
i number;
CURSOR get_nb_clients IS select count(*) from clients;
BEGIN
open get_nb_clients;
fetch get_nb_clients INTO i;
return i;
END;
END;

Exécution: select nombre_clients() from dual
53
Administration des bases de données
Fonctions avec paramètres
Seuls les paramètres IN (en lecture
seule) sont autorisés pour les fonctions
Create or replace
Function euro_to_fr(somme IN number)
return number
IS
taux constant number:= 6.55957;
BEGIN
return somme * taux;
END;
54
Administration des bases de données
Procédures et fonctions

Suppression de procédures ou fonctions



DROP PROCEDURE nom_procedure
DROP FUNCTION nom_fonction
Table système contenant les procédures et fonctions :
user_source
55
Administration des bases de données
Compilation, exécution et utilisation

Compilation


Exécution



Sous SQL*PLUS, il faut taper une dernière ligne contenant
«/»pour compiler une procédure ou une fonction
Sous SQL*PLUS on exécute une procédure PL/SQL avec la
commande EXECUTE :
EXECUTE nomProcédure(param1, …);
Utilisation


56
Les procédures et fonctions peuvent être utilisées dans
d’autres procédures ou fonctions ou dans des blocs PL/SQL
anonymes
Les fonctions peuvent aussi être utilisées dans les requêtes
SQL
Administration des bases de données
Triggers
57
Administration des bases de données
Création de triggers

Exemple :
CREATE TRIGGER nom
BEFORE DELETE OR INSERT OR UPDATE ON
table
FOR EACH ROW
WHEN (new.empno>0)
DECLARE ............ <<<<déclarations>>>>
BEGIN
............ <<<< bloc d'instructions PL/SQL>>>>
END;
58
Administration des bases de données
Le nom du trigger

doit être unique dans un même schéma

peut être le nom d'un autre objet (table, vue, procédure)
mais à éviter
59
Administration des bases de données
Option BEFORE/AFTER

elle précise le moment de l'exécution du trigger

les triggers AFTER row sont plus efficaces que les
BEFORE row parce qu'ils ne nécessitent pas une double
lecture des données.
60
Administration des bases de données
Définition du trigger

Elle comprend le type d'instruction SQL qui déclenche le
trigger :




DELETE, INSERT, UPDATE
On peut en avoir une, deux ou les trois.
Pour UPDATE, on peut spécifier une liste de colonnes. Dans
ce cas, le trigger ne se déclenchera que si l'instruction UPDATE
porte sur l'une au moins des colonnes précisée dans la liste.
S'il n'y a pas de liste, le trigger est déclenché pour toute
instruction UPDATE portant sur la table.
61
Administration des bases de données
Définition du trigger

La définition du trigger précise la table associée au trigger :


62
une et une seule table
pas une vue.
Administration des bases de données
Types de triggers

Le type d’un trigger détermine :



quand ORACLE déclenche le trigger,
combien de fois ORACLE déclenche le trigger.
Le type du trigger est défini par l’utilisation de l’une ou
l’autre des options suivantes :

63
BEFORE, AFTER, FOR EACH ROW
Administration des bases de données
ORACLE propose deux types de triggers

les triggers lignes qui se déclenchent individuellement
pour chaque ligne de la table affectée par le trigger,

les triggers globaux qui sont déclenchés une seule fois.

Si l'option FOR EACH ROW est spécifiée, c'est un
trigger ligne, sinon c'est un trigger global.
64
Administration des bases de données
Types de triggers

Pour les triggers lignes, on peut introduire une restriction
sur les lignes à l'aide d'une expression logique SQL : c'est
la clause WHEN :




65
Cette expression est évaluée pour chaque ligne affectée par le
trigger.
Le trigger n'est déclenché sur une ligne que si l'expression
WHEN est vérifiée pour cette ligne.
L'expression logique ne peut pas contenir une sous-question.
Par exemple, WHEN (new.empno>0) empêchera l'exécution
du trigger si la nouvelle valeur de EMPNO est 0, négative ou
NULL.
Administration des bases de données
Triggers

Le corps du trigger est un bloc PL/SQL :



66
Il peut contenir du SQL et du PL/SQL.
Il est exécuté si l'instruction de déclenchement se produit et si
la clause de restriction WHEN, le cas échéant, est évaluée à
vrai.
Il est différent pour un trigger ligne et pour un trigger global.
Administration des bases de données
Les noms de corrélation




Dans un trigger ligne, on doit pouvoir accéder aux ancienne
et nouvelle valeurs de colonne de la ligne.
Les noms de corrélation permettent de désigner ces deux
valeurs : un nom pour l'ancienne et un pour la nouvelle.
Si l'instruction de déclenchement du trigger est INSERT,
seule la nouvelle valeur a un sens.
Si l'instruction de déclenchement du trigger est DELETE,
seule l'ancienne valeur a un sens.
67
Administration des bases de données
Les noms de corrélation

La nouvelle valeur est appelée


L'ancienne valeur est appelée


:new.colonne
:old.colonne
Exemple : IF :new.salaire < :old.salaire ........
68
Administration des bases de données
L’option REFERENCING

Si une table s'appelle NEW ou OLD, on peut utiliser
REFERENCING pour éviter l'ambiguïté entre le nom de
la table et le nom de corrélation.

Exemple :
CREATE TRIGGER nomtrigger
BEFORE UPDATE ON new
REFERENCING new AS newnew
FOR EACH ROW
BEGIN
:newnew.colon1:= TO_CHAR(:newnew.colon2);
END;
69
Administration des bases de données
Les prédicats conditionnels INSERTING,
DELETING et UPDATING


Quand un trigger comporte plusieurs instructions de
déclenchement (par exemple INSERT OR DELETE OR
UPDATE), on peut utiliser des prédicats conditionnels
(INSERTING, DELETING et UPDATING) pour
exécuter des blocs de code spécifiques pour chaque
instruction de déclenchement.
70
Administration des bases de données
Les prédicats conditionnels INSERTING,
DELETING et UPDATING

Exemple :
CREATE TRIGGER ...
BEFORE INSERT OR UPDATE ON employe
.......
BEGIN
......
IF INSERTING THEN ....... END IF;
IF UPDATING THEN ........ END IF;
......
END;
71
Administration des bases de données
Les prédicats conditionnels INSERTING,
DELETING et UPDATING

UPDATING peut être suivi d'un nom de colonne :
CREATE TRIGGER ...
BEFORE UPDATE OF salaire, commission ON employe
.......
BEGIN
......
IF UPDATING ('salaire') THEN ........ END IF;
......
END;
72
Administration des bases de données
Nombre de triggers par table

On peut avoir au maximum un trigger de chacun des types suivants
pour chaque table :













BEFORE UPDATE row
BEFORE DELETE row
BEFORE INSERT statement
BEFORE INSERT row
BEFORE UPDATE statement
BEFORE DELETE statement
AFTER UPDATE row
AFTER DELETE row
AFTER INSERT statement
AFTER INSERT row
AFTER UPDATE statement
AFTER DELETE statement.
Même pour UPDATE, on ne peut pas en avoir plusieurs avec des
noms de colonnes différents.
73
Administration des bases de données
Instructions SQL autorisées

les instructions du LMD sont autorisées

les instructions du LDD ne sont pas autorisées

les instructions de contrôle de transaction
(ROLLBACK, COMMIT) ne sont pas autorisées.
74
Administration des bases de données
Triggers

Ordre de traitement des lignes



On ne peut pas gérer l'ordre des lignes traitées par une
instruction SQL.
On ne peut donc pas créer un trigger qui dépende de l'ordre
dans lequel les lignes sont traitées.
Triggers en cascade


75
Un trigger peut provoquer le déclenchement d'un autre trigger.
ORACLE autorise jusqu'à 32 triggers en cascade à un moment
donné.
Administration des bases de données
Limite

Un trigger ligne ne peut pas lire et/ou modifier la table
concernée (appelée table mutante) par l'instruction (INSERT,
UPDATE ou DELETE) qui a déclenché ce trigger. Exemple :

On peut utiliser des tables temporaires.
76
Administration des bases de données
Triggers

Conditions nécessaires pour créer un trigger



il faut avoir le privilège CREATE TRIGGER
il faut soit posséder la table sur laquelle on veut définir un
trigger, soit posséder le privilège ALTER sur la table sur
laquelle on veut définir le trigger, soit posséder le privilège
ALTER ANY TABLE
Modification de triggers

77
Pour modifier un trigger, on refait une instruction CREATE
TRIGGER suivie de OR REPLACE ou bien on supprime le
trigger (DROP TRIGGER nomtrigger) et on le crée à
nouveau.
Administration des bases de données
Activation d’un trigger



Un trigger peut être activé ou désactivé.
S’il est désactivé, ORACLE le stocke mais l’ignore.
On peut désactiver un trigger si :



il référence un objet non disponible
on veut charger rapidement un volume de données important
ou recharger des données déjà contrôlées.
Par défaut, un trigger est activé dès sa création.
78
Administration des bases de données
Activation d’un trigger

Pour désactiver un trigger, on utilise l’instruction ALTER
TRIGGER avec l’option DISABLE :


On peut désactiver tous les triggers associés à une table
avec la commande :


ALTER TRIGGER nomtrigger DISABLE;
ALTER TABLE nomtable DISABLE ALL TRIGGERS;
A l’inverse on peut réactiver un trigger :



79
ALTER TRIGGER nomtrigger ENABLE;
ou tous les triggers associés à une table :
ALTER TABLE nomtable ENABLE ALL TRIGGERS;
Administration des bases de données
Recherche d’information sur les triggers

Les définitions des triggers sont stockées dans les tables
de la métabase, notamment dans les tables
USER_TRIGGERS, ALL_TRIGGERS et DBA_TRIGGERS
80
Administration des bases de données
La procédure raise_application_error

La procédure raise_application_error
(error_number,error_message)



81
error_number doit être un entier compris entre -20000 et 20999
error_message doit être une chaîne de 500 caractères
maximum.
Quand cette procédure est appelée, elle termine le trigger,
défait la transaction (ROLLBACK), renvoie un numéro d'erreur
défini par l'utilisateur et un message à l'application.
Administration des bases de données
Gestion des exceptions


Si une erreur se produit pendant l'exécution d'un trigger,
toutes les mises à jour produites par le trigger ainsi que
par l'instruction qui l'a déclenché sont défaites.
On peut introduire des exceptions en provoquant des
erreurs.




82
Une exception est une erreur générée dans une procédure
PL/SQL.
Elle peut être prédéfinie ou définie par l'utilisateur.
Un bloc PL/SQL peut contenir un bloc EXCEPTION gérant les
différentes erreurs possibles avec des clauses WHEN.
Une clause WHEN OTHERS THEN ROLLBACK; gère le cas
des erreurs non prévues.
Administration des bases de données
Exceptions prédéfinies – quelques exemples

NO_DATA_FOUND


DUP_VAL_ON_INDEX


tentative d'insertion d'une ligne avec une valeur déjà existante pour
une colonne à index unique
ZERO_DIVIDE


cette exception est générée quand un SELECT INTO ne retourne
pas de lignes
division par zéro
etc
83
Administration des bases de données
Quelques exemples
employe(numserv,....)
service(numserv,...)
/* vérifier que le service de l'employé existe bien */
CREATE TRIGGER verif_service
BEFORE INSERT OR UPDATE OF numserv ON employe
FOR EACH ROW WHEN (new.numserv is not null)
DECLARE
noserv integer;
BEGIN
noserv:=0;
SELECT numserv
INTO noserv
FROM SERVICE
WHERE numserv=:new.numserv;
IF (noserv=0)
84
Administration des bases de données
Quelques exemples (suite)
THEN raise_application_error(-20501, 'N° de service non
correct');
END IF;
END;
employe(salaire,....)
/* mettre une valeur par défaut si le champ ne contient rien */
/* affecter 240 au salaire d'un employe qui n'en a pas */
CREATE TRIGGER smic
BEFORE INSERT OR UPDATE OF salaire ON employe
FOR EACH ROW WHEN (new.salaire is null)
BEGIN
SELECT 240
INTO :new.salaire
FROM employe;
END;
85
Administration des bases de données
Quelques exemples
employe(numemp,salaire,grade,...)
grille(grade,salmin,salmax)
/* vérifier le salaire d'un employé */
/* s'assurer que le salaire est compris dans les bornes correspondant au
grade de l'employé */
CREATE TRIGGER verif_grade_salaire
BEFORE INSERT OR UPDATE OF salaire, grade ON employe
FOR EACH ROW
DECLARE
minsal number;
maxsal number;
BEGIN
/* retrouver le salaire minimum et maximum du grade */
SELECT salmin,salmax
INTO minsal, maxsal
FROM grille
86
Administration des bases de données
Quelques exemples (suite)
WHERE grade= :new.grade;
/* s'il y a un problème, on provoque une erreur */
IF (:new.salaire<minsal OR :new.salaire>maxsal)
THEN
raise_application_error (-20300,'Salaire‘||TO_CHAR
(:new.salaire)||'incorrect pour ce grade');
EXCEPTION
WHEN no_data_found THEN
raise_application_error(-20301,'Grade incorrect');
END;
87
Administration des bases de données
Quelques exemples
CREATE TABLE Livre (
noLivre NUMERIC PRIMARY KEY,
prix NUMERIC(9,2)
)
CREATE TABLE PrixLivre (
nb NUMERIC,
somme NUMERIC(12,2)
)
INSERT INTO PrixLivre VALUES (1,0);


Créer un Trigger qui permet de vérifier avant chaque insertion
d’un livre son prix :
Si le prix est supérieur à 0



S’il est compris entre le prix moyen * 0.7 et le prix moyen *1.3 ,
mettre à jour la table PrixLivre (somme et nb)
Si le prix est > prix_moyen*0.7 et <prix_moyen*1.3 soulever une
exception
Sinon mettre à jour uniquement le champ somme de la table
PrixLivre
88
Administration des bases de données
Quelques exemples (suite)
DROP TRIGGER moyenne_prix;
CREATE TRIGGER moyenne_prix
BEFORE INSERT OF Prix
ON Livre
FOR EACH ROW
DECLARE
prix_moyen number;
BEGIN
SELECT SOMME/NB INTO prix_moyen
FROM PrixLivre;
IF (prix_moyen>0)
THEN
IF (:new.PRIX < prix_moyen*0.7 OR :new.PRIX >
prix_moyen*1.3)
THEN raise_application_error(-20001,'Prix modifiant
trop la moyenne!');
END IF;
IF (:new.PRIX > prix_moyen*0.7 AND :new.PRIX <
prix_moyen*1.3)
THEN UPDATE PrixLivre SET NB=NB+1,
SOMME=SOMME+:new.PRIX;
END IF;
ELSE
UPDATE PrixLivre SET SOMME=SOMME+:new.PRIX;
END IF;
END;
89
Administration des bases de données
Quelques exemples

Soit une table quelconque TABL, dont la clé primaire
CLENUM est numérique.

Définir un trigger en insertion permettant
d’implémenter une numérotation automatique
de la clé. Le premier numéro doit être 1.
90
Administration des bases de données
Quelques exemples (suite)
create or replace trigger cleauto
before insert on tabl
for each row
begin
declare
n integer;
newkey integer;
mon_exception exception;
-- Recherche s'il existe des tuples dans la table
select count(*) into n from tabl;
if n=0 then
raise mon_exception; -- Premiere insertion
end if;
-- Recherche la valeur de cle C la plus elevee
-- et affecte C+1 a la nouvelle cle
select max(clenum) into newkey from tabl;
:new.clenum := newkey + 1;
exception
-- Premier numero = 1
when mon_exception then :new.clenum := 1;
end;
91
Administration des bases de données
Quelques exemples

CLIENT (NUMCL, NOM, PRENOM, ADR, CP, VILLE,
SALAIRE, CONJOINT) DETENTEUR (NUMCL, NUMCP)
COMPTE (NUMCP, DATEOUVR, SOLDE)

Écrire un trigger en insertion permettant de
contrôler les contraintes suivantes :
- le département dans lequel habite le client doit être 01,
07, 26, 38, 42, 69, 73, ou 74 (sinon il n’est pas en France*)
- le nom du conjoint doit être le même que celui du
client.
92
*Ceci est une supposition
Administration des bases de données
Quelques exemples (suite 1/3)
CREATE TRIGGER INS_CLIENT
BEFORE INSERT ON CLIENT
FOR EACH ROW
DECLARE
nom_conjoint CLIENT.NOM%TYPE ;
compteur CLIENT.NUMCL%TYPE ;
pb_dept EXCEPTION ;
pb_conjoint1 EXCEPTION ;
pb_conjoint2 EXCEPTION ;
BEGIN
-- Contrainte sur le département
IF (:NEW.CP) NOT IN (01, 07, 26, 38, 42, 69, 73, 74) THEN
RAISE pb_dept ;
END IF ;
93
Administration des bases de données
Quelques exemples (suite 2/3)
-- Contrainte sur le nom du conjoint (+ test d’existence du conjoint)
IF :NEW.CONJOINT IS NOT NULL THEN
SELECT COUNT(*), NOM
INTO compteur, nom_conjoint
FROM CLIENT
WHERE NUMCL = :NEW.CONJOINT
GROUP BY NOM ;
IF compteur = 0 THEN -- Pas de conjoint
RAISE pb_conjoint1 ;
END IF ;
IF nom_conjoint <> :NEW.NOM THEN
RAISE pb_conjoint2 ;
END IF ;
END IF ;
94
Administration des bases de données
Quelques exemples (suite 3/3)
EXCEPTION
WHEN pb_dept THEN RAISE_APPLICATION_ERROR (-20501,
‘Insertion impossible : le client n’habite pas en France!’) ;
WHEN pb_conjoint1 THEN RAISE_APPLICATION_ERROR (-20502,
‘Insertion impossible : le conjoint du client n’existe pas !’) ;
WHEN pb_conjoint2 THEN RAISE_APPLICATION_ERROR (-20503,
‘Insertion impossible : le nom du conjoint est différent de celui du client !’) ;
END ;
95
Administration des bases de données
Administration Oracle
96
Administration des bases de données
Types d’utilisateurs d’une BD




97
développeurs d’application développent les
programmes suivant une logique figée
utilisateurs finaux utilisent ces programmes
utilisateurs ‘évolués’ utilisent soit ces programmes soit
un langage spécifique comme SQL ou d’autres outils
administrateur de la base de données (DBA= Data
Base Administrator): installe, maintient, gère la BD
Administration des bases de données
Rôle du DBA



Niveau Conceptuel:
 définir ( ou participer à la définition) du schéma conceptuel
Niveau externe:
 décide qui peut accéder à quoi et comment
 gère les utilisateurs
Niveau interne :
 assurer la correspondance entre le schéma conceptuel et les
possibilités du SGBD de façon à en tirer les meilleurs profits
en terme de performance, d intégrité et de sécurité
98
Administration des bases de données
Rôle du DBA

Rôle organisationnel



Rôle technique


99
Définition du schéma conceptuel des données
Partage des données par les utilisateurs
Installation su SGBD et des outils associés
Création de la base de données et assurer son évolution
Administration des bases de données
Rôle du DBA

Gestion des privilèges d ’accès

Amélioration des performances

Sécurité et cohérences des données

Echange des données entre la base de données et le monde
extérieur
100
Administration des bases de données
Structure physique d’une BD

Un ensemble de fichiers



fichiers de données
fichiers de reprise
fichiers de contrôle
La spécification des fichiers de données et de reprise se fait
lors de la création ou de la modification de la structure
d ’une base
101
Administration des bases de données
Fichiers de données



Un ou plusieurs fichiers de données : assurent le stockage
des objets créés par les utilisateurs, et le dictionnaire de
données.
Un fichier de données est associé à une et une seule base
de données.
Taille d ’un fichier de données : attribuée au moment de
sa création, ne peut jamais être modifiée
102
Administration des bases de données
Fichiers de reprise



Contiennent les modifications des données les plus
récentes.
Lors d ’une panne Oracle utilise ces fichiers pour
remettre la base dans un état cohérent.
Fichiers de reprise archivés
103
Administration des bases de données
Fichiers de contrôle



Contient les informations relatives à la structure physique
de la BD: nom de la BD, les noms et localisation des
fichiers de données et de reprise.
Oracle utilise ce fichier pour identifier les fichiers de
données et de reprise
Duplication de ce fichier pour faire face aux incidents
éventuels
104
Administration des bases de données
Structure logique d’une Base de Données




Des tablespaces
Des segments
Des extensions
Des blocs
105
Administration des bases de données
TABLESPACE



Utilisé pour regrouper un ensemble d ’objets logiques (
ex: tables, index,etc.)
une base de donnée doit avoir au moins un tablespace
appelé SYSTEM qui contient le dictionnaire des données
un 2ème tablespace pour stocker les objets de la base.
106
Administration des bases de données
TABLESPACE

CREATE TABLESPACE nom_tablespace DATAFILE
spec_fichier [,spec_fichier] [DEFAULT STORAGE
(spec_stockage)] [ONLINE | OFFLINE ].

CREATE USER nom_user IDENTIFIED BY [mot_pass |
EXTERNALLY]
[DEFAULT TABLESPACE nom_tablespace]
[TEMPORARY TABLESPACE nom_tablespace] [QUOTA
{n [K | M] ON nom_tablespace | UNLIMITED]
107
Administration des bases de données
Segment, Extension et Bloc

Lors de la création d ’un fichier, Oracle réserve tout
l ’espace qui lui est associé. A l’intérieur de ce fichier,
l’espace est géré dynamiquement au fur et à mesure de
l ’utilisation de la base de données:

108
se fait selon trois niveau de granularité: le segment, l ’extension
et le bloc
Administration des bases de données
Bloc et extension

Bloc:



Composé d ’un certain nombre d ’octets, taille définie au
moment de la création de la BD
la plus petite unité logique E/S utilisée par Oracle
Extension

109
Unité logique d ’allocation d ’espace composée d ’un ensemble
contigu de blocs de données alloués simultanément à un
segment
Administration des bases de données
Segment


Un ensemble d ’une ou plusieurs extensions
contenant les données d’une structure logique
dans un tablespace
5 types de segments:





110
Segments de données
Segments d ’index
Segments d ’annulation (rollback)
Segments temporaires
Segments d ’amorçage (bootstrap)
Administration des bases de données
II. Création d’une base de données


Oracle est livré en standard avec une base de données
Le DBA a le choix:


modifier la structure de cette base
créer une nouvelle base.


111
Evaluer l ’espace disque nécessaire
prévoir les moyens assurant la sécurité de la base.
Administration des bases de données
Création d’une base de données





Sauvegarde des bases existantes
Création d ’un nouveau fichier d’initialisation
Edition du nouveau fichier d ’initialisation
Arrêt et redémarrage d’oracle
Création de la nouvelle base
112
Administration des bases de données
Edition du nouveau fichier d’initialisation

On doit modifier les paramètres suivants:




113
DB_NAME = nouveau_nom_de_bd
DB_DOMAIN =nom_domaine
CONTROL_FILES =(nom_fichier_control_,..)
INIT_SQL_FILES = (SQL.BSQ, CATALOG.ORA, nom_fichier1,
nom_fichier2…)
Administration des bases de données
Création de la nouvelle BD

114
CREATE DATABASE [nom_base]
[CONTROLFILE REUSE]
[LOGFILE [GROUP entier] spec_fich]
[MAXLOGFILES entier]
[MAXLOGMEMBERS entier]
[MAXLOGHISTORY entier]
[DATAFILE spec_fichier[,spec_fichier]..]
[MAXDATAFILES entier]
[MAXINSTANCES entier]
[ARCHIELOG |NOARCHIVELOG]
[EXLUSIVE]
[CHARACTER SET code];
Administration des bases de données
Création de la nouvelle BD

Création :






115
des fichiers de données
des fichiers de reprise
des fichiers de contrôle
un tablespace SYSTEM et un rollback segment SYSTEM
un dictionnaire de données
deux utilisateurs identifiés par SYS et SYSTEM
Administration des bases de données
Exemple
CREATE DATABASE gestion
LOGFILE
GROUP 1 (‘ gestion_log11 ’, ’gestion_log12 ’) SIZE 500K,
GROUP 2 (‘ gestion_log21 ’, ’gestion_log22 ’) SIZE 500K
DATAFILE ‘ gestion_system ’ SIZE 5M;

116
Administration des bases de données
Modification des caractéristiques d’une BD
ALTER DATABASE [nom_base]
[ADD]
[DROP]
[RENAME FILE ‘ fichier1 ’[,fichier2] TO ‘ fichierc1 ’….]
[RENAME GLOBAL_NAME TO nom_base[.domaine]

117
Administration des bases de données
Démarrage et fermeture d ’une BD

Démarrage :


Mettre la BD à la disposition des utilisateurs afin qu’ils puissent
y accéder et effectuer les différentes opérations
Fermeture:

118
déconnecter tous les utilisateurs et fermer toutes les
structures de données
Administration des bases de données
Démarrage et fermeture d’une BD
STARTUP [FORCE]
[RESTRICT][PFILE=nom_fichier_param]
{[OPEN] [RECOVER] [nom_base] | [MOUNT] [options]} |
[NOMOUNT];


SHUTDOWN [ABORD | IMMIDIATE | NORMAL];
119
Administration des bases de données
III.Transactions et accès concurrents


SGBD : mettre à la disposition d ’un grand nombre
d ’utilisateurs un ensemble cohérents de données. Cette
cohérence est assurée à l ’aide des concepts de
transactions et d ’accès concurrents.
Transaction: unité logique de traitement regroupant un
ensemble d ’opérations élémentaires ( commandes SQL)
120
Administration des bases de données
Atomicité de la transaction

Afin de conserver la cohérence de la base, le système
doit garantir l ’atomicité de l exécution des transactions:
toute transaction est soit complètement exécutée soit
pas du tout.
121
Administration des bases de données
Atomicité de la transaction

Si les CI ne sont pas vérifiées à la fin de l ’exécution du
corps de ’une transaction, la transaction doit être annulée
et l ’état de la base restitué.
Fin de transaction
Exécution totale et cohérente
Commit

Incohérence Annulation
Rollback
La gestion des transactions inclut 2 aspects majeurs:
 Contrôle de concurrence
 fiabilité.
122
Administration des bases de données
Journaux et Sauvegarde
• Journal des images avant
– Journal contenant les débuts de transactions, les valeurs d'enregistrement avant
mises à jour, les fins de transactions (commit ou Rollback)
– Il permet de défaire les mises à jour effectuées par une transaction
• Journal des images après
– Journal contenant les débuts de transactions, les valeurs d'enregistrement après
mises à jour, les fins de transactions (commit ou Rollback)
– Il permet de refaire les mises à jour effectuées par une transaction
123
Administration des bases de données
Points de Sauvegardes
• Introduction de points de sauvegarde
intermédiaires (savepoint)
–
•
•
124
il est possible d ’annuler un sous ensemble d ’opérations d ’une
transaction à partir d ’un point de repère
Begin_Trans
–
update
–
update
– savepoint
–
update
–
update
Commit
unité d'oeuvre
Non perte du contexte
unité d'oeuvre
Administration des bases de données
Point de sauvegardes

Création d ’un point de reprise:


SAVEPOINT nom_point_de_reprise;
Pour annuler les opérations à partir d ’un point de
repère, on utilise la commande ROLLBACK en
précisant le point de repère

125
ROLLBACK TO [SAVEPOINT] nom_point_de_reprise
Administration des bases de données
Propriétés des transactions
Atomicité: une transaction est une unité indivisible: Exécution
totale ou non
 Consistance: Une transaction doit assurer que les données
manipulées sont dans un état consistant ( inchangé) tout au long
de son exécution même si d ’autres transactions essaient de la
modifier.
 Indépendance: les transactions doivent s’exécuter
indépendamment l ’une de l ’autre : l ’effet partiel d ’une
transaction incomplète ne doit pas être visible pour les autres
 Durabilité: les effets d ’une transaction réussite doivent être
enregistrées dans la BD même en présence d ’une panne.
Restituer un état cohérent de la base après une panne

126
Administration des bases de données
Les menaces




Panne de transaction
 erreur en cours d'exécution du programme
 nécessité de défaire les mises à jour effectuées
Panne système
 reprise avec perte de la mémoire centrale
 toutes les transactions en cours doivent être défaites
Panne disque
 perte de données de la base
Problèmes de concurrence
 pertes d ’opérations
 introduction d ’incohérences
127
Administration des bases de données
Gestion des accès concurrents
SGBD : assurer la gestion des accès concurrents
 L’exécution simultanée d ’un ensemble de transactions peut fournir
des résultats faux ou détruire la cohérence de la base
 exemple
Temps Transaction T0
Transaction T1
Lire E
Lire E
Ecrire E
Ecrire E
Pb: Mise à jour de E par T0 est perdue  état de la base incohérence

128
Administration des bases de données
Mécanismes de prévention

Mécanisme de pré-séquencement


Mécanisme de sérialisation

129
les transactions sont exécutées en série  il n ’y a plus de
concurrence à l ’exécution.
basé sur le verrouillage des données à manipuler.
Administration des bases de données
Sérialisation



130
Une exécution simultanée de plusieurs transactions
est sérialisable si, quel que soit l ’état cohérent
initial de la BD, elle fournit un résultat identique à
celui produit par une exécution séquentielle des
transactions.
Il doit exister au moins un ordre d’exécution
séquentielle des transactions qui fournit le même
résultat.
Les transactions vérifiant le concept de sérialisation
sont dites sérialisées.
Administration des bases de données
Techniques de verrouillage



Verrouiller momentanément les données utilisées par
une transaction jusqu’à la fin de la mise à jour.
les autres transactions demandant ces données sont
mises en attente jusqu’à leur libération ( déverrouillage).
Le verrouillage s ’applique d ’une façon générale à une
ressource.


Objets créés par les utilisateurs ( tables, ou quelques lignes
d ’une table)
objets système ( éléments du dictionnaire ou des zones de
données en mémoire centrale)
131
Administration des bases de données
Exemple d’utilisation de verrous
T1
132
temps
T2
T1
temps
T2
t1
Début
t1
Début
Début
t2
Lock A
Début
t2
Lock x
Lock A
t3
Lire A
Lock y
t3
Lire x
wait
t4
A :=A+100
Lire y
t4
x:=x-10
wait
t5
Ecrire A
y :=y+100
t5
Ecrire x
wait
t6
Unlock A
Ecrire y
t6
Lock y
Lire A
t7
Commit
Lock x
t7
wait
A :=A-10
t8
Wait
t8
wait
Ecrire A
t9
wait
t9
wait
Unlock A
t10
wait
t10
commit
t11
commit
t11
Administration des bases de données
Deadlock
Interblocage (Deadlock)


L ’emploi des verrous peut entraîner des situations dans
lesquelles 2 ou plusieurs transactions se trouvent bloquées
parce qu’elles utilisent les mêmes ressources; chacune se
trouve en attente de la libération de ressources utilisées par
une autre transaction.
Solutions
 Détection/résolution: laisser les transactions se mettre en
situation d’interblocage. Détecter l’interblocage et le
résoudre en tuant l’une des transactions.
 Préventif : éviter les interblocages.
133
Administration des bases de données
Modes d’activation de verrouillage

Verrouillage implicite


Le SGBD Oracle effectue tous les verrouillage nécessaires
pour le maintien de la cohérence des données.
Verrouillage explicite


134
Au niveau des transactions
Au niveau d ’une instance
Administration des bases de données
Sécurité de données


L’une des tâches de l’administrateur de données est
d’assurer la sécurité de l ’information stockée.
SGBD Oracle assure la protection des données de la
base par:




135
le contrôle d’authenticité des utilisateurs au moment de la
connexion
les tablespaces accessibles par défaut,
la limitation des ressources
les privilèges d ’accès aux objets de la base.
Administration des bases de données
Les profils

Pour éviter une consommation incontrôlée des ressources
système, celles-ci peuvent être définies explicitement par
l’administrateur de chaque type d ’utilisateur.

Une base de données peut être composée de plusieurs profils
qui sont affectés à des utilisateurs. L ’administrateur de la base
possède une option pour activer ou désactiver les limites de
ressources au niveau de la BD.
136
Administration des bases de données
Création d’un profil
CREATE PROFILE nom_profil
LIMIT [SESSIONS_PER_USER{entier|UNLIMITED|DEFAULT}]
[CPU_PER_SESSION {entier|UNLIMITED|DEFAULT}]
[CPU_PER_CALL {entier|UNLIMITED|DEFAULT}]
[CONNECT_TIME {entier|UNLIMITED|DEFAULT}]
[IDLE_TIME {entier|UNLIMITED|DEFAULT}]
[LOGICAL_READS_PER_CALL{entier|UNLIMITED|DEFAULT}]
COMPOSITE_LIMIT {entier|UNLIMITED|DEFAULT}]
PRIVATE_SGA {entier [K|M]|UNLIMITED|DEFAULT}]

137
Administration des bases de données
Création de profil
Quand une limite de ressource est atteinte pour un utilisateur,
Oracle arrête l ’exécution en cours, annule la transaction et
renvoie un code d’erreur.
 Exemple
CREATE PROFILE maître
LIMIT SESSIONS_PER_USER UNLIMITED
CPU_PER_SESSION UNLIMITED
CONNECT TIME 15
COMPOSITE_LIMIT 3500000;

138
Administration des bases de données
Modification d’un Profil


ALTER PROFILE nom_profil LIMIT ………..
Le coût de ressources est positionné pour une base par :


ALTER RESSOURCE COST
CPU_PER_SESSION entier
CONNECT_TIME entier
LOGICAL_READS_PER_SESSION entier
PRIVATE_SGA entier ;
ex. ALTER RESSOURCE COST
CPU_PER_SESSION 150
CONNECT_TIME 2;

Suppression : DROP PROFILE nom_profile [CASCADE]
139
Administration des bases de données
Les utilisateurs


140
Définir son identité (nom et mot de passe), environnement.
CREATE USER utilisateur
IDENTIFIED {BY mot_passe |EXTERNALLY}
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {entier [K|M] |UNLIMITED} ON tablespace]
[PROFILE profile]
Administration des bases de données
Les utilisateurs


Modification
ALTER USER utilisateur
IDENTIFIED {BY mot_passe |EXTERNALLY}
[DEFAULT TABLESPACE tablespace]
[TEMPORARY TABLESPACE tablespace]
[QUOTA {entier [K|M] |UNLIMITED} ON tablespace]
[PROFILE profile]
[DEFAULT RÔLE {rôle[,rôle] …|ALL [EXCEPT rôle [,rôle]..]
|NONE]}

Suppression : DROP USER utilisateur [CASCADE]
141
Administration des bases de données
Les privilèges d’accès

L’accès à la base et la définition de ses objets « privilèges
système ».


TABLE, USER,VIEW, TRIGGER
La manipulation d’objets de la base de données
« privilèges objets »

142
ALTER, DELETE, INSERT, SELECT, UPDATE…
Administration des bases de données
Les privilèges Système

GRANT {priv_sys |rôle}[, priv_sys |rôle].. TO
{user|rôle|PUBLIC}[, user|rôle|PUBLIC]..[WITH
ADMIN OPTION]


GRANT CREATE TABLE, CREATE USER TO user1
REVOKE {priv_sys |rôle}[, priv_sys |rôle].. FROM
{user|rôle|PUBLIC}[, user|rôle|PUBLIC]..

143
REVOKE CREATE USER FROM user1;
Administration des bases de données
Les privilèges Objets

GRANT priv_obj[, priv_obj].. [(colonne[,colonne]..)]
ON [shéma.]objet TO {user|rôle|PUBLIC}[,
user|rôle|PUBLIC]..[WITH GRANT OPTION]



Tous les privilèges avec ALL ou ALL PRIVILEGES.
GRANT SELECT,INSERT ON user1.commande to user2
WITH GRANT OPTION.
REVOKE priv_obj[, priv_obj].. [(colonne[,colonne]..)]
ON [shéma.]objet FROM {user|rôle|PUBLIC}[,
user|rôle|PUBLIC].. [CASCADE CONSTRAINTS]
144
Administration des bases de données
Rôles


Un rôle est une agrégation de droits d ’accès aux
données et de privilèges système qui renforce la
sécurité et réduit significativement la difficulté et
le coût de son administration.
La création d ’un rôle peut servir deux aspects :
application et utilisateurs


145
Rôle application regroupe tous les privilèges nécessaires à
l’exécution d ’une application.
Rôle utilisateur gère des privilèges communs nécessaires
aux utilisateurs de la base.
Administration des bases de données
user1
user2
Rôle vente
Rôle STOCK
Privilèges de
l’application
STOCK
146
user1
Rôle admin
user4
Rôle paie
Rôle PERSONNEL
Privilèges de
l’application
PERSONNEL
Administration des bases de données
user5
Rôles
Utilisateurs
Rôles
application
Privilèges
Applications
Rôles

CREATE ROLE rôle [NOT IDENTIFIED |IDENTIFIED {BY
mot_passe |EXTERNALLY}




CONNECT, RESSOURCE et DBA. L ’importation et l ’exportation de
données: IMP_FULL_DATABASE et EXP_FULL_DATABASE
SET ROLE {rôle [IDENTIFIED BY mot_passe][,rôle
[IDENTIFIED BY mot_passe]]… | ALL [EXCEPT rôle
[,rôle]…|NONE}
ALTER ROLE rôle {NOT IDENTIFIED | IDENTIFIED {BY
mot_passe |EXTERNALLY}}
DROP ROLE rôle
147
Administration des bases de données