Auditing
From dbawiki
- Master note on Oracle Auditing
- Oracle Database Security Guide 12c
- Oracle-Base 11gR2 Auditing enhancements
- Basic example to manage AUD$ table in 11.2 with dbms_audit_mgmt and dbms_scheduled_jobs
Contents
- 1 See the latest activity on the database
- 2 Has your database migrated to Unified Auditing?
- 3 Show the audit trail (12c)
- 4 Disable unified auditing
- 5 12c auditing is protected
- 6 Manage the audit trail (12c)
- 7 Set auditing properties
- 8 Check whether audit trail cleanup has been initialised
- 9 Audit Inserts on a particular table
- 10 AUD$ has not been cleaned up and is now too big to work with
See the latest activity on the database[edit]
select username
, userhost
, count(*) num_connections
, max(timestamp)
from dba_audit_trail
where username not in ('OPS$ORACLE','OPS$ORAIBM')
/
Has your database migrated to Unified Auditing?[edit]
SELECT VALUE FROM V$OPTION WHERE PARAMETER = 'Unified Auditing';
Show the audit trail (12c)[edit]
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
Disable unified auditing[edit]
select policy_name, enabled_opt from audit_unified_enabled_policies /
POLICY_NAME ENABLED_ ------------------------------ -------- ORA_SECURECONFIG BY
noaudit policy ORA_SECURECONFIG;
Noaudit succeeded.
12c auditing is protected[edit]
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
--------------------------------------------------------------------------------------------------------------------------------
CLI_SWP$4f81f886$1$1
SYS@RMANV12> truncate table audsys."CLI_SWP$4f81f886$1$1";
truncate table audsys."CLI_SWP$4f81f886$1$1"
*
ERROR at line 1:
ORA-55941: DML and DDL operations are not allowed on table "AUDSYS"."CLI_SWP$4f81f886$1$1"
SYS@RMANV12> 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)[edit]
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.
SYS@RMANV12> 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[edit]
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 whether audit trail cleanup has been initialised[edit]
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;
/
Check audit settings[edit]
SELECT * FROM dba_audit_mgmt_config_params;
When was audit trail last purged?[edit]
COLUMN audit_trail FORMAT A20 COLUMN last_archive_ts FORMAT A40 SELECT * FROM dba_audit_mgmt_last_arch_ts; no rows selected
Set a date to purge to[edit]
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;
/
Recheck[edit]
COLUMN audit_trail FORMAT A20 COLUMN last_archive_ts FORMAT A40 SELECT * FROM dba_audit_mgmt_last_arch_ts; AUDIT_TRAIL RAC_INSTANCE LAST_ARCHIVE_TS -------------------- ------------ ---------------------------------------- STANDARD AUDIT TRAIL 0 08-NOV-14 06.24.44.000000 PM +00:00
Run the audit cleanup[edit]
begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std
, use_last_arch_timestamp => true
);
end;
or
begin
dbms_audit_mgmt.clean_audit_trail ( audit_trail_type => dbms_audit_mgmt.audit_trail_unified
, use_last_arch_timestamp => true
);
end;
Audit Inserts on a particular table[edit]
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(1)
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?[edit]
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[edit]
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[edit]
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[edit]
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
