Oracle Database login audit

In some cases you are required as an Oracle database administrator to provide audit information on the usage of your database. This can include providing information on who logged on to the database both successful and unsuccessful. Also in some cases you might be required to help users to find out why they are unable to connect to your database. In those cases it is good to know that Oracle is creating an audit trail for all login attempts both successful and unsuccessful. You can query this audit trail via DBA_AUDIT_SESSION.

when you query DBA_AUDIT_SESSION you will note that you can view all moments a session is doing a LOGON or LOGOFF action. This can be seen in the action_name column. In most cases you are not that interested however in all connections that where successful, commonly the unsuccessful attempts are much more of interest to you.

Every unsuccessful attempt has a return code which is not equal to 0. the return code 0 is a successful attempt to create a connection. If you wan to query all unsuccessful ones the query below might be of interest to you. This is also providing you a not more information then only the returncode, it is telling you the meaning of the code. Next to this it is showing you the username used, the username of the person who is attempting to connect on his local workstation, the name of the local workstation used to try and establish a connection and a timestamp (of the database server)

SELECT 
      username, 
      os_username, 
      userhost, 
      extended_timestamp,
      decode( returncode
            , 00911, 'Invalid Character'
            , 00988, 'Missing or invalid password(s).'
            , 01004, 'Logon denied.'
            , 01005, 'Null Password'
            , 01017, 'Invalid username/password.'
            , 01031, 'No Privilege'
            , 01045, 'User string lacks CREATE SESSION privilege; logon denied.'
            , 01918, 'No Such UserID'
            , 01920, 'No Such Role'
            , 09911, 'Incorrect user password.'
            , 28000, 'The account is locked.'
            , 28001, 'Your password has expired.'
            , 28002, 'Your account will expire soon; change your password now.'
            , 28003, 'The password is not complex enough.'
            , 28007, 'Password cannot be reused.'
            , 28008, 'Invalid old password.'
            , 28009, 'Connection to sys should be as sysdba or sysoper.'
            , 28011, 'Your account will expire soon; change your password now.'
            , 28221, 'The original password was not supplied.') as returncode
FROM 
    SYS.DBA_AUDIT_SESSION 
WHERE 
     returncode != 0 
ORDER BY 
        extended_timestamp desc