Les principaux sont expliqués ici :

• la notion d’utilisateur, d’authentification et de profil
• privilèges DBA et exploitant au niveau OS
• les roles
• les droits sur les objets
• les droits systeme
• la sécurité niveau ligne

Il existe 2 types gestion des droits des procédures (et fonctions et packages bien sûr)
Procédure avec Droits du propriétaire ou Procédure avec droits de celui qui lance la procédure.
Respectivement DEFINER’S RIGHT Procedure et INVOKER’S Right procedure.

Nous allons étudier dans cet article les DEFINER’s RIGHT .

Par défaut une procédure stockée s’exécute avec les droits du propriétaire, et non les droits de celui qui l’exécute !
Ceci implique que, par défaut l’exécuteur n’a pas besoin de droits particuliers sur les objets sous jacents, mais juste le droit d’exécuter la procédure !
Ces procédures ont la visibilité par défaut des objets du propriétaire, et pas de celui qui lance la procédure.
Et dernier point : LES ROLES NE SONT PAS SUPPORTéS !!!!

————————————————————-
1er cas simple :
une procédure (PDD) appartenant a DD, accede à une table de DD (TDD).
Cette procédure est utilisée par SCOTT.

SQL> connect DD
SQL> CREATE TABLE tdd (n integer);
SQL> create or replace PROCEDURE PDD AS
BEGIN
INSERT INTO tdd values (1);
END;
SQL> GRANT EXECUTE ON pdd TO scott;
SQL> connect SCOTT
SQL> CREATE SYNONYM pdd for dd.pdd
SQL> EXECUTE PDD
—> OK !!

rem : créer un synonyme pour la procédure est obligatoire ici (?!) car Oracle cherche l’objet
dans le shema courant..et il n’existe pas de DD.PDD dans SCOTT.
Le synonyme est lui traduit sans ambiguité.
- un EXECUTE DD.PDD donnerait:
erreur : ORA-06550: line 1, column 10:
PLS-00302: component ‘PDD’ must be declared

rem: il est à noter également que les objets accédés par la procédure sont implicitement
ceux du schéma propriétaire :
Bien que ce soit SCOTT qui appelle la procédure,
TDD référence un objet de DD
(sans avoir a le préfixer et même si SCOTT avait aussi une table qui s’appelle TDD !!!!)

————————————————————-
2eme cas simple :
une procédure (PDD) appartenant a DD, accede à une table de SCOTT (TSCOTT)

L’exécuteur n’étant pas le propriétaire de l’objet accédé il a donc besoin de droits explicites.

A) droit direct

SQL> connect SCOTT/TIGER
CREATE TABLE tscott (n INTEGER);
GRANT INSERT ON tscott to DD;

SQL> connect DD/DD
create or replace PROCEDURE PDD AS
BEGIN
INSERT INTO SCOTT.TSCOTT values (1);
END;

EXECUTE pdd –> OK !

B) via un role

SQL> REVOKE INSERT ON tscott FROM DD;
SQL> CREATE ROLE inscott;
SQL> GRANT INSERT ON scott.tscott TO inscott;

l’execution du package le rend invalide et sa recompilation nous donne plus de précisions :

SQL> CONNECT dd/dd
EXECUTE pdd

ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object DD.PDD is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> ALTER PROCEDURE pdd COMPILE
Error
PL/SQL: SQL Statement ignored
PL/SQL: ORA-00942: table or view does not exist

CQFD les roles ne sont pas supportés.

Privilèges système
Les privilèges systèmes d’accès aux objets s’intéressent plutôt au contenant qu’au contenu. Ils concernent principalement des ordres de création, de modification de structure et de suppression d’objets (SQL DDL plutot que DML)

Ce sont donc des privilèges d’assez haut niveau, que l’on réservera par exemple aux développeurs mais qui seront utilisés avec beaucoup de parcimonie en phase de production…

Liste de quelques privilèges système d’Oracle

Le compte DBSNMP présent par défaut sur les bases Oracle, peut être facilement exploiter pour accéder ? des données.

Ce compte est nécessaire pour pour pouvoir utiliser les agents de la console Oracle Enterprise Manager ou de la Grid console.

Pour des raisons de sécurité, il est important de changer le mot de passe par défaut qui vaut DBSNMP !!!!) mais en respectant la procédure suivante :
Changement de Mot de passe pour DBSNMP

1] Arreter l’agent
Oracle7 - Oracle8i
% lsnrctl dbsnmp_stop

Oracle9i
% agentctl stop

2] Editer le $ORACLE_HOME/network/admin/snmp_rw.ora file
Rajouter les parametres suivants :

snmp.connect.{SID}.NAME = dbsnmp
snmp.connect.{SID}.PASSWORD = {new password}

Sous UNIX, pensez ? donner la permission suivante au fichier SNMP_RW.ORA

% chmod 600 snmp_rw.ora

3] Changez le mdp de DBSNMP dans la base avec la commande
La 2ieme commande a le mérite de ne pas avoir de traces en clair dans le sqlnet.trc.

SQL> alter user “dbsnmp” identified by “”;

SQL> password DBSNMP
Modification de mot de passe pour dbsnmp
Nouveau mot de passe : *******
Ressaisir le nouveau mot de passe : *******
Mot de passe modifié

4] Redemarrez l’agent
En 10g le simple fait de changer l’agent comme ? l’étape 3, arrête la collecte des informations et vous propose de changer le mot de passe.

DEMO de la sécurité au niveau LIGNE avec Oracle 10g
(A.K.A Row Level Security / RLS / Fine Grained Access Control / VPDs)

Le but de cette demo est de mettre en place une strategie sur la table
SCOTT.EMP, qui s’appuie sur un contexte utilisateur, défini de manière automatique..
Un user ne devra voir que la (les) ligne’s) qui le concerne dans la table,
même en faisant un ‘SELECT * FROM emp’.
ex : KING ne voit que la ligne de KING, CLARK la ligne de CLARK.
On créera un user Oracle ‘CLARK’ présent dans la table des employés,
pour tester les filtres appliqués au user connecté.

—————————-
– 0) Environnement de TEST
—————————-
– en tant que SYS

