Compléments BD

FORMATION D’INGÉNIEURS EN INFORMATIQUE FIIFO
UNIVERSITÉ PARIS SUD
IUT d’ORSAY et FACULTÉ des SCIENCES d’ORSAY
G.Ausseresse
Spécialisation Bases de données
FIIFO 769802668 édité le 16/04/2017 22:45:00
COMPLÉMENTS
de BASES de DONNÉES
ORACLE
Page
I. DICTIONNAIRE DE DONNEES ............................................................................................... 2
1. Cas général ............................................................................................................................... 2
2. Oracle ....................................................................................................................................... 3
II. PRIVILEGES ET ROLES ........................................................................................................... 5
1. Privilèges objets ....................................................................................................................... 5
2. Privilèges système .................................................................................................................... 5
3. Rôles......................................................................................................................................... 6
III. VUES ........................................................................................................................................... 9
1. Définition ................................................................................................................................. 9
2. Syntaxe ..................................................................................................................................... 9
3. Intérêt ..................................................................................................................................... 10
4. Mise à jour SQL92 ................................................................................................................. 11
5. Mise à jour Oracle .................................................................................................................. 13
IV. DIVERS ..................................................................................................................................... 14
1. Synonymes ............................................................................................................................. 14
2. Pseudo-colonnes..................................................................................................................... 15
3. Table DUAL .......................................................................................................................... 15
4. Séquences ............................................................................................................................... 15
ANNEXE ........................................................................................................................................... 17
Base exemple ETUDES ................................................................................................................. 17
V. VOCABULAIRE ORACLE ...................................................................................................... 18
VI. REFERENCES........................................................................................................................... 19

769802668 Page 2 sur 19
1. Cas général
Définition d’une base de données :
La norme SQL92 ne définit pas formellement la notion de base de données. La norme définit par
contre :
- des concepts : environnement-SQL, catalogue, schéma, tables, vues, colonnes, domaines.
- une structure hiérarchique de ces concepts : un environnement-SQL contient zéro, un ou
plusieurs catalogues ; un catalogue contient un ou plusieurs schémas et un schéma est contenu
dans un seul catalogue ; un schéma contient zéro, une ou plusieurs tables et chaque table
appartient à un seul schéma, etc.
Un schéma est un ensemble logique de domaines, tables, contraintes, vues et privilèges. C’est la
notion théorique la plus proche de celle de base de données usuelle (par exemple, Ingres). Chaque
constructeur de SGBD peut en avoir une implémentation physique qui lui est propre.
Chaque catalogue contient un schéma particulier, dit schéma d’information, regroupant des tables
qui définissent tous les objets se trouvant dans les schémas du catalogue. Certains parlent de
dictionnaire ou de méta-base de la base de données. La norme précise un certains nombre de
tables : table des schémas (c.-à-d. décrivant les schémas), table des tables, tables des colonnes,
tables des droits, etc. Mais les utilisateurs ne peuvent accéder à toutes les données de la méta-base
(penser aux mots de passe). Ce qui est alors souvent appelé tables systèmes est en fait un ensemble
de vues sur les tables de la méta-base. Les utilisateurs n’ont accès qu’à ces vues qu’en lecture (pour
des raisons de sécurité évidentes, ils ne peuvent les modifier). Par exemple, le SGBD Oracle définit
plus d’une centaine de vues sur les tables de la méta-base.
Noms des objets d’une base
Avant SQL92, la notion de schéma était couplée avec celle de nom-utilisateur, ce qui garantissait
l’unicité des noms de schémas. En SQL92, un utilisateur pouvant créer plusieurs schémas, ceux-ci,
en cas d’ambiguïté, sont désignés en fonction du catalogue qui les contient. Le nom complet d’une
table est alors :
nom_catalogue.nom_schéma.nom_table
(penser à la définition hiérarchique des fichiers UNIX)
Le schéma entité-association donné en annexe modélise les objets des bases de données SQL92.
Ces objets sont souvent identifiés relativement à d’autres.
A propos de la norme SQL
La norme ne définit pas tout. Parmi les éléments non définis, elle distingue :
- ceux définis par l’implémentation (149 éléments) : l’implémentation doit en définir la syntaxe,
les valeurs, le comportement,…, suivant les cas. Par exemple, la précision du type de donnée
entier doit être définie par l’implémentation.
I. DICTIONNAIRE DE DONNEES

769802668 Page 3 sur 19
- ceux dépendants de l’implémentation (75 éléments) : l’implémentation n’a pas besoin de les
préciser. Par exemple, la représentation physique d’un type de donnée, lequel peut varier au
cours des versions successives du SGBD.
2. Oracle
Dictionnaire de données et tables système Oracle :
Le dictionnaire de données d’une base de données se compose de tables de base et de vues (tables
système) sur ces tables. Ces diverses tables sont la propriété de l’utilisateur Oracle SYS et se
trouvent dans l’espace de tables (« tablespace ») SYSTEM de cette BD. Il est important pour
l’intégrité de la BD qu’aucun utilisateur ne puisse modifier ces tables
1
.
Dans les tables de base, une partie de l’information est cryptée. Seul Oracle peut lire et écrire dans
ces tables. Par contre, les vues sont lisibles par les utilisateurs (s’ils possèdent les droits adéquats)
en utilisant le langage SQL (dès que la base est ouverte).
Que contiennent ces tables ?
 La définition de tous les objets de la BD (tables, vues, index, clusters, synonymes, triggers,
procédures,…).
 Combien d’espace est alloué et combien d’espace est utilisé par ces objets.
 Des informations sur les contraintes d’intégrité.
 Les valeurs par défaut des colonnes.
 Des informations sur les utilisateurs, leurs droits et leurs rôles.
 Des informations d’audit : qui a accédé/mis à jour des objets.
 …
