22May/100
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 |
Related posts: