ALTER PROFILE nonexpiring LIMIT failed_login_attempts UNLIMITED;
After that I setup an audit to find out whats the issue:
AUDIT SESSION BY PASGEN BY ACCESS WHENEVER NOT SUCCESSFUL;
Then I had an hour or longer wasted trying to find out why I see wrong records from SYS.AUD$. I tried to filter only new entries using ntimestamp# - for unknown reason timestamp# (type DATE) has not been populated. Anway - ntimestamp# appears to be in UTC timezone and it took me some time to figure it out. Finally I found DBA_AUDIT_TRAIL and this is where I should start.
So query which work best for me is:
SELECT os_username,username,userhost,os_process,terminal, action_name,comment_text, timestamp
FROM dba_audit_trail
WHERE returncode = 1017
AND timestamp > localtimestamp - 1/24/60 * 5 -- last 5 minutes
AND username='PASGEN'
ORDER BY timestamp, os_username,userhost,os_process;
And sometimes - to see more details - I put asterisk instead of list of columns for SELECT above.
Another interesting issue with the database based audit trail (standard audit trail) is this log grows quickly if there is a lot of entries to be stored. That's particularly true statement when you have an application trying to connect with the wrong password over and over, sometimes a couple of times per second. So the SYSAUX tablespace may be overflowed quickly. This is the reason I implemented automatic cleanup for this tablespace:
SYS@DEV1> show parameter audit_trail;
NAME TYPE VALUE
----------- ------------------------------
audit_trail string DB
SYS@DEV1>
SET SERVEROUTPUT ON
BEGIN
IF
DBMS_AUDIT_MGMT.IS_CLEANUP_INITIALIZED(
DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD)
THEN
DBMS_OUTPUT.PUT_LINE('AUD$ is initialized for cleanup');
ELSE
DBMS_OUTPUT.PUT_LINE('AUD$ is not initialized for cleanup.');
END IF;
END;
/
SYS@DEV1>
AUD$ is not initialized for cleanup.
PL/SQL procedure successfully completed.
SYS@DEV1>
Here is an excerpt form the documentation:
DEFAULT_CLEANUP_INTERVAL: Specify the desired default hourly purge interval (for example, 12 for every 12 hours). The DBMS_AUDIT_MGMT procedures use this value to determine how to purge audit records. The timing begins when you run the DBMS_AUDIT_MGMT.INIT_CLEANUP procedure. To update this value later, set the DBMS_AUDIT_MGMT.CLEAN_UP_INTERVAL property of the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.The
DEFAULT_CLEANUP_INTERVAL setting must indicate the frequency in which DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL
is called. If you are uncertain about the frequency, set it to an
approximate value. You can change this value later on by using the DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_PROPERTY procedure.BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 48 );
END;
/
You can cancel the
DBMS_AUDIT_MGMT.INIT_CLEANUP settings, that is, the default cleanup interval, by invoking the DBMS_AUDIT_MGMT.DEINIT_CLEANUP procedure.For example, to cancel all purge settings for the standard audit trail:
BEGIN DBMS_AUDIT_MGMT.DEINIT_CLEANUP( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD); END; /
Views
SELECT * FROM DBA_FGA_AUDIT_TRAIL;
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS;
SELECT * FROM DBA_AUDIT_MGMT_CLEANUP_JOBS;
SELECT * FROM DBA_AUDIT_MGMT_CONFIG_PARAMS;
SELECT * FROM DBA_AUDIT_MGMT_LAST_ARCH_TS;
Documentation Pointers
- Oracle® Database Security Guide 11g Release 2 (11.2)
