Ce message ne sera pas long ; je suis déçu ! Celui ou celle qui vous a laissé miroiter que DBMS_FGA permet de créer des triggers sur des SELECT avait tord, intentionnellement ou non. Le pire, c’est que je fais parti de ceux qui l’on cru. Je m’explique…

Voici comment mettre en œuvre un exemple simple avec DBMS_FGA pour traquer les programmes qui font des SELECT ou des ordres DML sur une table DEMO.

Étape 1 : Un schéma exemple

Pour les besoins de l’exemple, créez un schéma DEMO et une table DEMO comme ci-dessous :

sqlplus / as sysdba

create user demo
identified by demo
default tablespace users
temporary tablespace temp;

grant connect, resource to demo;

grant select on v_$session to demo;

connect demo/demo

create table demo(id number);

exit;

Étape 2 : Une table pour traquer les SELECT et une procédure pour l’alimenter

Pour les besoins de l’exemple on va créer une table AUDIT_DEMO qui contiendra l’heure, ainsi que le programme qui accède à la table DEMO. Remarquez que je fais exprès de choisit le PROGRAM puisque ce n’est pas une information qui est traquée dans la table d’audit du système et qu’il faut donc écrire du code pour capturer cette information.

sqlplus / as sysdba

create table demo.audit_demo (
            sdate   date,
            otype   number,
            program varchar2(50));

Maintenant nous allons créer un package PL/SQL qui va retrouver les informations de la session comme le type de l’ordre ou le PROGRAM associée. Comme ce package devra s’exécuter à l’extérieur de la transaction en cours, nous alons positionner le « PRAGMA AUTONOMOUS_TRANSACTION » comme ci-dessous :

create or replace package demo.demo_fga_handler is
PROCEDURE track_demo_access( object_schema VARCHAR2,
                             object_name   VARCHAR2,
                             policy_name VARCHAR2 );
end;
/
show errors

create or replace package body demo.demo_fga_handler as
PROCEDURE track_demo_access( object_schema VARCHAR2,
                             object_name   VARCHAR2,
                             policy_name VARCHAR2 )
is
  PRAGMA AUTONOMOUS_TRANSACTION;
begin
  insert into audit_demo(sdate, otype, program)
    (select sysdate, command, substr(program,1,50)
       from v$session
      where audsid=sys_context('USERENV','SESSIONID')
        and sid=sys_context('USERENV','SID'));
  commit;
end track_demo_access;
end;
/
show errors

Étape 3 : Créer un POLICY DBMS_FGA qui déclenche la procédure sur un select ou un ordre DML à la table DEMO

Maintenant, il suffit de créer une policy qui permette de déclencher le package dès qu’un ordre select ou dml est exécuté :

BEGIN
DBMS_FGA.ADD_POLICY(
  object_schema   => 'DEMO',
  object_name     => 'DEMO',
  policy_name     => 'TRACK_DEMO_DEMO',
  handler_schema  => 'DEMO',
  handler_module  => 'demo.demo_fga_handler.'||
                     'track_demo_access',
  enable          => true,
  statement_types => 'SELECT, INSERT, UPDATE, DELETE',
  audit_trail     => DBMS_FGA.DB);
END;
/

Étape 4 : Tester

Un test simple montre bien que la procédure fonctionne comme attendue (Attention ca ne marche pas si vous êtes connecté SYSDBA en 10.2.0.1 et 10.2.0.2 😉 ). Remarquez que c’est vrai même si la transaction est annulée par un ROLLBACK;

connect demo/demo

alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

insert into demo values(1);

update demo set id=2 where id=1;

delete from demo where id=1;

select * from demo;

select otype, sdate, program
from audit_demo;

OTYPE SDATE               PROGRAM
----- ------------------- -------
2     13/09/2007 13:43:49 sqlplus
6     13/09/2007 13:43:49 sqlplus
7     13/09/2007 13:43:49 sqlplus
3     13/09/2007 13:43:49 sqlplus

ROLLBACK;

select otype, sdate, program
from audit_demo;

OTYPE SDATE               PROGRAM
----- ------------------- -------
2     13/09/2007 13:43:49 sqlplus
6     13/09/2007 13:43:49 sqlplus
7     13/09/2007 13:43:49 sqlplus
3     13/09/2007 13:43:49 sqlplus

Étape 5 : Où es le problème ?

Peut-être n’y a-t-il pas de problème mais moi, j’en ai un ! En plus du trigger, les informations d’audit sont journalisées dans la table SYS.FGA_LOG$ ou, éventuellement, dans un fichier XML. Et mon problème, c’est moins l’espace (On peut toujours faire regulièrement un « delete from SYS.FGA_LOG$ ») que l’impact sur les performances de cette opération. Il y a 2 index système sur cette table ; Sur ma base de données, j’ai évalué à 2 ms par appel, l’impact de cette journalisation avec un debit de 500 appels par secondes et je ne vous dirai pas le pire ! Ce qui est « funny », c’est que écrire dans un fichier XML est plus efficace dans le cas de ma configuration

Voilà, je n’ai pas trouvé de moyen de désactiver cette journalisation ; regardez les valeurs possible du parametre audit_trail de la procedure DBMS_FGA.ADD_POLICY ! Bref, si vous voulez utiliser DBMS_FGA, c’est simple et très efficace mais il y a certaines limites à pas franchir et on dirait que c’est ce que j’ai essayé de faire ! Heureusement, je m’en suis rendu compte lors de mes tests de charge.

Étape 6 : Nettoyer

Comme d’hab…

sqlplus / as sysdba

drop user demo cascade;