LE LANGAGE DE DEFINITION ET CONTRÔLE DES DONNEES Gestion ...etienne.baudrier.free.fr/L3_Inf/TP1/ldd.pdf · Le langage de définition J.Schnell /2005 (I.U.T. Robert Schuman) LE LANGAGE

Le langage de définition

J.Schnell /2005 (I.U.T. Robert Schuman) LE LANGAGE SQL 40

LE LANGAGE DE DEFINITION ET CONTRÔLE DES DONNEES

● Gestion des tables � créer une table par copie d'une autre

CREATE TABLE nom [ ( colonne , ... ) ] AS Requête ;

La structure de la nouvelle table est définie à partir des colonnes citées dans la requête; les colonnes de la table créée héritent des spécifications de type des colonnes citées dans la requête, et éventuellement des mêmes noms (dans le cas où on ne précise pas de nom de colonne pour la nouvelle table). En outre, la table est initialisée avec les lignes retournées par la requête create table service(noservice,ville) as select deptno,loc from dept where lower(dname)='research' ; Dans cet exemple, on crée une table service , dont les colonnes noservice et ville héritent resp. des types de deptno et loc , initialisée avec les départements de recherche (s'il y en a).

� créer une table (avec les contraintes d'intégrité)

CREATE TABLE table

( { descriptionColonne | contrainteTable } [ , { idem } ] … ) ; • descriptionColonne , c'est: nomColonne type [ DEFAULT expression ] [ contrainteColonne ]

• type • char (n) chaîne de caractères de longueur fixe = n (avec n<=255) • varchar (n) chaîne de caractères de longueur variable (maximale = n) • number entier ou réel • number (p) entier (d'au plus p chiffres) number (p,d) décimal sur p positions en tout (séparateur décimal compris),

dont d chiffres décimaux



• date • long chaîne longue (jusqu'à 2 Go); restriction: par table, pas plus

d'une colonne de type long

☞ On peut aussi utiliser integer , float , decimal , double precision , numeric (n,m) et smallint ou encore un type nommé, créé par l'utilisateur avec la commande CREATE TYPE ( voir + loin)

• contrainteColonne se rapporte à une colonne donnée:

[ NULL | NOT NULL ] [ { UNIQUE | PRIMARY KEY } ] [ REFERENCES [ user. ] table [ (colonne) ] [ ON DELETE CASCADE ] ] [ CHECK (condition) ] Chaque contrainte peut être précédée de la clause CONSTRAINT nomContrainte (nom attribué à la contrainte par l'utilisateur) Plusieurs expressions de contraintes sont possibles pour une colonne (mais UNIQUE et PRIMARY KEY s'excluent). Plusieurs colonnes de la table peuvent être affublées de la contrainte UNIQUE (pas de doublons dans la colonne), mais on ne spécifie qu'une fois PRIMARY KEY (pour l'identifiant). La clause REFERENCES permet de déclarer une contrainte d'intégrité de référence. Par exemple : toute valeur du champ deptno de la table emp (n° de département auquel est rattaché un employé) doit exister dans le champ deptno de la table dept (n° des départements existants).

☞ Si le nom de colonne n'est pas indiqué pour la table (cible) référencée, il s'agit implicitement de la colonne de cette table qui est déclarée PRIMARY KEY.

create table emp ( empno number(8) PRIMARY KEY, mgr number(8), ename varchar(20) NOT NULL UNIQUE, job varchar(20) NOT NULL, sal number(8,2) CONSTRAINT c_salaire CHECK(sal between 2000 and 50000), comm number(8) , hiredate date DEFAULT sysdate, deptno number(2) REFERENCES dept(deptno) ) ; Facultatif si deptno est

PRIMARY KEY de dept



La contrainte de référence sera vérifiée lors de l'ajout ou la mise à jour d'un employé; en outre, toute suppression de département encore référencé par des employés sera refusée, sauf si la clause ON DELETE CASCADE est prévue. Dans ce cas la suppression d'un département provoque la suppression (en cascade) des employés qui en font partie (solution radicale …).

☞ Je n'ai précisé de nom (c_salaire ) que pour la contrainte relative à sal ; il est recommandé de le faire systématiquement (sauf pour la spécification NULL ou NOT NULL)

• contrainteTable : contrainte concernant une ou plusieurs colonnes de la

table. On retrouve, avec une légère nuance dans la syntaxe de REFERENCES, les mêmes clauses (sauf NULL). [ { UNIQUE | PRIMARY KEY } (colonne [ , colonne] …) ] [ CHECK (condition) ] [ FOREIGN KEY (colonne [ , colonne ]… ) REFERENCES [user.] table [ (colonne [ , colonne ]… ) ] [ ON DELETE CASCADE ] ] Chaque contrainte peut être précédée de la clause CONSTRAINT nomContrainte (nous conseillons d’ailleurs de le faire ! voir exemple plus loin ) FOREIGN KEY signifie clé étrangère ; le groupe de colonnes citées (éventuellement une seule) est l'identifiant (PRIMARY KEY) d'une autre table. Exemples de contrainteTable: CONSTRAINT commJob CHECK((comm is not null and job

='Salesman')or(comm is null and job != 'Salesman) ) PRIMARY KEY(nhotel,nordre)

Contrainte concernant les 2 colonnes job et comm

Pour une table de chambre (d'hôtel) : Identifiant composé de 2 champs: le N° d'hôtel et le n° d'ordre (le n° inscrit sur la porte d'une chambre).



Autre manière ( recommandée ) d'écrire le CREATE TABLE : Ici, toutes les contraintes (autres que not null ) sont regroupées après les définitions des colonnes et nommées Toutes les contraintes sont exprimées sous la forme de contrainteTable

create table emp ( empno number(8), mgr number(8), ename varchar2(20) NOT NULL, job varchar2(20) NOT NULL, sal number(8,2) NOT NULL, comm number(8), deptno number(2), hiredate date DEFAULT sysdate, CONSTRAINT c_salaire CHECK(sal between 2000 and 50 000) CONSTRAINT c_commJob CHECK((comm is not null and job = 'Salesman')or (comm is null and job != 'Salesman) ) , CONSTRAINT u_ename UNIQUE(ename), CONSTRAINT pk_emp PRIMARY KEY (empno), CONSTRAINT fk_deptno FOREIGN KEY (deptno) REFERENCES dept ON DELETE CASCADE) ; • Pour le « fun » : un exemple de référence composée (clause FOREIGN KEY) create table chambre ( nhotel number(4) NOT NULL , nordre number(2) NOT NULL , … , CONSTRAINT pk_chambre PRIMARY KEY (nhotel,nordre), CONSTRAINT fk_hotel FOREIGN KEY(nhotel) REFERENCES hotel ) ; Considérons à présent qu'on fait des réservations (pour une seule chambre). create table reserve ( nreserve number(8), code_client char(6) NOT NULL, num_hotel number(4) NOT NULL , num_chambre number(2) NOT NULL , … , CONSTRAINT pk_reserve PRIMARY KEY (nreserve), CONSTRAINT fk_client FOREIGN KEY(code_client) REFERENCES client, FOREIGN KEY (num_hotel,num_chambre)REFERENCES chambre ) ;

Rappel : attention avec cette

option !



☞ Lors de la création d’une table par copie (cf début du chapitre : CREATE

TABLE … AS Requête), par défaut, on ne récupère pas les contraintes liées à la table d’origine (citée dans la requête) ! On peut toutefois doter la table créée par copie de contraintes d’intégrité

Exemple create table service (nservice, ville,

constraint pk_service primary key(nservice )) as select deptno,loc from dept Restriction dans les contraintes : pas de spécification FOREIGN KEY possible avec cette façon de faire ; mais il est toujours possible de l’ajouter après coup (voir commande ALTER TABLE) � créer un type (structuré)

CREATE TYPE type AS OBJECT ( { nom-colonne type } [ , { idem } ] … ) / create type adresse_t as object (norue number(3),rue varchar(20), codePostal char(5),ville varchar(15) ) / On peut maintenant créer une table dont une colonne de type adresse_t create table etudiant (code number(4) primary key, adr adresse_t,…) ; L’accès au sous-champ rue de la colonne adr est réalisé par la notation pointée : adr.rue Toutefois, une requête telle que : select code, adr.rue,adr.ville from emp where adr.codePostal like ‘67%’ ; curieusement ne fonctionne pas !



Il faut obligatoirement se servir d’un alias : select e.code, e.adr.rue, e.adr.ville from emp e where e.adr.codePostal like ‘67%’ ; � On peut aussi créer une table ayant la structure d’un type par CREATE TABLE nom OF type ; create table adresses OF adresse_t ; � Supprimer, renommer une table DROP TABLE [ utilisateur. ] nomTable ; On ne peut supprimer que les tables qu'on a soi-même créés (sauf si on dispose d'un privilège administrateur, auquel cas on peut préciser devant le nom de la table, le nom de son créateur). drop table emp ; RENAME ancienNom TO nouveauNom ; rename emp to salarie rename salarie to emp

☞ Ne pas confondre avec CREATE SYNONYM qui donne à un objet un nom supplémentaire (alias).

� modifier la structure d'une table ALTER TABLE [ utilisateur. ] nomTable [ ADD ( colonne type [ NULL | NOT NULL ] , ... ) ] [ MODIFY ( colonne [ type ] [ NULL | NOT NULL ] , ... ) ] [ DROP COLUMN colonne ] [ RENAME COLUMN old TO new ] ; Avec la clause ADD, on ajoute des colonnes à la structure de la table

salarie est le nouveau nom de la table emp



Avec MODIFY, on modifie le type d'une (ou plusieurs) colonne existante. Exemples alter table emp modify (sal number(10 ,2),job varchar(30) ) ; ☞ Dans la plupart des cas, on a besoin d'élargir une colonne. Si toutefois, on veut diminuer la longueur d'une colonne ou changer son type (number

en char , par ex.), il faut que toutes les valeurs de cette colonne soient à null ; il est alors nécessaire de passer par une table intermédiaire si on souhaite conserver les actuelles valeurs de la colonne.

On procède de la manière suivante (soit t le nom de la table à modifier) : 1) créer une table intermédiaire, avec les nouvelles spécifications de types 2) copier les données de t dans intermédiaire (insert ) 3) détruire t ( drop ) 4) renommer intermédiaire en t (rename ) alter table emp modify (job null)

Pour l'opération inverse (contraindre une colonne à not null ): pas de problème si la table est vide; sinon, la colonne ne doit, évidemment, pas comporter actuellement de valeur = null . alter table dept add (budget number(6) ) ;

☞ On ne peut créer un nouveau champ avec l'indication not null que lorsque la table est vide (à moins de la doter d’une valeur DEFAULT pour ce champ)

exemple ( table supposée non vide) : alter table emp add(city varchar(20) default 'LA' n ot null);

OK ! toutes les lignes existantes contiennent ‘LA’ dans le champ city Si la table n’est pas vide et qu’on ne spécifie pas de valeur DEFAULT, procéder en 3 étapes : - alter table t add (colonne...) sans indiquer not null - Mettre à jour les valeurs dans la colonne (update ) - alter table t modify (colonne ... not null )

supprime la spécification not null pour la colonne job ; les valeurs null y sont maintenant autorisées

ajout de la colonne budget à la table dept (toutes les valeurs de ce champ sont automatiquement mises à null)



Pour supprimer une colonne (par exemple, la colonne budget qu'on vient d'ajouter précédemment) :

alter table dept

drop column budget; Renommer une colonne

alter table emp rename column sal to salary Réarranger l'ordre des colonnes d'une table, et (si besoin) les renommer 1) create table departement(nom, ville, numero) as select dname, loc, deptno from dept ; 2) drop table dept 3) rename departement to dept Pour ajouter ou supprimer des contraintes , on utilise aussi la commande ALTER TABLE. ALTER TABLE table [ ADD ( { descriptionColonne | contrainteTable }…) ] [ MODIFY ( descriptionColonne …) ] [ DROP contrainte ] [ { ENABLE | DISABLE } contrainte ] ; En plus de l'ajout de nouvelles colonnes (avec, éventuellement, des contraintes) à une table et la modification de colonnes existantes, la commande offre les possibilités d'ajout de contraintes, de suppression (définitive par DROP), d'inhibition de contraintes (mise hors service provisoire) et d'activation (réactivation).

