Gestion des

utilisateurs

1
Notion de schéma
Un schéma est un ensemble nommé d'objets, tels que des tables,
des vues, des procédures et des packages, associés à un utilisateur.
Lorsque vous créez un utilisateur de base de données, un schéma
correspondant portant le nom de l'utilisateur est créé. Un utilisateur
ne peut être associé qu'à un schéma du même nom ; par
conséquent, nom utilisateur et schéma sont souvent utilisés
indifféremment.

2
 Lister les utilisateurs sur une BD

1 Comptes d’administration

2 Comptes créés pour des

fonctionnalités (comptes non
administratifs)

3 Exemple de compte utilisateur avec

des tables remplies

Oracle crée un ensemble d’utilisateurs par défaut lors de l’installation d’une nouvelle
base de données.
1 Les comptes d’administration : sys et system
2 Les utilisateurs associés à des fonctionnalités spéciales. (e.g. Oracl_OCM : utilisateur
propriétaire des packages utilisés par l’outil Oracle Configuration Manager)
3 Des utilisateurs simples tels que iir4 et HR

On trouve : SYSTEM, SYS, ANONYMOUS et d’autres utilisateurs créés lors de l’installation

(variables selon les versions installées).
On peut regarder les rôle et droits de chaque utilisateur.

3
 Remarque: SYS vs SYSTEM
Après l’installation il est préférable de modifier les mots de passe des
utilisateurs Sys et System.

Dans une base fraîchement créée, SYS est le seul compte qui possède le privilège
SYSDBA, ce qui permet en particulier d'arrêter et de redémarrer une base, et d'en créer
une nouvelle.

SYSTEM est muni du rôle DBA, et peut donc effectuer la plupart des tâches
administratives liées à une base Oracle, à l'exception de celles citées ci-dessus (arrêter et
redémarrer une base, et d'en créer une nouvelle).

4
 Création d’un utilisateur
CREATE USER nomUtilisateur
IDENTIFIED BY motDePasse
[ DEFAULT TABLESPACE nomTablespace ]
[ TEMPORARY TABLESPACE nomTablespace ]
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace ]
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace]
[...]
[ PASSWORD EXPIRE ]
[ ACCOUNT { LOCK | UNLOCK }]

Où :
BY password indique que l'utilisateur est authentifié par le dictionnaire de
données et qu'il doit fournir un mot de passe pour se connecter.
DEFAULT TABLESPACE ou TEMPORARY TABLESPACE désigne le tablespace par
défaut ou le tablespace temporaire de l'utilisateur.
QUOTA définit l'espace maximum alloué aux objets détenus par l'utilisateur dans
le tablespace (le quota peut être défini par un entier représentant des octets ou
des kilo-octets et des mégaoctets. Le mot-clé UNLIMITED permet d'indiquer que
les objets détenus par l'utilisateur peuvent utiliser l'ensemble de l'espace
disponible du tablespace. Par défaut, aucun quota de tablespace n'est affecté aux
utilisateurs).
PASSWORD EXPIRE force l'utilisateur à réinitialiser le mot de passe lorsqu'il se
connecte à la base de données à l'aide de SQL*Plus
ACCOUNT LOCK/UNLOCK permet de verrouiller ou de déverrouiller explicitement
le compte de l'utilisateur (la valeur par défaut est UNLOCK).

7
Exercice
Créez l’utilisateur « iir4 » avec le mot de passe « emsi », en utilisant une
commande minimale
Sans utilisation de la vue système dba_users, comment peut on savoir quel est
le tablespace permanent par défaut de l’utilisateur «IIR4 »?
Affichez le tablespace permanent et temporaire par défaut de l’utilisateur
« iir4 », ainsi que l’état de son compte ( bloqué ou non) en utilisant une vue
système.
Accordez à l’utilisateur « IIR4 » les droits « Create session » et « create table »

Lancez une session pour « IIR4 » et créez la table suivante: Client(idCli, nomCli).
Qu’est ce que vous remarquez?

9
Exemples
Créez l’utilisateur « iir4 » avec le mot de passe « emsi », en utilisant une
commande minimale

CREATE USER iir4 IDENTIFIED BY emsi

Sans utilisation de la vue système dba_users, comment peut on savoir quel est
le tablespace permanent par défaut de l’utilisateur «IIR4 »?

