Administration

de bases de données

Enseignante:
Nouha KHYARI

Filière:
2ème année LSI

2023/2024

1
Chapitre 3
Gestion des utilisateurs et de
leurs droits
I. Dictionnaire de données
II. Notion de Schéma
III. Gestion des utilisateurs
IV. Gestion des profils
V. Gestion des droits/privilèges
VI. Gestion des rôles
I. Dictionnaire de données
A- Définition
C’est un ensemble de tables et de vues qui donnent des informations sur le contenu d’une BDD:
☞ Les structures de stockages
☞ Les utilisateurs et les droits
☞ Les objets (tables, vues, index, procédures, fonctions, etc.)

B- Emplacement
Il est stocké dans le tablespace SYSTEM

C- Création
Il est créé lors de la création de la BDD, et mis à jour lors des opérations DDL (CREATE,
ALTER, DROP)

D- Contenu
☞Des tables et vues statiques
☞Des tables et vues dynamiques 3
 I. Dictionnaire de données
Vues Statiques Vues Dynamiques
Contenu basées sur des vraies tables stockées dans le basées sur des informations en
tablespace SYSTEM mémoire ou extraites du
fichier de contrôle.
Accessible uniquement quand la base est ouverte même si la base n’est pas
complètement ouverte.
Préfixe  USER_% : Les objets appartenant à V$%
l’utilisateur
 ALL_% : les objets auxquels l’utilisateur peut
accéder
 DBA_% : Tous les objets de la BDD
Exemples  USER_TABLESPACES; V$CONTROLFILE
 DBA_DATA_FILES
DICTIONARY / DICT_COLUMNS
description de toutes les tables/vues du dictionnaire de données 4
II. Notion de Schéma

 Un schéma est une collection d'objets de base de données.

 Il appartient à un utilisateur de base de données et porte le même nom que cet utilisateur.
 Les objets y appartenant sont créées par l’utilisateur. Ils peuvent être:
• Table • Vue
• Synonyme • Index
• Séquence • Programme PL/SQL

Informations
Par exemple, pour consulter la liste des tables d’un schema:

SQL> SELECT DISTINCT OWNER, OBJECT_NAME FROM ALL_OBJECTS WHERE

OBJECT_TYPE = 'TABLE' AND OWNER = '[schema]’ ;
5
 III. Gestion des utilisateurs
Utilisateur de BDD Oracle (User) :
☞ Identifié par un login avec lequel il se connecte au système
☞ Possède un ensemble de droits ou privilèges associés à son login.
☞ Dispose d'un espace de stockage par défaut (tablespace) dans lequel sont stockés
les objets qu'il créés

Oracle assimile un schéma à son utilisateur :

☞Chaque utilisateur possède un seul schéma et réciproquement
☞Le login sert à identifier à la fois un utilisateur et son schéma
☞La création d'un utilisateur entraîne celle de son schéma
☞Accès à un objet : login.nomObjet (Ex. sys.dba_tablespaces)

Informations
DBA_USERS : informations sur l’utilisateur
DBA_TS_QUOTAS : informations sur les quotas des utilisateurs
6
III. Gestion des utilisateurs
A- Création d’utilisateur

1. Choisir un nom d'utilisateur et un mécanisme d'authentification;

2. Décider des tablespaces dans lesquels l'utilisateur peut stocker certains
de ses objets;
3. Affecter un tablespace par défaut et un tablespace temporaire;
4. Décider des quotas pour chaque tablespace;
5. Accorder des privilèges et des rôles a l'utilisateur;

7
 III. Gestion des utilisateurs
A- Création d’utilisateur

SQL> CREATE USER nom IDENTIFIED [BY motDePasse| EXTERNALLY]

[ DEFAULT TABLESPACE nom_tablespace]
dans quel TBS l’utilisateur va créer par défaut ses objets
[ TEMPORARY TABLESPACE nom_tablespace ]
dans quel TBS les segments temporaires seront créés
[ QUOTA { valeur [K|M|G] | UNLIMITED } ON nom_tablespace ]
limiter l’espace qu’un utilisateur peut employer dans un TBS
[ PROFILE nom_profil ]
Indiquer le profil attribué à l’utilisateur
[ PASSWORD EXPIRE ]
Forcer une modification du mot de passe lors de la première connexion
[ ACCOUNT [ LOCK | UNLOCK ] ];
Indiquer si le compte est verrouillé ou pas.
8
III. Gestion des utilisateurs
B- Modification d’utilisateur
SQL> ALTER USER USER1
Modification du mot de passe
IDENTIFIED BY p$wd;
Modification des quotas
QUOTA 15 M ON TBS1 QUOTA 0M ON TBS2;
Modification des tablespaces par défaut
DEFAULT TABLESPACE TBS2 TEMPORARY TABLESPACE TEMPTBS;
Verrouillage
ACCOUNT LOCK;
Déverrouillage
ACCOUNT UNLOCK;