Exemples alter table emp modify ( ename varchar(30)) drop CONSTRAINT c_salaire ;

Rappel: mgr est le n° de l'employé responsable d'un employé

Détendez-

vous !!!



alter table emp add ( prime number(4),FOREIGN KEY(mgr) REFERENCES e mp ) ; alter table emp drop UNIQUE(ename) ; alter table emp disable CONSTRAINT commJob ; alter table emp disable PRIMARY KEY ; alter table emp enable PRIMARY KEY ;

● Gestion des index Les index (construits sur une table) dans une base de données ont le même fonctionnement que les index (ou tables de matières) d'un quelconque ouvrage; la consultation de l'index dans l'annuaire des pages jaunes permet de retrouver rapidement un mot-clé (rubrique professionnelle) avec les pages associées. Un index accélère (en principe) les requêtes dont la condition de recherche (clause WHERE) porte sur une colonne indexée. � ajout d'un index à une table

CREATE [ UNIQUE ] INDEX nomIndex ON table (colonne [ , colonne ] ... ) La mention UNIQUE garantit l'unicité des valeurs des colonnes indexées dans la table.

☞ Inutile de créer un index (UNIQUE) sur la colonne clé ! La clause PRIMARY KEY (si elle a été spécifiée…), lors de la création de la table, provoque automatiquement la création d'un tel index

