Saturday, October 19, 2013

How to use sqlerrm to reveal the meaning of audit information


As an example, the audit information may look like the following:

ALTER SESSION SET NLS_DATE_FORMAT='DD.MM.YYYY HH24:MI:SS';

SELECT
 os_username,
 userhost,
 timestamp,
 returncode
FROM  dba_audit_session
WHERE action_name = 'LOGON'
AND   returncode > 0
AND   trunc(timestamp) = to_date('08.07.2013', 'DD.MM.YYYY')
AND   username = 'TSF'
ORDER BY timestamp DESC;

Result:
OS_USERNAME USERHOST       TIMESTAMP            RETURNCODE
----------- -----------    -------------------  ---------- 
billyb      CHM06071130    08.07.2013 12:24:07  1017  
billyb      CHM06071130    08.07.2013 10:06:06  28000  
You can use the sqlerrm keyword to print the meaning of a return code, like this:

exec dbms_output.put_line(sqlerrm(-1017)) ;
exec dbms_output.put_line(sqlerrm(-28000)) ;
These commands will yield:

ORA-01017: invalid username/password; logon denied
ORA-28000: the account is locked

No comments:

Post a Comment