C- Suppression d’utilisateur (et de son schéma)

SQL> DROP USER USER1 [CASCADE] --CASCADE pour supprimer ses objets 9
 III. Gestion des profils

A- Définition
 Un ensemble nommé de limitations de ressources qui peut être attribué à un
utilisateur.
 Le profil « DEFAULT » est créé automatiquement lors de la création de la BDD. Il est
attribué par défaut à tous les utilisateurs;
 Le contrôle de limitation des ressources n’est pas activé par défaut. Il faut passer le
paramètre RESOURCES_LIMIT à TRUE (FALSE par défaut) :
SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE [clause SCOPE];

10
III. Gestion des profils
B- Ressources
☞ Temps CPU par appel et/ou par session;
☞ Nombre de lectures logiques par appel et/ou par session (nombre de bloc; logique/physique);
☞ Nombre de sessions ouvertes simultanément par un utilisateur;
☞ Temps d’inactivité par session;
☞ Durée totale de session;
☞ Quantité de mémoire privée dans la SGA (configuration de serveurs partagés uniquement);
☞ Mot de passe:
 Verrouillage de compte au delà d’un nombre d’échec de connexion précis
 Durée de vie des mots de passe
 Complexité de mot de passe, etc. 11
III. Gestion des profils

C- Affectation de profil a un utilisateur

Un profil peut être attribué à un utilisateur:

☞ lors de la création de l’utilisateur (CREATE USER)
☞ lors d’une modification de l’utilisateur (ALTER USER)

Exemple:
SQL> CREATE USER USER1 IDENTIFIED BY temp
PROFILE DEFAULT
PASSWORD EXPIRE;

Informations
DBA_PROFILES : Contient les noms des profils et des informations sur les
ressources limitées 12
 III. Gestion des profils
D- Modification de profil
SQL> ALTER PROFILE nom …. ;

Exemple:
SQL> ALTER PROFILE default LIMIT
SESION_PER_USER 3
IDLE_TIME UNLIMITED
FAILED_LOGIN_ATTEMPTS 5;

E- Suppression de profil
SQL> DROP PROFILE nom [CASCADE];
-- CASCADE doit être utilisé si ce profil à été affecté
à un utilisateur. Le profil default lui sera affecté à
la prochaine connexion.
-- Le profil default ne peut pas être supprimé
13
III. Gestion des privilèges et des droits

Définition
Un privilège est un droit d’exécuter un ordre SQL.

Types
On distingue deux types de privilèges:
☞ Privilège système
☞ Privilège objet

14
III. Gestion des privilèges et des droits
A. Privilège Système

 C’est le droit d’exécuter un ordre SQL en général, par exemple créer une table.
 Chaque ordre SQL a au moins un privilège système associé qui porte le même nom que
l’ordre.
Exemple: l’ordre CREATE TABLE possède un privilège système appelé CREATE
TABLE qui donne le droit de créer une table dans son propre schéma.

 Rajouter le mot ANY au privilège, par exemple CREATE ANY TABLE, rend possible à
l’utilisateur possédant ce privilège de créer des tables dans n’importe quel schéma.
Ceci n’est valable que pour les ordres concernant les objets d’un schéma :
CREATE ANY TABLESPACE

Informations
DBA_SYS_PRIVS; SESSION_PRIVS; SYSYTEM_PRIVILEGE_MAP 15
 III. Gestion des privilèges et des droits
A. Privilège Système
Exemple de privilèges système
 CREATE SESSION , ALTER USER, DROP USER, ANY PRIVILEGE, GRANT ANY ROLE
 SELECT ANY DICTIONARY : donne le doit d’interroger n’importe quel objet du dictionnaire
de données dans le schéma SYS  Intéressant pour lire les vues DBA sans pour autant être
DBA

A.1 Attribution d’un privilège système à un utilisateur

GRANT nom_privilege [,..] TO {nom_utilisateur|PUBLIC} [,..] [WITH ADMIN OPTION];
☞La clause WITH ADMIN OPTION donne au bénéficiaire le droit de transmettre le privilège
système. Ceci est aussi possible ayant reçu le privilège système GRANT ANY PRIVILEGE
☞Le privilège attribué est immédiatement actif

16
III. Gestion des privilèges et des droits
A. Privilège Système
A.2 Révocation d’un privilege système
Syntaxe : SQL>REVOKE nom_privilege [,..] FROM {nom_utilisateur|PUBLIC}[,..];
Le privilege est immédiatement révoqué et ne peut plus être exercé

Il n’y a pas de cascade dans la revocation d’un privilege système qui a été transmis grace à la
clause WITH ADMIN OPTION;
Exemple: Si Pierre a reçu un p.s. avec WITH ADMIIN OPTION, et que Pierre l’ait transmis à Paul,
révoquer le privilege de Pierre est sans effect sur le privilege de Paul.

