Ramin Hossaini (blog)

22May/10

Oracle: Basic user-logon auditing

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
Tagged as: , No Comments
15May/10

Basic Auditing in Oracle

To check if auditing is enabled:

SHOW parameter AUDIT;

To enable auditing, modify/add the following in your PFILE and restart the database:

audit_trail = db;

To audit SELECTs on an object:

AUDIT SELECT ON object;

To list all Audit data on a database:

SELECT * FROM sys.aud$;

Purge/delete entries from the audit table:

DELETE FROM sys.aud$;

To view audit data:

SELECT * FROM sys.aud$;

To disable auditing:

NOAUDIT SELECT ANY TABLE;
Tagged as: , No Comments
Page 1 of 11

Pages

Categories

Bear