Ramin Hossaini

Oracle: Basic user-logon auditing

May
22

Oracle 8i introduced logon-triggers which could be used for auditing.

To start, create a table which will store your audit-logs (I usually do this as SYSTEM):

1
2
3
4
5
6
7
8
9
10
CREATE TABLE AUDIT$user_logs
 (
    user_id           VARCHAR2(30),
    session_id        NUMBER(8),
    host              VARCHAR2(30),
    logon_day         DATE,
    logon_time        VARCHAR2(10)
 );
 
TABLE created.

Next, create the trigger to capture the data:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE TRIGGER
 logon_audit_trigger
 AFTER LOGON ON DATABASE
 BEGIN
 INSERT INTO AUDIT$user_logs VALUES(
    USER,
    SYS_CONTEXT('USERENV','SESSIONID'),
    SYS_CONTEXT('USERENV','HOST'),
    SYSDATE,
    TO_CHAR(SYSDATE, 'hh24:mi:ss')
 );
 END;
 /
 
TRIGGER created.

Display Audit-data:

1
2
3
4
5
6
7
SQL> SELECT * FROM AUDIT$user_logs;
 
USER_ID         SESSION_ID HOST               LOGON_DAY LOGON_TIME
--------------- ---------- ------------------ --------- ----------
DBSNMP              123716 HOST               01-OCT-08 10:21:32
SYSTEM              123717 DOMAIN\PCNUMBER    01-OCT-08 10:21:53
SYSMAN                   0 HOST               01-OCT-08 10:21:58

Disable and Enable Logon-auditing:

1
2
3
4
ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER DISABLE
/
ALTER TRIGGER SYSTEM.LOGON_AUDIT_TRIGGER ENABLE
/

To purge audit-data:

1
TRUNCATE TABLE AUDIT$user_logs

Leave a Reply

Your email address will not be published. Required fields are marked *