Trace session logon

-- ############################################################
-- # Creator: Nicolas Chabault
-- # Created: 2011/06/14
-- # Name: Trace session logon
-- ############################################################
-- #
-- # Compatible: Oracle 9i 10g 11g
-- #
-- ############################################################
-- #
-- # Trace session and store logon data in the table dba_audit_trail 
-- # to analyze security or account locking.
-- #
-- ############################################################

Trace session to prevent account lock with the following query:

   - Active the parameter audit_trail = db 
                               SQL> alter system  set audit_trail=DB scope=spfile + startup force);
   - Execute : 
                               SQL> audit create session whenever not successful;
   - Execute : 
                               SQL select os_username, username, terminal, to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') 
                               from dba_audit_trail where action_name='LOGON';

-- If you have the response : "no row selected", there's not login authentification problems
-- if you have a login failed you'll have the following lines :              
                administrator
                SYS
                PTS/2
                07-14-2011 16:00:00


-- To remove the audit (You can also put the parameter "audit_trail = none" for every audits)
                               SQL> NOaudit create session whenever not successful;

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.