Schema

Fiche express
Type	Administration Oracle (schémas, utilisateurs, privilèges)
Voir aussi	Tablespace · Tables · User droits privileges connection oracle

Un schéma Oracle regroupe l'ensemble des objets (tables, vues, synonymes, index, séquences, programmes PL/SQL) appartenant à un utilisateur. Dans Oracle, un schéma correspond toujours à un utilisateur : les deux notions sont confondues (contrairement à PostgreSQL par exemple, où un schéma est indépendant des utilisateurs).

À un utilisateur/schéma, on peut associer :

• un tablespace par défaut et un tablespace temporaire
• des quotas d'espace disque
• une politique de limitation de ressources et de mot de passe (profil)
• des privilèges système et des privilèges objet

Authentification

Un utilisateur peut s'authentifier de deux façons, y compris simultanément :

• par Oracle (identification interne) :

SQL> CONNECT oheu/motdepasse

• par le système (authentification externe, déléguée à l'OS) :

SQL> CONNECT /

Pour l'authentification externe, il faut faire correspondre le compte OS et le compte Oracle via le préfixe défini par le paramètre OS_AUTHENT_PREFIX (par défaut OPS$) :

utilisateur OS "oracle"       -> utilisateur Oracle "OPS$oracle"
utilisateur OS "DOMAINE\user" -> utilisateur Oracle "OPS$DOMAINE\user"

Gestion des utilisateurs/schémas

Création :

CREATE USER nom IDENTIFIED { BY mot_de_passe | EXTERNALLY }
  [ DEFAULT TABLESPACE nom_tablespace ]
  [ TEMPORARY TABLESPACE nom_tablespace ]
  [ QUOTA { valeur [K|M] | UNLIMITED } ON nom_tablespace [,...] ]
  [ PROFILE nom_profil ]
  [ PASSWORD EXPIRE ]
  [ ACCOUNT { LOCK | UNLOCK } ];

CREATE USER "OPS$DOMAINE\user" IDENTIFIED EXTERNALLY;

CREATE USER appuser IDENTIFIED BY motdepasse_temporaire
  DEFAULT TABLESPACE data
  QUOTA UNLIMITED ON data
  PASSWORD EXPIRE;

Important : penser systématiquement à affecter un tablespace par défaut à la création. Sans DEFAULT TABLESPACE, l'utilisateur hérite du tablespace par défaut de la base — souvent SYSTEM ou SYSAUX, ce qu'il faut éviter (tablespaces réservés au dictionnaire de données).

Modification :

ALTER USER nom
  [ IDENTIFIED { BY mot_de_passe | EXTERNALLY } ]
  [ DEFAULT TABLESPACE nom ]
  [ TEMPORARY TABLESPACE nom ]
  [ QUOTA { valeur [K|M] | UNLIMITED } ON nom_tablespace [,...] ]
  [ PROFILE nom_profil ]
  [ PASSWORD EXPIRE ]
  [ ACCOUNT { LOCK | UNLOCK } ];

Déverrouiller un compte :

ALTER USER oracle ACCOUNT UNLOCK;

Suppression :

DROP USER nom CASCADE;

Vues d'information : DBA_USERS, DBA_TS_QUOTAS.

Profils

Un profil est un ensemble nommé de limites de ressources et de règles de mot de passe, affecté à un ou plusieurs utilisateurs.

Ressources limitables :

• temps CPU par appel/session (CPU_PER_CALL / CPU_PER_SESSION)
• nombre de lectures logiques par appel/session
• nombre de sessions simultanées par utilisateur (SESSIONS_PER_USER)
• temps d'inactivité par session (IDLE_TIME)
• durée totale de session (CONNECT_TIME)
• quantité de mémoire privée réservée dans la SGA (PRIVATE_SGA)
• limite composite (COMPOSITE_LIMIT)

Règles de mot de passe : verrouillage de compte après échecs, durée de vie, non-réutilisation, complexité (fonction de vérification).

CREATE PROFILE exploitation LIMIT
  SESSIONS_PER_USER 3
  IDLE_TIME 30
  FAILED_LOGIN_ATTEMPTS 3
  PASSWORD_LIFE_TIME 30
  PASSWORD_REUSE_TIME 180
  PASSWORD_LOCK_TIME UNLIMITED
  PASSWORD_GRACE_TIME 3
  PASSWORD_VERIFY_FUNCTION verif_mdp_exploitation;

Modification d'un profil :

ALTER PROFILE default LIMIT
  SESSIONS_PER_USER 3
  IDLE_TIME 30
  FAILED_LOGIN_ATTEMPTS 5;

Affectation à un utilisateur :

ALTER USER oracle PROFILE exploitation;

Activer la limitation des ressources (non active par défaut) :

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Suppression :

DROP PROFILE nom [ CASCADE ];

Vues d'information : DBA_USERS, DBA_PROFILES.

Privilèges système

GRANT CREATE SESSION, CONNECT TO nom_utilisateur;

Quelques privilèges système courants : CREATE/ALTER/DROP USER, GRANT ANY PRIVILEGE, GRANT ANY ROLE, CREATE/DROP ANY TABLE, SELECT ANY DICTIONARY. Liste complète : vue SYSTEM_PRIVILEGE_MAP.

GRANT CREATE SESSION, CREATE TABLE TO oracle;
GRANT ALL PRIVILEGES TO oracle;

Options :

• WITH ADMIN OPTION — l'utilisateur peut à son tour accorder ce privilège système à d'autres.
• WITH GRANT OPTION — équivalent pour les privilèges objet.

Révocation :

REVOKE CREATE TABLE FROM oracle;

SYSDBA / SYSOPER

Privilèges nécessaires pour arrêter/démarrer une instance (SYSDBA donne également tous les droits sur la base, SYSOPER un sous-ensemble orienté exploitation). Pour activer SYSDBA à distance (hors authentification OS), il faut un fichier de mots de passe exclusif :

REMOTE_LOGIN_PASSWORDFILE = EXCLUSIVE

Privilèges objet

Par défaut, seul le créateur d'un objet peut y accéder ; les autres utilisateurs doivent recevoir un privilège objet explicite.

Privilège	Table	Vue	Séquence	Programme
SELECT	X	X	X
INSERT	X	X
UPDATE	X	X
DELETE	X	X
EXECUTE				X

GRANT SELECT, INSERT, UPDATE(nom, prenom) ON schema_name TO oracle;

Révocation :

REVOKE INSERT, UPDATE ON client FROM oracle;
REVOKE ALL ON client FROM oracle;

Synonymes

CREATE PUBLIC SYNONYM schema_name FOR schema_name.schema_name;
ALTER SESSION SET CURRENT_SCHEMA = schema_name;

Rôles

CREATE ROLE nom;
ALTER ROLE nom [ IDENTIFIED { BY mdp | EXTERNALLY | USING package } | NOT IDENTIFIED ];
GRANT nom_privilege TO nom_role [ WITH ADMIN OPTION ];
GRANT { nom_privilege [(liste_privileges)] [,...] | ALL [PRIVILEGES] }
  ON [nom_schema.]nom_objet TO nom_role [,...];

Vues d'information

Privilèges système : DBA_SYS_PRIVS, SESSION_PRIVS, SYSTEM_PRIVILEGE_MAP.

Privilèges objet : DBA_TAB_PRIVS, DBA_COL_PRIVS, TABLE_PRIVILEGES_MAP.

Rôles : DBA_ROLES, DBA_APPLICATION_ROLES, DBA_ROLE_PRIVS, ROLE_SYS_PRIVS, ROLE_TAB_PRIVS, ROLE_ROLE_PRIVS, SESSION_ROLES.

Voir aussi

• Tablespace — gestion des tablespaces affectés aux schémas
• Tables — stockage physique des objets d'un schéma
• User droits privileges connection oracle — comptes SYS/SYSTEM et méthodes de connexion
• Oracle — panorama général