Audit failed logon attempts - Oracle

How to audit failed logon attempts

Oracle Audit -- failed connection
Background:
In some situation DBA team wants to audit failed logon attempts when "unlock account"  requirement becomes frequently and user cannot figure out who from where is using incorrect password to cause account get locked.

Audit concern:
Oracle auditing may add extra load and require extra operation support. For this situation DBA only need audit on failed logon attempts and do not need other audit information. Failed logon attempt is only be able to track through Oracle audit trail, logon trigger does not apply to failure logon attempts


Hint: The setting here is suggested to use in a none production system. Please evaluate all concern and load before use it in production.



Approach:
1. Turn on Oracle audit function by set init parameter:
               audit_trail=DB
Note:
database installed by manual script, the audit function may not turn on:
database installed by dbca, the default audit function may already turn on:
Check:

SQL> show parameter audit_trail
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
audit_trail                          string      NONE

Turn on Oracle audit
a. If database use spfile
SQL> alter system set audit_trail=DB scope=spfile ;
System altered.

b. if database use pfile, modify init<Sid>.ora directly.

Restart database
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup ;
ORACLE instance started.


2. Turn off Oracle default audit
Privilege audit information stored in dba_priv_audit_opts;
Note: Oracle 11g has couple of audit turned on default when the audit_trail is set. It will just utilize more resources that you might not want.
Oracle 10g, audit options is setup by explicit command (we can audit these options back any time).

Generate a script to turn off default privilege audit which we don't need here.
SQL>  SELECT 'noaudit '|| privilege||';' from dba_priv_audit_opts where user_name is NULL;
'NOAUDIT'||PRIVILEGE||';'
-------------------------------------------------
noaudit ALTER SYSTEM;
noaudit AUDIT SYSTEM;
noaudit CREATE SESSION;
noaudit CREATE USER;
noaudit ALTER USER;
noaudit DROP USER;
noaudit CREATE ANY TABLE;
noaudit ALTER ANY TABLE;
noaudit DROP ANY TABLE;
noaudit CREATE PUBLIC DATABASE LINK;
noaudit GRANT ANY ROLE;
noaudit ALTER DATABASE;
noaudit CREATE ANY PROCEDURE;
noaudit ALTER ANY PROCEDURE;
noaudit DROP ANY PROCEDURE;
noaudit ALTER PROFILE;
noaudit DROP PROFILE;
noaudit GRANT ANY PRIVILEGE;
noaudit CREATE ANY LIBRARY;
noaudit EXEMPT ACCESS POLICY;
noaudit GRANT ANY OBJECT PRIVILEGE;
noaudit CREATE ANY JOB;
noaudit CREATE EXTERNAL JOB;
23 rows selected.

-- run above commands

3. Turn on audit on failed connection
SQL> AUDIT CONNECT WHENEVER NOT SUCCESSFUL;

Audit succeeded.

SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;

PRIVILEGE                                SUCCESS    FAILURE
---------------------------------------- ---------- ----------
CREATE SESSION                           NOT SET    BY ACCESS

4. Retrieve information
Note: audit information is stored on sys.aud$. There multiple views Oracle provide to help you read sys.aud$. Logon failed information can be retrieve from  dba_audit_session

SQL>   select os_username,  username, userhost,  to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') logon_time,  action_name, returncode from dba_audit_session;

OS_USERNAME                    USERNAME                       USERHOST                                           TIMESTAMP           ACTION_NAME                  RETURNCODE
------------------------------ ------------------------------ -------------------------------------------------- ------------------- ---------------------------- ----------
Arvind                    machine1         
HOME-Arvind                                       12/06/2014 13:40:12 LOGON                              1017
Arvind                    machine1         
HOME-lArvind                                       12/06/2014 13:40:25 LOGON                              1017
Arvind                   machine1         
HOME-Arvind                                       12/06/2014 15:31:29 LOGON                              1017
Arvind                   machine1         
HOME-Arvind                                       12/06/2014 15:31:38 LOGON                              1017
4 rows selected.

Note: RETURNCODE is the ORA error code return to user.
ORA-1017 is incorrect password
ORA-28000 is account locked
ORA-1045 is missing connect privilege

------------------------------------------------------------
Up here, we be able to audit who is the bad boy causing account locked.

Turning off the audit:
If you no longer need the audit on failed attempts, run this command to turn off
SQL> noaudit CONNECT;

Noaudit succeeded.

SQL> SELECT PRIVILEGE,SUCCESS,FAILURE FROM dba_priv_audit_opts;

no rows selected


Oracle use system tablespace for sys.aud$. For enhancement, you may consider to move sys.aud$ to separate tablespace.


6. Move sys.aud$ out of system tablespace.
Oracle 11g provide package dbms_audit_mgmt.set_audit_trail_location to relocate the aud$ table.
SQL>  SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';
TABLE_NAME                     TABLESPACE_NAME
----------------------------- ------------------------------
AUD$                           SYSTEM

Following example shows how to move sys.aud$ from system tablespace to user_data1 tablespace.

SQL> exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'USER_DATA1');


PL/SQL procedure successfully completed.

SQL> SELECT table_name, tablespace_name FROM dba_tables WHERE table_name ='AUD$';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
AUD$                           USER_DATA1



7. Clean up AUD$

You can simply run delete or truncate command


delete from sys.AUD$;
truncate table sys.AUD$;