Enable trace for particular user, privileges, action, object & role in Unified auditing

For Enable the Unified auditing for particular User or tables. We can enable audit at different level:

1. Privileges audit: Privileges audit is used for audit system privileges. You can check system privileges with SYSTEM_PRIVILIGES_MAP.

CREATE AUDIT POLICY privilege_test_policy
PRIVILEGES CREATE TABLE, CREATE SEQUENCE, CREATE VIEW
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''HR'''
EVALUATE PER SESSION
CONTAINER = CURRENT;


2. Action Audit: Action audit is used for specified objects or all objects. Like DML statements on specified objects etc

CREATE AUDIT POLICY action_test_policy
ACTIONS DELETE ON scott.emp,
INSERT ON scott.emp,
UPDATE ON scott.emp,
SELECT ON scott.emp,
ALL ON scott.dept
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''HR'''
EVALUATE PER SESSION
CONTAINER = CURRENT;


3. Role Auditing: Role audit is used to audit the specified role. Create a ROLE with different privileges then create unified audit policy on that role.

CREATE AUDIT POLICY role_test_policy
ROLES role_name_hr
WHEN 'SYS_CONTEXT(''USERENV'', ''SESSION_USER'') = ''HR'''
EVALUATE PER SESSION
CONTAINER = CURRENT;


Enable or Disable the Unified audit policy

--For Enable the unified audit policy
AUDIT POLICY privilege_test_policy;
AUDIT POLICY action_test_policy;
AUDIT POLICY role_test_policy;

--For specific user:
AUDIT POLICY privelege_test_policy by HR;


--For Disable the Unified audit policy
NOAUDIT POLICY privilege_test_policy;
NOAUDIT POLICY action_test_policy;
NOAUDIT POLICY role_test_policy;


Check the Detail of unified audit policy

SET LINE 250 PAGES 250
COLUMN object_schema FORMAT A13
COLUMN object_name FORMAT A11
COLUMN object_type FORMAT A11
COLUMN audit_option FORMAT A12
COLUMN condition_eval_opt FORMAT A18
COLUMN audit_condition FORMAT A40
COLUMN audit_option_type FORMAT A40
SELECT object_schema,
object_name,
object_type,
audit_option,
condition_eval_opt,
audit_condition,
audit_option_type
FROM audit_unified_policies
WHERE policy_name = 'PRIVILEGE_TEST_POLICY';


Check the records of audit in the unified audit trail table

SET LINE 250 PAGES 250
COLUMN event_timestamp FORMAT A30
COLUMN dbusername FORMAT A10
COLUMN action_name FORMAT A15
COLUMN object_schema FORMAT A10
COLUMN object_name FORMAT A20
SELECT event_timestamp,
dbusername,
action_name,
object_schema,
object_name
FROM unified_audit_trail
WHERE dbusername = 'HR'
ORDER BY event_timestamp;