– créer un user spécifique pour mettre en place la sécurié
CREATE USER sec IDENTIFIED BY nbvcxw;
– les privileges minimum necessaire pour DEV1
GRANT CREATE SESSION, CREATE TABLE, CREATE PROCEDURE,
CREATE ANY CONTEXT TO sec;
GRANT EXECUTE ON DBMS_RLS TO sec;
GRANT EXECUTE ON DBMS_SESSION TO sec;
– rem on peut remplacer les 2 precedents par un
– GRANT EXECUTE_CATALOG_ROLE TO dev1 (moins ciblé / sécurisé)
CREATE USER clark IDENTIFIED BY clark;
GRANT CREATE SESSION TO clark;
GRANT SELECT ON scott.emp TO PUBLIC;
GRANT EXECUTE ON sec.pack_contexte_emp TO PUBLIC; (pour debug)
GRANT EXECUTE ON filtre_emp TO PUBLIC; (pour debug)

—————————
– 1) déclarer le contexte
—————————

CONNECT sec/nbvcxw
CREATE OR REPLACE CONTEXT contexte_employe
USING pack_contexte_emp;

——————————————————————————–
– 2) definir le contexte applicatif (code du package et attributs du contexte)
——————————————————————————–

– ici c’est le no d’emp et le niveau (chef ou nom)
– qui determineront les privileges d’acces ? l’applicatif

– les specs du package :

CREATE OR REPLACE PACKAGE pack_contexte_emp
AS
PROCEDURE def_contexte;
END;

– le corps du package :

CREATE OR REPLACE PACKAGE BODY pack_contexte_emp
IS
PROCEDURE def_contexte
IS
v_empno NUMBER;
nb_subordonnes NUMBER;
BEGIN

– on recupere le no de l’employe et sa fonction (chef ou non)
– et on definit les attributs du contexte en consequence
– le ‘NAMESPACE’ ‘contexte_employe’ a donc 2 attributs ici
– on ne se sert que du premier dans cet exemple…

– d’abord le no d’emp
– en utilisant le ‘SESSION_USER’ de SYS_CONTEXT
– equivallent a un SELECT username FROM dual
– rem : si le user n’apparait pas dans la table –> no data found (a trapper)

SELECT empno INTO v_empno FROM scott.emp
WHERE ename = SYS_CONTEXT(’USERENV’, ‘SESSION_USER’);

DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘no_emp’, v_empno);

– puis son niveau de responsabilite : chef ou non
– si oui son no d’emp est le no de manager de quelqu’un…

SELECT COUNT(*) INTO nb_subordonnes
FROM scott.emp
WHERE mgr= v_empno;
IF (nb_subordonnes <> 0) THEN
DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘niveau’, ‘chef’);
ELSE
DBMS_SESSION.SET_CONTEXT(’contexte_employe’, ‘niveau’, ‘employe’);
END IF;

END def_contexte;
END pack_contexte_emp;

– test

select SYS_CONTEXT(’USERENV’, ‘SESSION_USER’) from DUAL
exec sec.pack_contexte_emp.def_contexte

———————————————-
– 3) definir les fonctions limitant les acces
———————————————-

– la ou les fonctions vont retourner une chaine
– qui sera ajoutee a la clause WHERE (predicat supplementaire)
– on utilise le contexte ‘employe’ pour identifier le user

CREATE OR REPLACE PACKAGE filtre_emp
AS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;
END;

CREATE OR REPLACE PACKAGE BODY filtre_emp
IS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2
IS
predicat VARCHAR2(1000);
BEGIN
predicat := ‘empno=SYS_CONTEXT(”contexte_employe”,”no_emp”)’;
RETURN predicat;
END;
END;

– test

SELECT SYS_CONTEXT(’contexte_employe’,'no_emp’) FROM dual
SELECT sec.filtre_emp.vue_emp(’SCOTT’,'EMP’) FROM dual

————————————————————
– 4) creer stratégie d’acces (policy) attachée a la table
————————————————————

DBMS_RLS.ADD_POLICY (proprietaire_objet,nom_objet,nom strategie,proprietaire strategie,fonction_filtre, SQL_concerne) :
execute DBMS_RLS.ADD_POLICY (’scott’, ‘emp’, ‘policy_emp’,’sec’, ‘filtre_emp.vue_emp’, ‘SELECT’);

(si necessaire
DBMS_RLS.DROP_POLICY (nom_owner, nom_table, nom_police) :
execute DBMS_RLS.DROP_POLICY (’scott’, ‘emp’, ‘policy_emp’); )

–test

select * from sys.rls$ where PFSCHMA=’DEV1′;
OBJ# GNAME PNAME STMT_TYPE CHECK_OPT ENABLE_FLAG PFSCHMA PPNAME PFNAME PTYPE
52606 SYS_DEFAULT POLICY_EMP 513 0 1 DEV1 FILTRE_EMP VUE_EMP

———————————————————————
– 5) activer le contexte, avant l’acces aux données des utilisateurs
———————————————————————
– manuellement (ou géré par l’applicatif)
connect clark/clark
exec sec.pack_contexte_emp.def_contexte;

– ou automatiquement par un trigger, en tant que DBA !!

CREATE OR REPLACE TRIGGER dev1.active_contexte_emp
AFTER LOGON ON DATABASE
BEGIN
sec.pack_contexte_emp.def_contexte;
END;

——————-
– 6) Test VPD
——————-

SQL> connect clark/clark
SQL> select * from emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– —– ——– —- ——– —- —- ——
7782 CLARK MANAGER 7839 09/06/81 2450 10

SQL> connect system/xdcfvgh
SQL> select * from emp;
aucune ligne sélectionnée.

et sans la VPD :

SQL> execute DBMS_RLS.DROP_POLICY (’dev1′, ‘emp’, ‘policy_emp’);
ou moins violent :
SQL> grant exempt access policy to DEV1;
SQL> connect dev1/dev1
SQL> select * from emp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
—– ———- ——— ———- ——– ———- —– ——
7369 SMITH CLERK 7902 17/12/80 800 20
7499 ALLEN SALESMAN 7698 20/02/81 1600 300 30
7521 WARD SALESMAN 7698 22/02/81 1250 500 30
7566 JONES MANAGER 7839 02/04/81 2975 20
7654 MARTIN SALESMAN 7698 28/09/81 1250 1400 30
7698 BLAKE MANAGER 7839 01/05/81 2850 30
7782 CLARK MANAGER 7839 09/06/81 2450 10
7788 SCOTT ANALYST 7566 19/04/87 3000 20
7839 KING PRESIDENT 17/11/81 5000 10
7844 TURNER SALESMAN 7698 08/09/81 1500 0 30
7876 ADAMS CLERK 7788 23/05/87 1100 20
7900 JAMES CLERK 7698 03/12/81 950 30
7902 FORD ANALYST 7566 03/12/81 3000 20
7934 MILLER CLERK 7782 23/01/82 1300 10
1111 DEV1 Engineer 7782 01/01/07 2000 10

