Oracle auditing: enable, query and purge the audit trail
Enable Oracle auditing, audit logons and failed statements, query the audit views, purge SYS.AUD$ with DBMS_AUDIT_MGMT and locate the alert log.
Oracle auditing records who connects to a database and which statements, privileges or objects are used. This article covers locating the diagnostic and alert log directories, enabling the standard audit trail, auditing logons and failed statements, reading the results, and purging the audit trail.
Locate the diagnostic and alert log directories
From version 11g onwards, V$DIAG_INFO gives the location of the diagnostic directories, including the alert log:
set linesize 121
col name format a30
col value format a75
SELECT * FROM gv$diag_info;
On older versions (before 11g), check the audit and background dump parameters:
show parameter audit;
show parameter background;
background_dump_dest holds the alert log. For SQL tracing, see SQL trace, TKPROF and trcsess.
Enable the audit trail
The AUDIT_TRAIL initialization parameter controls where audit records are stored.
| Value | Behavior |
|---|---|
NONE |
Auditing is disabled (default on older versions). |
DB |
Records are stored in the table SYS.AUD$. |
DB,EXTENDED |
As DB, and the SQL_BIND and SQL_TEXT columns are also populated. |
XML |
Records are stored in XML files. |
XML,EXTENDED |
As XML, with SQL_BIND and SQL_TEXT populated. |
OS |
Records are written to text files on the operating system (AUDIT_FILE_DEST gives the directory). |
show parameter audit_trail;
alter system set audit_trail=db,extended scope=spfile;
Other forms:
ALTER SYSTEM SET audit_trail = NONE SCOPE=SPFILE;
-- OS: audit records are written to the operating system audit trail
ALTER SYSTEM SET audit_trail = OS SCOPE=SPFILE;
-- DB: audit records are written to the data dictionary table SYS.AUD$
ALTER SYSTEM SET audit_trail = DB SCOPE=SPFILE;
-- DB_EXTENDED (11g): as DB, with the SQL text and bind variables
ALTER SYSTEM SET audit_trail = DB_EXTENDED SCOPE=SPFILE;
The parameter is static: restart the instance for the change to take effect.
SHUTDOWN IMMEDIATE
STARTUP
Audit levels
Four levels exist:
- Connection and disconnection: monitors logons.
- SQL statement: audit by type of SQL statement.
- Privilege: audit of a system privilege (
SELECT ANY,DROP ANY,CREATE ANY...). - Object: a given statement on a given object (for example
SELECTonSCOTT.EMP).
At each level you can audit successes, failures, or both, and get one record per command (BY ACCESS) or one per session (BY SESSION). The AUDIT command sets what is audited: the action type, successful and/or failed attempts, and the granularity.
AUDIT SESSION;
AUDIT SESSION WHENEVER SUCCESSFUL;
AUDIT SESSION WHENEVER NOT SUCCESSFUL;
AUDIT CONNECT WHENEVER NOT SUCCESSFUL;
-- one record per command
AUDIT CREATE ANY PROCEDURE
BY ACCESS
WHENEVER SUCCESSFUL;
-- one record per session, failed attempts only
AUDIT SELECT TABLE, DELETE TABLE
BY SESSION
WHENEVER NOT SUCCESSFUL;
-- object audit
AUDIT INSERT ON scott.dept;
To stop auditing a session:
NOAUDIT SESSION;
Audit data dictionary views
| View | Content |
|---|---|
| STMT_AUDIT_OPTION_MAP | option type codes |
| AUDIT_ACTIONS | action codes |
| ALL_DEF_AUDIT_OPTS | default object audit options |
| DBA_STMT_AUDIT_OPTS | current statement audit options |
| DBA_PRIV_AUDIT_OPTS | current system privilege audit options |
| DBA_OBJ_AUDIT_OPTS, USER_OBJ_AUDIT_OPTS | audit options on all objects / on the user's objects |
| DBA_AUDIT_TRAIL, USER_AUDIT_TRAIL | all audit entries / only the user's |
| DBA_AUDIT_OBJECT, USER_AUDIT_OBJECT | object audit entries (all / user) |
| DBA_AUDIT_SESSION, USER_AUDIT_SESSION | connection and disconnection entries (all / user) |
| DBA_AUDIT_STATEMENT, USER_AUDIT_STATEMENT | GRANT, REVOKE, AUDIT, NOAUDIT and ALTER SYSTEM entries (all / user) |
| DBA_AUDIT_EXISTS | AUDIT EXISTS and AUDIT NOT EXISTS entries |
Check the current audit options
Query the %AUDIT_OPTS views:
SELECT * FROM sys.dba_obj_audit_opts
WHERE owner = 'SCOTT' AND object_name LIKE 'EMP%';
OWNER OBJECT_NAME OBJECT_TY ALT AUD COM DEL GRA IND INS LOC
----- ----------- --------- --- --- --- --- --- --- --- ---
SCOTT DEPT TABLE -/S -/- -/- A/- -/- S/S -/- -/- ...
SCOTT D VIEW -/- -/- -/- -/A -/- -/- -/- -/- ...
Reading the flags: - means no audit, S per session, A per access. The value on the left of / applies to successful attempts, the value on the right to unsuccessful ones. The first row above reads: for table SCOTT.DEPT, ALTER is audited on failure per session, DELETE on success per access, and INDEX on success and failure per session.
To list everything that is currently enabled:
-- Audit-related parameters
select name || '=' || value PARAMETER from sys.v_$parameter where name like '%audit%';
-- Statement audits
column user_name format a10
column audit_option format a40
select * from sys.dba_stmt_audit_opts;
-- Privilege audits
select * from dba_priv_audit_opts;
-- Object audits
select (owner ||'.'|| object_name) object_name,
alt, aud, com, del, gra, ind, ins, loc, ren, sel, upd, ref, exe
from dba_obj_audit_opts
where alt != '-/-' or aud != '-/-'
or com != '-/-' or del != '-/-'
or gra != '-/-' or ind != '-/-'
or ins != '-/-' or loc != '-/-'
or ren != '-/-' or sel != '-/-'
or upd != '-/-' or ref != '-/-'
or exe != '-/-';
-- Default audits
select * from all_def_audit_opts;
Read the audit results
SELECT os_username, username, terminal, timestamp
FROM dba_audit_session;
SELECT os_username, username, terminal, timestamp, owner, obj_name
FROM dba_audit_object;
SELECT username, returncode, action_name, comment_text, sessionid, proxy_sessionid
FROM dba_audit_trail
ORDER BY sessionid;
Number of logon records, with details and aggregation per user and month:
select count(*) from dba_audit_session;
COL OSUSER FOR A20
COL DBUSER FOR A20
COL TERMINAL FOR A15
SET LINESIZE 150
SET PAGESIZE 1000
SELECT os_username "OSUSER",
username "DBUSER",
terminal,
returncode,
timestamp,
TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') LOGON_TIME,
TO_CHAR(logoff_time, 'DD-MON-YYYY HH24:MI:SS') LOGOFF_TIME
FROM dba_audit_session
WHERE logoff_time IS NOT NULL
AND logoff_time > to_date('01-12-2021','DD-MM-YYYY')
AND username != 'MONITOR_USER'
ORDER BY timestamp;
COL OSUSER FOR A20
COL DBUSER FOR A20
COL TERMINAL FOR A15
SET LINESIZE 150
SET PAGESIZE 1000
SELECT os_username "OSUSER",
username "DBUSER",
terminal,
count(*),
to_char(timestamp, 'YYYY-MM') month
FROM dba_audit_session
WHERE timestamp IS NOT NULL
GROUP BY os_username, username, terminal, to_char(timestamp, 'YYYY-MM')
ORDER BY to_char(timestamp, 'YYYY-MM') DESC;
Last month of activity per database user (useful to spot unused accounts):
COL DBUSER FOR A20
SET LINESIZE 150
SET PAGESIZE 1000
SELECT
username "DBUSER",
count(*),
to_char(max(timestamp), 'YYYY-MM') month
FROM dba_audit_session
WHERE timestamp IS NOT NULL
GROUP BY username
ORDER BY to_char(max(timestamp), 'YYYY-MM') DESC;
Find the origin of failed logins
Enable auditing of failed logons, then read SYS.AUD$ for return code 1017:
show parameter audit_trail
audit session whenever not successful;
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
set linesize 400
set pagesize 10000
col ntimestamp# for a30 heading "Timestamp"
col userid for a20 heading "Username"
col userhost for a15 heading "Machine"
col spare1 for a15 heading "OS User"
col comment$text for a80 heading "Details" wrap
select ntimestamp#, userid, userhost, spare1, comment$text
from sys.aud$
where returncode=1017
order by 1;
The same information is available through DBA_AUDIT_TRAIL:
SELECT os_username, username, terminal,
to_char(timestamp,'MM-DD-YYYY HH24:MI:SS') as timestamp,
returncode
FROM DBA_AUDIT_TRAIL
WHERE returncode <> 0
ORDER BY timestamp DESC;
RETURNCODE is the number of the ORA- error returned to the client:
0: success1017: invalid password28000: account locked
Audit failed statements and DDL
Audit all failed statements on tables and procedures, and disable it afterwards:
AUDIT SELECT TABLE, INSERT TABLE, UPDATE TABLE, DELETE TABLE, EXECUTE PROCEDURE
BY ACCESS WHENEVER NOT SUCCESSFUL;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
Audit table creation and drops:
AUDIT CREATE TABLE, SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
AUDIT EXECUTE PROCEDURE;
AUDIT CREATE ANY TABLE;
AUDIT DROP ANY TABLE;
View the records:
SELECT USERNAME, OS_USERNAME, ACTION_NAME, OBJ_NAME,
TO_CHAR(TIMESTAMP,'dd/mm/yyyy hh24:mi') Time, PRIV_USED
FROM dba_audit_trail
WHERE ACTION_NAME='CREATE TABLE';
select extended_timestamp, username, owner, obj_name, action_name, sql_text
from dba_audit_trail
where action_name not in ('LOGON','LOGOFF','LOGOFF BY CLEANUP')
order by extended_timestamp desc;
Check which session-related statement options are enabled:
SELECT user_name, audit_option, success, failure
FROM DBA_STMT_AUDIT_OPTS
WHERE audit_option LIKE '%SESSION%';
Disable:
NOAUDIT CREATE TABLE, SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE BY ACCESS WHENEVER NOT SUCCESSFUL;
NOAUDIT EXECUTE PROCEDURE;
NOAUDIT CREATE ANY TABLE;
NOAUDIT DROP ANY TABLE;
Audit all actions on an object
With traditional auditing:
alter system set audit_trail=db,extended scope=spfile;
audit all on scott.emp by access;
select extended_timestamp, username, owner, obj_name, action_name, sql_text
from dba_audit_trail
order by extended_timestamp desc;
There is no "audit all objects" setting. With unified auditing, create a policy and add the objects to it:
-- create the policy
create audit policy my_policy actions all on hr.regions;
alter audit policy my_policy actions all on hr.locations;
-- enable the policy
audit policy my_policy;
-- generate the statements for every table of a schema
select 'alter audit policy my_policy actions all on '||owner||'.'||table_name||';'
from dba_tables
where owner in ('HR','OE');
Inspect the unified auditing policies and records:
SELECT policy_name, enabled_opt, user_name FROM audit_unified_enabled_policies;
SET LINESIZE 200
SET PAGESIZE 300
COLUMN audit_option FORMAT A40
COLUMN condition_eval_opt FORMAT A10
COLUMN audit_condition FORMAT A50
COLUMN policy_name FORMAT A20
SELECT policy_name, audit_option, condition_eval_opt, audit_condition
FROM audit_unified_policies
WHERE policy_name in ('ORA_SECURECONFIG','ORA_LOGON_FAILURES')
ORDER BY 1,2;
select count(*), action_name, unified_audit_policies
from cdb_unified_audit_trail
group by action_name, unified_audit_policies
order by 1 desc;
To list every audit-related view:
SELECT view_name
FROM dba_views
WHERE view_name LIKE 'DBA%AUDIT%'
ORDER BY view_name;
Disable auditing
NOAUDIT resets statement, privilege and object audit options:
NOAUDIT;
NOAUDIT session;
NOAUDIT session BY scott, hr;
NOAUDIT DELETE ON emp;
NOAUDIT SELECT TABLE, INSERT TABLE, DELETE TABLE, EXECUTE PROCEDURE;
NOAUDIT ALL;
NOAUDIT ALL PRIVILEGES;
NOAUDIT ALL ON DEFAULT;
NOAUDIT session BY appowner;
NOAUDIT create session BY appowner;
NOAUDIT SELECT TABLE BY appowner;
NOAUDIT INSERT TABLE BY appowner;
NOAUDIT EXECUTE PROCEDURE BY appowner;
NOAUDIT DELETE TABLE BY appowner;
NOAUDIT DELETE ANY TABLE BY appowner;
Purge the audit trail
Check the space used
When the audit trail is stored in the SYSAUX tablespace, SYS.AUD$ can grow large:
SET LINES 400 PAGES 800
COL SCHEMA_NAME FOR A20
COL OCCUPANT_NAME FOR A25
COL OCCUPANT_DESC FOR A55
SELECT SCHEMA_NAME, OCCUPANT_NAME, OCCUPANT_DESC,
ROUND(SPACE_USAGE_KBYTES/1024) SPACE_USAGE_MB
FROM V$SYSAUX_OCCUPANTS
ORDER BY SPACE_USAGE_MB;
SELECT SEGMENT_TYPE, ROUND(SUM(BYTES)/1024/1024) MB
FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME = 'SYSAUX'
GROUP BY SEGMENT_TYPE
ORDER BY 2;
COL OWNER FOR A20
COL SEGMENT_NAME FOR A20
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, BYTES/1024/1024
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME='AUD$';
Check the audit management configuration:
COLUMN parameter_name FORMAT A30
COLUMN parameter_value FORMAT A20
COLUMN audit_trail FORMAT A20
SET LINES 2000
SELECT * FROM dba_audit_mgmt_config_params;
Example of output after INIT_CLEANUP with a 12 hour interval:
DB AUDIT TABLESPACE SYSAUX STANDARD AUDIT TRAIL
DB AUDIT TABLESPACE SYSAUX FGA AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 OS AUDIT TRAIL
AUDIT FILE MAX SIZE 10000 XML AUDIT TRAIL
AUDIT FILE MAX AGE 5 OS AUDIT TRAIL
AUDIT FILE MAX AGE 5 XML AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 STANDARD AUDIT TRAIL
DB AUDIT CLEAN BATCH SIZE 10000 FGA AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 OS AUDIT TRAIL
OS FILE CLEAN BATCH SIZE 1000 XML AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 STANDARD AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 FGA AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 OS AUDIT TRAIL
DEFAULT CLEAN UP INTERVAL 12 XML AUDIT TRAIL
Check the last purges and, on RAC, the last archive timestamp:
SELECT * FROM DBA_AUDIT_MGMT_CLEAN_EVENTS WHERE CLEANUP_TIME > SYSDATE - 31;
-- RAC
COLUMN AUDIT_TRAIL FORMAT A20
COLUMN LAST_ARCHIVE_TS FORMAT A40
select AUDIT_TRAIL, RAC_INSTANCE, DATABASE_ID, CONTAINER_GUID, LAST_ARCHIVE_TS
from DBA_AUDIT_MGMT_LAST_ARCH_TS;
Purge with DBMS_AUDIT_MGMT
Initialize the cleanup once, with a default interval in hours:
BEGIN
DBMS_AUDIT_MGMT.INIT_CLEANUP(
AUDIT_TRAIL_TYPE => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
DEFAULT_CLEANUP_INTERVAL => 12 );
END;
/
Declare that all standard database audit records before a given date have been archived:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
last_archive_time => to_timestamp('2013-01-01 10:00:00','YYYY-MM-DD HH24:MI:SS'),
rac_instance_number => null
);
END;
/
Purge the records older than that timestamp:
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => TRUE);
END;
/
-- purge everything, ignoring the last archive timestamp
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,
use_last_arch_timestamp => FALSE);
END;
/
For the OS audit trail, the same procedures take AUDIT_TRAIL_OS:
BEGIN
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
last_archive_time => TO_TIMESTAMP('21-MAR-18','DD-MON-YY'),
rac_instance_number => 1);
END;
/
BEGIN
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_OS,
use_last_arch_timestamp => TRUE);
END;
/
Verify the result before and after the purge, and check that the automatic purge job exists:
select min(timestamp), max(timestamp) from dba_audit_session;
select dbid, count(*) from aud$ group by dbid;
SELECT COUNT(*) FROM aud$;
select * from DBA_AUDIT_MGMT_CLEAN_EVENTS where CLEANUP_TIME > sysdate - 31;
SELECT distinct job_name from dba_scheduler_job_run_details where job_name like '%PURGE%';
On 11g with SQL*Plus, a full emptying of the table is also possible:
truncate table sys.aud$;
-- or
DELETE FROM SYS.AUD$;
Workaround for old versions
Without DBMS_AUDIT_MGMT, a procedure can delete old records periodically:
create or replace procedure purge_audit_trail (days in number) as
purge_date date;
begin
purge_date := trunc(sysdate-days);
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' started');
delete from aud$ where ntimestamp# < purge_date;
commit;
dbms_system.ksdwrt(2,'AUDIT: Purging Audit Trail until ' ||
purge_date || ' has completed');
end;
/
Schedule it with the scheduler (here a job that keeps 31 days):
BEGIN
sys.dbms_scheduler.create_job(
job_name => 'AUDIT_PURGE',
job_type => 'PLSQL_BLOCK',
job_action => 'begin purge_audit_trail(31); end;',
schedule_name => 'MAINTENANCE_WINDOW_GROUP',
job_class => '"DEFAULT_JOB_CLASS"',
comments => 'Audit Trail Purge',
auto_drop => FALSE,
enabled => TRUE);
END;
/
Error ORA-01688 and tablespace usage
When a segment cannot extend (ORA-01688), check the free space per tablespace:
SELECT df.tablespace_name "Tablespace",
totalusedspace "Used MB",
(df.totalspace - tu.totalusedspace) "Free MB",
df.totalspace "Total MB",
round(100 * ((df.totalspace - tu.totalusedspace) / df.totalspace)) "Pct. Free"
FROM (SELECT tablespace_name, round(sum(bytes) / 1048576) TotalSpace FROM dba_data_files GROUP BY tablespace_name) df,
(SELECT round(sum(bytes)/(1024*1024)) totalusedspace, tablespace_name FROM dba_segments GROUP BY tablespace_name) tu
WHERE df.tablespace_name = tu.tablespace_name;
Last statements executed by a user
Look in the shared pool for the most recent statements of a given database user (replace the user name in the filter):
alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";
set linesize 400
set pagesize 0
column MODULE format a20
column SQL_PROFILE format a5
column SERVICE format a20
column LAST_LOAD_TIME format a20
SELECT
S.LAST_ACTIVE_TIME,
S.MODULE,
S.SQL_FULLTEXT,
S.SQL_PROFILE,
S.EXECUTIONS,
S.LAST_LOAD_TIME,
S.PARSING_USER_ID,
S.SERVICE
FROM
SYS.V_$SQL S,
SYS.ALL_USERS U
WHERE
S.PARSING_USER_ID=U.USER_ID
AND UPPER(U.USERNAME) IN ('ORACLE_USER_NAME_HERE')
ORDER BY TO_DATE(S.LAST_LOAD_TIME, 'YYYY-MM-DD/HH24:MI:SS') desc;
Notes
AUDIT_TRAILchanges need an instance restart (SCOPE=SPFILE).TRUNCATEandDELETEonSYS.AUD$remove audit evidence: archive the records first if they must be kept.- Auditing every successful
SELECTorLOGONcan growSYS.AUD$quickly; plan a purge job. - The purge procedure relies on
DBMS_SYSTEM, an undocumented package. - Unified auditing (12c and later) replaces the traditional audit trail and uses policies instead of the
AUDITstatements shown above.