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.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.

Oracle 10g, audit options is setup by explicit command.

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

------------------------------ ------------------------------ -------------------------------------------------- ------------------- ---------------------------- ----------

linda xu JET_DEV102
HOME-linda xu 02/06/2013 13:40:12 LOGON 1017

linda xu JET_DEV102
HOME-linda xu 02/06/2013 13:40:25 LOGON 1017

linda xu JET_DEV102
HOME-linda xu 02/06/2013 15:31:29 LOGON 1017

linda xu JET_DEV102
HOME-linda xu 02/06/2013 15:31:38 LOGON 1017

4 rows selected.

Note: RETURNCODEis 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.

5. Turn 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

select OS_USERNAME,USERNAME,USERHOST,TIMESTAMP
from dba_audit_session where returncode=1017 and username='*******';

How to audit failed logon attempts

相關文章