Introduction
Oracle permet de créer des triggers sur les opérations DML suivantes : INSERT, UPDATE, DELETE mais pas SELECT. Nous allons voir comment contourner cette restriction avec un audit de granularité fine créé via le package DBMS_FGA, et, lorsque l'audit se déclenchera, celui-ci appellera une procédure stockée.

Un SELECT qui exécute du code PL/SQL : cela ressemble fortement à un trigger sur SELECT, non? 
Attention, c'est un pseudo-trigger, de niveau Statement et pas de niveau Row : si le SELECT ramène 100 lignes, la procédure ne sera exécutée que une fois. A noter l'absence de traitement des variables OLD et NEW mais, pour un SELECT, cela n'a pas de sens :-)



Points d'attention
Aucun.



Base de tests
Une base Oracle 12c.



Exemples
Oracle refuse de créer un trigger sur SELECT.
     SQL> CREATE OR REPLACE TRIGGER TRIG01 before SELECT ON EMPLOYEES
     BEGIN
          null;
     END;
     /
     CREATE OR REPLACE TRIGGER TRIG01 before SELECT ON EMPLOYEES
     *
     ERROR at line 1:
     ORA-04072: invalid trigger type

Création de l'environnement de test
Création d'une table d'audit qui servira à tester le trigger.
     SQL> create table AUDIT_SELECT(ID TIMESTAMP, AUDIT_SCHEMA VARCHAR2(30), AUDIT_OBJECT VARCHAR2(30), AUDIT_POLICY VARCHAR2(30), AUDIT_COMMENT VARCHAR2(4000));
     Table created.     


Création de la police d'audit de granularité fine; l'objectif est de déclencher la procédure stockée HR.PROC_AUDIT_SELECT_SALARY lors des SELECT sur la colonne SALARY de la table HR.EMPLOYEES. Le point clé ici est l'utilisation du paramètre handler_module qui permet justement d'appeler une procédure stockée.
     BEGIN

         DBMS_FGA.ADD_POLICY (
             object_schema      =>  'HR',
             object_name        =>  'EMPLOYEES',
             policy_name        =>  'AUDIT_SALARY',
             audit_column       =>  'SALARY',
             statement_types    =>  'SELECT',
             audit_trail        =>   DBMS_FGA.DB + DBMS_FGA.EXTENDED,
             handler_schema     =>   'HR',
             handler_module     =>   'PROC_AUDIT_SELECT_SALARY',
             audit_column_opts  =>   DBMS_FGA.ANY_COLUMNS);
     END;
     /
     PL/SQL procedure successfully completed.   

Création de la procédure stockée. Son code est basique, on fait un INSERT dans une table juste pour notre démonstration.
     SQL> CREATE OR REPLACE PROCEDURE PROC_AUDIT_SELECT_SALARY (p_schema varchar2, p_object varchar2, p_audit_policy varchar2) AS
     BEGIN
         INSERT INTO AUDIT_SELECT VALUES ((SELECT systimestamp FROM dual), p_schema, p_object, p_audit_policy, 'TEST TRIGGER SUR SELECT');
         COMMIT;                
     END;
     /
     Procedure created.
     
Vérification que la procédure seule fonctionne bien : c'est OK, la table AUDIT_SELECT est bien renseignée.
     SQL> exec PROC_AUDIT_SELECT_SALARY('SCHEMA', 'OBJET', 'POLICE');
     PL/SQL procedure successfully completed.
     
     SQL> select * from AUDIT_SELECT;
     ID  AUDIT_SCHE AUDIT_OBJE AUDIT_POLICY  AUDIT_COMMENT
     ---------------------------------------- -------------
     19-JUN-18 04.02.21.253649 AM   SCHEMA  OBJET  POLICE  TEST TRIGGER SUR SELECT
    
     SQL> truncate table AUDIT_SELECT;
     Table truncated.
    
Je multiplie les salaires de la base de test Oracle par 5 pour mon test, pour avoir des montants plus réalistes en 2018.
     SQL> select max (salary) from employees;

     MAX(SALARY)
     -----------
           24000
     
     SQL> update employees set salary = salary*5;
     107 rows updated.
     
     SQL> commit;
     Commit complete.

           

Tests du trigger
     SQL> select count(*) from employees;
       COUNT(*)
     ----------
            107
     
SELECT sans clause WHERE avec SALARY : la table d'audit n'est pas renseignée.       
     SQL> select * from AUDIT_SELECT;
     no rows selected
     
SELECT avec clause WHERE avec SALARY : la table AUDIT_SELECT est cette fois bien renseignée --> notre SELECT a déclenché la police d'audit qui a son tour a exécuté la procédure stockée; nous avons donc bien simulé un trigger sur SELECT  :-)
     SQL> select count(*) from employees where salary > 10000;
       COUNT(*)
     ----------
            107
     
     SQL> select * from AUDIT_SELECT;
     ID  AUDIT_SCHE AUDIT_OBJE AUDIT_POLICY  AUDIT_COMMENT
     ---------------------------------------- ------------
     19-JUN-18 04.47.48.115518 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT

Autre test : OK également.

     SQL> select count(*) from employees where salary = 50000;
       COUNT(*)
     ----------
          4
     
     SQL> select * from AUDIT_SELECT;
     ID   AUDIT_SCHE AUDIT_OBJE AUDIT_POLICY  AUDIT_COMMENT
     ---------------------------------------- ---------- ----
     19-JUN-18 04.47.48.115518 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT
     19-JUN-18 04.48.07.573438 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT
     
Un SELECT sur une autre colonne que SALARY ne déclenche pas la procédure stockée.
     SQL> select count(*) from employees where first_name = 'KING';
       COUNT(*)
     ----------
          0
     
     SQL> select * from AUDIT_SELECT;
     ID   AUDIT_SCHE AUDIT_OBJE AUDIT_POLICY  AUDIT_COMMENT
     ---------------------------------------- ---------- --
     19-JUN-18 04.47.48.115518 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT
     19-JUN-18 04.48.07.573438 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT
     
On voit bien que c'est un trigger de niveau statement : le SELECT est exécuté une fois, ramène quatre lignes mais il y a un seul INSERT en plus dans la table d'audit.      
     SQL> select first_name from employees where salary = 50000;
     FIRST_NAME
     --------------------
     Peter
     Janette
     Harrison
     Hermann
     
     SQL> select * from AUDIT_SELECT;
     ID  AUDIT_SCHE AUDIT_OBJE AUDIT_POLICY  AUDIT_COMMENT
     ---------------------------------------- ---------- --
     19-JUN-18 04.47.48.115518 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT
     19-JUN-18 04.48.07.573438 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT
     19-JUN-18 04.55.22.366881 AM  HR  EMPLOYEES  AUDIT_SALARY  TEST TRIGGER SUR SELECT


Nous avons réussi notre démonstration : un trigger sur SELECT peut se mettre en place via le package PL/SQL DBMS_FGA.     
     

Terminons avec la suppression de la police d'audit FGA.      
     BEGIN
         DBMS_FGA.DROP_POLICY (
         object_schema   =>  'HR',
         object_name     =>  'EMPLOYEES',
         policy_name     =>  'AUDIT_SALARY');
     END;
     /
     PL/SQL procedure successfully completed.