Afficher le tablespace de la base de données.

10
Exemples
2- Sans utilisation de la vue système dba_users, comment peut on savoir quel est
le tablespace permanent par défaut de l’utilisateur «IIR4 »?

11
Exemples
3- Afficher le tablespace permanent et temporaire par défaut de l’utilisateur
« iir4 », ainsi que l’état de son compte ( bloqué ou non) en utilisant une vue
système.

12
Exemples
4- Accordez à l’utilisateur « IIR4 » les droits « Create session » et « create table »

13
Exemples
5-

14
Quelle est la cause de
cette erreur?

17
Cause de cette erreur
L’utilisateur IIR4 a le privilège « Create table » qui lui
permet de lancer l’action de création de table,
L’utilisateur IIR4 n’a pas de quota sur le tablespace
« SYSTEM » qui est son tablespace permanent par
défaut.
Le quota définit l'espace maximum alloué aux objets
détenus par l'utilisateur dans le tablespace

18
Afficher les quotas sur les tablespaces

19
Afficher les quotas sur les tablespaces
Column Datatype NULL Description
TABLESPACE_NAME VARCHAR2(30) NOT NULL Tablespace name
USERNAME VARCHAR2(30) NOT NULL User with resource rights
on the tablespace

BYTES NUMBER Number of bytes charged

to the user
MAX_BYTES NUMBER User's quota in bytes, or -
1 if no limit
BLOCKS NUMBER NOT NULL Number of Oracle blocks
charged to the user

MAX_BLOCKS NUMBER User's quota in Oracle

blocks, or -1 if no limit
DROPPED VARCHAR2(3) Whether the tablespace
has been dropped

20
Afficher les quotas sur les tablespaces
Affichez les quotas de l’utilisateur IIR4.

Par défaut un utilisateur a un quota NULL sur tous les tablespaces. Par
conséquent il n’apparait pas sur la table dba_ts_quotas

21
Comment
ajouter/supprimer un
quota ?

22
Modifier un utilisateur
Alter USER nomUtilisateur
IDENTIFIED BY motDePasse
[ DEFAULT TABLESPACE nomTablespace ]
[ TEMPORARY TABLESPACE nomTablespace ]
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace ]
[ QUOTA {integer [K | M ] | UNLIMITED } ON tablespace]
[...]
[ PASSWORD EXPIRE ]
[ ACCOUNT { LOCK | UNLOCK }]

23
Modifier un utilisateur (ajouter un quota)
Exemple 1:

24
Modifier un utilisateur (Ajouter un
quota)
Exemple 2: ajouter un quota sur un tbs différent du tbs par défaut

25
Modifier un utilisateur (Supprimer un
quota)
Supprimer un quota

26
 Remarque:
Quand on accorde à un utilisateur le rôle « resource », il détiendra
automatiquement le privilège « Unlimited tablespace » qui lui permet d’utiliser
tout l’espace disponible sur les tablespaces.

Aucun résultat dans user_ts_quotas ou dba_ts_quotas car il a un privilège unlimited tablespace et

non pas un quotas précis.

27
 Remarque:
Il peut être judicieux d'utiliser des quotas car des quotas
illimités peuvent créer des problèmes d'espace. De plus, les
utilisateurs n’ont besoin d’aucun quota sur les tablespaces
temporaires ou sur les tablespaces de rollback.

28
Comment modifier le
mot de passe d’un
utilisateur?

29
Modifier le mot de passe
Solution 1: A partir d’une session active de l’utilisateur concerné

30
Modifier le mot de passe
Solution 2: A partir d’une session Admin

31
Modifier le mot de passe
Solution 3: A partir d’une session Admin en spécifiant un MP temporaire

32
Modifier le mot de passe
Solution 3: A partir d’une session Admin en spécifiant un MP temporaire

33
Comment bloquer le
compte d’un utilisateur?

34
Bloquer le compte d’un utilisateur

35
 Bloquer le compte d’un utilisateur
Problème: le blocage du compte ne prendra effet qu’à partir de la prochaine connexion !!!

Solution ?

comment déconnecter des usagers ayant une ou plusieurs sessions Oracle