15 ligne(s) selectionnee(s).

L’erreur ORA-28112

l’utilisation des policies déclenche parfois une erreur ORA-28112 , même si les polices sont correctes et les packages aussi.
Voir le musée des erreurs - erreur ORA-28112 ” Failed to execute policy function ”

——-
Notes
——-

1) Pourquoi un compte SEC spécifique pour les objets nécessaire ? la VPD ?
- c’est + sécurisé, les données ne sont pas mélangées avec les règles d’accès
- c’est + facile a debugger : on peut soustraire ce compte aux règles des VPDs
- on évite les pbs d’accès récursif de la VPD

2) les parametres des fonctions de filtre sont OBLIGATOIRES !

3) Les DBAs soont normalement soumis aux stratégies comme tous les autres users.
Il est néanmoins possible d’outrepasser les VPDs
(pour le deboggage, pour l’administration, ou pour eviter les pbs de récursion)
SQL> grant exempt access policy to nom_user_cible;

4) une ORA-28112 = echec d’execution de la fonction de regle, lors du SELECT
==> la fonction est OK, mais il y a une exception qui n’est pas trappée par la fonction.
voir le fichier trace généré par l’erreur dans le répertoire USER_DUMP_DEST
pour résoudre le problème

5) vues du referentiel utiles
%_POLICIES, V$VPD_POLICY, RLS$, v$SQL

6) ne pas confondre les policies générales et les policies utilisées pour l’audit (Fine Grain Auditing).
Les packages, procédures et vues du dictionnaire ne sont pas les mêmes !
exemple : les packages DBMS_RLS (généraux) et DBMS_FGA (audit)

7) il n’existe pas de DISABLE_POLICY en 9i !!! mais un DBMS_RLS.ENABLE_POLICY avec ENABLE=….FALSE !!!

Les prémices

Le principe de base des accès au données Oracle, s’appelle le ‘Discretionary Access Control’ ou DAC.
Il s’appuie sur des privilèges d’accès aux objets donnés aux utilisateurs concernés, grace ? la commande SQL ‘GRANT’.
Ainsi on donne ? Mr Clark le droit de consulter la table EMP de SCOTT, par la commande
GRANT SELECT ON scott.EMP TO clark’.
Ce controle d’accès ne gère pas de niveau plus fin que la table complète : soit CLARk ? a l’accès ? la totalité de la table ou ? aucune de ses lignes…
Ceci peut être affiné en utilisant des vues.
On peut limiter l’accès ? un niveau plus fin, par exemple autoriser CLARK ? ne lire que la ou les lignes qui le concerne, en créant une vue avec prédicat :

SQL> CREATE VIEW v_emp AS SELECT * FROM scott.emp
WHERE ename=user;
SQL> CONNECT clark/clark
SQL> SELECT * FROM scott.v_emp;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7782 CLARK MANAGER 7839 09-JUN-81 2450 10

Ceci peut néanmoins dans certains être contourné si Mr Clark iu d’autres utilisateurs accèdent directement ? la table sous jacente EMP.
La sécurité au niveau ligne (RLS) ou l’accès au niveau fin (Fine Grain Access Control ou FGAC)

sont une solution complètement ttransparente pour tous les users, ? ce type de besoin.
Faire du RLS (FGAC) nécessite
- 1 fonction predicat, qui limite les accès ? certaines lignes
Cette fonction, a 2 paramètres obligatoires (le proprietaire et le nom de l’objet filtré) et retourne

une chaine qui contient une expression booleenne.
- 1 strategie (policy) attachée ? la table qui met en oeuvre automatiquement cette fonction.
cette strategie, a un nom et doirt preciser l’objet filtré et la fonction utilisée.
Les stratégies sont gérées par un package prédéfini appelé DBMS_RLS. Le créateur de la startégie doit

donc avoir les droits d’EXECUTE sur ce package.
Pour la création on utilisera par exemple DBMS_RLS.ADD_POLICY(…)

Dans un Schmé sécurité (compte SEC) cela nous donne :

création de la fonction prédicat et de la stratégie

SQL> CONNECT sec/sec
SQL> CREATE OR REPLACE PACKAGE filtre_emp
AS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2;
END;

SQL> CREATE OR REPLACE PACKAGE BODY filtre_emp
IS
FUNCTION vue_emp (owner VARCHAR2, objname VARCHAR2)
RETURN VARCHAR2
IS
predicat VARCHAR2(1000);
BEGIN
predicat := ‘ename=user’;
RETURN predicat;
END;
END;

