Create and manage your own unified audit policy in Oracle

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 3
CREATE AUDIT POLICY testpolicy3
ACTIONS DELETE on hr.employees,
INSERT on hr.employees,
UPDATE on hr.employees,
ALL on hr.departments;

--Example 4 Enable all action on RDBMS
CREATE AUDIT POLICY testpolicy4 ACTIONS ALL;

--Example 5 FOR Local PDB
CREATE AUDIT POLICY testpolicy5
PRIVILEGES CREATE ANY TABLE, DROP ANY TABLE
CONTAINER = CURRENT;

--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;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.