GRANT CREATE TABLE TO User1; GRANT CREATE TABLE TO User2; REVOKE CREATE
TABLE FROM PUBLIC ?
GRANT CREATE SESSION to User1; GRANT CREATE SESSION TO PUBLIC;  REVOKE
CREATE SESSION FROM User1? User1 possède toujours le privilege via PUBLIC 17
III. Gestion des privilèges et des droits
B. Privilège Objet
C’est le droit d’accéder à un objet d’un autre utilisateur. Par exemple, mettre à jour la table
EMPLOYEES;
Par défaut, seul le propriétaire d’un objet a le droit d’y accéder.
Pour qu’un autre utilisateur puisse accéder à l’objet, le propriétaire doit lui donner un
privilège objet.
Les principaux privilèges objet sont:
Privilège Table Vue Séquence Programme
SELECT X X X
INSERT X X
UPDATE X X
DELETE X X
EXECUTE X 18
 III. Gestion des privilèges et des droits
B. Privilège Objet

B.1. Accès à l’objet

Pour accéder à un objet, il faut le qualifier par le nom de son propriétaire ( exemple:
user1.EMPLOYEES), sinon Oracle pense que vous cherchez un objet dans votre schéma.
(utilisation des synonymes)

B.2. Attribution de privilège objet

Syntaxe:
SQL> GRANT { nom_privilege[(liste_colonne)] [,..]| ALL [PRIVILEGES]} ON
[nom_schéma.]nom_objet TO {nom_utilisateur|PUBLIC} [,..]
[WITH GRANT OPTION] ;
Exemple
SQL> GRANT SELECT, UPDATE(Employee_ID, Salary) ON EMPLOYEES To User1;
19
III. Gestion des privilèges et des droits
B. Privilège Objet

B.3. Transmission de privilège

Un utilisateur peut donner un privilège objet lorsque:

☞ Il est son propriétaire
☞ Il a reçu le privilège en question avec la clause WITH GRANT OPTION
☞ Il a reçu le privilège système ANY OBJECT PRIVILEGE.

Informations
DBA_TAB_PRIVS; DBA_COL_PRIVS; TABLE_PRIVILEGE_MAP;

20
 III. Gestion des privilèges et des droits
B. Privilège Objet
B.4. Révocation
Syntaxe
SQL> REVOKE {nom_privilege [,..] | ALL PRIVILEGES]} ON
[nom_schema.]nom_objet
FROM {nom_utilisateur|PUBLIC} [,..];
Exemple
SQL> REVOKE INSERT, UPDATE ON Jobs FROM User2;

Il y a une cascade dans la révocation d’un privilège objet qui a été transmis grâce à
la clause WITH GRANT OPTION; Si Pierre a reçu un p.o. avec WITH GRANT
OPTION, et que Pierre l’ait transmis à Paul, révoquer le privilège de Pierre révoque
également celui de Paul.
21
 III. Gestion des rôles
Définition
 Un rôle est un regroupement nommé de privilèges (système et objet).
 Il peut être affecté à un utilisateur et activé automatiquement dès sa connexion.
 Si le rôle est attribué au moment où l’utilisateur est déjà connecté, il doit être
activé par la commande SET ROLE.
Caractéristiques
Un rôle peut être attribué à un autre rôle
Un utilisateur peut avoir plusieurs rôles
Un rôle n’appartient à personne
Etapes de mise en œuvre
1. Création de rôle
2. Attribution des privilèges au rôle
3. Attribution du rôle aux utilisateurs
22
III. Gestion des rôles
Création de rôle (non exaustive)
SQL> CREATE ROLE nom_role [IDENTIFIED BY p$wd |NOT
IDENTIFIED];

Activation de rôle
SQL> SET ROLE nom_role IDENTIFIED BY p$wd ;

Modification de rôle
SQL> ALTER ROLE nom_role IDENTIFIED BY p$wd ;

Attribution/révocation d’un privilège à un rôle

SQL> GRANT/REVOKE nom_privilege TO/FROM nom_role;
Exemple: SQL> GRANT SELECT ON JOBS TO ROLE1;
23
 III. Gestion des rôles

Attribution/révocation d’un rôle à un utilisateur/rôle

SQL> GRANT/REVOKE nom_role TO/FROM
{nom_utilisateur|PUBLIC|nom_role} [,..] [WITH ADMIN
OPTION];

La clause WITH ADMIN OPTION permet de transmettre le rôle, le modifier

(ALTER) et le supprimer (DROP).

Suppression d’un rôle

SQL>DROP ROLE nom_role;

24
III. Gestion des rôles

Rôles prédéfinis
DBA: Donne tous les privileges système avec l’option WITH ADMIN OPTION;
CONNECT: Autorise la connexion (contient le privilege système CREATE
SESSION)

Informations
DBA_ROLES;
DBA_ROLE_PRIVS; etc.

25