Guide Oracle

Rôles et privilèges sont définis pour sécuriser l'accès aux données de la base

Ces concepts sont mis en oeuvre pour protéger les données en accordant (ou retirant) des privilèges a un utilisateur ou un groupe d'utilisateurs

Un rôle est un regroupement de privilèges. Une fois créé il peut être assigné à un utilisateur ou à un autre rôle

Les privilèges sont de deux types

• Les privilèges de niveau système

Qui permettent la création, modification, suppression, exécution de groupes d'objets

les privilèges CREATE TABLE, CREATE VIEW, CREATE SEQUENCE par exemple permettent à l'utilisateur qui les a reçu de créer des tables, des vues et des séquences

• Les privilèges de niveau objet

Qui permettent les manipulations sur des objets spécifiques

les privilèges SELECT, INSERT, UPDATE, DELETE sur la table SCOTT.EMP par exemple permettent à l'utilisateur qui les a reçu de sélectionner, ajouter, modifier et supprimer des lignes dans la table EMP appartenant à l'utilisateur SCOTT

Assigner des privilèges système à un utilisateur

Lorsqu'un utilisateur est créé avec l'instruction CREATE USER, il ne dispose encore d'aucun droit car aucun privilège ne lui a encore été assigné
Il ne peut même pas se connecter à la base !

Il faut donc lui assigner les privilèges nécessaires

Il doit pouvoir se connecter, créer des tables, des vues, des séquences.
Pour lui assigner ces privilèges de niveau système il faut utiliser l'instruction GRANT dont voici la syntaxe