A quoi servent ces tables ?
 Oracle y accèdent pour trouver des informations sur les utilisateurs, les objets des schémas et les
structures de stockages.
 Oracle les modifie chaque fois qu’un ordre du DDL (langage de définition de données) est
exécuté.
 Chaque utilisateur de la BD peut y lire des informations sur la BD.
Des synonymes existent pour plusieurs vues couramment utilisées. Il est recommandé aux
programmeurs de les utiliser car ils sont moins susceptibles de changement d’une version à l’autre
du SGBD.
Exemples : TABS est le synonyme de USER_TABLES, COLS de USER_TAB_COLUMNS,
CAT de USER_CATALOG, DICT de DICTIONNARY…
Les tables système, qui n’ont pas une appellation définie dans la norme SQL2, portent dans le cas
d’Oracle un nom préfixé suivant leur contenu. Dans plusieurs cas, il existe 3 vues contenant des
informations similaires et qui se distinguent par leur préfixe. Leurs colonnes sont alors les mêmes
sauf les cas signalés ci-après en remarque.
1
A l’exception d’une table d’audit, SYS.AUD§, qui peut devenir trop volumineuse et dont l’administrateur peut enlever
des lignes sans préjudice pour le fonctionnement de la base.

769802668 Page 4 sur 19
PREFIXE
REMARQUE
USER_
Vue utilisateur
Ce qui se trouve dans le
schéma utilisateur
Pas de colonne OWNER, car sa
valeur est le nom de l’utilisateur
ALL_
Vue utilisateur étendue
Tout ce à quoi peut
accéder l’utilisateur
Sur-ensemble de la vue USER_
associée
DBA_
Vue pour administrateur BD
Nécessite le droit SELECT
ANY TABLE pour y accéder
Tout ce qui concerne
tous les utilisateurs
Pour des raisons de sécurité,
- certaines des colonnes de ces
vues ne figurent pas dans les
autres vues
- elles ne possèdent pas de
synonymes
EXEMPLES
Un utilisateur veut connaître les objets de son schéma :
SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS
Remarque : Pour bien comprendre la particularité d’Oracle, voici l’équivalent avec Ingres de cette
requête. Si l’utilisateur Ingres veut savoir quelles sont ses tables, vues et index :
SELECT table_name, table_type
FROM iitables
WHERE table_owner = USER
Un utilisateur veut connaître tous les objets auxquels il a accès :
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM ALL_OBJECTS
Un administrateur veut connaître tous les objets de la BD :
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM SYS.DBA_OBJECTS
Contraintes portant sur une table :
Les contraintes associées à une table sont décrites dans la table USER_CONSTRAINTS qui
contient les colonnes suivantes
CONSTRAINT_NAME : nom de la contrainte
CONSTRAINT_TYPE : type de contrainte C pour CHECK, P pour primary key, R pour clé étrangère (intégrité
référentielle)
SEARCH_CONDITION : énoncé de la contrainte
Oracle maintient aussi des tables virtuelles qui reflètent l’activité de la BD et sont appelées
« dynamic performance tables ». Ces tables sont préfixés par V_$. Seuls les DBA peuvent les
utiliser, créer des vues dessus et autoriser des utilisateurs à interroger ces vues.
Des vues de synonymes publics préfixés par V$ existent :
V$DATAFILE : fichiers de la BD
V$FIXED_TABLES : « dynamic performance » tables et vues

769802668 Page 5 sur 19
Dans Oracle, les possibilités en matière de sécurité des données sont plus développées que dans la
norme SQL2 et les privilèges sont accordés à trois niveaux :
1. Privilèges objets
2. Privilèges système
3. Rôles
1. Privilèges objets
Il permettent de réaliser des opérations particulières sur des objets (tables, vues, séquences,
procédures stockées, synonymes de table, …)
ALTER
DELETE
EXECUTE
INDEX
INSERT
REFERENCES
SELECT
UPDATE
TABLE
*
*
*
*
*
*
*
VUE
*
*
*
*
SEQUENCE
*
*
PROCEDURE
*
Ils ne peuvent être accordés que par le propriétaire de l’objet ou par qui a reçu le droit d’accorder.
GRANT liste_privilèges | ALL ALL=en une seule commande tous les privilèges possibles sur l’objet
ON objet
TO liste_utilisateurs | rôle | PUBLIC PUBLIC=tous les utilisateurs
[WITH GRANT OPTION] ; permet de transmettre le privilège
Suppression du privilège:
REVOKE liste_privilèges | ALL
ON objet FROM liste_utilisateurs | rôle | PUBLIC
[CASCADE CONSTRAINT][FORCE] ;
La suppression d’un privilège a des effets, dits en CASCADE, si l’utilisateur avait utilisé ce
privilège pour l’accorder à d’autres ou créer des objets ou des contraintes d’intégrité référentielles.
De plus, deux options sont possibles :
CASCADE CONSTRAINT : option pour supprimer toutes les contraintes de références
définies grâce au privilège REFERENCES par le révoqué.
FORCE : option pour révoquer un privilège EXECUTE sur un objet
défini par le révoqué.
2. Privilèges système
Ils permettent aux utilisateurs d’une base de réaliser diverses opérations ou catégories d’opérations,
sans qu’elles portent sur un objet précis, mais sur un type de commande. Par exemple : créer une
table, se connecter à la base, etc. Il en existe presque une centaine dans Oracle 8i.
II. PRIVILEGES ET ROLES