Pour aller dans le sens de n’octroyer que les privilèges nécessaires et pas plus… il est aussi nécessaire de révoquer tous les privilèges inutilisés.
Le package DBMS_PRIVILEGE_CAPTURE, disponible depuis la version 12c d’Oracle Database, permet d’auditer les privilèges utilisés; cette fonctionnalité est référencée sous l’appellation « Privilege Analysis ».
L’audit peut porter sur des privilèges objet, système ou des rôles.
Il suffit ensuite de générer un rapport d’analyse donnant la liste des privilèges sollicités ou non durant la période d’analyse.

La fonctionalité « Privilege Analysis » est disponible seulement en EE avec l’option Database Vault : https://docs.oracle.com/cd/E50790_01/doc/license.121/e17614.pdf

1 – Définir le type de l’analyse : DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE

Analyse au niveau base de données : Si aucune condition n’est spécifiée, l’analyse porte sur les privilèges utilisés (à l’exception des privilèges utilisés par les administrateurs) dans l’intégralité de la base de données.

BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
 name => 'TOUTE_LA_BASE_policy',
 description => 'Surveillance de tous les privileges de la base',
 type => DBMS_PRIVILEGE_CAPTURE.G_DATABASE);
END;
/

Analyse des rôles : Si les rôles sont définis, l’analyse porte sur les privilèges exercés par l’intermédiaire d’un rôle donné.
N.B. : Les privilèges octroyés à « PUBLIC » peuvent être suivis comme si c’était un rôle.

BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
 name => 'SELECT_CATALOG_ROLE_policy',
 description => 'Surveillance du role SELECT_CATALOG_ROLE',
 type => DBMS_PRIVILEGE_CAPTURE.G_ROLE,
 roles => role_name_list('SELECT_CATALOG_ROLE'));
END;
/

Analyse propre au contexte : Si les contextes sont définis, l’analyse porte sur les privilèges utilisés dans un module d’application donné ou dans des contextes spécifiés.
Différentes conditions peuvent être associées avec les opérateurs booléens AND et/ou OR.

BEGIN
DBMS_PRIVILEGE_CAPTURE.CREATE_CAPTURE(
 name => 'USER_HR_policy',
 type => DBMS_PRIVILEGE_CAPTURE.G_CONTEXT,
 condition => 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''HR''');
END;
/

2 – Activation de l’audit : DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE

Comme la stratégie créée n’est pas activée par défaut, l’étape suivante consiste à procéder à l’activation afin de lancer l’analyse des privilèges utilisés.

BEGIN
DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(
 name => 'USER_HR_policy');
END;
/
BEGIN
DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(
 name => 'SELECT_CATALOG_ROLE_policy');
END;
/
BEGIN
DBMS_PRIVILEGE_CAPTURE.ENABLE_CAPTURE(
 name => 'TOUTE_LA_BASE_policy');
END;
/

3 – Laisser les utilisateurs « travailler »

sqlplus HR/xxx
DROP SEQUENCE seq1;
CREATE SEQUENCE seq1;
SELECT * FROM oe.orders WHERE ROWNUM = 1;
SELECT * FROM v$tablespace;

4 – Stopper l’analyse DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE

BEGIN
 DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(
 name => 'USER_HR_policy');
 END;
 /
 BEGIN
 DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(
 name => 'SELECT_CATALOG_ROLE_policy');
 END;
 /
 BEGIN
 DBMS_PRIVILEGE_CAPTURE.DISABLE_CAPTURE(
 name => 'TOUTE_LA_BASE_policy');
 END;
 /

5 – Générer le rapport d’analyse : DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT

BEGIN
 DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT (
 name => 'USER_HR_policy');
 END;
 /
 BEGIN
 DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(
 name => 'SELECT_CATALOG_ROLE_policy');
 END;
 /
 BEGIN
 DBMS_PRIVILEGE_CAPTURE.GENERATE_RESULT(
 name => 'TOUTE_LA_BASE_policy');
 END;
 /

6 – Consulter le résultat de l’analyse

Les privilèges utilisés sont visibles dans les vues DBA_USED_xxx et DBA_USED_xxx_PATH

 DBA_USED_PUBPRIVS
 DBA_USED_OBJPRIVS
 DBA_USED_SYSPRIVS
 DBA_USED_PRIVS
 DBA_USED_OBJPRIVS_PATH
 DBA_USED_SYSPRIVS_PATH

Les privilèges inutilisés sont visibles dans les vues DBA_UNUSED_xxx et DBA_UNUSED_xxx_PATH.

 DBA_UNUSED_OBJPRIVS
 DBA_UNUSED_SYSPRIVS
 DBA_UNUSED_PRIVS
 DBA_UNUSED_OBJPRIVS_PATH
 DBA_UNUSED_SYSPRIVS_PATH

Exemple de résultat

 SET lines 350
 SET pages 500
 COL username FORMAT A9
 COL used_role FORMAT A20
 COL object_name FORMAT A30
 COL object_type FORMAT A12
 COL capture FORMAT A30
 COL os_user FORMAT A10
 COL userhost FORMAT A10
 COL module FORMAT A30
 COL object_owner FORMAT A10
 COL column_name FORMAT A10
 COL obj_priv FORMAT A10
 COL sys_priv FORMAT A10
 COL path FORMAT A60
 COL rolename FORMAT A25
SQL> SELECT USERNAME, OBJ_PRIV, USED_ROLE, OBJECT_OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_USED_OBJPRIVS WHERE USERNAME = 'HR' ;
 USERNAME OBJ_PRIV USED_ROLE OBJECT_OWN OBJECT_NAME OBJECT_TYPE
 --------- ---------- -------------------- ---------- ------------------------------ ------------
 HR SELECT SELECT_CATALOG_ROLE SYS V_$TABLESPACE VIEW