DOYENSYS Knowledge Portal

We Welcome you to visit our DOYENSYS KNOWLEDGE PORTAL : Doyensys Knowledge Portal

Friday, September 5, 2014

Enable Auditing for DB user login attempts in 11g

1 - Add initialization parameters & bounce instance:

alter system set audit_trail=db scope=spfile;

2 - Enable auditing of failed logion attempts :

SQL> audit create session whenever not successful;

To view all the login attempts either success or failure :

col os_username format a15
col username format a15
col terminal format a15
col to_char(timestamp) format a35
select os_username,username,terminal,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') from dba_audit_trail;

To view the failed login attempts:

set lines 130
col OS_USERNAME for a20
col USERNAME for a20
col USERHOST for a20
select OS_USERNAME,USERNAME,USERHOST,to_char(timestamp,'MM-DD-YYYY HH24:MI:SS'), returncode
from dba_audit_trail
where returncode > 0;

Also we can able to see the sys failed and succesful login attempts in audit_file_dest files.

1 comment:

Sridevi K said...

Sridevi Koduru (Senior Oracle Apps Trainer
LinkedIn profile -
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at | +91 - 9581017828.