d’ouvertes. Ceci permet entre autre de fermer des connexions fantômes (qui ne
sont plus utilisées), ou de déconnecter des sessions empêchant la fermeture
normale (SHUTDOWN NORMAL) de la base de données.

36
 Déconnecter les usagers
1- afficher les sessions actuellement ouvertes :

comment déconnecter des usagers ayant une ou plusieurs sessions Oracle

d’ouvertes. Ceci permet entre autre de fermer des connexions fantômes (qui ne
sont plus utilisées), ou de déconnecter des sessions empêchant la fermeture
normale (SHUTDOWN NORMAL) de la base de données.

37
 Déconnecter les usagers
1- afficher les sessions actuellement ouvertes :

comment déconnecter des usagers ayant une ou plusieurs sessions Oracle

d’ouvertes. Ceci permet entre autre de fermer des connexions fantômes (qui ne
sont plus utilisées), ou de déconnecter des sessions empêchant la fermeture
normale (SHUTDOWN NORMAL) de la base de données.

38
 Déconnecter les usagers
2- Déconnecter l’utilisateur IIR4:

Il annulera les transactions en cours

39
Déconnecter les usagers

40
Comment supprimer un
utilisateur?

41
Supprimer un utilisateur

Drop user NomUser [Cascade];

42
Supprimer un utilisateur

43
Conseils pour la création des utilisateurs
Il peut être intéressant d'utiliser l'option EXPIRE lors de la création de
l'utilisateur ce qui forcera l'utilisateur à changer son mot de passe lors
de sa première connexion.
Il est nécessaire de spécifier les tablespaces temporaire et par défaut
pour tous les utilisateurs afin d'éviter que le tablespace SYSTEM soit
utilisé par défaut.
Il peut être judicieux d'utiliser des quotas car des quotas illimités
peuvent créer des problèmes d'espace. De plus, les utilisateurs ne
nécessitent aucuns quotas sur les tablespaces temporaires ou sur les
tablespaces de rollback.

44
 Exercice
Ecrire un script SQL permettant de déconnecter tous les utilisateurs connectés
actuellement

2
 Gestion des Profils

2
 Introduction
 Comment pourrais-je limiter le nombre de sessions qui peuvent
être lancées par un utilisateur ?
 Pourrais-je fixer un temps d’inactivité aux sessions des utilisateurs ?
 Si je souhaite limiter le nombre de tentatives de connexion
erronées à 3 ? Que dois-je utiliser ?
 Et la durée de vie des mots de passe ?
 Qu’en est il de la gestion de la complexité
des mots de passe?

Les profils Oracle

Un profil est un ensemble nommé de limitations de

ressources et de gestion de mot de
passe qui peut être attribué à un utilisateur.

4
 Fonctions des profils
 Empêcher les utilisateurs d'exécuter des opérations qui utilisent beaucoup
de ressources.
 Garantir que les utilisateurs sont déconnectés de la base de données
lorsque leur session reste inactive pendant un certain temps.
 Affecter facilement aux utilisateurs des limites relatives aux ressources.
 Gérer l'utilisation des ressources dans les systèmes de base de données
multiutilisateurs complexes et volumineux.
 Contrôler l'utilisation des mots de passe.

Limitations Ressource / Mot de Passe

Ressources Mots de passe (Objet de notre séance )
 temps CPU par appel et/ou par session ;  verrouillage de compte (et durée de verro
uillage) au-
 nombre de lectures logiques par appel delà d’un certain nombre d’échecs de tenta
et/ou par session ; tive de connexion ;
 nombre de sessions ouvertes simultanéme  durée de vie des mots de passe (avec éventue
nt par un utilisateur ; llement une période de grâce) ;
 Non réutilisation d’un mot de passe avant un
 temps d’inactivité par session ; certain temps ou avant un certain nombre de
 durée totale de la session ; changements ;
 complexité du mot de passe.
…

6
 Afficher les informations des profils

Afficher le nom du profil d’un utilisateur.

Afficher les informations des profils

Permet d’afficher les profils avec leurs limitations Ressources et Mot de passe.

8
 Afficher les informations des profils

Permet d’afficher les limitations Mot de passe de l’utilisateur connecté.

Afficher les informations des profils

Travail à faire:

1- Créez l’utilisateur u1 dont le mot de passe est u1.

2- Quel est le profil de cet utilisateur ?

