Audit et triggers

L’administrateur d’une base de données, a toujours besoin de savoir ce qui se passe dans la bas données qu’il administre, surtout lorsqu’il y a perte de données suspecte ou effacement de table index  etc.  …

Pour cela Oracle donne deux possibilités d’auditer une base de données :

     1. Audit par des déclencheurs (triggers), et c’est à l’administrateur d’intervenir pour écrire les procédures d’audit, et de décider comment et quelles informations doit stocker, et ou il d stocker.

Exemple pour auditer les accès à la base de données, un déclencheur système ON LOGO  nécessaire et ce déclencheur peut être de niveau base de données (pour tous les utilisateurs comme il peut être au niveau schéma (seulement pour l’utilisateur propriétaire).


Il existe deux types de déclencheurs, system triggers et DML triggers.

Les system triggers pour les ( LOGON, STARTUP, CREATE, DROP  …) et les DML triggers pour toutes requêtes (update, select, delete …).

L’avantage de ce type d’audit est que c’est l’administrateur qui décide de tout, et pour les triggers de type DML, il peut aussi auditer les valeurs de la base de données, -exemple  les lignes effacées, ou les  colonnes modifiées  par les références (OLD) pour les ancienne valeurs et (NEW)  pour les nouvelles valeurs.
2.  Audit par la commande AUDIT ou l’administrateur a seulement besoin d’activer l’audit su action ou un objet, et Oracle s’occupe de l’enregistrement des informations sur l’action produite.  
1.    Audit des bases de données par les triggers (déclencheurs) :
1.1          Les Systems triggers :
Je vous donne plusieurs exemples pratiques pour une meilleure compréhension :
Exemple01 : Comment  savoir la date et l’heur de chaque démarrage ou arrêt de la base ?  
1.  Créez une table trace (action varchar2(50)) ;
2.  Créez deux triggers  dans un fichier SQL :
------------------ Démarrage ------------------------
CREATE OR REPLACE TRIGGER TRACE_BD1
AFTER STARTUP ON DATABASE
DECLARE
a constant varchar2(50):='DEMMARAGE DE LA BASE :';
BEGIN
EXECUTE IMMEDIATE  'INSERT INTO TRACE VALUES('''||A||TO_CHAR(SYSDATE,'yyyymmdd_hh24mi')||''')';
EXCEPTION
WHEN others THEN NULL;
END;
         ----------------------------------------          ----------------- Arrêt ---------------- 
CREATE OR REPLACE TRIGGER TRACE_BD2
BEFORE SHUTDOWN ON DATABASE
DECLARE
a constant varchar2(50):='ARRET DE LA BASE :';
BEGIN
EXECUTE IMMEDIATE  'INSERT INTO TRACE VALUES('''||A||TO_CHAR(SYSDATE,'yyyymmdd_hh24mi')||''')';
EXCEPTION
WHEN others THEN NULL;
END; 
     ------------------------------------------- 
NB : lancer le script sous SQL avec la commande :
>star nom_fich_sql   ou : >@  nom_fich_sql
                     -----------------------------------------------------------
Exemple02 : Comment  créer une copie du spfile (dans un pfile) a chaque démarrage de la base  avec le nom du pfile est concaténer avec la date et l’heur?
-------------------------------------------
      CREATE OR REPLACE TRIGGER copy_spfile
      AFTER STARTUP ON DATABASE
      DECLARE
      cPath CONSTANT varchar2(80) := 'E:\oracle\admin\ORCL\pfile';
      BEGIN
      EXECUTE IMMEDIATE
     'create pfile='''||cPath||'\init.ora.'||       TO_CHAR(SYSDATE,'yyyymmdd_hh24mi')||''' from spfile';
      EXCEPTION
      WHEN others THEN NULL;
      END;
            ----------------------------------------
Exemple03 : Comment  auditer la suppression des tables de la base ?
 
-    Créer une table trace_del(nom_table, utilisateur, date, heur) ;
---------------------------------------------
        CREATE OR REPLACE TRIGGER declencheur_supp_tab
        BEFORE DROP ON DATABASE
        DECLARE action_utilisateur VARCHAR2(50);
        BEGIN
        SELECT user INTO utilisateur
        FROM DUAL;
        IF ( ora_dict_obj_type = 'table' ) THEN
        INSERT INTO trace_del VALUES (ORA_DICT_OBJ_NAME,utilisateur,
        TO_CHAR(SYSDATE, 'DD/MON/YYYY'),TO_CHAR(SYSDATE, 'HH24:MI:SS'));
        END IF;
                   END ;
 -------------------------------------------- 
Exemple04 : Comment  bloquer l’accès des utilisateurs étrange (filtrage par adresse IP /Host ou  programmes) ?
Voici le script complet: --------------------------------------------------------------------------
create or replace TRIGGER "SYS"."BLOCK_USER_ACCESS"
AFTER LOGON ON DATABASE
DECLARE
v_prog sys.v_$session.program%TYPE;
v_dbuser sys.v_$session.username%TYPE;
v_osuser sys.v_$session.osuser%TYPE;
v_db sys.v_$database.name%TYPE;
v_terminal  sys.v_$session.terminal%TYPE;
v_ausid  sys.v_$session.audsid%TYPE;
ip_add  varchar2(256);
BEGIN
/* Récupération des informations utiles dans v$sessios */
SELECT upper(program), upper(username), upper(osuser),upper(terminal)
INTO   v_prog, v_dbuser, v_osuser,v_terminal
FROM sys.v_$session
WHERE  audsid = USERENV('SESSIONID')
AND  audsid != 0  -- N'impacte pas la connexion SYS
AND  rownum = 1;
SELECT UTL_INADDR.get_host_address(v_terminal) INTO ip_add FROM dual;
SELECT upper(name)
INTO   v_db
from v$database;
/* Utilisation à proscrire */
IF  v_dbuser NOT IN ('SYSTEM', 'SYS')
AND (ip_add !='192.168.0.2')   /* filtrage par @ip */
AND(v_terminal not like'WIN2003%')   /* filtrage par host */
AND(v_prog not LIKE 'SQLPLUS%')     /* filtrage par programme */
THEN
RAISE_APPLICATION_ERROR(-20000,  'ORACLE  n''est pas autorisé à connecté puisque vous ete etranger' ||' sur l''environnement'||v_db);
/* RAISE_APPLICATION_ERROR(-20000,  v_osuser ||' n''est pas autorisé à utiliser  '|| v_prog ||' sur l''environnement '||v_db);*/
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN NULL;
END; ------------------------------------------------------------------------
1.2    Les DML  triggers :
Les déclencheurs DML sont des procédures stockées appartenant à une table précise et s'exécutant lorsqu'une action spécifique se produit sur la table concernée.
Le déclenchement d'une telle procédure s'effectue subséquemment à une instruction de manipulation de données (DML) comme INSERT, DELETE ou UPDATE. Il existe donc trois types de déclencheurs DML, sur insertion, sur mise à jour et sur suppression
 Question :
Une table peut comporter plusieurs déclencheurs d'un type donné ?
 Oui, à condition que chacun possède un nom différent. Cependant, un déclencheur donné ne peut être assigné qu'à une seule et unique table tout en s'appliquant à la fois, à l'insertion, la mise à jour et la suppression d'enregistrements sur la table en question.
 Exemple01 : Comment  savoir qui a supprimer n’importe quelle ligne dans une table employee ?
1.  Créez une table trace_del (nom_tab , nom_user, date, heur) ;
2.  Créez deux triggers  dans un fichier SQL :
----------------------------------------------
  CREATE OR REPLACE TRIGGER declencheur_suppression
  AFTER DELETE ON employee
  FOR EACH ROW
  WHEN (1 = 1)
  DECLARE action_utilisateur VARCHAR2(50);
  BEGIN
  SELECT user INTO action_utilisateur
  FROM DUAL;
  INSERT INTO trace_del
  VALUES ('tbl_1',action_utilisateur,TO_CHAR(SYSDATE, 'DD/MON/YYYY'),TO_CHAR(SYSDATE, 'HH24:MI:SS'));
  END;
 -----------------------------------------------------------------
Exemple02 : Comment  contrôler les salaires des employées et interdire le dépassement d’une somme par exemple 10000 ?
 --------------------------------------------------------------------------
Sql>create or replace trigger contrôle_salaire   2  BEFORE INSERT OR UPDATE   3  of salary   4  on employee   5  for each row   6  declare   7      v_error VARCHAR2(2000);   8  begin   9      if :new.salary > 10000  10      then  11          v_error:=:old.first_name||' cannot have that much!';  12          raise_application_error(-20999,v_error);  13      end if;  14  end;  15  /
Trigger created.
-----------------------------------------------------------------------
NB : Les variables spéciales NEW et OLD sont disponibles pour se référer respectivement à des nouveaux ou d'anciens enregistrements. Les deux points (:) précédent NEW et OLD dans VALUES sont dans ce cas obligatoires.
------------------------------------------------------------------------------------
Exemple03:Comment auditer toutes les opérations sur la table employee dans le schéma HR (par exemple)?
 Créer dans le schéma HR une table trace (nom_table,action, user, date_maj);
------------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER HR.AUDIT_EMP     
BEFORE INSERT OR DELETE OR UPDATE ON employee       FOR EACH ROW     DECLARE       v_ChangeType CHAR(1);
      utilisateur VARCHAR2(50);
    BEGIN
SELECT user INTO utilisateur FROM DUAL;       /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */       IF INSERTING THEN
BEGIN v_ChangeType := 'I';
EXECUTE IMMEDIATE  'INSERT INTO HR.TRACE    VALUES(‘employee’,  v_ChangeType, utilisateur, TO_CHAR(SYSDATE,'yyyymmdd_hh24mi'))';
END ;
    ELSIF UPDATING THEN
BEGIN v_ChangeType := 'U';
EXECUTE IMMEDIATE  'INSERT INTO HR.TRACE    VALUES(‘employee’,  v_ChangeType, utilisateur, TO_CHAR(SYSDATE,'yyyymmdd_hh24mi'))';
END ;      ELSE
BEGIN v_ChangeType := 'D';
EXECUTE IMMEDIATE  'INSERT INTO HR.TRACE    VALUES(‘employee’, v_ChangeType, utilisateur, TO_CHAR(SYSDATE,'yyyymmdd_hh24mi'))';
END ;     END IF; 
END;
------------------------------------------------------------------------------
Suppression et mise à jour des déclencheur :
La suppression des déclencheurs s'effectue par l'intermédiaire de l'instruction DROP :
DROP TRIGGER nom_déclencheur [ , nom_déclencheurN ]
Tous les déclencheurs (ALL) ou certains peuvent être activés (ENABLE) ou désactivés (DISABLE) au moyen de l'instruction ALTER TABLE :
ALTER TABLE table
  { ENABLE | DISABLE } TRIGGER
        { ALL | nom_déclencheur [ , nom_déclencheurN ] }
--------------------------------------------------------------------------------------------                     2. Audit des bases de données par la commande AUDIT :
           
Les vues d'audit dans le dictionnaire de données Autorisation de Mise en route de l'audit Déclenchement effectif de l'audit Vérification des options d'audit en cours Les résultats dans les vues d'audit

Les vues d'audit dans le dictionnaire de données

Les vues d'audit sont normalement créées par CATALOG.SQL, et + précisément par le script CATAUDIT.SQL, lors de la création de la base.
Vue d'audit
Description du contenu
STMT_AUDIT_OPTION_MAP 
code des types d'option
AUDIT_ACTIONS 
codes des actions
ALL_DEF_AUDIT_OPTS 
otion d'audit OBJET part défaut
DBA_STMT_AUDIT_OPTS 
options d'audit courantes
DBA_PRIV_AUDIT_OPTS 
options d'audit SYSTEME courantes
DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS 
options d'audit sur tous les objets et sur ceux du USER
DBA_AUDIT_TRAIL,
USER_AUDIT_TRAIL 
toutes les entrées d'audit et celles concernant uniquement le USER
DBA_AUDIT_OBJECT,
USER_AUDIT_OBJECT 
toutes les entrées d'audit OBJET et celles concernant uniquement le USER
DBA_AUDIT_SESSION,
USER_AUDIT_SESSION  
les entrées d'audit concernant toutes les (dé)connexions et celles concernant uniquement le USER
DBA_AUDIT_STATEMENT, USER_AUDIT_STATEMENT 
les entrées d'audit concernant GRANT, REVOKE, AUDIT, NOAUDIT, et ALTER SYSTEM de tous ou du user connecté
DBA_AUDIT_EXISTS 
les entrées d'audit concernant AUDIT EXISTS et AUDIT NOT EXISTS. 

Autorisation de Mise en route de l'audit

Il faut positionner le paramètre de démarrage AUDIT_TRAIL dans le fichier INIT.ORA de la base.
3 valeurs possibles :
  • NONE : invalide l'audit (valeur par default)
  • DB : valide l'audit et stocke les résultats dans la table d'audit
  • OS : valide l'audit et stocke les résultats dans un fichier externe (un autre paramèter : AUDIT_FILE_DEST précise le répertoire de destination...)
NB : attention cette opération nécessite l'arrêt / démarrage de la base et NE DEMARRE PAS L'AUDIT !!

Déclenchement effectif de l'audit (ciblé) par le DBA ou un user autorisé

Il existe 4 niveaux d'audit :
  • connexion / déconnexion : surveille les connexions
  • ordre SQL : audit par type d'ordre SQL utilisé
  • privilège : audit d'un privilège SYSTEM (SELECT ANY, DROP ANY, CREATE ANY, * ANY...)
  • objet : un ordre SQL particulier sur un objet particulier (audit SELECT sur SCOTT.EMP)
A chaque niveau d'audit, on peut de + surveiller aussi bien LES SUCCES que LES ECHECS, et avoir une entrée d'audit par commande utilisateur ou globalement pour la session.
Le déclenchement de l'audit effectif se fait par la commande AUDIT et une ou des option(s), qui précise(nt) :
  • le type d'action à auditer,
  • si l'on veut les tentatives réussies ou échouées,
  • pour une session globale ou pour chaque ordre SQL
Exemples :
AUDIT CONNECT WHENEVER NOT SUCCESSFULL surveille toutes les tentatives de connexions infructueuses
AUDIT CREATE ANY PROCEDURE BY ACCESS WHENEVER SUCCESSFULL audit système : surveille les création de procédures réussies nsur toutes la base, une entrée par commande passée.
AUDIT SELECT TABLE, DELETE TABLE BY SESSION WHENEVER NOT SUCCESSFUL surveille les select et insert infructueux sur n'importe quelle table, une entrée seulement par session
AUDIT INSERT ON scott.dept audit objet : surveille les insertions (réussies ou échouées) sur la table DEPT de SCOTT

Vérification des options d'audit en cours

Il suffit d'aller consulter les vues adéquates du dictionnaire de données (%AUDIT_OPTS) : ALL_DEF_AUDIT_OPTS  , DBA_STMT_AUDIT_OPTS  , DBA_PRIV_AUDIT_OPTS  , DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS  

Sélection des résultats dans les vues d'audit

Faire un SELECT sur %_AUDIT_OBJECT ou %_AUDIT_SESSION ou %_AUDIT_STATEMENT
Inscription à : Articles (Atom)