Table des matières

But d’un SGBD 2

Protections des données 2
Accès non autorisés 2
Accès Autorise 2
Privilèges 2
GRANT 3
Syntaxe 3
Explications 3
REVOKE 4
Syntaxe 4
Explication 4
Exemple 5
Transaction 6
Exemple de cas 7
Des problèmes lors des transactions 8
Perte d’opération 8
Lecture sale 8
Ecriture sale 9
Non reproductibilité des lectures 9
Analyse incohérente 9
Concurrence 10
Solutions 10
Les verrous 10
Analyse incoherentes 11
DCL
But d’un SGBD
Petit rappel des différentes fonctions d’un système de gestion de base de données.

● Organisation des données (schéma, DDL, . . .)

● Gestion des données (CRUD, DML, . . .)
● Accès aux données (DML, . . .)
● Personnalisation des données extraites (vues, . . .)
● Privilèges et contrôle d’accès (DCL (GRANT, REVOKE), . . .)
● Accès par programme (e-SQL, JDBC, . . .)
● Protection contre incidents (backup, Log File, . . .)
● Gestion des accès concurrents (DCL, transactions, verrous, . . .)
● Dictionnaire des données

Protections des données

Accès non autorisés

● Abus de privilèges, injections, faiblesse d’audit, attaque, utilisation de failles, . . .
● Nous étudierons les injections dans le chapitre e-SQL.

Accès Autorise
● Privilèges, contraintes d’intégrités, gestion de la concurrence, backup, log File, . . .
● Nous avons étudié les privilèges et les contraintes d’intégrités.
● Nous étudierons la gestion de la concurrence dans ce chapitre.

Privilèges
Il est évident que les utilisateurs d’une base de données ne doivent pas toujours avoir les mêmes
droits sur les différents objets (tables, vues, . . .). Le but est de fournir des droits pour certain
utilisateurs et pour d’autre leur refuser le droit. Pour ce faire deux instructions permettent de gérer
ces droits et sont appliqué instantanément.

Il est possible de donner des privilèges sur la vue. Quels est l’utilité ?

Ça permet aux utilisateurs d’avoir accès aux seules informations que fournis la vue. Exemple, les
comptes bancaires positifs car ceux qui sont négatifs sont vulnérable.

GRANT
Grant va permettre de données un ou plusieurs droits sur les différentes instructions tels que
(SELECT, UPDATE, DELETE, INSERT, …) a un ou plusieurs utilisateurs donnés.
Syntaxe

Sélectionner un certain nombre d’instruction ou toutes les sélectionner grâce à ALL.

Ces privilèges seront appliqués sur un objet tels que des tables, des vues, etc. …
Autorisation donnée a un user bien précis, a un groupe d’user ou à tout le monde
Autoriser l’utilisateur qui a reçu le privilège à le transmettre aux autres.

Explications
Pour le droit de modification sur un table, il faut lister dans l’update les attributs qui peuvent être
modifier.

Exemple de WITH GRANT OPTION

Nous avons l’utilisateur U0 qui est propriétaire de la Table1, il va donner à l’utilisateur UZ l’accès a
l’instruction INSERT et lui fournir la possibilité de transmettre aux autres se privilège.

Question : Si l’utilisateur UZ souhaite transmettre l’accès a toutes les instructions sur la Table1 a
l’utilisateur Ud. Cela va-t-il fonctionner ?

La réponse ne se trouve pas dans le point-virgule qui est absente, mais bien dans le privilège que
possède UZ. UZ n’a le droit qu’en INSERT, donc il peut transmettre que l’accès INSERT a Ud.

REVOKE
Revoke va permettre de supprimer un ou plusieurs droits sur les différentes instructions tels
que (SELECT, UPDATE, DELETE, INSERT, …) a un ou plusieurs utilisateurs donnés.
Syntaxe

Sélectionner un certain nombre d’instruction ou toutes les sélectionner grâce à ALL.

Ces privilèges seront supprimés sur un objet tels que des tables, des vues, etc. …
Interdiction donnée à un user bien précis, a un groupe d’user ou à tout le monde

Explication
Lors de la suppression du privilège, le REVOKE est appliquer immédiatement. Il faut être propriétaire
de l’objet ou avoir reçu ce privilège via WITH GRANT OPTION.

Les droits doivent être enlevé de la même maniéré dont ils ont été accordés tkt ta pas compris, un
exemple ne te fera pas de mal :

Moi propriétaire de la table1, je décide de donner l’accès en select à ma table a tout le monde.

Mais aujourd’hui, je ne veux plus laisser accès a ma table car l’utilisateur Ua m’énerve. Je ne peux pas
faire de REVOKE que sur lui.

Je dois retirer l’accès a tout le monde. Tant mieux, ils m’emmerdent tous car l’accès a été donnée en
PUBLIC.

Dans le cas ou plusieurs utilisateurs ont donné un droit sur un objet, il faut que chacun retire ses
droits pour que la victime n’en dispose plus. Lors de la transmission WITH GRANT OPTION.

Voici un exemple :

