auditing
Table of Contents
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
auditing.txt · Last modified: 2025/02/20 11:41 by 127.0.0.1