la contrainte est réactivée (entre temps, on aurait pu faire des mises à jour incohérentes…)

la contrainte est inhibée (provisoirement)

à condition, bien sûr, que la contrainte unique(ename) (d'ailleurs « audacieuse », soit dit en passant) a été spécifiée auparavant



Exemples create index I_deptno on emp(deptno) create index I_job on emp(job); ☞ index sans l'option UNIQUE puisque doublons autorisés

Ces index sont exploités par l'optimiseur de requêtes (intégré au S.G.B.D.) pendant une interrogation (SELECT) en fonction de la présence de colonnes indexées dans la clause WHERE de la requête. Par exemple, l'index I_deptno sera automatiquement utilisé par le système pour une requête comme: select * from emp where deptno = 1 ;

☞ Il est souvent utile d'indexer les colonnes de jointure (deptno de emp et deptno de dept , par exemple) ; les requêtes joignant 2 tables y gagnent en performance !

Evitez toutefois de créer des index à tort et à travers; il n'est, par exemple, pas utile d'indexer la colonne job si 80% des employés ont le job de 'Salesman' !!

La colonne doit être discriminante. D'un point de vue structure interne : l'index contient une entrée pour chaque valeur de la colonne indexée, et y associe l'adresse de la (les) ligne(s) qui a(ont) cette valeur de colonne; il est organisé en arbre hiérarchisé équilibré (B -Tree).

� suppression d'index DROP INDEX nomIndex ; drop index I_deptno ;

☞ La suppression d'une table provoque la suppression des index rattachés à celle-ci.

on peut créer plusieurs index sur la même table (mais ne pas en abuser...)

Cf cours de structures de données



● Les tables virtuelles, ou vues � créer une vue CREATE VIEW nomVue [ ( colonne [ , colonne] ... ) ] AS Requête [ WITH CHECK OPTION ] ; ☞ la clause ORDER BY n'est pas autorisée dans la requête associée Par défaut, la vue hérite des noms de colonne de la requête Une vue est une table virtuelle (en ce sens qu'elle n'est pas un récipient de données) définie, grâce à la requête SQL qui lui est associée, à partir des tables, voire d'autres vues. Elle peut être manipulée (avec quelques restrictions toutefois) par les utilisateurs, comme s'il s'agissait d'une table. La définition de la vue est cataloguée dans le dictionnaire de données; chaque fois qu'une requête invoque cette vue, Oracle accède au dictionnaire pour lancer l'exécution de la requête liée à la vue. Exemple create view personnel as select empno, ename, job, dname, loc from emp, dept where emp.deptno = dept.deptno ; Une requête telle que : select * from personnel ; invoque la vue définie précédemment; Oracle en recherche la définition et exécute, en réalité, la requête: select empno, ename, job, dname, loc from emp, dept where emp.deptno = dept.deptno ; Plus généralement, une requête initiale (R) portant sur une vue est transformée par l'interpréteur en une requête qui est la combinaison de R et de la requête associée à la vue (tâche parfois assez difficile...)

La définition de personnel est stockée dans le dictionnaire; la requête select ne s'exécute pas !



select * from personnel where ename like 'S%' ; devient: select empno, ename, job, dname, loc from emp, dept where emp.deptno = dept.deptno and ename like 'S%' ; select loc, count(*) from personnel where lower(job)='salesman' group by loc ; est transformée en: select loc, count(*) from emp, dept where emp.deptno = dept.deptno and lower(job)='sale sman' group by loc; Intérêt des vues ➢➢➢➢ facilitent l'écriture de requêtes complexes (le problème peut être décomposé en créant une vue pour une partie de la requête). ➢➢➢➢ la gestion de la confidentialité (des données) C'est la vocation première des vues. Une vue permet de cacher à certains utilisateurs des données sensibles; il suffit, pour cela, de leur autoriser l'accès aux vues, mais pas aux tables réelles. ✍ la commande GRANT, que nous verrons d'ici peu -patience, que diable... - permet de

donner aux utilisateurs des droits d'accès sur des tables ou vues; notez dès à présent que, par défaut, seul le créateur d'un objet possède des droits sur celui-ci.

Ainsi, la vue personnel masque aux utilisateurs ayant reçu le droit de consulter personnel (et pas les tables réelles emp et dept ) les colonnes sal et comm, entre autres. En outre, les utilisateurs en question ignorent que les informations proviennent de 2 tables distinctes qui sont jointes lors de la consultation de personnel . Ce qui nous amène tout naturellement au 3° intérêt des vues... ➢➢➢➢ renforçent l'indépendance logique Les programmes d'application utilisant des vues sont (en partie) immunisés contre les modifications de la structure de la base; si une table ou une colonne change de nom, il suffit de faire des retouches dans les définitions des vues dépendantes.



Grâce aux vues, des utilisateurs différents peuvent avoir des visions différentes de la même base de données (schémas externes); les mêmes données peuvent être présentées selon diverses perspectives; la vue personnel , par exemple, présente les données des tables emp et dept sous une forme assemblée. La vue qui suit présente une colonne qui est, en réalité, le résultat d'un calcul: create view moyennes (num_dept,moysal) as select e.deptno,avg(e.sal), from emp e group by e.deptno ; La requête: select num_dept, moysal from moyennes ; déclenchera le calcul de avg(sal) pour chaque département. ➢➢➢➢ la vérification des contraintes d'intégrité Les vues offrent aussi un mécanisme de vérification de contraintes d’intégrité avec la clause WITH CHECK OPTION (moins en vogue depuis qu’on peut spécifier, sous ORACLE, des contraintes lors du CREATE TABLE ; c’est à dire depuis la V7) Dans l’exemple qui suit, la vue permet de contrôler des mises à jour et de rejeter celles qui ne respectent pas la condition énoncée (dans la clause WHERE de la requête associée à la vue) create view good_emp as select * from emp where deptno in (select deptno from dept) and ( job = 'Salesman' or comm is null) with check option ; Conditions vérifiées: le n° de département doit exi ster dans la table dept (intégrité de référence !) et les employés qui ne sont pas vendeurs doivent avoir une commission à null . ☞ Cela implique, bien sûr, qu’on ait donné aux utilisateurs les droits (voir

plus loin, commande GRANT) de mise à jour (insert , ...) sur la vue au lieu de la table réelle

job ≠ 'Salesman' ⇒ comm is null a ⇒ b est traduit sous la forme a ou b

colonne calculée = avg(sal)



Mises à jour des tables au travers des vues Comme vous l’avez sûrement compris (…), la mise à jour d'une vue (par INSERT, UPDATE, DELETE) consiste, en réalité, à mettre à jour la table sur laquelle elle est définie. Toutefois, la mise à jour au travers d'une vue est soumise aux restrictions suivantes: • la vue doit être définie à partir d'une seule table, et la requête associée ne pas

comporter de clause GROUP BY, ni la mention DISTINCT pour une colonne citée dans SELECT.

• pour utiliser UPDATE ou INSERT, la clause SELECT de la requête associée à la vue ne

doit pas mentionner d'expression calculée comme, par exemple, sal+comm . • pour que INSERT n'échoue pas, toutes les colonnes de la table (à partir de laquelle la

vue est définie) qui ont été déclarées not null doivent figurer dans la vue. Un sérieux inconvénient des mises à jour au travers de vues (avec clause WITH CHECK

OPTION) réside dans la non convivialité du message d'erreur, suite à la violation de l'une des contraintes (on reçoit en pleine figure un message comme: ORA-1402: VIEW WITH CHECK OPTION WHERE-CLAUSE VIOLATION... et débrouillez-vous avec ça ! ) Un autre inconvénient des vues est qu'elles ralentissent sensiblement les opérations de consultation et de mise à jour des données. � supprimer une vue DROP VIEW nomVue ; La suppression d'une table n'engendre pas automatiquement la suppression des vues où cette table intervient; il faut faire le ménage soi-même.



● La gestion des privilèges On distingue deux catégories de privilèges: les privilèges de type système (gérés par un administrateur de la base) et les privilèges vis à vis des objets ( tables, vues) créés par les utilisateurs Nous nous intéressons essentiellement aux privilèges de type objet, la première catégorie (système) relevant davantage de l'administration (nous les présentons toutefois afin d’assouvir votre curiosité naturelle …) � les privilèges système En principe réservés aux administrateurs, ils permettent de créer/modifier des objets essentiels de la base (tables, profils d’utilisateurs, …)

GRANT { priv-syst | rôle } [, { priv-syst | rôle } ] … TO { utilisateur | rôle | PUBLIC } [, { utilisateur | rôle | PUBLIC } ] … [ WITH ADMIN OPTION ] ;

WITH ADMIN OPTION : avec cette option, l'utilisateur bénéficiaire de privilèges peut transmettre ceux-ci à d'autres utilisateurs. La commande sert aussi bien à créer un nouvel utilisateur (option CONNECT obligatoire + éventuellement RESOURCE et/ou DBA) qu'à étendre des privilèges ou modifier le mot de passe initial. La seule utilisation possible de la commande pour un utilisateur ne disposant pas du droit DBA (database administrator) est la modification de son mot de passe. Le privilège(un rôle en fait !) CONNECT autorise un utilisateur à: • se connecter à la base en appelant l'un des outils Oracle (SQL*Plus , par

ex.) • changer son mot de passe • manipuler les objets (tables, vues) de la base si des droits (select ,

insert , ...) lui ont été accordés par les propriétaires des objets . • transmettre des autorisations sur des objets s'il a obtenu le droit de

retransmettre. • créer des vues et des synonymes (nous en parlons d'ici peu, patience) pour

des objets autorisés.



Par exemple, pour pouvoir créer une vue, il faut avoir reçu le droit select sur les tables mises en jeu. Un tel utilisateur ne peut, par contre, pas créer de table ni d'index. Le rôle RESOURCE autorise un utilisateur à: • créer des tables, index . • donner des droits de manipulation (ou les reprendre...) sur ses propres

objets à d'autres utilisateurs. Le rôle DBA permet de créer des utilisateurs, de donner (ou retirer) des privilèges aux utilisateurs, d'accéder à tous les objets de la base, de créer des synonymes publics et d'effectuer les opérations de maintenance de la base. Quelques autres privilèges (plus fins) CREATE ANY TABLE, ALTER ANY TABLE , DELETE ANY TABLE , CREATE TABLE,

CREATE VIEW, ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK,

CREATE SEQUENCE, CREATE SESSION, CREATE SYNONYM, DROP USER, AUDIT

SYSTEM, … Liste plus complète en annexe (inutile de les apprendre par cœur)

La commande REVOKE permet de supprimer partiellement ou totalement des privilèges accordés. REVOKE { priv-syst | rôle } [, { priv-syst | rôle } ] … FROM { utilisateur | rôle | PUBLIC } [, { utilisateur | rôle | PUBLIC } ] …; Exemples de gestion des privilèges grant connect to villepin identified by ump; grant connect,resource to chirac identified by rpr;

création (par un dba nécessairement) de l'utilisateur villepin , avec un mot de passe.

L'utilisateur chirac a, en plus, la possibilité de définir des structures de tables (et des index, ...)



grant resource to villepin; grant dba to chirac ; grant connect to chirac identified by elysee; grant create session, create table to schnell; grant create user to lachiche ; revoke create user from lachiche; � la gestion des privilèges (ou droits) sur les objets Accorder un (ou plusieurs) privilège(s)

GRANT { droit [ , droit ] ... | ALL } [ (colonne [, colonne ] … ) ] ON [ utilisateur. ] objet TO { utilisateur | rôle [ , utilisateur | rôle] ... | PUBLIC } [ WITH GRANT OPTION ] ;

objet = table ou vue Quelques droits possibles:

Droit

Table

Vue

ALTER ✔ DELETE ✔ ✔ INDEX ✔ INSERT ✔ ✔ REFERENCES ✔ SELECT ✔ ✔ UPDATE ✔ ✔

☞ pour les droits UPDATE et REFERENCES, on peut préciser des colonnes.

GRANT update (colonne, ...) ON table

villepin jouit à présent des mêmes privilèges que chirac

Changement du mot de passe



REFERENCES: permet au bénéficiaire de créer une contrainte faisant référence à la table. INDEX et REFERENCES ne peuvent être attribués à un rôle. Le créateur d'un objet en est le propriétaire et a automatiquement tous les droits sur celui-ci: SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER (pour alter table ) et INDEX (pour ajouter des index à une table). (les privilèges ALTER et INDEX ne s'appliquent pas aux vues). Les utilisateurs (hormis les dba ) n'ont, par défaut, aucun droit sur les objets des autres utilisateurs. Le propriétaire peut accorder, sélectivement, à d'autres utilisateurs des privilèges sur ses objets.

• tous les utilisateurs = PUBLIC • tous les privilèges = ALL

En transmettant un droit avec l'option WITH GRANT OPTION, l'heureux bénéficiaire peut, à son tour, le transmettre à un autre utilisateur, mais pas à un rôle (rassurez-vous, ce n'est pas contagieux...). Exemples grant all on emp to jules with grant option ; grant select, update(sal, comm) on emp to jim; La reprise de droits est ordonnée par la commande REVOKE.

REVOKE { droit [ , droit ] … | ALL } ON [ utilisateur. ] objet FROM { utilisateur | rôle [ , utilisateur |rôle ] … | PUBLIC } ;

revoke insert on emp from dupont ;

� La clause FROM PUBLIC reprend les privilèges qui avaient été donnés à PUBLIC ( GRANT ... TO PUBLIC), mais ne reprend pas les privilèges qui avaient été accordés sur cet objet individuellement (c'est à dire: en nommant un utilisateur) !

grant select on dept to durand,dupont; grant select on dept to public ;

Tous les utilisateurs (actuels et futurs) peuvent lire dept



revoke select on dept from public ;

☞ Quand bien même un utilisateur a bénéficié d'un droit sur un objet, il doit, par défaut, en indiquer le propriétaire (en préfixant le nom de l'objet par le nom du propriétaire: propriétaire.objet ).

Si, par ex., chirac transmet à juppe le droit select pour une table de nom cadeau , celui-ci doit écrire: select * from chirac.cadeau ; Toutefois, ceci peut être évité par l'emploi de synonymes.

CREATE [ PUBLIC ] SYNONYM [ utilisateur. ] nomSynonyme FOR [ utilisateur. ] Objet ;

Si le nom d'utilisateur est omis (dans la clause FOR), l'objet (table ou vue) doit appartenir à l'utilisateur créant le synonyme. Seuls les utilisateurs de type DBA sont habilités à définir des synonymes PUBLIC; en outre, ils peuvent créer un synonyme pour un utilisateur donné (en indiquant son identité avant le nom du synonyme). Les autres utilisateurs ne peuvent créer que des synonymes à usage privé. Il y a plusieurs façons de régler le problème de juppe (supposé non DBA) : • L'utilisateur juppe crée lui-même le synonyme create synonym cadeau for chirac.cadeau; • Un utilisateur DBA s'y colle create synonym juppe.cadeau for chirac.cadeau ; create synonym juppe.kdo for chirac.cadeau;

☞ si chirac (DBA) fait lui-même le travail, il peut écrire:

create synonym juppe.cadeau for cadeau ;

mais …durand et dupont conservent le droit select !

Dans les 2 cas, le synonyme est réservé à juppe

implicitement: chirac.cadeau

Nom de synonyme différent du nom de l'objet



Le DBA peut aussi être plus généreux : create public synonym cadeau for chirac.cadeau; Tous les utilisateurs (ayant reçu le droit select sur cadeau ) pourront y faire référence par: select * from cadeau ;

� Une erreur (assez fréquente) est de croire que la création d'un synonyme (PUBLIC notamment) dispense d'accorder des droits; GRANT est indispensable ! ( les synonymes évitent seulement d'avoir à indiquer le propriétaire de l'objet).

La suppression d'un synonyme s'effectue par:

DROP [ PUBLIC ] SYNONYM [ utilisateur. ] nomSynonyme Exemples de transmission (et révocation) de privilèges Hypothèse: chirac , propriétaire de emp et dept , est aux manoeuvres � grant select on dept to jolie, pitt; � grant insert,update(sal)

on emp to public ;

� grant all on dept to karembeu ; grant all on emp to karembeu; � revoke select on dept from jolie; � revoke insert,update on emp from public ;

voir exemple � : les utilisateurs n'ont plus, sur emp, que le droit select karembeu conserve les droits qu'il (elle) a obtenu personnellement en � revoke delete on dept from karembeu;

Combinaison de � et : karembeu a tous les droits sur dept , sauf delete . ...

revoke all on dept from karembeu; revoke all on emp from karembeu ;

Merci !

Le rêve fut de courte durée ...

Disgrâce

angelina & brad



grant select,update on dept to villepin with grant option ;

villepin bénéficie à présent des privilèges select,update sur dept et de la possibilité de transmettre ceux-ci à d'autres utilisateurs.

villepin ne se fait pas prier et propage…

grant select,update on chirac.dept to basinger; grant select on chirac.dept to cruise with grant o ption ;

(comme nous l'avons indiqué précédemment, l'utilisation d'un synonyme pour chirac.dept faciliterait la tâche de villepin )

� grant select, update(sal,comm) on emp to kidman ; � create view gains(nom,date_embauche,salaire) as select ename,hiredate,sal from emp where job='Manager' ; grant select on gains to nicholson ; • Exemple de vue avec contrainte

create view good_emp as select * from emp where deptno in (select deptno from dept) and ( job='Salesman' or comm is null) with check option ; grant select,insert,update on good_emp to stone ; create synonym bon_employe for chirac.good_emp ; insert into bon_employe values(66,1,'Schnell','PDG',20000,30000,2,'16/07/2 005'); La contrainte (job='Salesman' or comm is null) est violée et l’utilisateur stone va devoir faire face au terrible: "ORA-01402: VIEW WITH CHECK OPTION WHERE-CLAUSE VIOLATION" !!

Rappel : il est préférable de spécifier les contraintes directement lors du CREATE TABLE

instructions écrites par l'utilisateur stone

comm



• Un exemple de vue variable (en fonction de l'identité de l'utilisateur), accessible en lecture à tout le monde.

create view homonymes (nom,deptno,job )

as select ename,deptno,job from emp where upper(ename)=upper( user) ; grant select on homonymes to public ; Explication: un utilisateur consultant la vue homonymes verra le nom (à titre de vérification), le n° de département et le job de s employés qui portent le même nom que lui . Tous les utilisateurs (comptes Oracle) peuvent consulter homonymes; mais ceux dont le nom ne correspond pas à un nom d'employé ne verront rien (no rows selected ).

☞ Les tables du dictionnaire de données (en réalité, des vues ) sont construites sur ce principe. exemple: user_tables , qui donne la liste des tables créées par un utilisateur; user_tables est une vue variable.

� Le concept de RÔLE Jusqu'à présent, nous n'avons vu que la notion d'utilisateur ; il existe en plus le concept de rôle . Un rôle est un ensemble nommé de privilèges (système et objet) qui peut être attribué à des utilisateurs. Il offre une plus grande facilité de gestion des droits d'accès aux objets (voir exemple plus loin pour s'en convaincre). On peut fabriquer un rôle avec la commande:

CREATE ROLE nomRole [ NOT IDENTIFIED | IDENTIFIED BY motDePasse ] ;

(à condition d'avoir le droit de créer des rôles ! ) La commande GRANT supporte: • la transmission de privilèges à des utilisateurs (là, nous sommes en terrain

connu...) • la transmission de privilèges à des rôles • l'attribution d'un rôle à un utilisateur • l'attribution d'un rôle à un rôle ! (et la boucle est bouclée...)

user = nom de l'utilisateur courant



Exemples

1) sans définir de rôle : grant all on emp to raffarin; grant insert on dept to raffarin; grant select,update on dept to raffarin with grant option ; revoke all on emp from raffarin; revoke all on dept from raffarin; grant all on emp to villepin; grant insert on dept to villepin; grant select,update on dept to villepin with grant option ; 2) Maintenant, nous créons et utilisons un rôle : create role premierMinistre identified by matignon; grant all on emp to premierMinistre; grant insert on dept to premierMinistre; grant select,update on dept to premierMinistre with grant opti on ; grant premierMinistre to raffarin ; revoke premierMinistre from raffarin; grant premierMinistre to villepin; Dans une session, un utilisateur peut activer (interpréter comme au cinéma) un rôle donné (à condition de connaître le mot de passe éventuel de ce rôle).

SET ROLE { rôle [ IDENTIFIED BY mot-de-passe ] [ , rôle [ IDENTIFIED BY mot-de-passe ] ] … | ALL [ EXCEPT rôle [, rôle] …] | NONE } ;

L’instruction permet aussi de demander l’activation de tous les rôles à l’exception de certains ; on peut également souhaiter ne jouer aucun rôle ou se libérer d’un (ou plusieurs) rôles. La suppression d’un rôle s’effectue par :

DROP ROLE rôle

Donner des droits à un rôle

On donne à villepin les mêmes droits qu'à raffarin (avant qu'on n'ait enlevé à ce dernier ses droits)

Donner un rôle à un utilisateur l'utilisateur hérite des droits attachés au rôle premierMinistre



☞ Les rôles système CONNECT, RESOURCE, DBA sont des rôles prédéfinis assurant la compatibilité avec les privilèges systèmes antérieurs (à la V7).

Rôles de base Privilèges attachés CONNECT ALTER SESSION, CREATE CLUSTER,

CREATE DATABASE LINK, CREATE SEQUENCE,

CREATE SESSION, CREATE SYNONYM,

CREATE TABLE, CREATE VIEW

RESOURCE CREATE CLUSTER, CREATE PROCEDURE, CREATE

SEQUENCE, CREATE TABLE, CREATE TRIGGER

DBA Tous privileges avec WITH ADMIN OPTION

� Les utilisateurs Nous présentons juste les commandes de gestion d’utilisateurs sans entrer dans les détails (relèvent de l’administration de base de données)

CREATE USER util [ IDENTIFIED { BY mot-de-passe | EXTERNALLY } ] [ DEFAULT TABLESPACE nom-ts ] [ TEMPORARY TABLESPACE nom-ts ] [ QUOTA { entier [ K | M ] | UNLIMITED ] ON nom-ts ] .. [ PROFILE profil ] ;

EXTERNALLY : le nom de l'utilisateur est identique à celui utilisé pour se connecter au système, préfixé par la paramètre OS_AUTHENT_PREFIX du fichier INI.ORA. Le mot-de-passe n'est alors pas requis (par compatibilité avec la V6, ce paramètre vaut généralement OPS$)

ALTER USER util [ IDENTIFIED { BY mot-de-passe | EXTERNALLY } ] [ DEFAULT TABLESPACE nom-ts ] [ TEMPORARY TABLESPACE nom-ts ] [ QUOTA { entier [ K | M ] | UNLIMITED ] ON nom-ts ] .. [ PROFILE profil ] [ DEFAULT ROLE { rôle[, rôle] .. | ALL [EXCEPT rôle [, rôle] … | NONE } ] ;

Comme on le constate, un rôle par défaut peut être attribué à un utilisateur.

DROP USER util [ CASCADE ] ; CASCADE : pour supprimer tous les objets contenus dans le schéma de util

option indispensable si le schéma n'est pas vide.



Le Dictionnaire de Données

Le dictionnaire est un ensemble de tables ou vues qui rassemblent toutes les informations sur les objets de la base, par exemple:

• les noms des utilisateurs d'Oracle • les privilèges accordés • les caractéristiques de tous les objets : tables, vues, index, clusters,

synonymes et séquences • les clés primaires et les clés étrangères • les contraintes imposées aux tables • les informations sur les espaces physiques alloués aux objets, l'audit

(sécurité et performance), ... Le dictionnaire, qui est organisé comme une base de données "interne" est géré dynamiquement par le noyau d'Oracle. Les objets qu'il contient appartiennent à l'utilisateur SYS. Il peut être consulté par les utilisateurs au travers de vues particulières, la vue dictionary (ou dict ) fournissant quant à elle, la liste complète des objets constituant le dictionnaire: select * from dictionary;

Structure du dictionnaire Il est formé de quatre classes de vues. Les trois premières rassemblent des vues de noms identiques, au préfixe près, ce dernier indiquant la nature des objets concernés par la vue. Ce peut être USER_ : la vue porte sur les objets propriétés de l'utilisateur ALL_ : la vue porte sur les objets accessibles à l'utilisateur DBA_ : la vue porte sur tous les objets (nécessite le privilège DBA). Exemple : USER_TABLES : tables m'appartenant ALL_TABLES : tables qui me sont accessibles DBA_TABLES : toutes les tables de la base La liste qui suit indique les principales vues; util peut être remplacé par l'un des préfixes précédents. Sont portés entre parenthèses les noms de colonnes de la vue.



Remarque : le nom du propriétaire d'un objet (owner) n'est évidemment pas cité dans les vues USER_xxxx. util_CATALOG : liste des tables, vues synonymes et séquences (owner, table_name, table_type) util_COMMENTS: commentaires associés aux colonnes (owner, table_name, colum_name, comments) util_CONSTRAINTS : liste des contraintes sur les tables

(owner, constraint-name, constrainst-type, table_name, search_condition, r-owner, r_constrainst_name)

util_INDEXES : liste des index sur les tables et clusters (owner, index_name, table_owner, table_name, table_type, uniqueness, tablespace_name, ini_trans…)

util_OBJECTS : liste des objets (owner, object_name, object_id, object_type, created, modified) util_SYNONYMS : liste des synonymes (owner, synonym_name, table_owner, table_name, db_link) util_TABLES : liste des tables (owner, table_name, tablespace_name, cluster_name, …) util_TAB_PRIVS : liste des privilèges

(grantee, owner, table_name, grantor, select_priv, insert_priv, delete_priv, update_priv, references_priv, alter_priv, index_priv, created)

util_TABLESPACES : description des tablespaces (tablespace_name, initial_extent, next_extent, min_extents, max_extents, pct_increase, status)

util_TRIGGERS : liste des triggers possédés

(owner, trigger_name, trigger_type, triggering_event, table_owner table_name, referencing_names, when_clause, status, description trigger_body)

util_TS_QUOTAS : quotas sur les tablespaces

(tablespace_name, bytes, max_bytes, blocks, max_blocks) util_VIEWS : liste des vues (owner, view_name, text-length, text)



ALL_USERS : caractéristiques de tous les utilisateurs (username, user_id, created) USER_USERS : mes caractéristiques

(username, user_id, connect_priv, resource_priv, dba_priv, default_tablespace, temporary_tablespace, created, expires)

DICTIONARY: liste de toutes les tables et vues

(table_name, comments)

DICT_COLUMNS: description des colonnes des objets du dictionnaire (table_name, column_name, comments) EXCEPTIONS : informations sur la violation de contraintes d'intégrité

(row_id owner table_name constraint)

SESSION_PRIVS : privilèges accordés à l'utilisateur (privilege) SESSION_ROLES: rôles validés pour l'utilisateur (role)



Devinette Nous supposons qu'il existe dans la table emp une colonne ident_oracle , donnant pour chaque employé son compte Oracle pour se connecter à la base. Que se passe t-il lorsqu'un utilisateur quelconque consulte l'objet devinette défini ci-après ? Commandes écrites par un utilisateur dba : create view my_department as select * from emp where deptno = ( select deptno from emp where job='Manager' and upper(ident_oracle)= upper(user) ) ; grant select on my_department to public ; create public synonym devinette for my_department ;



Annexe (variante des exemples grant /revoke , pour les amateurs de gauloises) Hypothèse: abraracourcix, propriétaire de emp et dept, est aux commandes

� grant select on dept to assurancetourix, idefix;

� grant select,insert, update on emp to public ;

� grant all on dept to obelix; grant all on emp to obelix;

� revoke select on dept from assurancetourix;

� revoke insert,update on emp from public ;

voir exemple � : les utilisateurs n'ont plus, sur emp, que le droit select obelix conserve les droits qu'il a obtenu personnellement en � revoke delete on dept from obelix;

Combinaison de � et : obelix a tous les droits sur dept , sauf delete .

revoke all on dept from obelix ; revoke all on emp from obelix;

Plus aucun droit pour obelix.

grant select,update on dept to asterix with grant option ;

asterix bénéficie à présent des privilèges select, update sur emp et de la possibilité de transmettre ceux-ci à d'autres utilisateurs. asterix ne se fait pas prier et propage… grant select,update on abraracourcix.dept to diagno stix ; grant select on abraracourcix.dept to doubleclix wi th grant option ;

(l'utilisation d'un synonyme pour abraracourcix.dept faciliterait la lourde tâche d'asterix)

� grant select,update(sal,comm ) on emp to bonemine ;



create role chefs identified by gaule;

grant all on emp to chefs; grant insert on dept to chefs; grant select,update on dept to chefs with grant opt ion ;

Donner des droits à un rôle

grant chefs to obelix; revoke chefs from obelix; grant chefs to asterix;

Donner un rôle à un utilisateur; l'utilisateur hérite automatiquement des droits attachés au rôle chefs .



Annexe (privilèges système) ALTER ANY CLUSTER ALTER ANY INDEX ALTER ANY PROCEDURE ALTER ANY ROLE ALTER ANY SEQUENCE ALTER ANY SNAPSHOT ALTER ANY TABLE ALTER ANY TRIGGER ALTER DATABASE ALTER PROFILE ALTER RESOURCE COST ALTER ROLLBACK SEGMENT ALTER SESSION ALTER SYSTEM ALTER TABLESPACE ALTER USER ANALYZE ANY analyser toute table, tout cluster, tout index AUDIT ANY auditer tout objet avec les cdes AUDIT AUDIT SYSTEM auditer tout le système BACKUP ANY TABLE exporter toute table de tout utilisateur BECOME USER devenir un autre utilisateur (pour importer une base) COMMENT ANY TABLE commenter toute table, vue ou colonne CREATE ANY CLUSTER CREATE ANY INDEX CREATE ANY PROCEDURE CREATE ANY SEQUENCE CREATE ANY SNAPSHOT CREATE ANY SYNONYM CREATE ANY TABLE CREATE ANY TRIGGER CREATE ANY VIEW CREATE CLUSTER CREATE DATABASE LINK CREATE PROCEDURE CREATE PROFILE CREATE PUBLIC DATABASE LINK CREATE PUBLIC SYNONYM CREATE ROLE CREATE ROLLBACK SEGMENT CREATE SEQUENCE CREATE SESSION autoriser la connexion à la base CREATE SNAPSHOT CREATE SYNONYM CREATE TABLE CREATE TABLESPACE CREATE TRIGGER CREATE USER CREATE VIEW DELETE ANY TABLE supprimer des lignes de toute table ou vue DROP ANY CLUSTER DROP ANY INDEX DROP ANY PROCEDURE DROP ANY ROLE DROP ANY SEQUENCE DROP ANY SNAPSHOT DROP ANY SYNONYM DROP ANY TABLE DROP ANY TRIGGER DROP ANY VIEW DROP PROFILE DROP PUBLIC DATABASE LINK DROP PUBLIC SYNONYM DROP ROLLBACK SEGMENT DROP TABLESPACE DROP USER EXECUTE ANY PROCEDURE FORCE ANY TRANSACTION forcer commit/rollback sur toute transaction FORCE TRANSACTION GRANT ANY PRIVILEGE GRANT ANY ROLE INSERT ANY TABLE LOCK ANY TABLE MANAGE TABLESPACE mettre online/offline, ou begin/end backup de tablespace



READUP autoriser des requêtes sur des données de classe supérieure à la classe de la session RESTRICTED SESSION autoriser connexion à une base démarrée en RESTRICT SELECT ANY SEQUENCE SELECT ANY TABLE UNLIMITED TABLESPACE utiliser sans restriction tout tablespace UPDATE ANY TABLE WRITEDOWN opérer sur des objets de classe inférieure WRITEUP opérer sur des objets de classe supérieure



Annexe (algèbre relationnelle)

select e.ename, e.sal r1 = restrict (emp, sal>3000) from emp e resultat = proj (r1, { ename, sal }) where e.sal > 3000 ; select distinct ename,job resultat = proj (emp, {ename, ,job }) from emp ; select dname r1 = restrict (dept, loc='Chicago' ou loc='Dallas') from dept resultat = proj (r1, { dname }) where loc in ('CHICAGO','DALLAS'); select e.* r1 = restrict (dept, loc='Chicago') from emp e r2 = proj (r1, {deptno} ) where e.deptno in resultat = join (emp, r2, emp.deptno=r2.deptno) (select d.deptno from dept d where d.loc = 'CHICAGO'); select dname from dept r1 = restrict (emp, empno=66) where deptno in r2 = proj (r1, {job} ) ( select deptno from emp r3 = join (emp, r2, emp.job=r2.job) where job = r4 = proj (r3, {deptno} ) ( select job from emp r5 = join (r4, dept, r4.deptno=dept.deptno) where empno=66 )) ; resultat = proj (r5, {dname} ) select e.* , dname r1 = restrict (dept, loc='Chicago') from emp e, dept d r2 = proj (r1 , {deptno, dname} ) where e.deptno=d.deptno resultat= join (emp, r2, emp.deptno=r2.deptno) and loc = 'CHICAGO'; select deptno from dept r1= proj (emp, {deptno} ) where deptno not in r2 = proj (dept, {deptno} ) (select deptno from emp) resultat = r2 \ r1

Documents

LE LANGAGE DE DEFINITION ET CONTRÔLE DES DONNEES Gestion ...etienne.baudrier.free.fr/L3_Inf/TP1/ldd.pdf · Le langage de définition J.Schnell /2005 (I.U.T. Robert Schuman) LE LANGAGE