/*******************************************************************************************
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
ORACLE DATABASE SECURITY HANDBOOK
PART 16 : ORACLE FINE-GRAINED AUDITING (FGA)
Topics Covered
---------------
1 Fine-grained auditing concept
2 Difference between standard auditing and FGA
3 DBMS_FGA package
4 Auditing specific columns
5 Conditional auditing
6 Salary access monitoring example
7 Viewing FGA audit records
8 Removing FGA policies
9 Enterprise compliance monitoring
10 FGA architecture overview
*******************************************************************************************/
/*******************************************************************************************
QUESTION 1
What is Fine-Grained Auditing (FGA)?
*******************************************************************************************/
-- Fine-Grained Auditing allows Oracle to audit
-- access to specific rows or columns
-- under defined conditions.
-- It provides more detailed monitoring
-- than standard auditing.
/*******************************************************************************************
QUESTION 2
Why is Fine-Grained Auditing needed?
*******************************************************************************************/
-- Standard auditing records entire statements.
-- FGA allows auditing when specific
-- sensitive data is accessed.
/*******************************************************************************************
QUESTION 3
Example FGA scenario
*******************************************************************************************/
-- Audit when someone queries employee salary.
/*******************************************************************************************
QUESTION 4
Which package manages FGA policies?
*******************************************************************************************/
-- Oracle provides the package:
-- DBMS_FGA
/*******************************************************************************************
QUESTION 5
Create FGA policy for salary access
*******************************************************************************************/
BEGIN
DBMS_FGA.ADD_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'SALARY_ACCESS_AUDIT',
audit_column => 'SALARY'
);
END;
/
/*******************************************************************************************
QUESTION 6
What does this policy do?
*******************************************************************************************/
-- Whenever SALARY column is accessed,
-- Oracle records an audit event.
/*******************************************************************************************
QUESTION 7
Generate audit event
*******************************************************************************************/
SELECT employee_id,
salary
FROM hr.employees;
/*******************************************************************************************
QUESTION 8
Where are FGA audit logs stored?
*******************************************************************************************/
-- FGA records appear in
-- DBA_FGA_AUDIT_TRAIL
/*******************************************************************************************
QUESTION 9
View FGA audit logs
*******************************************************************************************/
SELECT db_user,
object_name,
sql_text
FROM dba_fga_audit_trail;
/*******************************************************************************************
QUESTION 10
Conditional auditing example
*******************************************************************************************/
-- Audit only when salary > 10000.
/*******************************************************************************************
QUESTION 11
Create conditional FGA policy
*******************************************************************************************/
BEGIN
DBMS_FGA.ADD_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'HIGH_SALARY_AUDIT',
audit_condition => 'SALARY > 10000',
audit_column => 'SALARY'
);
END;
/
/*******************************************************************************************
QUESTION 12
What happens when condition is met?
*******************************************************************************************/
-- Oracle records audit entry
-- only if SALARY > 10000 is accessed.
/*******************************************************************************************
QUESTION 13
Example query triggering audit
*******************************************************************************************/
SELECT employee_id,
salary
FROM hr.employees
WHERE salary > 10000;
/*******************************************************************************************
QUESTION 14
Audit multiple columns
*******************************************************************************************/
BEGIN
DBMS_FGA.ADD_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_DATA_AUDIT',
audit_column => 'SALARY,COMMISSION_PCT'
);
END;
/
/*******************************************************************************************
QUESTION 15
Audit INSERT operations
*******************************************************************************************/
BEGIN
DBMS_FGA.ADD_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_INSERT_AUDIT',
statement_types => 'INSERT'
);
END;
/
/*******************************************************************************************
QUESTION 16
Audit UPDATE operations
*******************************************************************************************/
BEGIN
DBMS_FGA.ADD_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'EMP_UPDATE_AUDIT',
statement_types => 'UPDATE'
);
END;
/
/*******************************************************************************************
QUESTION 17
View active FGA policies
*******************************************************************************************/
SELECT *
FROM DBA_AUDIT_POLICIES;
/*******************************************************************************************
QUESTION 18
Check FGA policy details
*******************************************************************************************/
SELECT *
FROM DBA_FGA_AUDIT_TRAIL;
/*******************************************************************************************
QUESTION 19
Remove FGA policy
*******************************************************************************************/
BEGIN
DBMS_FGA.DROP_POLICY
(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_name => 'SALARY_ACCESS_AUDIT'
);
END;
/
/*******************************************************************************************
QUESTION 20
Advantages of Fine-Grained Auditing
*******************************************************************************************/
-- Tracks access to sensitive data
-- Supports compliance requirements
-- Detects suspicious activity
/*******************************************************************************************
QUESTION 21
Enterprise FGA example
*******************************************************************************************/
-- Audit when someone queries
-- customer credit card data.
/*******************************************************************************************
QUESTION 22
Healthcare example
*******************************************************************************************/
-- Audit access to patient medical records.
/*******************************************************************************************
QUESTION 23
Financial system example
*******************************************************************************************/
-- Audit access to transaction records.
/*******************************************************************************************
QUESTION 24
Performance considerations
*******************************************************************************************/
-- Excessive auditing may increase overhead.
-- Audit only sensitive operations.
/*******************************************************************************************
QUESTION 25
FGA architecture
*******************************************************************************************/
-- USER QUERY
-- |
-- v
-- FGA POLICY
-- |
-- v
-- AUDIT TRAIL
/*******************************************************************************************
QUESTION 26
Security monitoring query
*******************************************************************************************/
SELECT db_user,
object_name,
timestamp
FROM dba_fga_audit_trail
ORDER BY timestamp DESC;
/*******************************************************************************************
QUESTION 27
Difference between VPD and FGA
*******************************************************************************************/
-- VPD restricts row access.
-- FGA audits data access.
/*******************************************************************************************
QUESTION 28
Compliance requirements
*******************************************************************************************/
-- FGA supports regulatory compliance
-- including financial auditing.
/*******************************************************************************************
QUESTION 29
FGA security summary
*******************************************************************************************/
-- Audits specific columns
-- Supports conditional auditing
-- Tracks sensitive data access
/*******************************************************************************************
QUESTION 30
Interview summary
*******************************************************************************************/
-- FGA audits data access at column
-- and condition level using DBMS_FGA.
/*******************************************************************************************
END OF PART 16
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.
*******************************************************************************************/