Audit Failed Login(s)

This artlcle explains the detail procedure of auditing. This doesn’t cover full function of the audit_trail but could help us to make our database to meet a company’s compliance.

1. Set parameters

AUDIT_TRAIL = DB AUDIT_FILE_DEST = /home/app/oracle/admin/$ORACLE_SID/adump

2. Set Audit for Failed Connection

SQL> AUDIT SESSION [WHENVER NOT SUCCESSFULLY] [BY SYS,SYSTEM];

3. Query Audit Logs

set line 130 col userhost format a15 col userid format a15 col comment$text format a30 select USERHOST,USERID,comment$text, to_char(timestamp#,’DD-MON-YY:HH24:MI:SS’) login, to_char(logoff$time,’DD-MON-YY:HH24:MI:SS’) logoff, to_char(ntimestamp#,’DD-MON-YY:HH24:MI:SS’) time_tried from sys.aud$ / or set line 130 col os_username format a15 col userhost format a20 col username format a20 col comment_text format a30 col action_name format a20 select OS_USERNAME,USERHOST,USERNAME, to_char(TIMESTAMP,’DD-MON-YY:HH24:MI:SS’) TIMESTAMP,ACTION_NAME,COMMENT_TEXT from DBA_AUDIT_TRAIL /

3. Maintain Audit Logs

DELETE FROM SYS.AUD$ WHERE NTIMESTAMP# > TO_TIMESTAMP (’29-FEB-08 09.07.59.907000 PM’) AND NTIMESTAMP# < TO_TIMESTAMP ('29-MAR-08 09.07.59.907000 PM'); To delete all audit records from the audit trail, enter one of the following statements: DELETE FROM SYS.AUD$; TRUNCATE TABLE SYS.AUD$; Use the DELETE and TRUNCATE TABLE statements to purge the fine-grained audit trail as well. For example: DELETE FROM SYS.FGA_LOG$; TRUNCATE TABLE SYS.FGA_LOG$;

Only the user SYS, a user who has the DELETE ANY TABLE privilege, or a user to whom SYS granted the DELETE privilege on SYS.AUD$ can delete records from the database audit trail.

4. Automate Audit Report using Package/Trigger

CREATE OR REPLACE PACKAGE SYSTEM.USER_AUDIT AS PROCEDURE AUDIT_USER_LOGIN ( USERNAME VARCHAR2 ); END; / CREATE OR REPLACE PACKAGE BODY SYSTEM.USER_AUDIT AS PROCEDURE AUDIT_USER_LOGIN ( USERNAME VARCHAR2 ) IS BEGIN INSERT INTO SYSTEM.AUDIT_USER_LOGIN VALUES ( USERNAME , SYSDATE ); DELETE FROM SYSTEM.AUDIT_USER_LOGIN WHERE LOGIN_TIME < SYSDATE – 60; END; END; / DROP TRIGGER SYSTEM.AFTER_USER_LOGIN; CREATE OR REPLACE TRIGGER SYSTEM.AFTER_USER_LOGIN AFTER LOGON ON DATABASE DECLARE BEGIN – IF SYS_CONTEXT(‘userenv’,'session_user’) NOT IN ( ‘CRONTAB’,'SYSMAN’,'DBSNMP’,'WHITNEY’,'IPS_REPORT’,'READONLY’ ) THEN USER_AUDIT.AUDIT_USER_LOGIN ( SYS_CONTEXT(‘userenv’,'session_user’ )); – END IF; END; /