– si nécessaire
execute DBMS_RLS.DROP_POLICY (’scott’, ‘emp’, ‘policy_emp’);
– et on crée donc…
execute DBMS_RLS.ADD_POLICY (’scott’,'emp’,'policy_emp’,’sec’,'filtre_emp.vue_emp’, ‘SELECT’);

Démontration du résultat de la stratégie mise en place

SQL> CONNECT clark/clark
SQL> SELECT COUNT(*) FROM scott.emp;
–> 1
SQL> CONNECT system/xxxx
SQL> SELECT COUNT(*) FROM scott.emp;
–> …
no row selected
car tout le monde est a priori sousmis ? la stratégie de sécurité !
y compris le DBA, et comme son nom d’utilisateur n’apparait pas dans la table EMP de SCOTT, le prédicat renvoie un résultat faux…et aucune ligne n’est affichée.

Nous verrons dans un prochain article comment créer une ébauche de VPD en associant ? cette stratégie un contexte utilisateur.

Certains ‘administrateurs’ : les opérateurs et techniciens d’exploitation, ou ‘exploitants’ pour faire court, n’ont pas forcément besoin d’un niveau de privilège DBA.

Les opérations concernés sont par exemple :

• les démarrage / arrêts,
• les sauvegardes / restaurations,
• la planification et l’exécution de batch

Oracle fournit 2 niveaux de privilèges, qui peuvent être assimilés ? des niveaux de connexion, qui satisfont ces besoins : les privilèges d’exploitation ‘SYSDBA’ et ‘SYSOPER’.
Comme tout accès privilégié il s’acquiert via un processus d’identification / authentification.

Authentification locale au niveau du système d’exploitation

C’est une forme d’authentification externe, en ce sens que ce n’est pas Oracle qui contrôle la connexion grace a son référentiel interne. On se connecte directement (via telnet ou ssh par exemple) au système qui héberge le serveur de données, puis a la base locale, sans plus faire intervenir le réseau.
Ce type de connexion originale ne nécessite pas d’identifiant ni de mot de passe Oracle, mais d’être un utilisateur privilégié au niveau O.S.

note : un utilisateur quelconque, non privilégié de la base, peut aussi être défini avec une authentificatin externe, et se connecter localement avec une commande du type : sqlplus /

On peut dire que dans ce cas la sécurité est déportée au niveau O.S. et qu’Oracle accorde sa ‘confiance’ aux mécanismes d’identification / authentification de ce dernier.

exemples de connexion avec le client SQL standard :
connexion ‘normale’
$sqlplus scott/tiger
connexion avec authentification externe
$sqlplus / as sysdba
$sqlplus / as sysoper

Pour obtenir un ‘privilège’ d’exploitation il suffit d’appartenir au groupe utilisateur correspondant au niveau système :

privilège  gpe unix    groupe windows
SYSDBA     dba         ORA_DBA
SYSOPER    oper        ORA_OPER

Ces groupes sont créés lors de l’installation, et un administrateur système en ‘hérite’ automatiquement

note : Le ‘CONNECT INTERNAL’ des versions précédentes est définitivement obsolète et a été remplacé par le ‘CONNECT SYS AS SYSDBA. Parallèlement il n’est plus possible de se connecter SYS ‘tout court’ sans préciser ‘AS SYSDBA’.

Authentification distante au niveau du système d’explotation

Elle présente les mêmes caractéristiques que précédemment sauf que la base est située sur une machine distante de la connexion système courante.

La syntaxe de connexion devient donc :
$ sqlplus /@nom_base_distante AS SYSDBA (ou SYSOPER)

Un paramètre d’initialisation de la base : REMOTE_OS_AUTJENTICATION=TRUE autorise cette fonctionnalité.

Note importante : il est vivement conseillé pour des raisons de sécurité d’invalider cette possibilité.

Authentification via fichier de mots de passe
Dans ce cas de figure, les privilèges seront controlés a partir d’un fichier de mot de passe cryptés local.
exemple de création du fichier :
$ ORAPWD FILE=monfic PASSWORD=monpasse ENTRIES=100
avec
PASSWORD : le mot de passe de SYS
ENTRIES : le nb mas d’utilisateurs référencables dans le fichier.

on peut ensuite créer un utilisateur TOTO avec mot de passe TUTU et que le DBA lui donne le privilège oracle (et non pas système cette fois) nécessaire : SYSDBA ou SYSOPER :

$> sqlplus / AS SYSDBA
SQL> CREATE USER TOTO IDENTIFIED BY TUTU;
SQL> GRANT CREATE SESSION TO TOTO (qu’il aie le droit de se connecter quand même…)
$ GRANT SYSDBA TO TOTO (et lui donner le privilège d’exploitation qui va bien)

note : le paramètre d’initialisation REMOTE_LOGIN_PASSWORDFILE doit être a EXCLUSIVE (c’est le défaut) pour pouvoir utiliser et modifier le password file

La notion d’utilisateur

Quels que soient l’architecture utilisée,le programme client, ou votre profil utilisateur : administrateur, développeur ou simple utilisateur final, l’accès aux données d’une base exige de se connecter ? un compte utilisateur.

note : les utilitaires système d’export, d’import, de sauvegarde, et de chargement par exemple impliquent également une connexion ? un compre utilisateur.

Un compte peut éventuellement contenir des données (tables principalement) on l’appelle dans ce cas un SCHEMA. Il peut également être verrouillé par l’administrateur.

Pour créér un utilisateur on utilisera soit du SQL : instruction CREATE USER…
soit un outil d’administration graphique comme la console OEM ou la console GRID.
Pour le supprimer on utilisera la commande :
DROP USER nom_user CASCADE pour supprimer également les données associées.

La description de tous les utilisateurs est donnée par la vue SYS.DBA_USERS ou pour un user non privilégié dans USER_USERS

Les principaux attributs d’un utilisateur sont les suivants :
- le nom
- la méthode d’authentification…et le mot de passe associé le cas échéant
- ses espaces logiques de travail : TABLESPACEs par défaut et temporaire
- ses quotas de création de structures données sur ses TABLESPACES permanents (optionnel)
- ses droits
- son PROFIL de consommation de ressources (optionnel)

note : un utilisateur final n’a pas besoin de quota, puisque’il ne créé pas de structure, mais tout au plus insère des données dans des structures existantes.
Il n’a besoin que de privilèges minimaux, celui de se connecter et de consulter voire mettre ? jour quelques tables d’un schéma.

Voir l’article sur les Roles et l’article sur les privilèges objets pour plus d’infos

En pratique dans les entreprises il existe un nombre limités de types d’utilisateurs. Ceci aura pour conséquence de pouvoir limiter sensiblement les ‘groupes d’utilisateur’.
On trouve, du moins privilégié au + privilégié :
- l’utilisateur d’infocentre (consultation de données uniquement)
- l’utilisateur d’application (consultation et mise ? jour)
- le responsable ou administrateur d’application (idem + gestion des utilisateurs et de l’accès aux fonctionnalités de l’application)
- le développeur (droit de cration de tables, vues, indexs, procédures stockées, triggers, séquences,…)
- le DBA (accès en lecture total au dictionnaire et droits de consultation mis ? jour de toutes les données utilisateurs, tous les droits sur la base)

Les ‘profils’ (PROFILE) Oracle

Ces ‘profils’ assez mal nommés, fixent des limites de consommation de ressouces (temps de session et de connexion, consommation mémoire partagée et E/S disque), la politique de mot de passe des utilisateurs (expiration et historique) et peuvent être comme les roles, partagés par un groupe d’utilisateurs.

Il existe 2 profils prédéfinis : DEFAULT, pours les users lambdas et MONITORING_PROFILE utilisé pour le compte DBSNMP.

SQL> SELECT * FROM DBA_PROFILES
WHERE profile=’DEFAULT’

DEFAULT COMPOSITE_LIMIT 	  KERNEL 	UNLIMITED
DEFAULT SESSIONS_PER_USER 	  KERNEL 	UNLIMITED
DEFAULT CPU_PER_SESSION 	  KERNEL 	UNLIMITED
DEFAULT CPU_PER_CALL 		  KERNEL 	UNLIMITED
DEFAULT LOGICAL_READS_PER_SESSION KERNEL 	UNLIMITED
DEFAULT LOGICAL_READS_PER_CALL 	  KERNEL 	UNLIMITED
DEFAULT IDLE_TIME 		  KERNEL 	UNLIMITED
DEFAULT CONNECT_TIME 		  KERNEL 	UNLIMITED
DEFAULT PRIVATE_SGA 		  KERNEL 	UNLIMITED
DEFAULT FAILED_LOGIN_ATTEMPTS 	  PASSWORD 	30
DEFAULT PASSWORD_LIFE_TIME 	  PASSWORD 	UNLIMITED
DEFAULT PASSWORD_REUSE_TIME 	  PASSWORD 	UNLIMITED
DEFAULT PASSWORD_REUSE_MAX 	  PASSWORD 	UNLIMITED
DEFAULT PASSWORD_VERIFY_FUNCTION  PASSWORD 	NULL
PROFILE RESOURCE_NAME 		  RESOURCE 	LIMIT
DEFAULT PASSWORD_LOCK_TIME        PASSWORD 	UNLIMITED
DEFAULT PASSWORD_GRACE_TIME 	  PASSWORD 	UNLIMITED

Ils permettent par exemple de limiter le nombre de sessions simultanées (très pratique en cas de développeurs trop enthousiastes !)SQL> CREATE PROFILE deux_sessions_max LIMIT
SESSIONS_PER_USER 2
SQL> ALTER USER dev1 PROFILE deux_sessions

Les utilisateurs prédéfinis d’Oraclel 10g

Essentiellement 2 catégories, les utilisateurs d’administration ou système :
- SYS : DBA et propriétaire du dictionnaire
- SYSTEM : DBA et propriétaire de qq vues système et outils
- SYSMAN : utilisateur de la console OEM ou GRID
- DBSNMP : utile pour Oracle AGent qui remonte des infos sur la base locale ? la console
- XDB, pour la BD XML
et les users/schémas de démo :
- le célèbre SCOTT et ses tables EMP et DEPT
- HR, schéma “ressources Humaine”, purement relation au niveau du modèle de données
- OE, schéma “gestion des commandes” au modèle relationnel / objet
- SH, schéma “ventes”, modèle relationnel en étoile
et des users génériques
PUBLIC et ANONYMOUS

La liste exhaustive des USERS prédéfinis est donnée par le SQL suivant (principales colonnes seulement):

SQL> SELECT username, account_status, lock_date, default_tablespace,
temporary_tablespace, profile
FROM DBA_USERS

USERNAME ACCOUNT_STATUS DEFAULT_TABLESPACE TEMPORARY_TABLESPACE PROFILE
——– ————– —————— ——————– ——-
SYS OPEN SYSTEM TEMP DEFAULT
SYSTEM OPEN SYSTEM TEMP DEFAULT
DBSNMP OPEN SYSAUX TEMP MONITORING_PROFILE
SYSMAN OPEN SYSAUX TEMP DEFAULT
SCOTT OPEN USERS TEMP DEFAULT
…

note : il est vivement conseillé de se connecter au minimum dans SYS ou SYSTEM. On créera un compte avec le role ‘DBA’ pour l’administration.
Il est également conseillé de verrouiller les comptes non utilisés et de changer leur mot de passe par défaut car ils peuvent présenter des failles de sécurité.

Les méthodes d’authentification

Pour assurer la confidentialitté d’accès aux données des utilisateurs, on doit s’authentifier pour se connecter ? la base de données.
Il existe 3 formes différentes d’authentification : par mot de passe, externe ou globale.

1) authentification par mot de passe
C’est une authentification classique, et répandue qui utilise un mot de passe crypté, stocké localement dans la table des utilisateurs de la base.

SQL> SELECT username, password FROM dba_users;

USERNAME PASSWORD
——– —————-
SYS 4C1C01757062C16D
SYSTEM D4DF7931AB130E37
DD EXTERNAL
SCOTT F894844C34402B67

2) authentification externe

Oracle offre une possibilité de se connecter sans fournir explicitement le mot de passe du compte. Ceci ne veut pas dire que le compte n’est pas protégé, mais que la protection est déporté au niveau de l’OS, ou en d’autres termes que le SGBD ‘fait confiance’ au système d’authentification de l’OS qui le porte. Pour que ceci soit possible il faut

- que l’utilisateur, soit défini comme authentifié de manière externe,
- que le nom du compte aie pour suffixe le nom du compte de l’OS et pour préfixe un préfixe générique défini dans les paramètres d’initialisation de la base (init.ora ou spfile) par la variable OS_AUTHENT_PREFIX.
Par défaut OS_AUTHENT_PREFIX vaut ‘OPS$’.
- que l’utilisateur se connecte ? Oracle en ne fournissant ni nom ni mot de passe mais simplement le séparateur ‘/’.
exemple :
— creation du user par le DBA
SQL> CREATE USER OPS$DD IDENTIFIED EXTERNALLY
– on pourra ensuite (moyennant que l’utilisateur aie au moins
– un privilège ‘CREATE SESSION’…) se connecter en externe :
– connexion ? l’OS
login: DD
pwd : *******
– connexion externe dans le compte OS
$> sqlplus /
SQL> user OPS$DD connected…

note : l’authentification externe permet d’une certaine façon de sécuriser les scripts Shell ou d’une manière générale les batchs, car le mot de passe Oracle n’apparait plus en clair dans les scripts

