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)

A 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

Paramètre Description
ACTION Position dans le module (application name), appliquée avec le package DBMS_APPLICATION_INFO ou OCI.
AUTHENTICATED_IDENTITY Identité utilisée dans l'authentification :
  • Kerberos : nom principal
  • Utilisateur de la base de données avec mot de passe : nom de l'utilisateur/schéma
  • SYSDBA/SYSOPER avec fichier Password : nom du login
  • SYSDBA/SYSOPER avec authentification OS : compte du système d'exploitation
  • SSL : DN dans le certificat PKI de l'utilisateur
  • Bien d'autres, mais ça reste pour des niches techniques…
AUTHENTICATION_DATA Données utilisées pour authentifier le login (données du certificat X.503 au format HEX2 par exemple).
AUTHENTICATION_METHOD Méthode d'authentification.
  • Utilisateur (proxy ou non) avec mot de passe
    ou SYSDBA/SYSOPER avec fichier password : PASSWORD
  • Utilisateur Kerberos : KERBEROS
  • Utilisateur SSL: SSL
  • Utilisateur externe Radius : RADIUS
  • Utilisateur externe OS ou SYSDBA/SYSOPER: OS
  • Utilisateur externe DCE: DCE
CLIENT_IDENTIFIER Identifiant de client appliqué avec la procédure DBMS_SESSION.SET_IDENTIFIER l'attribut OCI OCI_ATTR_CLIENT_IDENTIFIER ou la classe Java Oracle.jdbc.OracleConnection.setClientIdentifier. Attribut utilisé par des composants bases de données pour identifier des applications qui s'authentifient avec le même utilisateur.
CLIENT_INFO Information de session client stockée avec le package DBMS_APPLICATION_INFO.
CURRENT_SCHEMA Schéma par défaut. Cette valeur est modifiée avec la commande ALTER SESSION SET CURRENT_SCHEMA.
CURRENT_SCHEMAID Identifiant du schéma par défaut dans la session courante.
DB_DOMAIN Domaine de la base de données (paramètre d'initialisation DB_DOMAIN).
DB_NAME Nom de la base de données (paramètre d'initialisation DB_NAME).
DB_UNIQUE NAME Nom de la base unique (paramètre d'initialisation DB_UNIQUE_NAME).
IDENTIFICATION_TYPE Méthode utilisée pour créér le schéma dans la base. Elle reflète la clause IDENTIFIED dans la syntaxe CREATE/ALTER USER.
  • IDENTIFIED BY password: LOCAL
  • IDENTIFIED EXTERNALLY: EXTERNAL
  • IDENTIFIED GLOBALLY: GLOBAL SHARED
  • IDENTIFIED GLOBALLY AS DN: GLOBAL PRIVATE
INSTANCE Numéro d'identification de l'instance courante.
INSTANCE_NAME Nom de l'instance.
IP_ADDRESS IP Adresse IP de la machine cliente.
ISDBA TRUE si connecté en tant que SYS.
LANG Abbréviation ISO de la langue (F), forme courte du paramètre LANGUAGE.
LANGUAGE Langue et pays utilisés par la session, plus le jeu de caractères de la base, sous la forme: language_territory.characterset.
MODULE Nom de l'application (module) appliqué avec le package DBMS_APPLICATION_INFO ou OCI.
NETWORK_PROTOCOL Protocole réseau utilisé (tcp…).
NLS_CALENDAR Calendrier courant de la session.
NLS_CURRENCY Devise monétaire de la session.
NLS_DATE_FORMAT Format de date de la session.
NLS_DATE_LANGUAGE Langue des dates.
NLS_SORT BINARY ou ordre de tri linguistique spécifique.
NLS_TERRITORY Pays de la session courante.
OS_USER Compte du système d'exploitation qui initie la session.
SERVER_HOST Nom de la machine de l'instance (noeud dans un environnement RAC).
SERVICE_NAME Le nom du service auquel la session donnée est connectée (SYS.$USERS).
SESSION_USER Nom de l'utilisateur de base de données. Cette valeur demeure tout le long de la session.
SESSION_USERID Id de l'utilisateur.