Friday, July 6, 2012

Audit failed logon attempts

it is difficult to audit failed sign-on attempts because the user never gets connected to Oracle but you can try this solution below :


Step 1 :
sqlplus / as sysdba
Connected.
SQL> alter system set audit_trail=DB scope=spfile ;
OR
audit_trail=true

Step 2 : 
 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Step 3 : 

sqlplus / as sysdba
 SQL> audit session whenever not successful ;

Step 4 :

Test auditing if it works 

sqlplus scott/osama ;

ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.

Step 5:

sqlplus / as sysdba

Use the below Script :

select    os_username,   username,   terminal,   to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') "TIME" from   dba_audit_trail;
 OR

select os_username,username,userhost,to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,returncode from dba_audit_session where action_name = 'LOGON' and returncode > 0
order by timestamp ;

OS_USERNAME     USERNAME        TERMINAL        TIME
--------------- --------------- --------------- -------------------
oracle                               SCOTT              pst/1        07-06-201216:21:13

Enjoy 

Osama ...

No comments:

Post a Comment