3) authentification globale

… ? compléter…

Quelques exemples de commandes SQL

Autant que faire se peut on utilisera la console, mais ca peut dépanner de connaitre un minimum de SQL (ca peut aussi épater les filles…mais lequelles?)

SQL> CREATE USER TOTO IDENTIFIED BY TUTU;
– créé un user toto avec mot de passe tutu
SQL> DROP USER TOTO
– supprime icelui
SQL> ALTER USER tete IDENTIFIED BY tata
– on modifie le mot de passe
SQL> CREATE USER dev1 IDENTIFIED BY xxx
DEFAULT TABLESPACE hr
TEMPORARY TABLESPACE temp
QUOTA 100M ON hr
PROFILE profile_standard_de_ma_societe

SQL> GRANT CREATE SESSION to dev1;
SQL> CREATE ROLE role_developpeur_standard;
– ce role sera partagé par tous les développeurs
SQL> GRANT CREATE TABLE, CREATE VIEW, CREATE INDEX,
CREATE PROCEDURE, CREATE SEQUENCE TO role_developpeur_standard;
SQL> GRANT ROLE role_developpeur_standard TO dev1 WITH GRANT OPTION;
– on donne le droit de crééer des objets standards
SQL> GRANT CREATE TRIGGER TO dev1;
– on rajoute un droit spécifique ? DEV1…

Les rôles
———

Il n’existe pas de notion de groupes d’utilisateur sous Oracle, mais la notion de rôle, qui permet de nommer un

groupe de privilèges. On peut affecter un rôle ? un ou n utilisateurs, voir ? un rôle.

Roles prédéfinis
—————-

Il existe un certain nombre de rôles prédéfinis, fournis avec Oracle 10g. Voici une liste de ces rôles avec les

privilèges système qu’ils offrent :
CONNECT :
Se connecter ! équivallent du privilède système ‘CREATE SESSION’

Attention !!! en version 10gR1 et antérieures, ce rôle donnait beaucoup + de privilèges, ? savoir :
ALTER SESSION, CREATE CLUSTER, CREATE DATABASE LINK, CREATE SEQUENCE,
CREATE SESSION, CREATE SYNONYM, CREATE TABLE, CREATE VIEW
RESOURCE :
créer des données avec des quotas sur tous les tablespaces ->
CREATE CLUSTER, CREATE INDEXTYPE, CREATE OPERATOR, CREATE PROCEDURE,
CREATE SEQUENCE, CREATE TABLE, CREATE TRIGGER, CREATE TYPE

DBA :
Tous les privilèges système avec ADMIN OPTION

EXP_FULL_DATABASE :
export complet ou incrémental ->
SELECT ANY TABLE, BACKUP ANY TABLE, EXECUTE ANY PROCEDURE, EXECUTE ANY TYPE, ADMINISTER RESOURCE MANAGER, and

INSERT, DELETE, and UPDATE on the tables SYS.INCVID, SYS.INCFIL, and SYS.INCEXP. Also the following roles:

EXECUTE_CATALOG_ROLE and SELECT_CATALOG_ROLE.

IMP_FULL_DATABASE :
import full + tous les privilèges système + role

DELETE_CATALOG_ROLE :
droit DELETE sur la table SYS.AUD$

EXECUTE_CATALOG_ROLE : privilège EXECUTE sur les objets du dictionnaire + HS_ADMIN_ROLE.

SELECT_CATALOG_ROLE : accès en consultation au dictionnaire + HS_ADMIN_ROLE.

RECOVERY_CATALOG_OWNER : privilèges pour le propriétaire du catalogue de restauration ->
CREATE SESSION, ALTER SESSION, CREATE SYNONYM, CREATE VIEW, CREATE DATABASE LINK,
CREATE TABLE, CREATE CLUSTER, CREATE SEQUENCE, CREATE TRIGGER, and CREATE PROCEDURE

HS_ADMIN_ROLE
protection des accès (SELECT et EXECUTE) au référentiel HS (Heterogeneous Services) data dictionary tables

(grants SELECT) and packages
AQ_ADMINISTRATOR_ROLE
privilèges d’administration de l’ Advance Queuing. Notamment : ENQUEUE ANY QUEUE, DEQUEUE ANY QUEUE, et MANAGE

ANY QUEUE + SELECT sur les tables AQ + EXECUTE sur les packages AQ.

roles applicatifs
—————–
Des roles définis pour les applicatifs, en fonction des besoins et ? minima !
Ainsi on peut créer un rôle qui donne des droits de consultation sur les tables d’un schéma :

– creation du role
SQL> create role consult_finance;
– affectation des privileges au role
SQL> grant select on budget to consult_finance;
SQL> grant select on fournisseur to consult_finance;
SQL> grant select on client to consult_finance;
– cession du role aux utilisateurs
SQL> grant consult_finance to user_finance_1;
SQL> grant consult_finance to user_finance_2;

Note : un user ne peut pas hériter de plus de ‘MAX_ENABLED_ROLES’ paramètre d’initialisation de la base, dont la valeur par défaut est 30 en 10g. CEla parait peu probable pour un applicatif classique (voire déraisonnable).
Dans le cas d’une console d’administration centrale (console 10g GRID ou server manager console). Le user d’administration cionnecté hérite de tous les droits qu’il crée…ce qui est beaucoup plus fréquent ! et la limite MAX_ENABLED_ROLES peut être facilement atteinte et … empêcher une connexion ? la console !
Informations sur les rôles dans le référentiel (dictionnaire) Oracle 10g
————————————————————————

DBA_ROLES
Tous les roles !
DBA_ROLE_PRIVS, USER_ROLE_PRIVS
roles données aux users et/ou aux roles
ROLE_ROLE_PRIVS
roles donnés aux roles
ROLE_SYS_PRIVS
privilèges systèmes donnés aux roles (accessible au user)
ROLE_TAB_PRIVS
privilèges objets donnés aux roles (accessible au user)
SESSION_PRIVS
privilèges de la session courante
SESSION_ROLES
roles de la session courante

Les roles et la console GRID 10g
———————————

La console donne par defaut le role CONNECT, cela ne pose pas de pb de sécurité sur une base 10g…mais si on s’en sert pour administrer une base 9i (avec l’agent qui va bien) le role CONNECT offre presque tous les privilèges de création/suppression de données dans le schéma.

