/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 12 : UNIFIED AUDITING (Oracle 19c / 21c / 23ai)
Topics Covered
---------------
1 Unified auditing architecture
2 Why unified auditing is used
3 Audit policies
4 Auditing login activity
5 Auditing DML operations
6 Auditing specific tables
7 Auditing privilege usage
8 Querying audit trail
9 Disabling audit policies
10 Enterprise auditing strategy
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Unified Auditing in Oracle?
*******************************************************************************************/
-- Unified auditing is a centralized auditing framework
-- introduced in modern Oracle versions.
-- It records database activities including:
-- Login attempts
-- DML operations
-- Privilege usage
-- Schema changes
/*******************************************************************************************
QUESTION 2
Why unified auditing replaced traditional auditing?
*******************************************************************************************/
-- Traditional auditing used multiple mechanisms:
-- Standard auditing
-- Fine-grained auditing
-- SYS auditing
-- Unified auditing centralizes all audit data.
/*******************************************************************************************
QUESTION 3
Where are unified audit records stored?
*******************************************************************************************/
-- Audit records are stored in the view:
-- UNIFIED_AUDIT_TRAIL
/*******************************************************************************************
QUESTION 4
View audit records
*******************************************************************************************/
SELECT *
FROM UNIFIED_AUDIT_TRAIL;
/*******************************************************************************************
QUESTION 5
What is an Audit Policy?
*******************************************************************************************/
-- An audit policy defines what activities
-- should be audited.
/*******************************************************************************************
QUESTION 6
Create an audit policy for employee table access
*******************************************************************************************/
CREATE AUDIT POLICY hr_employee_audit
ACTIONS SELECT
ON hr.employees;
/*******************************************************************************************
QUESTION 7
Enable the audit policy
*******************************************************************************************/
AUDIT POLICY hr_employee_audit;
/*******************************************************************************************
QUESTION 8
Generate audit activity
*******************************************************************************************/
SELECT employee_id,
first_name
FROM hr.employees;
/*******************************************************************************************
QUESTION 9
View audit trail results
*******************************************************************************************/
SELECT event_timestamp,
dbusername,
object_name,
action_name
FROM unified_audit_trail
WHERE object_name='EMPLOYEES';
/*******************************************************************************************
QUESTION 10
Audit user login activity
*******************************************************************************************/
CREATE AUDIT POLICY login_audit
ACTIONS LOGON;
/*******************************************************************************************
QUESTION 11
Enable login auditing
*******************************************************************************************/
AUDIT POLICY login_audit;
/*******************************************************************************************
QUESTION 12
View login audit records
*******************************************************************************************/
SELECT dbusername,
event_timestamp,
action_name
FROM unified_audit_trail
WHERE action_name='LOGON';
/*******************************************************************************************
QUESTION 13
Audit DML operations
*******************************************************************************************/
CREATE AUDIT POLICY hr_dml_audit
ACTIONS INSERT, UPDATE, DELETE
ON hr.employees;
/*******************************************************************************************
QUESTION 14
Enable DML auditing
*******************************************************************************************/
AUDIT POLICY hr_dml_audit;
/*******************************************************************************************
QUESTION 15
Example DML operation
*******************************************************************************************/
UPDATE hr.employees
SET salary = salary + 100
WHERE employee_id = 100;
/*******************************************************************************************
QUESTION 16
View DML audit logs
*******************************************************************************************/
SELECT dbusername,
action_name,
object_name
FROM unified_audit_trail
WHERE object_name='EMPLOYEES';
/*******************************************************************************************
QUESTION 17
Audit privilege usage
*******************************************************************************************/
CREATE AUDIT POLICY privilege_audit
PRIVILEGES CREATE TABLE;
/*******************************************************************************************
QUESTION 18
Enable privilege auditing
*******************************************************************************************/
AUDIT POLICY privilege_audit;
/*******************************************************************************************
QUESTION 19
Example privilege usage
*******************************************************************************************/
CREATE TABLE training_user.audit_test
(
id NUMBER
);
/*******************************************************************************************
QUESTION 20
View privilege audit results
*******************************************************************************************/
SELECT dbusername,
action_name
FROM unified_audit_trail
WHERE action_name='CREATE TABLE';
/*******************************************************************************************
QUESTION 21
Audit role usage
*******************************************************************************************/
CREATE AUDIT POLICY role_audit
ROLES hr_reporting_role;
/*******************************************************************************************
QUESTION 22
Enable role auditing
*******************************************************************************************/
AUDIT POLICY role_audit;
/*******************************************************************************************
QUESTION 23
Disable audit policy
*******************************************************************************************/
NOAUDIT POLICY hr_employee_audit;
/*******************************************************************************************
QUESTION 24
Drop audit policy
*******************************************************************************************/
DROP AUDIT POLICY hr_employee_audit;
/*******************************************************************************************
QUESTION 25
Enterprise auditing strategy
*******************************************************************************************/
-- Enterprises audit
-- Login attempts
-- Privilege usage
-- Sensitive table access
-- Role activation
-- Schema modifications
/*******************************************************************************************
QUESTION 26
Detect suspicious activity
*******************************************************************************************/
SELECT dbusername,
action_name,
object_name
FROM unified_audit_trail
WHERE action_name IN ('DROP TABLE','ALTER SYSTEM');
/*******************************************************************************************
QUESTION 27
Audit failed logins
*******************************************************************************************/
SELECT dbusername,
return_code
FROM unified_audit_trail
WHERE return_code <> 0;
/*******************************************************************************************
QUESTION 28
Security monitoring example
*******************************************************************************************/
SELECT event_timestamp,
dbusername,
action_name
FROM unified_audit_trail
ORDER BY event_timestamp DESC;
/*******************************************************************************************
QUESTION 29
Unified auditing advantages
*******************************************************************************************/
-- Centralized audit framework
-- Improved performance
-- Simplified compliance
-- Better security monitoring
/*******************************************************************************************
QUESTION 30
Unified auditing summary
*******************************************************************************************/
-- Unified auditing records database activity.
-- Audit policies define what to monitor.
-- UNIFIED_AUDIT_TRAIL stores audit data.
/*******************************************************************************************
END OF PART 12
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied warranties, including but not limited to warranties of accuracy, completeness, reliability, merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees that they do so at their own risk. The author shall not be held liable for any direct, indirect, incidental, consequential, special, or exemplary damages, including but not limited to loss of data, loss of profits, system failures, security breaches, or any other damages or losses arising from the use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and configurations in a controlled, non-production (test) environment prior to deploying them in any live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author from any claims, damages, liabilities, or expenses arising from their use.
*******************************************************************************************/