Objectifs du chapitre
- Connaître les attributs d’un compte utilisateur de base de données et les comptes prédéfinis SYS et SYSTEM.
- Connaître les modes d’authentification des utilisateurs et savoir créer un compte.
- Distinguer les privilèges système et les privilèges objet, et savoir les attribuer (GRANT) ou les retirer (REVOKE).
- Connaître les avantages des rôles et savoir en créer un.
- Connaître le rôle des profils dans la gestion des mots de passe et des ressources.
1. Compte utilisateur de base de données
Chaque compte utilisateur comporte un nom utilisateur unique, une méthode d’authentification, un tablespace par défaut, des quotas sur un ou plusieurs tablespaces, un tablespace temporaire, un profil utilisateur et un statut de compte (verrouillé ou expiré). Le nom de l’utilisateur et la méthode d’authentification sont obligatoires à la création ; les autres attributs peuvent prendre des valeurs par défaut. On consulte les informations des utilisateurs via la vue DBA_USERS. Une fois créé, le nom de l’utilisateur ne peut pas être modifié.
1.1 Comptes prédéfinis : SYS et SYSTEM
Le compte SYS reçoit le rôle d’administrateur de base de données (DBA), dispose de tous les privilèges associés à ADMIN OPTION, est requis pour les opérations de démarrage et d’arrêt ainsi que pour certaines commandes de maintenance, et est propriétaire du dictionnaire de données. Le compte SYSTEM reçoit lui aussi le rôle d’administrateur de base de données (DBA).
1.2 Authentification des utilisateurs
| Mode | Fonctionnement |
|---|---|
| Operating System | Nécessite le privilège SYSDBA ou SYSOPER ; permet de copier les mots de passe des utilisateurs du dictionnaire de données dans un fichier de mots de passe externe, lisible par l’instance même si la base n’est pas ouverte. CONNECT / AS [SYSOPER|SYSDBA]; |
| Password File | Nécessite le privilège SYSDBA ou SYSOPER. CONNECT username/password AS [SYSOPER|SYSDBA]; |
| Password | Un mot de passe est associé à chaque utilisateur créé, saisi à chaque connexion ; il peut être configuré pour expirer immédiatement, obligeant l’utilisateur à le changer à la première connexion. CREATE USER <name> IDENTIFIED BY <password>; |
| External | Oracle délègue l’authentification à un service externe (serveur Kerberos, RADIUS, ou authentification native Windows avec l’option de sécurité avancée). Sans cette option, le seul moyen externe est le système d’exploitation : il faut créer des utilisateurs avec le même nom que sous l’OS, préfixés par OS_AUTHENT_PREFIX (ops$ par défaut sous Unix). CREATE USER <name> IDENTIFIED EXTERNALLY; |
| Global | Identifie les utilisateurs via Oracle Internet Directory. CREATE USER <name> IDENTIFIED GLOBALLY; |
1.3 Créer un compte utilisateur
CREATE USER nom IDENTIFIED BY mot_de_passe
[ DEFAULT TABLESPACE nom_tablespace ]
[ TEMPORARY TABLESPACE nom_tablespace ]
[ QUOTA { valeur [K|M] | UNLIMITED } ON nom_tablespace [,...] ]
[ PROFILE nom_profil ]
[ PASSWORD EXPIRE ]
[ ACCOUNT { LOCK | UNLOCK } ];
-- exemple
CREATE USER scott IDENTIFIED BY pwdscott
DEFAULT TABLESPACE users
QUOTA UNLIMITED ON users
PASSWORD EXPIRE;
2. Privilèges
Un privilège est le droit d’exécuter un type particulier d’instruction SQL ou d’accéder à l’objet d’un autre utilisateur.
| Type | Description |
|---|---|
| Privilèges système | Autorisent un utilisateur à effectuer certaines opérations de base de données (créer des tablespaces, des sessions, etc). Peuvent être accordés par l’administrateur, ou par quelqu’un à qui cette permission a été explicitement accordée. |
| Privilèges objet | Permettent d’effectuer une action particulière sur un objet spécifique (table, vue, séquence, procédure, fonction, package). Sans ce privilège, un utilisateur n’accède qu’à ses propres objets. Peuvent être octroyés par le propriétaire de l’objet, par l’administrateur, ou par un utilisateur habilité à le faire. |
Attribution avec GRANT, retrait avec REVOKE :
GRANT CREATE SESSION TO HR;
GRANT SELECT, UPDATE(SALARY) ON EMPLOYEES TO SCOTT;
REVOKE CREATE SESSION FROM HR;
REVOKE SELECT, UPDATE(SALARY) ON EMPLOYEES FROM SCOTT;
2.1 Privilèges système, par catégorie
| Catégorie | Exemples de privilèges |
|---|---|
| PROCEDURE | CREATE PROCEDURE (crée des procédures/fonctions dans son propre schéma), CREATE ANY PROCEDURE, ALTER ANY PROCEDURE, DROP ANY PROCEDURE, EXECUTE ANY PROCEDURE. |
| ROLE | CREATE ROLE, ALTER ANY ROLE, DROP ANY ROLE, GRANT ANY ROLE. |
| SESSION | CREATE SESSION (connexion à la base), ALTER SESSION (modification de l’état de connexion), RESTRICTED SESSION (connexion quand la base est en mode STARTUP RESTRICT). |
| SYNONYME | CREATE SYNONYM, CREATE ANY SYNONYM, DROP ANY SYNONYM. |
| TABLE | CREATE TABLE (dans son propre schéma, avec attribution d’un quota sur le tablespace), CREATE ANY TABLE, BACKUP ANY TABLE, DROP ANY TABLE, LOCK ANY TABLE, COMMENT ANY TABLE, SELECT ANY TABLE, INSERT ANY TABLE, UPDATE ANY TABLE, DELETE ANY TABLE. |
| TRANSACTION | FORCE TRANSACTION (commit/rollback des transactions propriétaires dans une base locale), FORCE ANY TRANSACTION (sur toute transaction de la base locale). |
| TRIGGER | CREATE TRIGGER, CREATE ANY TRIGGER, ALTER ANY TRIGGER, DROP ANY TRIGGER. |
| USER | CREATE ANY USER, BECOME ANY USER, ALTER USER (modifier mot de passe, tablespaces, limites, rôles et privilèges d’un autre utilisateur), DROP USER. |
2.2 Privilèges objet
| Privilège | SQL concerné |
|---|---|
| ALTER | ALTER sur l’objet (table ou séquence). |
| DELETE | DELETE FROM l’objet (table ou vue). |
| EXECUTE | EXECUTE l’objet (procédure ou fonction) ; référence aux variables de package publiques. |
| INDEX | CREATE INDEX ON l’objet (table). |
| INSERT | INSERT sur l’objet (table ou vue). |
| REFERENCES | CREATE ou ALTER TABLE avec une définition de clé étrangère REFERENCES sur l’objet (table). |
| SELECT | SELECT ... FROM (vue, table, séquence). |
| UPDATE | UPDATE sur l’objet (table ou vue). |
2.3 Options d’administration
Les options WITH ADMIN OPTION et WITH GRANT OPTION permettent de déléguer l’administration des privilèges à un autre utilisateur.
Déléguer un privilège système avec WITH ADMIN OPTION :
GRANT CREATE SESSION TO HR WITH ADMIN OPTION;
CONNECT hr/hr
GRANT CREATE SESSION TO SCOTT;
Un REVOKE du privilège système CREATE SESSION pour HR ne sera pas transmis à SCOTT : SCOTT garde le privilège.
Déléguer un privilège objet avec WITH GRANT OPTION :
GRANT SELECT ON EMPLOYEES TO STOCK WITH GRANT OPTION;
CONNECT STOCK/STOCK
GRANT SELECT ON EMPLOYEES TO USER1 WITH GRANT OPTION;
Un REVOKE du privilège objet SELECT pour STOCK sera, lui, transmis à USER1 : USER1 perd le privilège SELECT ON EMPLOYEES.
3. Rôles
3.1 Avantages des rôles
Les rôles simplifient la gestion des privilèges : plutôt que d’accorder le même ensemble de privilèges à plusieurs utilisateurs, on accorde les privilèges à un rôle, puis ce rôle à chaque utilisateur. La gestion devient dynamique : si les privilèges associés à un rôle sont modifiés, tous les utilisateurs auxquels ce rôle est accordé bénéficient automatiquement et immédiatement des privilèges modifiés. Les rôles peuvent aussi être activés et désactivés afin d’activer ou de désactiver temporairement les privilèges.
3.2 Affecter des privilèges à des rôles et des rôles à des utilisateurs
3.3 Rôles prédéfinis
| Rôle | Privilèges |
|---|---|
| CONNECT | CREATE SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE SEQUENCE, CREATE DATABASE LINK, CREATE CLUSTER, ALTER SESSION. |
| RESOURCE | CREATE TABLE, CREATE PROCEDURE, CREATE SEQUENCE, CREATE TRIGGER, CREATE TYPE, CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR. |
| SCHEDULER_ADMIN | CREATE ANY JOB, CREATE JOB, EXECUTE ANY CLASS, EXECUTE ANY PROGRAM, MANAGE SCHEDULER. |
| DBA | La plupart des privilèges système et plusieurs autres rôles. Ne doit pas être accordé aux utilisateurs qui ne sont pas administrateurs. |
| SELECT_CATALOG_ROLE | Pas de privilèges système, mais plus de 1600 privilèges objet sur le dictionnaire de données. |
3.4 Créer un rôle
Création d’un rôle SUPERDBA avec mot de passe, intégrant le rôle DBA et des privilèges système, puis affectation à l’utilisateur STOCK :
CREATE ROLE "SUPERDBA" IDENTIFIED BY "sup"; -- le mot de passe sera demandé lors de l'activation du rôle
GRANT ALTER ANY INDEXTYPE TO "SUPERDBA";
GRANT ALTER ANY PROCEDURE TO "SUPERDBA";
GRANT ALTER ANY TABLE TO "SUPERDBA";
GRANT "DBA" TO "SUPERDBA" WITH ADMIN OPTION;
GRANT "SUPERDBA" TO STOCK WITH ADMIN OPTION;
4. Profils
Un seul profil est affecté à un utilisateur à un instant donné. Les profils gèrent les fonctionnalités de mots de passe (statut de compte, durée de vie, tentatives de connexion…), option toujours activée, et contrôlent la consommation des ressources (CPU, temps de connexion, nombre de sessions ouvertes…), option activée seulement si le paramètre d’initialisation RESOURCE_LIMIT est à TRUE (valeur par défaut FALSE).
4.1 Gestion des mots de passe
| Paramètre | Description |
|---|---|
FAILED_LOGIN_ATTEMPTS | Nombre d’échecs de connexion avant le verrouillage du compte. |
PASSWORD_LOCK_TIME | Nombre de jours pendant lesquels le compte est verrouillé après le nombre déterminé d’échecs de connexion. |
PASSWORD_LIFE_TIME | Durée de vie du mot de passe, en jours, avant expiration. |
PASSWORD_GRACE_TIME | Période de grâce, en jours, permettant le changement de mot de passe après la première connexion réussie suite à l’expiration du mot de passe. |
PASSWORD_REUSE_TIME | Nombre de jours pendant lesquels le mot de passe ne peut pas être réutilisé. |
PASSWORD_REUSE_MAX | Nombre de fois où le mot de passe actuel peut être réutilisé. |
PASSWORD_VERIFY_FUNCTION | Fonction PL/SQL qui effectue une vérification de complexité avant l’affectation d’un mot de passe. |
Les fonctions de vérification des mots de passe doivent appartenir à l’utilisateur SYS et renvoyer une valeur booléenne (true ou false).
4.2 Gestion des ressources
| Paramètre | Description |
|---|---|
SESSIONS_PER_USER | Nombre de sessions concurrentes pour un compte utilisateur. |
CPU_PER_SESSION | Temps CPU (en centisecondes) que le processus serveur d’une session peut consommer avant que la session soit automatiquement déconnectée. |
CPU_PER_CALL | Temps CPU (en centisecondes) que le processus serveur d’une session peut consommer pour exécuter une requête SQL avant arrêt automatique de l’exécution. |
CONNECT_TIME | Temps de connexion (en secondes) pour une session. |
IDLE_TIME | Temps d’inactivité (en secondes) pour une session. |
4.3 Créer un profil
CREATE PROFILE <profile_name> LIMIT
[limit_ressource value]
[limit_password value];
-- value := {integer | UNLIMITED | DEFAULT};
-- value := {function | null | DEFAULT} pour le paramètre PASSWORD_VERIFY_FUNCTION
-- exemple
CREATE PROFILE developer_profile LIMIT
SESSIONS_PER_USER 2
CPU_PER_SESSION 10000
CONNECT_TIME 480
IDLE_TIME 60
FAILED_LOGIN_ATTEMPTS 3
PASSWORD_LIFE_TIME 90
PASSWORD_REUSE_TIME 2
PASSWORD_VERIFY_FUNCTION my_function;