U0 propriétaire de la table1 a donnée accès à la table1 a Victime, les utilisateurs U1 et U5 on fait de

mêmes, car il avait l’option WITH GRANT OTPION.

U0 et U1 décide de retirée Victime de l’accès à la table1. Victime pourra toujours accéder a la table
car U5 ne lui a pas retiré l’accès.

Exemple
Voici le résultat des requêtés précédente.

Voici le résultat des requêtés précédente.

Transaction
Chaque utilisateur connecter à un SGBD commence une transaction avec celle-ci. Le SGBD va gérer
les différentes transactions des utilisateurs pour éviter les conflits et veiller à ce que les contraintes
d’intégrité soient respecter. Une transaction va être composer de toutes les requêtes exécuter par
l’utilisateur jusqu’au COMMIT ou au ROOLBACK.

Une nouvelle transaction est créée à la fin de la précédente. La validation d’une transaction entraine
la persistance des effets des opérations effectuée. L’annulation d’une transaction annule les effets
des opérations. Une transaction non validée est automatiquement annulée.

Une unité logique signifie une a plusieurs opération sur la base de données.

Lors des modifications sur une base de données, il est possible de valider les transactions à l’aide du
mot clé COMMIT, d’une instruction DDL et d’annuler une transaction à l’aide du mot clé ROLLBACK.

Le SGBD peut mettre une transaction en attente s’il estime que cela est nécessaire. Cela signifie que
la requête demander par un utilisateur ne sera pas exécuter tant que la requête en cours n’a pas été
valider.

Donc une transaction est :

● Unité de traitement séquentiel, exécutée pour le compte d’un usager qui, appliquée a une
base de données cohérente, restitue une base de données cohérente.
● Une transaction est une séquence d’opérations du DML qui est atomique vis-à-vis des
problèmes de concurrences et reprise après panne.
● Une transaction est une opération complexe
o Atomique
L’exécution d’une transaction est atomique si, quoi qu’il arrive, elle est exécutée
complétement ou pas du tout.
o Cohérente
La transaction doit faire passer la base de données d’un état cohérent a un autre.
o Isolée
Les transactions, même si elles s’exécutent sur un même intervalle de temps,
travaillent sans interférence. Les transactions sont exécutées comme si chaque
transaction disposait de la base de données pour elle seule.
o Durable
Dès que la transaction valide ses modifications, le SGBD doit garantir qu’elles sont
permanentes même en cas de panne.

En fonction des SGBD, il est possible de faire des COMMIT ou ROOLBACK automatiquement après
chaque requêtes grâce à l’option AUTOCOMMIT ON. Oracle ne gère les transactions que pour les
ordres DML. Un COMMIT ou un ROLLBACK sera automatiquement exécuté après toutes les autres
requêtes.
Exemple de cas

Je souhaite augmenter le salaire de mon employé 030 de 1€. Je l’affiche pour voir son salaire :

Son salaire est de 11890 €, je vais l’augmenter de 1€ et faire un COMMIT pour que la transaction se
termine.

Si j’affiche le salaire de l’employé 030, il a bel et bien augmenté de 1€.

Je souhaite maintenant augmenter le salaire de 031 de 1€, mais j’ai oublié de changer le numéro de
l’employé en copiant la ligne précédente.

J’annule l’update grâce à la commande ROLLBACK.

Si je réaffiche le salaire de 030, je remarque que l’update précèdent n’a pas été pris en compte grâce
au ROLLBACK.
Des problèmes lors des transactions
Imaginons que deux utilisateurs veulent insérer une valeur pour la même clé primaire. Le SGBD va
mettre des techniques pour éviter à deux transactions d’accéder aux mêmes données.

Perte d’opération
Nous avons une certaine donnée A qui vaut 3 et deux transactions qui vont y accéder. Ils vont chacun
faire des manipulations d’écriture sur la donnée. Mais la seul et dernière modifications qui va être
retenue c’est celle de la transactions T1, car il écrit en dernier lieu.

x2=3+3
X1=3+1
A=6

Lecture sale
Le cas de lecture sale apparait lors d’un ROLLBACK dans une transaction qui va affecter la valeur
principale.

Exemple :

1. La donnée A vaut 3.
2. T2 modifie sa valeur en 5
3. T1 va lire le brouillon de la valeur A qui est devenu 5 à cause de T2.
4. T2 va appliquer un ROLLBACK est A va devenir 3

La valeur est 3 car T2 a fait un ROLLBACK, mais T1 a une valeur erronée de A qui vaut 5 car le
ROLLBACK est appliquée a la transaction T2 seulement.
Ecriture sale

Non reproductibilité des lectures

Analyse incohérente
Concurrence

Il y a concurrence d’accès quand au moins 2 transactions accèdent aux mêmes données. Le

Contrôleur d’accès concurrents est le module du système chargé de contrôler les accès concurrents
aux données.

L’unité de donnée contrôler par le contrôleur est appelée granule de concurrence pouvant être,
suivant le SGBD et/ ou des configurations :

● La BD
● La table
● Le bloc
● Le tuple

