Standard auditing records that a table was queried. Fine-grained auditing records only the queries that matter: those that read specific columns of rows that meet a condition. DBMS_FGA.ADD_POLICY defines such a policy, and an optional handler procedure runs each time it fires, for example to log or alert.
Code for This Guide
The main examples are in the examples/pkg-security folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_fga.add_policy( object_schema => 'SCHEMA', object_name => 'TABLE', policy_name => 'NAME', audit_condition => 'condition on the row', -- null: every row audit_column => 'COL1, COL2', -- null: any column handler_module => 'PROCEDURE', -- optional statement_types => 'SELECT');
The handler takes the schema, table, and policy name. Managing policies needs EXECUTE on DBMS_FGA.
Audit Who Reads Dates of Birth
The policy audits queries that read DATE_OF_BIRTH for customers in India, and its handler logs the user and the SQL text.
Setup:
create table dob_access_log (accessed_at timestamp default systimestamp,
db_user varchar2(128), sql_text varchar2(400));Example:
create or replace procedure log_dob_access (p_schema varchar2, p_table varchar2,
p_policy varchar2) is
pragma autonomous_transaction;
begin
insert into dob_access_log (db_user, sql_text)
values (sys_context('USERENV', 'SESSION_USER'),
substr(sys_context('USERENV', 'CURRENT_SQL'), 1, 400));
commit;
end;
/
begin
dbms_fga.add_policy(object_schema => 'NIMBUS',
object_name => 'CUSTOMERS',
policy_name => 'DOB_ACCESS',
audit_condition => 'country_code = ''IN''',
audit_column => 'DATE_OF_BIRTH',
handler_module => 'LOG_DOB_ACCESS',
statement_types => 'SELECT');
end;
/
select count(*) from customers where country_code = 'IN'; -- no audit column
select max(date_of_birth) from customers where country_code = 'AE'; -- no matching row
select max(date_of_birth) from customers where country_code = 'IN'; -- audited
select db_user, sql_text from dob_access_log;Output:
Procedure LOG_DOB_ACCESS compiled
PL/SQL procedure successfully completed.
COUNT(*)
___________
8
MAX(DATE_OF_BIRTH)
_____________________
12-JUN-2001
MAX(DATE_OF_BIRTH)
_____________________
13-JUN-2002
DB_USER SQL_TEXT
__________ _____________________________________________________________________
NIMBUS select max(date_of_birth) from customers where country_code = 'IN'- The count of Indian customers does not read the audited column, so it is not audited.
- The query for UAE customers reads the column, but no row meets the condition.
- Only the third query reads DATE_OF_BIRTH of Indian customers, and the handler logs it.
The handler is an autonomous transaction, so its log entry is saved even if the query's transaction rolls back. The example drops the policy, the handler, and the table afterward.
Things to Know
- Fine-grained audit records also go to the unified audit trail, where auditors can query them.
- A query is audited when it reads a matching row, even if it returns only an aggregate, as MAX does here.
- Keep handlers short: they run every time a query meets the policy.
Related Guides
Conclusion
DBMS_FGA audits only the queries that read chosen columns of rows meeting a condition, and can call a handler each time. Use it to watch access to sensitive data precisely, without auditing every query on the table.
