With AUDIT_TRAIL
The first and preferred solution is with Oracle standard auditing feature.
Start by setting initialization parameter
AUDIT_TRAIL to
db and restart your Oracle database as it is static
parameter.
Then activate network auditing with (as SYS):
SQL> AUDIT network BY ACCESS;
AUDIT succeeded.
|
With below query you get everything needed:
SELECT *
FROM dba_audit_session
ORDER BY sessionid DESC;
|
Returncode column contains Oracle error code and so
different of 0 if logon/logoff issue. The invalid password is the error we are
chasing:
[oracle@server1 ~]$ oerr ora 1017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:
|
So if you find 1017 values in this column then we have found what we were
looking for. For example with my test case where I intentionally specify a wrong
password for my account:
SQL> SELECT username,userhost,returncode
FROM dba_audit_session
WHERE username='YJAQUIER'
ORDER BY sessionid DESC;
USERNAME USERHOST RETURNCODE
------------------------------ -------------------- ----------
YJAQUIER server1 1017
YJAQUIER GVADT30596 0
YJAQUIER server1 0
YJAQUIER server1 0
.
.
.
|
And if you insist, as explained, you get:
SQL> SELECT username, account_status,lock_date, PROFILE FROM dba_users WHERE username='YJAQUIER';
USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE
------------------------------ -------------------------------- -------------------- ------------------------------
YJAQUIER LOCKED(TIMED) 23-nov-2012 10:30:37 DEFAULT
|
If you set AUDIT_TRAIL to db behave the size of SYS.AUD$ table as a small
list of audits are already implemented by default:
SQL> SET lines 200
SQL> SET pages 200
SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;
USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE
------------------------------ ------------------------------ ---------------------------------------- ---------- ----------
ALTER SYSTEM BY ACCESS BY ACCESS
SYSTEM AUDIT BY ACCESS BY ACCESS
CREATE SESSION BY ACCESS BY ACCESS
CREATE USER BY ACCESS BY ACCESS
ALTER USER BY ACCESS BY ACCESS
DROP USER BY ACCESS BY ACCESS
PUBLIC SYNONYM BY ACCESS BY ACCESS
DATABASE LINK BY ACCESS BY ACCESS
ROLE BY ACCESS BY ACCESS
PROFILE BY ACCESS BY ACCESS
CREATE ANY TABLE BY ACCESS BY ACCESS
ALTER ANY TABLE BY ACCESS BY ACCESS
DROP ANY TABLE BY ACCESS BY ACCESS
CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS
GRANT ANY ROLE BY ACCESS BY ACCESS
SYSTEM GRANT BY ACCESS BY ACCESS
ALTER DATABASE BY ACCESS BY ACCESS
CREATE ANY PROCEDURE BY ACCESS BY ACCESS
ALTER ANY PROCEDURE BY ACCESS BY ACCESS
DROP ANY PROCEDURE BY ACCESS BY ACCESS
ALTER PROFILE BY ACCESS BY ACCESS
DROP PROFILE BY ACCESS BY ACCESS
GRANT ANY PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY LIBRARY BY ACCESS BY ACCESS
EXEMPT ACCESS POLICY BY ACCESS BY ACCESS
GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS
CREATE ANY JOB BY ACCESS BY ACCESS
CREATE EXTERNAL JOB BY ACCESS BY ACCESS
|
So you must put in place a purging policy for this table.
Without AUDIT_TRAIL
The only drawback of the previous solution is that you have to restart the
database. And maybe two times because after problem solved you would like to
deactivate auditing. This is most probably not reliable solution on a production
database so I have been looking for a
better solution with no database
reboot.
I initially thought of the
AFTER LOGON trigger but you need
to be logged-in and the BEFORE LOGON does not exits. Then at same documentation
place I found the
AFTER SERVERERROR trigger and decided to give
it a try.
First I created a dummy table to log server error (columns inherited from
dba_audit_session dictionary table):
CREATE TABLE sys.logon_trigger
(
USERNAME VARCHAR2(30),
USERHOST VARCHAR2(128),
TIMESTAMP DATE
);
|
Second I created below trigger:
CREATE OR REPLACE TRIGGER sys.logon_trigger
AFTER SERVERERROR ON DATABASE
BEGIN
IF (IS_SERVERERROR(1017)) THEN
INSERT INTO logon_trigger VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE);
COMMIT;
END IF;
END;
/
|
Then third simulated a wrong password access with my account and issued:
SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';
SESSION altered.
SQL> SET lines 200
SQL> col USERHOST FOR a30
SQL> SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;
USERNAME USERHOST TIMESTAMP
------------------------------ ------------------------------ --------------------
yjaquier ST\GVADT30596 23-nov-2012 11:05:56
|
