Triggers système Oracle (AFTER LOGON) et application à SOX

Introduction

Avec Sarbanes-Oxley Act, des nouveaux besoins apparaissent pour auditer et contrôler les accès en base et éventuellement accepter et/ou refuser les accès si des accès sont faits par des logins génériques en analysant les programmes/osuser… entrant. Les triggers de logon introduits avec Oracle 8i apportent une réponse.

Dans le contexte de cette documentation, le login Oracle SOP_DATATEAM est utilisé par une équipe en particulier, seulement l’accès ne peut être accepté que si il s’agit de certains utilisateurs OS clairement identifiés.

Vue V$SESSION et fonction sys_context

Vue V$SESSION

La vue V$SESSION permet de déterminer avec précision d’où vient l’utilisateur qui se connecte.

desc v$session;

Name                        NULL ?   Type
--------------------------- -------- ----------------------------
SADDR                       RAW(4)
SID                         NUMBER
SERIAL#                     NUMBER
...
USERNAME                    VARCHAR2(30)
...
OSUSER                      VARCHAR2(30)
PROCESS                     VARCHAR2(12)
MACHINE                     VARCHAR2(64)
TERMINAL                    VARCHAR2(16)
PROGRAM                     VARCHAR2(64)
...

La colonne USERNAME donne le login Oracle utilisé et la colonne OSUSER le login OS/NT utilisé , exemple :

select osuser, username from v$session where osuser !='SYSTEM';

OSUSER                         USERNAME
------------------------------ ------------------------------
lerouxf                        OPS$BRU

Dans l’exemple ci-dessus : l’utilisateur lerouxf (login XP) s’est connecté avec le login Oracle OPS$BRU.

Dans le contexte de cet article, on souhaite n’autoriser que certains OSUSER clairement identifiés dans une table de référence à se connecter avec un login Oracle SOP_DATATEAM.

Fonction sys_context

La fonction sys_context permet dans une programmation PL/SQL de récupérer les informations courantes de la session et que l’on retrouve dans la vue V$SESSION.

La syntaxe générale de la fonction sys_context est la suivante :

SELECT sys_context('<namespace>', '<parameter>', <length>);
FROM dual;

Pour les informations de l’environnement de l’utilisateur, <namespace> est toujours USERENV.

Pour récupérer l’OSUSER :

select sys_context('USERENV','OS_USER') from dual ; => lerouxf

Bien d’autres paramètres peuvent être récupérés par la fonction sys_context :

select sys_context('USERENV','SESSIONID') from dual ;
select sys_context('USERENV','HOST') from dual ;

La liste des paramètres de l’environnement les plus importants qui peuvent être récupérés avec le namespace USERENV est donnée en Annexe.

System triggers (logon, logoff, startup, shutdown, servererror)

À partir d’Oracle 8i, les triggers systèmes peuvent être déclenchés à la connexion et à la déconnexion, mais aussi après quelques autres évènements système ( BEFORE SHUTDOWN, AFTER SERVERERROR…)

Syntaxe :

CREATE OR REPLACE TRIGGER <trigger_name>
<trigger_type>
ON DATABASE
…

Les types de trigger systèmes peuvent être :

AFTER LOGON
AFTER STARTUP
BEFORE LOGOFF
BEFORE SHUTDOWN
AFTER SERVERERROR

Si il y a une erreur dans un trigger système et notamment dans un trigger de type AFTER STARTUP, la base de données peut ne pas être redémarrée. Pour désactiver les triggers systèmes au démarrage, dans le fichier init.ora, ajouter la ligne ci-dessous :

-- add the following to the init.ora:
_SYSTEM_TRIG_ENABLED=FALSE

Application pratique

Dans cet exemple pratique, crééons une table de référence dans laquelle seront implémentés tous les osuser autorisés à se connecter avec le login SOP_DATATEAM, cette dernière table sera créée dans le schéma SYS :

create table SECURITY_OSUSERS
(
  OSUSER VARCHAR2(30) NOT NULL
);

Le trigger AFTER LOGON peut être alors codé : si pour le login Oracle SESSION_USER=SOP_DATATEAM, il n’existe pas de lignes dans la table SECURITY_OSUSERS pour l’OS_USER entrant, une exception est levée et la connexion est refusée.

CREATE OR REPLACE TRIGGER systrg_logon
  AFTER LOGON
  ON DATABASE
  DECLARE
    username VARCHAR2(30);
    osuser VARCHAR2(30);
    is_authorized NUMBER;

    BEGIN
      SELECT sys_context ('USERENV', 'SESSION_USER')
      INTO username
      FROM dual;

      IF username='SOP_DATATEAM' then

        SELECT sys_context ('USERENV', 'OS_USER') INTO osuser
          FROM dual;

        SELECT COUNT(*) INTO is_authorized from SECURITY_OSUSERS
          WHERE OSUSER=osuser;

        IF is_authorized=0 then
          raise_application_error( -20001, 'Connection refused, OS User not allowed' );
        END IF;
      END IF;
    END;
/

En cas d’échec :

ERROR:
ORA-00604: une erreur s’est produite au niveau SQL rÚcursif 1
ORA-20001: Connection refused, OS User not allowed
ORA-06512: Ó ligne 16

Le trigger peut aller plus loin pour tracer les OSUSER entrants.

Annexe : Paramètres principaux du namespace USERENV pour la fonction sys_context