systeme_privilege représente un privilège système (liste en annexe 1)
role représente un rôle préalablement créé
ALL PRIVILEGES représente tous les privilèges système (à l'exception de SELECT ANY DICTIONARY)
user représente le nom de l'utilisateur qui doit bénéficier du privilège
PUBLIC assigne le privilège à tous les utilisateurs
WITH ADMIN OPTION assigne à l'utilisateur le droit d'assigner, de retirer, de modifier et de supprimer à son tour les privilèges du rôle reçus


Attention avec l'option ALL PRIVILEGES. Celle-ci accorde des droits quasi illimités à l'utilisateur qui en hérite, avec les risques de sécurité que cela implique

Pour que l'utilisateur puisse simplement se connecter à la base, il doit bénéficier du privilège système CREATE SESSION

GRANT CREATE SESSION TO nom_utilisateur ;

Ensuite il faut lui assigner des droits de création de table

GRANT CREATE TABLE TO nom_utilisateur ;

Puis les droits de création de vues

GRANT CREATE VIEW TO nom_utilisateur ;

Et il en va de même pour tous les autres privilèges qui lui sont assignés.

L'ensemble de ces privilèges peuvent être assignés au sein d'une même commande

GRANT 
   CREATE SESSION
  ,CREATE TABLE
  ,CREATE VIEW 
  TO nom_utilisateur ;

object_privilege représente un privilège objet (liste en annexe 2)
role représente un rôle préalablement créé
ALL PRIVILEGES représente tous les privilèges assignés à l'exécuteur de l'instruction
column représente le nom de colonne d'une table
schema représente le nom d'un schéma
object représente le nom d'un objet du schéma
directory_name représente le nom d'une directory
JAVA SOURCE représente le nom d'une source Java
JAVA RESOURCE représente le nom d'une ressource Java
WITH GRANT OPTION assigne à l'utilisateur de droit d'assigner à son tour le privilège reçu à un autre utilisateur
(WITH GRANT OPTION s'applique à un utilisateur ou à PUBLIC, mais pas à un rôle)
WITH HIERARCHY OPTION assigne le privilèges aux sous-objets


Pour assigner à l'utilisateur le droit de sélectionner, insérer, modifier et supprimer des lignes dans la table EMP de l'utilisateur SCOTT

GRANT 
   SELECT
  ,INSERT
  ,UPDATE
  ,DELETE
  ON SCOTT.EMP  
  TO nom_utilisateur ;

Une liste de colonnes peut être indiquée dans l'instruction afin de restreindre davantage les droits sur une table

GRANT 
  UPDATE ( JOB, MGR )
  ON SCOTT.EMP  
  TO nom_utilisateur ;

L'utilisateur peut modifier la table SCOTT.EMP mais uniquement les colonnes JOB et MGR


Pour pouvoir mettre à jour ou supprimer des lignes d'une table, les privilèges UPDATE ET DELETE ne suffisent pas. Le privilège SELECT est nécessaire


Un utilisateur munis des droits DBA ne pourra pas accorder de privilèges sur un objet qui ne lui appartient pas


Principes généraux appliqués aux privilèges

• Un utilisateur possède automatiquement tous les privilèges sur un objet qui lui appartient
• Un utilisateur ne peut pas donner plus de privilèges qu'il n'en a reçu
• s'il n'a pas reçu le privilège avec l'option WITH GRANT OPTION, un utilisateur ne peux pas assigner à son tour ce même privilège

L'instruction GRANT permet d'assigner un ou plusieurs privilèges système ou objet
Cependant, lorsque la liste des privilèges est importante, cette manière de procéder s'avère rapidement fastidieuse et répétitive

Si l'on prend l'exemple d'un utilisateur travaillant au service comptabilité, il doit recevoir un certain nombre de privilèges sur un certain nombre d'objets
Un autre utilisateur du même service à toutes les chances de se voir assigner des privilèges identiques

C'est pourquoi il est souhaitable de pouvoir regrouper des privilèges identiques dans un même ensemble

Cet ensemble s'appelle un rôle et se créé avec l'instruction CREATE ROLE

role représente le nom du rôle
NOT IDENTIFIED (défaut) indique qu'aucun mot de passe n'est nécessaire pour activer le rôle
IDENTIFIED BY password indique qu'un mot de passe est nécessaire pour activer le rôle
IDENTIFIED USING package indique qu'un package va être utilisé pour fixer les droits de l'utilisateur
IDENTIFIED EXTERNALLY indique que l'autorisation provient d'une source externe (S.E.)
IDENTIFIED GLOBALLY pour un user GLOBAL géré par exemple par Enterprise Directory Service

Lorsque le rôle est créé, il ne contient rien et il faut l'alimenter à l'aide d'instructions GRANT

CREATE ROLE comptabilite ;

GRANT SELECT, INSERT, UPDATE, DELETE ON CPT.FACTURE TO comptabilite ;
GRANT SELECT, INSERT, UPDATE, DELETE ON CPT.LIG_FAC TO comptabilite ;
GRANT SELECT, INSERT, UPDATE, DELETE ON CPT.JOURNAL TO comptabilite ;

Une fois le rôle créé, il peut être assigné à un utilisateur ou à un autre rôle

GRANT comptabilite TO nom_utilisateur ;

Exemple d'utilisation d'un package pour l'utilisation d'un rôle

-- CREATION DU ROLE IDENTIFIE PAR LA PROCEDURE 
create role test identified using scott.test_check; 

-- CREATION DU PACKAGE
create or replace procedure test_check 
authid current_user is 
Begin 
-- VERIFICATION DE L'ADRESSE IP DU DEMANDEUR
For i in (select sys_context (‘USERENV', ‘IP_ADDRESS') ip from dual) 
Loop 
	If i.ip != ‘192.168.12.196' Then
	   -- MAUVAISE IP, SORTIE DE LA PROCEDURE 
	   return; 
	End if; 
	-- IP OK, ON ACTIVE LE ROLE 
	dbms_session.set_role(‘TEST'); 
End loop; 
End; 

-- CREATION D'UNE TABLE DE TEST 
create table sys.test as select * from dual; 
-- ON DONNE LES DROITS AU ROLE 
grant select on sys.test to test; 
-- ON DONNE LE ROLE A SCOTT 
grant test to scott; 
-- ON DESACTIVE LE ROLE POUR SCOTT CAR SINON IL SERAIT ACTIVE PAR DEFAUT,
-- CE QUI NE SERAIT D'AUCUNE UTILITE 
ALTER USER scott DEFAULT ROLE ALL EXCEPT test; 

-- CONNEXION 
SQL> connect scott/tiger 

SQL>select * from sys.test; 

-- DROITS REFUSES
-- SCOTT DEMANDE L'ACTIVATION DU ROLE TEST 
set role test; 

-- SI L'ADRESSE IP EST RECONNUE

SQL>select * from sys.test; 
D 
- 
X 

SQL> select * from session_role; 
ROLE 
-------- 
CONNECT 
RESOURCE 
TEST

Trois rôles existent en standard

• CONNECT
• RESOURCE
• DBA

Les privilèges système assignés au rôle CONNECT

SQL> select * from DBA_SYS_PRIVS where grantee='CONNECT' ;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
CONNECT                        CREATE VIEW                              NO
CONNECT                        CREATE TABLE                             NO
CONNECT                        ALTER SESSION                            NO
CONNECT                        CREATE CLUSTER                           NO
CONNECT                        CREATE SESSION                           NO
CONNECT                        CREATE SYNONYM                           NO
CONNECT                        CREATE SEQUENCE                          NO
CONNECT                        CREATE DATABASE LINK                     NO

8 ligne(s) sélectionnée(s).

Ce rôle permet l'ouverture (CREATE SESSION) et la modification (ALTER SESSION) d'une session
La création de tables, vues, clusters, séquences, synonymes et liens de bases de données

L'option ADMIN OPTION positionnée à NO indique que ces privilèges ne peuvent pas être assignés à un autre utilisateur ou rôle

Les privilèges système assignés au rôle RESOURCE

SQL>  select * from DBA_SYS_PRIVS where grantee='RESOURCE' ;

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
RESOURCE                       CREATE TYPE                              NO
RESOURCE                       CREATE TABLE                             NO
RESOURCE                       CREATE CLUSTER                           NO
RESOURCE                       CREATE TRIGGER                           NO
RESOURCE                       CREATE OPERATOR                          NO
RESOURCE                       CREATE SEQUENCE                          NO
RESOURCE                       CREATE INDEXTYPE                         NO
RESOURCE                       CREATE PROCEDURE                         NO

8 ligne(s) sélectionnée(s).

Ce rôle permet de créer des types, tables clusters, opérateurs, séquences, index et procédures

Le rôle RESOURCE accorde un privilège UNLIMITED QUOTA à l'utilisateur est n'est donc à assigner qu'en connaissance de cause

Les privilèges système assignés au rôle DBA

  1* select * from DBA_SYS_PRIVS where grantee='DBA' order by PRIVILEGE
SQL> /

GRANTEE                        PRIVILEGE                                ADM
------------------------------ ---------------------------------------- ---
DBA                            ADMINISTER DATABASE TRIGGER              YES
DBA                            ADMINISTER RESOURCE MANAGER              YES
DBA                            ALTER ANY CLUSTER                        YES
DBA                            ALTER ANY DIMENSION                      YES
DBA                            ALTER ANY EVALUATION CONTEXT             YES
DBA                            ALTER ANY INDEX                          YES
DBA                            ALTER ANY INDEXTYPE                      YES
DBA                            ALTER ANY LIBRARY                        YES
DBA                            ALTER ANY OUTLINE                        YES
DBA                            ALTER ANY PROCEDURE                      YES
DBA                            ALTER ANY ROLE                           YES
DBA                            ALTER ANY RULE                           YES
DBA                            ALTER ANY RULE SET                       YES
DBA                            ALTER ANY SEQUENCE                       YES
DBA                            ALTER ANY SNAPSHOT                       YES
DBA                            ALTER ANY TABLE                          YES
DBA                            ALTER ANY TRIGGER                        YES
DBA                            ALTER ANY TYPE                           YES
...
...
DBA                            SELECT ANY DICTIONARY                    YES
DBA                            SELECT ANY SEQUENCE                      YES
DBA                            SELECT ANY TABLE                         YES
DBA                            UNDER ANY TABLE                          YES
DBA                            UNDER ANY TYPE                           YES
DBA                            UNDER ANY VIEW                           YES
DBA                            UPDATE ANY TABLE                         YES

139 ligne(s) sélectionnée(s).

La liste des privilèges assignés au rôle DBA est beaucoup plus longue du fait que ce rôle est octroyé aux utilisateurs ayant des droits d'administration de la base

D'une façon générale, il est fortement déconseillé d'utiliser ces rôles standards car ils accordent trop de droits aux utilisateurs

La liste des rôles définis est visible depuis la vue DBA_ROLES

SQL> select * from DBA_ROLES ;
ROLE                           PASSWORD
------------------------------ --------
CONNECT                        NO
RESOURCE                       NO
DBA                            NO
SELECT_CATALOG_ROLE            NO
EXECUTE_CATALOG_ROLE           NO
DELETE_CATALOG_ROLE            NO
EXP_FULL_DATABASE              NO
IMP_FULL_DATABASE              NO
RECOVERY_CATALOG_OWNER         NO
GATHER_SYSTEM_STATISTICS       NO
LOGSTDBY_ADMINISTRATOR         NO
AQ_ADMINISTRATOR_ROLE          NO
AQ_USER_ROLE                   NO
GLOBAL_AQ_USER_ROLE            GLOBAL
OEM_MONITOR                    NO
HS_ADMIN_ROLE                  NO
WKUSER                         NO
WM_ADMIN_ROLE                  NO
JAVAUSERPRIV                   NO
JAVAIDPRIV                     NO
JAVASYSPRIV                    NO
JAVADEBUGPRIV                  NO
EJBCLIENT                      NO
JAVA_ADMIN                     NO
JAVA_DEPLOY                    NO
CTXAPP                         NO
XDBADMIN                       NO
AUTHENTICATEDUSER              NO
OLAP_DBA                       NO
SALES_HISTORY_ROLE             NO

30 ligne(s) sélectionnée(s).

La liste des privilèges système assignés à un rôle s'obtient en interrogeant les vues DBA_SYS_PRIVS et USER_SYS_PRIVS

SQL> desc DBA_SYS_PRIVS
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ------------
 GRANTEE                                   NOT NULL VARCHAR2(30)
 PRIVILEGE                                 NOT NULL VARCHAR2(40)
 ADMIN_OPTION                                       VARCHAR2(3)

La liste des rôles assignés à un utilisateur s'obtient via les vues
DBA_ROLE_PRIVS et USER_ROLE_PRIVS

SQL> select * from DBA_ROLE_PRIVS where grantee = 'FD' ;

GRANTEE                        GRANTED_ROLE                   ADM DEF
------------------------------ ------------------------------ --- ---
FD                             DBA                            NO  YES
FD                             CONNECT                        NO  YES
FD                             RESOURCE                       NO  YES
FD                             GATHER_SYSTEM_STATISTICS       NO  YES

La liste des privilèges objet assignés à un utilisateur s'obtient en interrogeant les vues
DBA_TAB_PRIVS, ALL_TAB_PRIVS et USER_TAB_PRIVS

select * from DBA_TAB_PRIVS where grantee = 'FD' ;

GRANTEE	OWNER	TABLE_NAME	GRANTOR	PRIVILEGE		GRANTABLE		HIERARCHY
FD	SYS	ALL_DIRECTORIES	SYS	SELECT		NO		NO
FD	SYS	DBA_DIRECTORIES	SYS	SELECT		NO		NO

La liste des privilèges objet sur les colonnes de tables assignés à un utilisateur s'obtient en interrogeant les vues
DBA_COL_PRIVS, ALL_COL_PRIVS et USER_COL_PRIVS

La liste des rôles assignés à l'utilisateur au cours de sa session est visible via la vue SESSION_ROLES

SQL>  select * from SESSION_ROLES ;

ROLE
------------------------------
CONNECT
RESOURCE

La liste des privilèges assignés à l'utilisateur au cours de sa session est visible via la vue SESSION_PRIVS

SQL>  select * from SESSION_PRIVS ;

PRIVILEGE
-----------------------------------
CREATE SESSION
ALTER SESSION
UNLIMITED TABLESPACE
CREATE TABLE
CREATE CLUSTER
CREATE SYNONYM
CREATE VIEW
CREATE SEQUENCE
CREATE DATABASE LINK
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE

Les privilèges assignés à un utilisateur via un rôle ne permettent pas la création de vues, de procédures, de fonctions de packages ni de clés étrangères par l'intermédiaire de code PL/SQL dynamique (DBMS_SQL, execute immediate)
Pour créer ces objets via PL/SQL, l'utilisateur doit avoir reçu directement les privilèges requis via l'instruction GRANT


Les vues SESSION_PRIVS et SESSION_ROLES sont utiles pour vérifier les droits acquis au cours de la session
Les rôles par défaut à la connexion pouvant être enrichis avec l'instruction SET ROLE (ou son équivalent PL/SQL : DBMS_SESSION.SET_ROLE)



Un mot de passe peut être ajouté à un rôle pour pouvoir l'activer avec l'instruction ALTER ROLE

IDENTIFIED permet de définir le système d'identification
NOT IDENTIFIED supprime le système d'identification

Cela permet de se prémunir de l'attribution du rôle par un utilisateur non autorisé qui tenterait de se l'attribuer via l'instruction SET ROLE

Un rôle peut être activé ou désactivé pour un utilisateur avec l'instruction ALTER USER

DEFAULT ROLE spécifie les rôles assignés par défaut à la connexion
ALL assigne tous les rôles à l'utilisateur
ALL EXCEPT assigne tous les rôles à l'utilisateur à l'exception des rôles désignés
NONE n'assigne aucun rôle à l'utilisteur


Pour activer un rôle qui ne fait pas partie de ceux par défaut il faut utiliser l'instruction SET ROLE

Un rôle peut être supprimé en utilisant l'instruction DROP ROLE

DROP ROLE nom_role ;

Le rôle spécifié ainsi que tous les privilèges qui lui sont associés sont supprimés de la base et également retiré à tous les utilisateurs qui en bénéficiaient

Les privilèges système qui ont été assignés à des utilisateurs ou à des rôles peuvent être retirés avec l'instruction REVOKE

Les arguments sont identiques à ceux décrits pour l'instruction GRANT

Pour pouvoir supprimer un privilège, il faut en avoir reçu l'autorisation avec l'option ADMIN OPTION


L'utilisteur disposant du rôle DBA peut révoquer les privilèges CONNECT, RESOURCE, DBA ou tout autre privilège système ou rôle


Retirer des privilèges à un utilisateur ne supprime pas son schéma ni les objets qu'il contient

Les privilèges objet qui ont été assignés à des utilisateurs ou à des rôles peuvent être retirés avec l'instruction REVOKE

CASCADE CONSTRAINTS (applicable uniquement pour révoquer des privilèges REFERENCES) supprime toutes les contraintes d'intégrité référentielles définies par l'utilisateur
FORCE retire le privilège EXECUTE sur les types objet

Pour pouvoir supprimer un privilège, il faut en avoir reçu l'autorisation avec l'option ADMIN OPTION

L'utilisteur disposant du rôle DBA ne peut pas retirer de privilèges qu'il n'a pas accordé