10
 Afficher les informations des profils

Profil par défaut créé avec la

base de données

11

Profil DEFAULT
 Les profils ne peuvent être affectés qu'à des utilisateurs. Vous ne pouvez les
affecter ni à des rôles, ni à d'autres profils.

 Si vous n'affectez pas de profil à un utilisateur que vous créez, ce dernier

reçoit automatiquement le profil DEFAULT.

20
  Le serveur Oracle crée automatiquement le profil DEFAULT en
même temps que la base de données.
 Si aucun profil spécifique n'est attribué explicitement à un
utilisateur, toutes les limites du profil DEFAULT s'appliquent.

13

Afficher les informations des profils

Travail à faire:

2- Affichez les limitations mot de passe du profil « DEFAULT ».

14
 Afficher les informations des profils

4- Affichez les limitations mot de passe depuis la session de l’utilisateur

« U1 »

17

Afficher les informations des profils

18
 Afficher les informations des profils

19

Gestion des limitations des mots de

passe
Limite Explication
FAILED_LOGIN_ATTEMPTS Nombre d’échecs de tentatives de connexion autorisés
avant verrouillage du compte, 10 dans le profil DEFAULT.

PASSWORD_LOCK_TIME Durée du verrouillage (jours), 1 dans le profil DEFAULT.

PASSWORD_LIFE_TIME Durée de vie du mot de passe (jours), 180 dans le profil

DEFAULT.
PASSWORD_GRACE_TIME Période de grâce après expiration du mot de passe (jours), 7
dans le profil DEFAULT.
PASSWORD_REUSE_TIME Nombre de jours pendant lequel un mot de passe ne peut
pas être réutilisé.
PASSWORD_REUSE_MAX Nombre de changements de mot de passe avant qu’un mot
de passe puisse être réutilisé.
PASSWORD_VERIFY_FUNCTION Fonction de vérification de la complexité du mot de passe.

20
 Création d’un profil
CREATE PROFILE NomDuProfil LIMIT
FAILED_LOGIN_ATTEMPTS valeur
PASSWORD_LIFE_TIME valeur
PASSWORD_REUSE_TIME valeur
PASSWORD_LOCK_TIME valeur
PASSWORD_GRACE_TIME valeur
…….

21

Création d’un Profil

22
 Modification d’un profil
ALTER PROFILE NomDuProfil LIMIT
FAILED_LOGIN_ATTEMPTS valeur
PASSWORD_LIFE_TIME valeur
PASSWORD_REUSE_TIME valeur
PASSWORD_LOCK_TIME valeur
PASSWORD_GRACE_TIME valeur
…….

23

Exemple 1 (FAILED_LOGIN_ATTEMPTS
, PASSWORD_LOCK_TIME)

Travail à faire:

1- Créez un profile « IIR4Profile » avec les limitations de mot de passe

suivantes:

- Nombre de tentatives : 3, le compte sera bloqué à la 4ème tentative

erronée
- Durée de verrouillage: le compte restera bloqué 30 secondes

24
 Exemple 1 (FAILED_LOGIN_ATTEMPTS)
1/(24*60*60) = 1 seconde= 1/86400
Solution: Création du profile 1 min= 1/(24*60)= 1/1440

Create profile IIR4Profile LIMIT Ou:

FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LOCK_TIME 30/86400 Create profile IIR4Profile LIMIT
PASSWORD_LIFE_TIME DEFAULT FAILED_LOGIN_ATTEMPTS 3
PASSWORD_GRACE_TIME DEFAULT PASSWORD_LOCK_TIME 30/86400;
PASSWORD_REUSE_TIME DEFAULT
PASSWORD_REUSE_MAX DEFAULT
PASSWORD_VERIFY_FUNCTION DEFAULT;

25

Exemple 1 (FAILED_LOGIN_ATTEMPTS)
Solution: affectation du profil à l’utilisateur « u1 »
SYS

U1

26
 Exemple 1 (FAILED_LOGIN_ATTEMPTS)
SQL> connect u1/u
ERROR:
ORA-01017: invalid username/password; logon denied
Solution: test et vérification
SQL> connect u1/u
ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect u1/u

ERROR:
ORA-01017: invalid username/password; logon denied

SQL> connect u1/u