Plus le granule sera grand plus le nombre de concurrence augmentera mais moins le contrôleur aura
de granules à surveiller.

Chaque SGBD a ces stratégies de concurrences qui panache sur deux techniques :

● Technique pessimiste

Le contrôleur empêche l’apparition de conflits créant des files d’attentes des granules
sollicités. Exemple lors de la modification d’une donnée dans une transaction par x, si y
essaye de modifier la même donnée que x alors que la transaction n’est pas terminée, il se
trouvera dans une file d’attente.

● Technique optimiste

Le contrôleur laisse travailler chacune des transactions et lors de la détection de conflits

amènera l’annulation de transactions conflictuelles.

Solutions
Le SGBD va mettre des solutions pour résoudre les problèmes de concurrences. Une des solutions les
plus courantes est la mise en place de verrous sur chaque granule contrôlé. On sait qu’un granule
peut être sois une BD, une table, un bloc ou un tuple.

Que doit verrouiller le SGBD, si deux transactions T1 et T2 veulent avoir accès à la table T1 pour y
écrire des données ?

A. Toute la base de données

B. Toute la table T1
C. Uniquement certaine ligne de T1

La solution est de gérer la concurrence tout en gardant de bonnes performances (les transactions
doivent s’exécuter rapidement) et permettre les accès en parallèles aux données.

Les verrous
Il existe deux types de verrous sur une donnée.

● Verrous court (C) est appliqué que durant l’action

● Verrou long (L) est appliqué jusqu’à la fin de la transaction
Chacun de ses verrous peut être sois partagé (S) sur la lecture le verrou sera appliqué sur un granule
libre ou sur lequel est déjà appliquer un verrou partager. Sois partagé sur l’écriture (X) le verrou ne
peut être applique que sur un granule libre sur lequel n’est pas appliquer un verrou.

Lorsqu’une transaction tente de déposer un verrou sur un granule et que cela est refusé, la
transaction est mise en attente.

Certains SGBD (MySQL, JavaDb, . . .) prévoient un time-out, d’autres pas (Oracle, PostgreSQL)

Ces derniers doivent disposer d’un contrôleur qui détecte les interblocages (“étreintes mortelles” ou
deadlock).

Il y a un protocole de verrouillage à respecter en 2 phase.

1. Avant d’agir sur un objet A, une transaction doit obtenu un verrou sur cet objet A
2. Après l’abandon d’un verrou, une transaction ne doit plus jamais pouvoir obtenir de verrous.

Exemple :

Phase 1 : Demande des verrous

● Si une transaction veut faire une lecture de A : demande préalable de verrou LS sur A.
● Si une transaction veut faire une écriture sur A : demande préalable de verrou LX sur A (si
elle a déjà un verrou LS, demande de le promouvoir en LX sur A).
● Si la demande ne peut être satisfaite, la transaction est en attente.

Phase 2 : Abandon des verrous

● Les verrous sont relâchés à la fin de la transaction.

Lecture Sale
Ecriture sale

Perte d’Operations

Nous avons une situation initiale ou A=3.

T2 va poser un verrou long de lecture (LS) sur A.
T2 va lire A.
T1 va poser un verrou long de lecture (LS) sur A
T1 va lire A.
T2 va placer un verrou long en écriture (LX) sur A
T1 va faire de même

La situation obtenue est appelée deadlock le système attend que T1 ou T2 finissent leur transactions.
Mais comme aucun ne veut arrêter une boucle appelé » deadlock apparait.
Analyse incohérente

Deadlock
La gestion des deadlock par le SGBD. L’outil essentiel de ce type de technique est la gestion des
versions des données telles que gérées par les différentes transactions. L’étude de cette technique
dépasse largement le cadre de ce cours.

Niveau d’isolation
Donc, un verrouillage correct des données garantit la sérialisabilité des transactions (ce qui évite les
problèmes). Cependant, le protocole de verrouillage en 2 phases strict est contraignant, dans la
mesure où il bloque potentiellement l’accès aux données durant une longue période de temps. Ne
peut-on assouplir ce protocole ?

Le protocole de verrouillage en 2 phases fonctionne bien(hormis le pb de deadlock)

=>Mais c’est contraignant.est ce qu’on peut assouplir ces règles?

=>SQL2 définit ce qu’on appel les NIVEAUX D’isolation (une certaine forme de tolérance de problème
de concurence ) voir suite pour comprendre ce bazard

ce qu’on appelle les differents niveau d’isolation sont ceci :

°Dirty read :une transaction peut lire des données non validées par une autre transaction :une
transaction peut donc être amenée à travailler sur le ‘brouillon’ d’autre transactions !

°NON-repeatable read:une transaction peut obtenir des valeurs différentes pour des lectures du
même tuples !

°Phantom-read:une transaction peut être amenée lors du même SELECT à recevoir un nombre de
tuples différents !

° Serializable:Aucun des 3 problèmes évoqués n’est possible

exemple
pour la transaction t1 le A4 est un fantôme car l’insertion a était faite par par la transaction T2

DCL - Niveau d’isolation -Exercices