Create and manage your own unified audit policy in Oracle

1. Verfied you unified audit is enable or disbled.

SELECT value FROM v$option WHERE parameter = 'Unified Auditing';
VALUE
------
TRUE


2. Check the enabled polices of unified audit.

select distinct policy_name from AUDIT_UNIFIED_ENABLED_POLICIES;


3. Create your own Unified audit Polices.

---Example of TESTPOLICY1
create audit policy TESTPOLICY1
ACTIONS CREATE TABLE,INSERT ON HR.EMPLOYEES,
TRUNCATE TABLE, select on HR.SALARY;

--Example 2
CREATE AUDIT POLICY testpolicy2
PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE;

--Example 6 For All containers in CDB
CREATE AUDIT POLICY testpolicy6 ROLES c##role1
CONTAINER = ALL;

Note: Action can be checked from following views:

SELECT NAME FROM AUDITABLE_SYSTEM_ACTIONS WHERE COMPONENT = 'STANDARD';


4. Enable or Disable the own created Polices;

-- Enable policy:
AUDIT POLICY TESTPOLICY1;
AUDIT POLICY TESTPOLICY1 BY HR, SH;
AUDIT POLICY TESTPOLICY1 BY HR WHENEVER NOT SUCCESSFUL;


--Disable Policy:
NOAUDIT POLICY TESTPOLICY1;
NOAUDIT POLICY TESTPOLICY1 BY HR;


5. Verified your policy is enabled.

SELECT DISTINCT POLICY_NAME FROM AUDIT_UNIFIED_ENABLED_POLICIES WHERE POLICY_NAME='TESTPOLICY1';


6. Check Policy Details created and detail.

SELECT POLICY_NAME,AUDIT_OPTION,AUDIT_CONDITION,OBJECT_SCHEMA,OBJECT_NAME FROM
AUDIT_UNIFIED_POLICIES WHERE POLICY_NAME='TESTPOLICY1';


7. Drop Unified audit Policy.

DROP AUDIT POLICY TESTPOLICY1;


8. Example of Alter unified audit policy:

ALTER AUDIT POLICY TESTPOLICY1
ADD PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE;


9. Check report of your audit records.

--Check today audit records
set lines 200
col SQL_TEXT for a30
col action_name for a20
col UNIFIED_AUDIT_POLICIES for a30
select action_name,SQL_TEXT,UNIFIED_AUDIT_POLICIES ,EVENT_TIMESTAMP from unified_AUDIT_trail
where EVENT_TIMESTAMP > sysdate -1;