ERROR:
ORA-28000: the account is locked

---- Après 30 secondes

SQL> connect u1/u1
Connected.

27

Exemple 2 (PASSWORD_LIFE_TIME,
PASSWORD_GRACE_TIME)

Travail à faire:

Modifiez les limitations de mot de passe du profile IIR4Profile:

- Durée de vie du MP : 72 jours

- Période de grâce : 1 jour

28
 Exemple 2 (PASSWORD_LIFE_TIME,
PASSWORD_GRACE_TIME)

29

Exemple 3 (PASSWORD_REUSE_TIME,
PASSWORD_REUSE_MAX)

Travail à faire:

Modifiez les limitations de mot de passe du profile IIR4Profile:

- Nombre de changement après lesquels un ancien PSW peut être réutilisé : 1

- Période après laquelle un mot de passe peut être réutilisé : 30 secondes

31
 Exemple 3 (PASSWORD_REUSE_TIME,
PASSWORD_REUSE_MAX)

32

Remarques
Attention:

33
 Remarques
Attention: Si le paramètre PASSWORD_REUSE_TIME a été initialisé avec
une valeur numérique, alors que le paramètre PASSWORD_REUSE_MAX est à
UNLIMITED, dans ce cas L’utilisateur ne pourra jamais réutiliser un mot de passe.

34

Remarques
Attention: Si le paramètre PASSWORD_REUSE_TIME a été initialisé avec une valeur numérique,
alors que le paramètre PASSWORD_REUSE_MAX est à UNLIMITED, dans ce cas
L’utilisateur ne pourra jamais réutiliser un mot de passe.

35
 Exemple (PASSWORD_REUSE_TIME,
PASSWORD_REUSE_MAX)

36

Suppression d’un profil

Drop PROFILE NomDuProfil;

Attention:

38
 Références
https://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_6010.htm#SQLRF01310

39

Gestion des Profils

« Fonction de vérification
des mots de passe »

40
 Gestion des limitations des mots de
passe
Limite Explication
FAILED_LOGIN_ATTEMPTS Nombre d’échecs de tentative de connexion autorisés avant
verrouillage du compte, 10 dans le profil DEFAULT.

PASSWORD_LOCK_TIME Durée du verrouillage (jours), 1 dans le profil DEFAULT.

PASSWORD_LIFE_TIME Durée de vie du mot de passe (jours), 180 dans le profil

DEFAULT.
PASSWORD_GRACE_TIME Période de grâce après expiration du mot de passe (jours), 7
dans le profil DEFAULT.
PASSWORD_REUSE_TIME Nombre de jours pendant lequel un mot de passe ne peut
pas être réutilisé.
PASSWORD_REUSE_MAX Nombre de changements de mot de passe avant qu’un mot
de passe puisse être réutilisé.
PASSWORD_VERIFY_FUNCTION Fonction de vérification de la complexité du mot de passe.

42
 Fonction de vérification de mot de passe
 Effectue une vérification de complexité du mot de passe afin de
garantir qu'il respecte certaines règles.

 La vérification doit permettre de garantir que le mot de passe est

suffisamment complexe pour offrir une protection contre les
intrus qui tenteraient de pénétrer dans le système en devinant le
mot de passe.

43

Afficher les informations des profils

44
 Comment peut on créer et appliquer une
fonction de vérification des mot de passe ?
1. Ecrire votre fonction Pl/SQL pour la vérification;
2. Lancer l’exécution du script de la fonction;
3. Le paramètre PASSWORD_VERIFY_FUNCTION nomme une fonction
PL/SQL qui effectue une vérification de complexité avant l'affectation d'un
mot de passe, il faut alors modifier le profil en question;
Remarque: Les fonctions de vérification des mots de passe doivent appartenir
à l'utilisateur SYS et renvoyer une valeur booléenne (TRUE ou FALSE).

45

Exemple

46
 Exemple

47

Exemple

La commande passe sans problème ! Pourquoi ?

48
 Exemple

Le profile par défaut de testverifUser est Default pour lequel

Password_verify_function est NULL.

49

Exemple

La modification du password génère une erreur.

50
 Exemple
On peut personnaliser le message d’erreur :

Ensuite réexécuter le script de la fonction

51

Exemple
On peut personnaliser le message d’erreur :

52
52