view audit policies
col user_name for a30
col object_name for a30
col proxy_name for a30
col policy_name for a30
select * from AUDIT_UNIFIED_ENABLED_POLICIES;
select * from dba_stmt_audit_opts;
select * from dba_priv_audit_opts;
select owner, object_name, object_type from dba_obj_audit_opts o
where o.owner not in ('LBACSYS', 'DVSYS')
;
select distinct policy_name from AUDIT_UNIFIED_POLICIES;
view tablespaces
select s.tablespace_name,
s.owner,
s.segment_name,
s.bytes/1024/1024 size_mb
from dba_segments s
where s.segment_name in ('AUD$', 'FGA_LOG$')
or s.owner = 'AUDSYS'
order by 1
;
select t.owner, t.table_name, t.tablespace_name
from dba_tables t
where t.table_name in ('AUD$', 'FGA_LOG$')
;
move audit to other tablespaces
create bigfile tablespace sys_audit datafile '+DATAC1' size 4G logging default compress for OLTP;
exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD, audit_trail_location_value => 'SYS_AUDIT');
exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED, audit_trail_location_value => 'SYS_AUDIT');
exec DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION(audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD, audit_trail_location_value => 'SYS_AUDIT');
purge audit
begin
DBMS_AUDIT_MGMT.SET_LAST_ARCHIVE_TIMESTAMP(
audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
last_archive_time => SYSTIMESTAMP-5/1440);
DBMS_AUDIT_MGMT.CLEAN_AUDIT_TRAIL(
audit_trail_type => SYS.DBMS_AUDIT_MGMT.AUDIT_TRAIL_UNIFIED,
use_last_arch_timestamp => true);
end;
enable/disable/drop audit policies
audit POLICY policy1;
audit POLICY policy2;
noaudit POLICY policy1;
noaudit POLICY policy2;
drop audit POLICY policy1;
drop audit POLICY policy2;
create audit policies
SELECT NAME FROM SYSTEM_PRIVILEGE_MAP;
CREATE AUDIT POLICY policy_name
{ {privilege_audit_clause [action_audit_clause ] [role_audit_clause ]}
| { action_audit_clause [role_audit_clause ] }
| { role_audit_clause }
}
[WHEN audit_condition EVALUATE PER {STATEMENT|SESSION|INSTANCE}]
[CONTAINER = {CURRENT | ALL}]
;
## audit privileges and roles
CREATE AUDIT POLICY policy1
PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
ROLES emp_admin, sales_admin;
## audit object level
CREATE AUDIT POLICY policy1
ACTIONS SELECT ON OE.ORDERS, UPDATE ON HR.EMPLOYEES
;
## audit actions
CREATE AUDIT POLICY policy1
ACTIONS
CREATE TABLE,
CREATE VIEW,
DROP ROLE,
DROP TABLE,
DROP VIEW,
GRANT,
TRUNCATE TABLE
;
CREATE AUDIT POLICY policy1
ACTIONS LOGON
WHEN 'SYS_CONTEXT (''USERENV'', ''OS_USER'')
not IN (''admin'',''oracle'')'
EVALUATE PER SESSION
;
## audit PRIVILEGES
CREATE AUDIT POLICY policy1
PRIVILEGES
ALTER ANY ROLE,
CREATE ANY INDEX,
CREATE TABLE,
CREATE TABLESPACE,
DROP ANY INDEX,
DROP ANY MATERIALIZED VIEW,
DROP ANY VIEW,
DROP TABLESPACE
;
Notes 
