If you enable the unified audit feature announced with 12c, all audit records will be in the UNIFIED_AUDIT_TRAIL table. Logon failure records will not be in this table because the corresponding policy is not enabled by default. The policy ORA_LOGON_FAILURES must be enabled to audit the Failed Logon attempts.
Enable ORA_LOGON_FAILURES to track failed logon attempts
You can enable the ORA_LOGON_FAILURES policy as follows.
|
1 2 3 |
SQL> audit policy ORA_LOGON_FAILURES; Audit succeeded. |
Query Failed Logon Attempts
Records will then be created. You can see the number of records related to this policy with a query like the one below.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT audit_type, unified_audit_policies, action_name, return_code, COUNT (*) FROM unified_audit_trail WHERE unified_audit_policies = 'ORA_LOGON_FAILURES' GROUP BY audit_type, unified_audit_policies, action_name, return_code ORDER BY COUNT (*); |
Cleaning SYSAUX Tablespace Reqularly
If the ORA_LOGON_FAILURES policy is enabled, your SYSAUX tablespace can be full in a short time, as too many records will be created. You can learn more about SYSAUX tablespace in the article named “SYSAUX Tablespace Usage”.
You can create a job to automatically remove old records. The following command can be used to create a job that will delete every 24 hours. The first deletion will take place 24 hours after you run the command.
|
1 2 3 4 5 6 7 8 9 |
BEGIN DBMS_AUDIT_MGMT.CREATE_PURGE_JOB ( AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, AUDIT_TRAIL_PURGE_INTERVAL => 24, AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_Purge_Job', USE_LAST_ARCH_TIMESTAMP => TRUE, CONTAINER => DBMS_AUDIT_MGMT.CONTAINER_CURRENT); END; / |
The following command can be used to change the job’s schedule.
|
1 2 3 4 5 6 |
BEGIN DBMS_AUDIT_MGMT.SET_PURGE_JOB_INTERVAL( AUDIT_TRAIL_PURGE_NAME => 'Audit_Trail_Purge_Job', AUDIT_TRAIL_INTERVAL_VALUE => 48); END; / |
