* [[https://dbaclass.com/article/unified-audit-trail-in-oracle-12c/|Good explanation of differences between standard and Unified auditing]]
* [[https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=s135vnnwx_87&_afrLoop=562289631709984#aref_section324|Master note on Oracle Auditing]]
* [[https://docs.oracle.com/database/121/DBSEG/E48135-11.pdf|Oracle Database Security Guide 12c]]
* [[https://oracle-base.com/articles/11g/auditing-enhancements-11gr2|Oracle-Base 11gR2 Auditing enhancements]]
* [[https://support.oracle.com/epmos/faces/DocumentDisplay?id=1362997.1|Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt and dbms_scheduled_jobs]]
TRADITIONAL AUDITING UNIFIED_AUDITING
Depends on db init parameter like audit_trail,audit_sys_log Independent of db parameter,bydefault enabled
Writes audit records to different trails depending upon audit type All audit trails are writeen to single trail
sys records are written to os .aud files sys records are written to unified_audit_trail
Auditing not possible for rman/expdp/sqllder Auditing can be enabled for db compoenents like rman/datapump/sqlldr
Each audit record was written to disk immediately, which causes i/o issue If querywrite method is enabled(default), then all audit records will queued in sga
and later will be flushed to disk which improves performance
Auditing need to enabled for each action/statement individually One policy can contain mulitiple actions/privilge/role audit option and
which can enabled or disables easily
==== Relink Oracle binaries to enable Unified Auditing ====
After having shutdown any instances and listeners, ...
cd $ORACLE_HOME/rdbms/lib
make -f ins_rdbms.mk uniaud_on ioracle
then restart evertything that was shutdown.
==== Audit records for unsuccessful login attempts ====
If a user fails to logon and auditing of failed logons is enabled with
audit session whenever not successful;
then
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$
order by 1;
==== Check if auditing is enabled (or what is being audited?) ====
=== where unified auditing is not used ===
select * from dba_obj_audit_opts;
=== where unified auditing is used ===
set lines 1000 pages 100
col user_name for a20
col policy_name for a30
col enabled_opt for a25
select *
from audit_unified_enabled_policies
/
USER_NAME POLICY_NAME ENABLED_OPT SUC FAI
-------------------- ------------------------------ ------------------------- --- ---
ALL USERS ORA_SECURECONFIG BY YES YES
ALL USERS ORA_LOGON_FAILURES BY NO YES
2 rows selected.
==== See the latest activity on the database (non-unified) ====
set lines 1000 pages 100
col username for a20
col userhost for a20
select username
, userhost
, count(*) num_connections
, max(timestamp) last_time
from dba_audit_trail
group by username
, userhost
/
set lines 80
==== Has your database migrated to (Pure) Unified Auditing? ====
select value from v$option where parameter = 'Unified Auditing';
==== Show the unified audit trail (12c+) ====
col unified_audit_policies for a30
col action_name for a20
select unified_audit_policies
, action_name
, count(*)
from unified_audit_trail
group by unified_audit_policies
, action_name
/
set lines 2000
col dbusername for a32
col event_timestamp for a23
col sql_text for a200
select dbusername
, event_timestamp
, sql_text
from unified_audit_trail
order by event_timestamp
/
==== Disable unified auditing ====
noaudit policy ORA_SECURECONFIG;
Noaudit succeeded.
==== 12c auditing is protected ====
The Unified Audit Trail is well protected. Not even SYS can mess with it.
select table_name from dba_tables where owner='AUDSYS';
TABLE_NAME
--------------------------------------------------------------------------------------------------------------------------------
AUD$UNIFIED
SQL> truncate table audsys."AUD$UNIFIED";
truncate table audsys."AUD$UNIFIED";
*
ERROR at line 1:
ORA-55941: DML and DDL operations are not allowed on table "AUDSYS"."AUD$UNIFIED";
SQL> drop user audsys cascade;
drop user audsys cascade
*
ERROR at line 1:
ORA-28050: specified user or role cannot be dropped
=== Manage the audit trail (12c+) ===
==== Check whether audit trail cleanup has been initialised ====
set serveroutput on
begin
if dbms_audit_mgmt.is_cleanup_initialized (dbms_audit_mgmt.audit_trail_aud_std) then
dbms_output.put_line('yes');
else
dbms_output.put_line('not yet...');
dbms_audit_mgmt.init_cleanup(
audit_trail_type => dbms_audit_mgmt.audit_trail_all,
default_cleanup_interval => 24 /* hours */);
dbms_output.put_line('yes!');
end if;
end;
/
The only way to work with the audit trail is with the dbms_audit_mgmt package.\\
Before the audit trail can be purged, you must initialise it. This is a one-off execution of the init_cleanup procedure.
==== Initialise the cleanup ====
begin
dbms_audit_mgmt.init_cleanup (
audit_trail_type => dbms_audit_mgmt.audit_trail_all,
default_cleanup_interval => 24 /* hours */
);
end;
/
PL/SQL procedure successfully completed.
==== Set auditing properties ====
exec dbms_audit_mgmt.set_audit_trail_property ( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std
, audit_trail_property => dbms_audit_mgmt.db_delete_batch_size
, audit_trail_property_value => 1000
);
=== Check audit settings ===
col parameter_name for a35
col parameter_value for a35
select * from dba_audit_mgmt_config_params order by 3;
=== When was audit trail last purged? ===
set lines 1000 pages 100
col audit_trail for a20
col last_archive_ts for a40
select *
from dba_audit_mgmt_last_arch_ts
order by 1,3
/
AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS DATABASE_ID CONTAINER_GUID
-------------------- ------------ ---------------------------------------- ----------- ---------------------------------
OS AUDIT TRAIL 1 17-JUL-20 07.00.00.000000 AM +01:00 3045137910 19FA24F4459E0060E0530A085A364FFF
OS AUDIT TRAIL 1 12-MAR-21 07.00.03.000000 AM +01:00 2956116393 19FA24F4459E0060E0530A085A364FFF
STANDARD AUDIT TRAIL 0 17-JUL-20 07.00.01.000000 AM +00:00 3045137910 19FA24F4459E0060E0530A085A364FFF
STANDARD AUDIT TRAIL 0 12-MAR-21 07.00.03.000000 AM +00:00 2956116393 19FA24F4459E0060E0530A085A364FFF
XML AUDIT TRAIL 1 17-JUL-20 07.00.01.000000 AM +01:00 3045137910 19FA24F4459E0060E0530A085A364FFF
XML AUDIT TRAIL 1 12-MAR-21 07.00.03.000000 AM +01:00 2956116393 19FA24F4459E0060E0530A085A364FFF
6 rows selected.
=== Set a date to purge to ===
Setup to purge anything older than 90 days before today
begin
dbms_audit_mgmt.set_last_archive_timestamp(
audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
last_archive_time => systimestamp-90);
end;
/
=== Run the audit cleanup ===
audit_trail_aud_std represents the standard old style auditing in aud$
begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std
, use_last_arch_timestamp => true
);
end;
/
or
audit_trail_fga_std represents the fine_grained auditing where records are held in fga_log$
begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => dbms_audit_mgmt.audit_trail_fga_std
, use_last_arch_timestamp => true
);
end;
/
or
audit_trail_db_std combines both aud and fga auditing where records are held in aud$ and fga_log$
begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => dbms_audit_mgmt.audit_trail_db_std
, use_last_arch_timestamp => true
);
end;
/
or
audit_trail_db_unified represents the unified auditing where records are held in the audsys schema (use UNIFIED_AUDIT_TRAIL view)
begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => dbms_audit_mgmt.audit_trail_unified
, use_last_arch_timestamp => true
);
end;
/
==== Ignore the timestamp and remove all records (will probably be a truncate instead of a delete) ====
begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => dbms_audit_mgmt.audit_trail_unified
, use_last_arch_timestamp => false
);
end;
/
For a full list of auditing types search for "DBMS_AUDIT_MGMT Constants - Audit Trail Types" in [[https://docs.oracle.com/database/121/ARPLS/d_audit_mgmt.htm#ARPLS65396]]
==== Audit Inserts on a particular table ====
select owner
, segment_name
, segment_type
, bytes/1024/1024/1024
from dba_segments
where 1=1
and segment_name = 'QUERY_RESULT'
/
OWNER SEGMENT_NAME SEGMENT_TYPE BYTES/1024/1024/1024
------------------------------ --------------------------------------------------------------------------------- ------------------ --------------------
ENDUR_PRD1 QUERY_RESULT TABLE .702148438
select count('e')
from query_result
/
COUNT(1)
----------
26413733
1 row selected.
OWNER TABLE_NAME Fragmented size Actual size Space reclaimable reclaimable space %
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------------------ --------------------
ENDUR_PRD1 QUERY_RESULT .7GB .29GB .41GB 48.5714286
SELECT table_name
, tablespace_name
FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$')
ORDER BY table_name
/
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
AUD$ AUDIT_DT
FGA_LOG$ SYSTEM
BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,
audit_trail_location_value => 'AUDIT_DT');
END;
/
dbms_fga.add_policy ( object_schema => 'ENDUR_PRD1'
, object_name => 'QUERY_RESULT'
, policy_name => 'QUERY_RESULT_POLICY'
, audit_condition => NULL
, audit_column_opts => DBMS_FGA.ALL_COLUMNS
, audit_trail => DBMS_FGA.DB_EXTENDED
, statement_types => 'INSERT, UPDATE'
);
select DB_USER
, OBJECT_SCHEMA
, OBJECT_NAME
, POLICY_NAME
, SQL_TEXT
from dba_fga_audit_trail
/
=== Which tablespace is the standard auditing table (AUD$) going to? ===
select string_value from dam_config_param$ where audit_trail_type# = 1 and param_id = 22;
or
select table_name, tablespace_name from dba_tables where table_name IN ('AUD$', 'FGA_LOG$') order by table_name;
=== Move the standard audit trail (AUD$) to a different tablespace ===
Can move both the standard and the fine-grained access (fga) audit trail in one go by specifying "audit_trail_db_std" as audit_trail_type.
create tablespace audit_dt datafile '/oracle/SID/oradata2/audit_dt01.dbf' size 100M autoextend on maxsize 30000M
/
begin
dbms_audit_mgmt.set_audit_trail_location ( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std
, audit_trail_location_value => 'AUDIT_DT'
);
end;
/
=== Move the unified audit trail to a different tablespace ===
begin
dbms_audit_mgmt.set_audit_trail_location ( audit_trail_type => dbms_audit_mgmt.audit_trail_unified
, audit_trail_location_value => 'AUDIT_DT'
);
end;
/
==== AUD$ has not been cleaned up and is now too big to work with ====
Trim down the audit table the old-fashioned way...
- !/usr/bin/ksh
sqlplus / as sysdba <<'EOSQL'
whenever sqlerror exit failure
create tablespace audit_dt_temp
datafile '/oracle/${ORACLE_SID}/oradata5/audit_dt_temp.dbf'
size 500M
autoextend on
maxsize 30G
/
create table aud2$
tablespace audit_dt_temp
parallel (degree 4)
nologging
as
select *
from aud$
where ntimestamp# > sysdate - 90
/
rename aud$ to aud_old
/
rename aud2$ to aud$
/
drop tablespace audit_dt including contents and datafiles
/
create tablespace audit_dt
datafile '/oracle/${ORACLE_SID}/oradata1/audit_dt.dbf'
size 500M
autoextend on
maxsize 30G
/
alter table aud$ enable row movement
/
alter table aud$ move tablespace audit_dt
/
create index i_aud
on aud$ (sessionid, ses$tid)
tablespace audit_dt
/
alter table aud$ move tablespace audit_dt
lob (sqltext) store as lobsegment1 (tablespace audit_dt)
lob (sqlbind) store as lobsegment2 (tablespace audit_dt)
/
drop tablespace audit_dt_temp including contents and datafiles
/
EOSQL