————————————————–
– une vue de BLAKE sur une table de SCOTT
————————————————–
connect scott
(scott a les droits sur la table)
create view vscott as select * from emp

connect blake
select * from scott.vscott
*
ERROR at line 1:
ORA-00942: table or view does not exist

connect scott
grant select on emp to blake

…le droit sur la table sert pour accéder …a la table
connect blake
select * from scott.vscott
*
ERROR at line 1:
ORA-00942: table or view does not exist

…Le droit sur la vue suffit
connect scott
grant select on vscott to blake

connect blake
select * from scott.vscott
–> OK

———————————————————
—KING accede a une vue de BLAKE sur une table de SCOTT
———————————————————
connect SYSTEM
grant select on scott.emp to blake;
create view blake.vblake as select * from scott.emp
connect SYSTEM
SQL> grant select on blake.vblake to king;
Autorisation de privilèges (GRANT) acceptée.
connect KING
select * from blake.vblake
*
ERROR at line 1:
ORA-01031: insufficient privileges

Si on avait tenté de donner les droits en tant que BLAKE
cela aurait été plus clair :
connect blake
grant select on blake.vblake to king
*
ERROR at line 1:
ORA-01720: grant option does not exist for ‘SCOTT.EMP’

LA SOLUTION
connect SCOTT (ou SYSTEM)
grant select on scott.emp to blake WITH GRANT OPTION

Pour des raisons de sécurité évidentes, un utilisateur autre que DBA n’a aucun droit a priori….même pas celui de se connecter ? la base de données !

Droits implicites

Par contre, le créateur d’un objet (TABLE, VUE , INDEX, etc.) est son propriétaire,et a implicitement des droits sur cet objet. Il possède tous les droits sur son contenu : consultation, mises ? jour, mais aussi suppression complète de sa structure (le contenant).

– exemple

– on suppose que l’utilisateur DD a reçu le droit de se connecter et de creer des tables…
SQL> connect DD/DD
SQL> create table essai (n integer);
Table created
– droit d’insertion (mais aussi suppression et modification) implicite
SQL> insert into essai values (1);
1 ligne créée.
SQL> commit;
Validation effectuée.
– a fortiori droit de consultation
SQL> select * from essai;
N
———-
1
– suppression de la table et de son contenu
SQL> drop table essai;
Table supprimée.Par contre un autre utilisateur (s’il n’est pas DBA) n’a a priori aucun droit sur les tables des autres:

– exemple

– on suppose que DD possede une table T1…
SQL> connect toto/toto
SQL> select * from dd.t1;
select * from dd.t1
*
ERREUR ? la ligne 1 :
ORA-00942: table or view does not exist

Droits explicites - GRANT et REVOKE

GRANT et REVOKE permettent respectivement de donner ou de supprimer les droits explicites d’accès en lecture ou

mise ? jour ? un utilisateur particulier, pour un objet particulier.
C’est en général le propriétaire de l’objet peut donner des droits d’accès ? un autrer utilisateur.

– exemples:

SQL> GRANT SELECT ON TAB_CLIENTS TO MARTIN
SQL> GRANT UPDATE, INSERT ON TAB_CLIENTS TO DUPONTpar défaut un DBA ne peut pas donner des droits d’accès ? un objet qui ne lui appartient pas. Le propriétaire doit lui céder les drois avec un grant option (!?) :

SQL> connect system/xxxx
Connected.
SQL> grant select on intranet.annuaire to scott;
ERROR at line 1: ORA-01031: insufficient privilegesLe type de privilège dépend évidemment de l’objet sur lequel il s’applique. Ceci est résumé ici :

SELECT (uniquement !) -> les séquences

SELECT, INSERT, UPDATE, DELETE -> les tables, les vues, les vues matérialisées

REFERENCE (possibilité de créer une clé étrangère sur la table) -> les tables et les vues

EXECUTE -> les procédures

INDEX, ON COMMIT REFRESH, QUERY REWRITE -> les tables

Il est possible de donner un privilège avec le droit de transférer ce privilège ? d’autres (? utiliser avec parcimonie). Ceci se fait avec l’otion ‘GRANT’ du GRANT (!) :GRANT SELECT ON TAB_CLIENTS TO DUPONT WITH GRANT OPTION
(on suppose que TAB_CLIENTS est un synonyme public visible par tout le monde). Dupont, même s’il n’est pas propriétaire pourra alors ? son tour faire :
GRANT SELECT ON TAB_CLIENTS TO MARTIN

une erreur très répandue consiste ? référencer un objet (dont on n’est pas proprétaire et sur lequel il n’y pas de synonyme) par son nom, SANS le préfixer par le nom du propriétaire. exemple : SELECT * from EMP au lieu de

SELECT * FROM SCOTT.EMP
ceci donne une erreur d’accès du type

ERROR at line 1:
ORA-00942: table or view does not exist (?!)

même si on a les droits de lecture…

DBA et droits Oracle (privilèges et roles)

Un utilisateur Oracle (déclaré au sein de la base, ? distinguer de l’utilisateur au niveau OS) peut être DBA.
Il a tous les ‘privilèges système’ AU SEIN DE LA BASE, et le droit de les transmettre (ADMIN OPTION)
Grace ? quoi, il peut essentiellement :
* consulter et mettre ? jour (SELECT, UPDATE, INSERT, DELETE) toutes les données utilisateur de la base
* créer, modifier des structures de données utilisateur (CREATE, ALTER, DROP) n’importe ou (ANY TABLESPACE)
* gérer des utilisateurs et des droits (CREATE/DROP USER, GRANT, REVOKE)
* consulter la totalité du dictionnaire
* exécuter des ordres d’administration purs (CREATE DATABASE, DATAFILE, TABLESPACE)

Il y a 2 utilisateurs privilégiés prédéfinis, SYS et SYSTEM (dont les mots de passe sont définis ? la création de la base ou par ‘ORAPWD’)
Ils sont tous les 2 DBA, mais SYS est plus privilégié en ce sens qu’il est propriétaire des tables et vues du dictionnaire.

