Cloud Photo

Azure Data Architect | DBA

Selective Oracle Logon Trigger for Auditing

The Oracle logon trigger can be a very powerful tool for recording who or what is accessing your database. However, you may not need to know when every user or every host or even a specific application logs into Oracle. The following trigger shows one possibility for selectively auditing user logons.

For this particular trigger, the audit data will be stored in a table owned by a restricted schema we’ll call myAuditor. This schema should not allow other users to run data modification requests against it in order to preserve the integrity of the data. This repository table and trigger are created by the system user.

First, we will create the repository table. Since the table and trigger are owned by the myauditor schema, be sure to create the table in a tablespace which the myauditor user can access. Column sizes are based on the sizes of the columns in dba_ and v$ views to remain consistent.

CREATE TABLE myauditor.logon_audit
 (
 username VARCHAR2(30),
 machname VARCHAR2(64),
 osuser VARCHAR2(30),
 progname VARCHAR2(48),
 dtstamp DATE
 )
 TABLESPACE "USERS";

 

Initially, this table will be used for inserts and occasional selects. Therefore, indexes are not necessary and may degrade the performance of the insert statements.

For the purposes of this trigger, we do not want to audit specific users and specific programs on specific hosts. The first IF…THEN statement excludes specific users. The second IF…THEN excludes specific apps on specific hosts and inserts records for everything else. In this case, we are not recording logons from web applications – more specifically, we are not recording connections from w3wp.exe, the Windows-based IIS worker program, on three separate web servers. The third IF…THEN statement includes everything from the web servers except w3wp.exe. This final IF…THEN statement will capture connections by other applications that are not connecting via the w3wp.exe process.

This trigger records the Oracle username, client’s computer name, client’s OS username, and the name of program (module) used to connect to the database as determined by the SYS_CONTEXT function. More details on what information the SYS_CONTEXT function provides can be found in the Oracle documentation (http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm).

CREATE OR REPLACE TRIGGER myAuditor.logon_trigger AFTER LOGON ON DATABASE
BEGIN
 IF UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER')) 
    NOT IN ('SYS','SYSTEM','SYSMAN','DBSNMP') -- list of users to ignore
 THEN
 IF (TRIM(UPPER(SYS_CONTEXT('USERENV', 'HOST'))) 
    IN ('WEB01','WEB02','WEB03') -- list of hosts to ignore
 AND TRIM(UPPER(SYS_CONTEXT('USERENV', 'MODULE'))) != 'W3WP.EXE') -- list of apps to ignore on hosts
 THEN
 INSERT
 INTO myauditor.logon_audit VALUES
 (
 UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER')) ,
 UPPER(SYS_CONTEXT('USERENV', 'HOST')) ,
 UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) ,
 UPPER(SYS_CONTEXT('USERENV', 'MODULE')) ,
 sysdate
 );
 ELSIF TRIM(UPPER(SYS_CONTEXT('USERENV', 'HOST'))) 
   NOT IN ('WEB01','WEB02','WEB03') -- list of hosts to ignore
 THEN
 INSERT
 INTO myauditor.logon_audit VALUES
 (
 UPPER(SYS_CONTEXT('USERENV', 'SESSION_USER')) ,
 UPPER(SYS_CONTEXT('USERENV', 'HOST')) ,
 UPPER(SYS_CONTEXT('USERENV', 'OS_USER')) ,
 UPPER(SYS_CONTEXT('USERENV', 'MODULE')) ,
 sysdate
 );
 END IF;
 END IF;
 EXCEPTION
 WHEN OTHERS THEN
 RETURN;
 END;

 

We used the EXCEPTION WHEN OTHERS to allow logons to continue even if there are errors in the trigger. One of the possible errors you might encounter will be tablespace issues, especially if the repository table is in a small tablespace. It will be critical to monitor tablespace usage to ensure that the trigger does not consume the tablespace or if you have AUTOEXTEND enabled.

You can disable and enable the trigger using the ALTER TRIGGER statement:

ALTER TRIGGER myAuditor.logon_trigger DISABLE|ENABLE;

Leave a Reply