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;
audit_file_dest='/u01/app/oracle/mysid/mydir/'


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

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.