Il existe un ensemble de privilège (ROLE) prédéfini nommé ‘DBA’ qui donne les privilèges nécessaire ? un DBA.
Après avoir créé un utilisateur ‘normal’ il suffit de lui donner ce rôle pour en faire un DBA :
SQL> GRANT DBA TO

note : Il existe un autre rôle prédéfini, parmi quelques dizaines, qui est également intéressant c’est le role ‘SELECT_CATALOG_ROLE’. Il est souvent utilisé par des progiciels ou applicatifs utilisant Oracle pour récupérer des méta données.

Privilèges d’exploitation (SYSDBA et SYSOPER)

Certains ‘administrateurs’ : les opérateurs et techniciens d’exploitation, ou ‘exploitants’ pour faire court, n’ont pas forcément besoin d’un niveau de privilège DBA.

Les opérations concernés sont par exemple :

* les démarrage / arrêts,

* les sauvegardes / restaurations,

* la planification et l’exécution de batch

Oracle fournit 2 niveaux de privilèges, qui peuvent être assimilés ? des niveuax de connexion, qui satisfont ces besoins : les privilèges d’exploitation ‘SYSDBA’ et ‘SYSOPER’.
Comme tout accès privilégié il s’acquiert via un processus d’identification / authentification.

Authentification locale au niveau du système d’explotation

C’est une forme d’authentification externe, en ce sens que ce n’est pas Oracle qui contrôle la connexion grace ? son référentiel interne. On se connecte directement (via telnet ou ssh par exemple) au système qui héberge le serveur de données, puis ? la base locale, sans plus faire intervenir le réseau.
Ce type de connexion originale ne nécessite pas d’identifiant ni de mot de passe Oracle, mais d’être un utilisateur privilégié au niveau O.S.

note : un utilisateur quelconque, non privilégié de la base, peut aussi être défini avec une authentificatin externe, et se connecter localement avec une commande du type : sqlplus /

On peut dire que dans ce cas la sécrité est déportée au niveau O.S. et qu’Oracle accorde sa ‘confiance’ aux mécanismes d’identification / authentification de ce dernier.

exemples de connexion avec le client SQL standard :
connexion ‘normale’
$sqlplus scott/tiger
connexion avec authentification externe
$sqlplus / as sysdba
$sqlplus / as sysoper

Pour obtenir un ‘privilège’ d’exploitation il suffit d’appartenir au groupe utilsateur correspondant au niveau système :

privilège  gpe unix    groupe windows
SYSDBA     dba         ORA_DBA
SYSOPER    oper        ORA_OPER

Ces groupes sont créés lors de l’installation, et un administrateur système en ‘hérite’ automatiquement

note : Le ‘CONNECT INTERNAL’ des versions précédentes est définitivement obsolète et a été remplacé par le ‘CONNECT SYS AS SYSDBA. Parallèlement il n’est plus possible de se connecter SYS ‘tout court’ sans préciser ‘AS SYSDBA’.

Authentification distante au niveau du système d’explotation

Elle présente les mêmes caratéristiques que précédemment saus que la base est située sur une machine distate de la connexion système courante.

La syntaxe de connexion devient donc :
$ sqlplus /@nom_base_distante AS SYSDBA (ou SYSOPER)

Un paramètre d’initialisation de la base : REMOTE_OS_AUTJENTICATION=TRUE autorise cette fonctionnalité.

Note importante : il est vivement conseillé pour des raisons de sécurité d’invalité cette possibilité.

Authentification via fichier de mots de passe
Dans ce cas de figure, les privilèges seront controlés ? partir d’un fichier de mot de passe cryptés local.
exemple de création du fichier :
$ ORAPWD FILE=monfic PASSWORD=monpasse ENTRIES=100
avec
PASSWORD : le mot de passe de SYS
ENTRIES : le nb mas d’utilisateurs référencables dans le fichier.

on peut ensuite créer un utilisateur TOTO avec mot de passe TUTU et que le DBA lui donne le privilège oracle (et non pas système cette fois) nécessaire : SYSDBA ou SYSOPER :

$> sqlplus / AS SYSDBA
SQL> CREATE USER TOTO IDENTIFIED BY TUTU;
SQL> GRANT CREATE SESSION TO TOTO (qu’il aie le droit de se connecter quand même…)
$ GRANT SYSDBA TO TOTO (et lui donner le privilège d’exploitation qui va bien)

note : le paramètre d’initialisation REMOTE_LOGIN_PASSWORDFILE doit être ? EXCLUSIVE (c’est le défaut) pour pouvoir utiliser et modifier le password file

DBA et droits niveau OS (système d’exploitation)

La fonction de DBA, nécessite des privilèges au niveau Système d’exploitation :
- pour l’installation,
- la maintenance,
- la gestion et l’execution de batchs, de scripts (SQL ou shell),
- les sauvegardes / restauration

Sur Unix / Linux :
Il existe un user Unix nommé ‘oracle’ et un groupe associé nommé ‘dba’.
Tous les fichiers Oracle, appartiennent ? ‘utilisateur Oracle.

On peut (doit ?) crééer autant d’utilisateur Unix que de DBAs dans l’entreprise ; dba1, dba2, appartenant au groupe ‘dba’.Ceci permet d’éviter les recouvrements et d’avoir une meilleure tracabilité.
On évitera de travailler connecté en tant qu’utilisateur ‘oracle’ pour éviter toute erreur de manipulation des fichiers Oracle.

Note : les programmes et processus, qui constituent le coeur d’Oracle, s’executent en tant qu’oracle, et ont conséquemment les droits nécessaires pour écrire dans les fichiers de données, journaux, archives, etc.

Le DBA et le super utilisateur ‘root’ :
lors de l’installation, il est nécessaire d’écrire dans certains répertoires protégés du système (/etc par exemple) ou d’exécuter certaines taches privilégiées.
Cependant l’installation se fait bien en tant qu’Oracle, et l’installeur demande simplement le privilège root pendant la période nécessaire ? ces opérations.
Il execute 2 scripts autonomes root.sh et rootpre.sh, en tant que root.
Il retourne ensuite en mode ‘normal’.
En production les no de ports TCP/IP utilisés par Oracle 10g sont tous > 1024, et ne nécessitent donc pas de privilèges particuliers.

Sur Windows :
Le principe est plus simple. L’install se fait en général en tant qu’administrateur système.