First page Back Continue Last page Graphics
Monitoring Logon Attempts
SQL> SELECT os_username, username, terminal,
2 DECODE(returncode, '0', 'Connected'
3 , '1005' , 'No Password'
4 , '1017' , 'Invalid Password'
5 , '1045' , 'No CREATE SESSION'
6 , '28001', 'Password Expired'
7 , returncode ) x,
8 TO_CHAR(timestamp, 'mm/dd/yy hh24:mi') y,
9 TO_CHAR(logoff_time, 'mm/dd/yy hh24:mi') z
10 FROM dba_audit_session
11 /
OS_USERNAME USERNAME TERMINAL RESULT TIME
------------ --------- ------------ ----------- --------------
suzanne SCOTT SUZANNE Connected 04/06/04 14:32
Notes:
As stated earlier in this lesson, audit records are written to an OS file or the SYS.AUD$ table, depending on the value of the AUDIT_TRAIL parameter. Instead of directly querying SYS.AUD$, there is a convenient set of views that are easier to use.
If AUDIT_TRAIL=DB, query the DBA_AUDIT_SESSION dictionary view to display audit records related to logon (and logoff) attempts (as shown in the example above). This query is available to you in your supplied scripts as AUD_LOGINS.SQL.
Supplemental Notes
The DBA_AUDIT_TRAIL view is an aggregate of all audited activity. It has many uses, including finding the IP address of the user connecting to your database:
SQL> select comment_text from dba_audit_trail;
COMMENT_TEXT
---------------------------------------------------------
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1503))
Authenticated by: DATABASE; Client address: (ADDRESS=(PROTOCOL=tcp)(HOST=10.0.0.3)(PORT=1502))