Oracle DBA Lesson 79 Part 1 — Audit Access to a Sensitive Column

Fine-grained auditing (FGA) helps answer a focused question: which statements involved a sensitive column? For an employee table, the requirement might be to audit salary references while leaving a names-only query outside that particular policy.

Oracle DBA Lesson 79 Part 1 — Audit Access to a Sensitive Column

How the policy makes its decision

An FGA policy belongs to a table or view. Its statement types select operations such as SELECT. Its relevant columns identify the column references that matter. An optional audit condition makes row values part of the decision.

Here, SALARY is the relevant column and the audit condition is NULL. The null condition adds no row-value restriction. The policy records qualifying salary-related statements while the reader continues to use existing grants and access controls. Creating an audit policy does not grant the reader access or remove an existing grant.

Executed query	Reference to SALARY	Expected coverage under this policy
Select employee names only	None	Outside this policy
Select names and salary	Selected column	Qualifies
Select names with a salary filter	WHERE expression	Qualifies
Select all columns with SELECT *	Includes SALARY in this table	Qualifies

Other policies can audit the same actions independently. See relevant columns and row conditions.

Create and inspect the policy

Create the policy in the intended pluggable database. A local FGA policy applies to objects in its own container. Creating it requires the AUDIT_ADMIN role, or EXECUTE on DBMS_FGA.

BEGIN
  DBMS_FGA.ADD_POLICY(
    object_schema   => 'APP_DATA',
    object_name     => 'EMPLOYEES',
    policy_name     => 'SALARY_READ_AUD',
    audit_condition => NULL,
    audit_column    => 'SALARY',
    statement_types => 'SELECT',
    enable          => TRUE);
END;
/

DBMS_FGA.ADD_POLICY creates the policy and enables it here. audit_column names the relevant column. statement_types confines this policy to SELECT. It does not configure insert, update, or delete auditing. audit_condition => NULL leaves row values unrestricted. The call uses no event handler. In pure unified auditing, omit audit_trail. Oracle writes these records to the unified trail.

Inspect the configuration rather than relying on a successful procedure call alone:

SELECT object_schema, object_name, policy_name,
       enabled, sel, ins, upd, del, policy_text
FROM dba_audit_policies
WHERE object_schema = 'APP_DATA'
  AND object_name = 'EMPLOYEES'
  AND policy_name = 'SALARY_READ_AUD';

SELECT object_schema, object_name, policy_name, policy_column
FROM dba_audit_policy_columns
WHERE object_schema = 'APP_DATA'
  AND object_name = 'EMPLOYEES'
  AND policy_name = 'SALARY_READ_AUD';

Confirm enabled status, SELECT scope, the intended object, the absence of an extra row condition, and the SALARY column. Use DBA_AUDIT_POLICY_COLUMNS for column information. POLICY_COLUMN in the older policy view is deprecated. If broader dictionary visibility is unavailable, the corresponding ALL_ views describe accessible objects. See policy fields and policy column fields.

Compare the reads

Fetch each query and record a distinct test interval. An execution plan alone does not establish that the read occurred.

-- Names only
SELECT employee_name
FROM app_data.employees;

-- Names and salary
SELECT employee_name, salary
FROM app_data.employees;

The table, reader, and policy stay the same. Adding SALARY supplies the deciding column reference. Expect policy evidence for the second statement. A names-only query has no salary reference under this policy. If another audit policy records that query, interpret its policy identity independently.

Next, test references beyond the displayed columns:

-- The salary filter matters even though salary is not displayed
SELECT employee_name
FROM app_data.employees
WHERE salary > 100000;

-- In this employee table, the star includes SALARY
SELECT *
FROM app_data.employees;

The value 100000 is a test threshold in the same units as SALARY, not a recommended business threshold. The filtered query still references salary through row selection, even though salary is not displayed. In this employee table, SELECT * includes SALARY. Inspect the entire SQL statement when checking column coverage.

Interpret no-row and many-row cases

Test a salary reference that returns no rows:

SELECT salary
FROM app_data.employees
WHERE 1 = 0;

Oracle documents audit creation even when no rows are processed with a null FGA audit condition. Relevant-column and statement-type scope still apply. A policy record can correspond to a statement whose result contains no salary values.

For these simple single-table reads, fetching many matching rows does not create one policy record per returned employee. Audit evidence identifies the qualifying statement and its context. Use the actual query result to establish what was returned. Complex SQL with multiple references to the same object can produce additional records. Do not use audit-row counts as a universal count of rows read or statements executed. See DBMS_FGA usage notes and FGA record behavior.

An optional non-null condition such as audit_condition => 'SALARY > 100000' changes the policy to value-based auditing. Review matching, nonmatching, and no-row cases separately. Changing the query WHERE clause and changing the policy audit condition are different operations: the query chooses its result, while the policy condition chooses when to audit. Keep the basic policy unchanged during its initial comparisons.

Find the evidence in the correct trail

In pure unified auditing, use UNIFIED_AUDIT_TRAIL. An audit viewer, normally through AUDIT_VIEWER, reads the trail. Supply actual start-inclusive and end-exclusive UTC timestamp binds for the particular test:

SELECT event_timestamp_utc, dbusername, object_name,
       action_name, fga_policy_name, sql_text
FROM unified_audit_trail
WHERE fga_policy_name = 'SALARY_READ_AUD'
  AND dbusername = 'REPORT_USER'
  AND object_schema = 'APP_DATA'
  AND object_name = 'EMPLOYEES'
  AND event_timestamp_utc >= :start_utc
  AND event_timestamp_utc < :end_utc
ORDER BY event_timestamp_utc;

:start_utc and :end_utc represent actual test boundaries, not literal text to paste. A database client can bind timestamp values directly. In SQL*Plus, if using character variables, convert each explicitly with TO_TIMESTAMP(:start_utc, 'YYYY-MM-DD HH24:MI:SS.FF') and the equivalent end expression. Capture UTC with SYS_EXTRACT_UTC(SYSTIMESTAMP), and keep the same precision and time basis throughout. Separate reader and viewer sessions do not share bind variables.

Interpret the returned columns together:

Field	Useful interpretation
EVENT_TIMESTAMP_UTC	Correlates the event with the recorded test interval
DBUSERNAME	Identifies the database user associated with the action
OBJECT_NAME	Connects the event to the target employee table
ACTION_NAME	Identifies the recorded action
FGA_POLICY_NAME	Identifies the FGA policy that generated the record
SQL_TEXT	Helps distinguish the exact tested statement and its column references

The FGA policy name has its own field. Do not search only UNIFIED_AUDIT_POLICIES, the list used for unified audit policies. This FGA configuration is created through DBMS_FGA. It is not a CREATE AUDIT POLICY unified policy. See unified audit trail fields.

In mixed mode, traditional FGA records written to the database are inspected through DBA_FGA_AUDIT_TRAIL. Its field names differ:

SELECT extended_timestamp, db_user, object_schema, object_name,
       statement_type, policy_name, sql_text
FROM dba_fga_audit_trail
WHERE policy_name = 'SALARY_READ_AUD'
  AND db_user = 'REPORT_USER'
  AND object_schema = 'APP_DATA'
  AND object_name = 'EMPLOYEES'
ORDER BY extended_timestamp;

Narrow that query to the actual test interval using timestamp-with-time-zone values compatible with EXTENDED_TIMESTAMP. Check the existing FGA destination and SQL-text settings. A database destination with DBMS_FGA.EXTENDED includes SQL text and binds. An XML destination requires inspection through the appropriate XML trail path instead. The policy's FGA audit_trail destination matters in mixed mode and is not replaced by changing the ordinary AUDIT_TRAIL parameter. See traditional FGA trail fields and audit modes.

Confirm that a salary reference produces policy evidence before interpreting a missing record for the names-only test. Check actual execution, the enabled policy, the object and container, column references, the test interval, the trail destination, retention, and viewer visibility. Audit SQL text and binds can contain sensitive information, so give trail access only to the appropriate roles and follow the retention procedure.

Event handlers

An optional FGA handler can respond when the policy condition is met. Handler exceptions can fail the user statement. Recursive audited operations can also fail, so handler availability, privileges, and recursion need separate review. The policy shown here sets no handler and sends no alerts. See DBMS_FGA.

Recap

Target the sensitive column, choose row conditions deliberately, test the full SQL references, and interpret the statement in the correct audit trail alongside actual results and permissions.

Quiz

1. Which change makes the second controlled query relevant to this policy?

It uses the same reader. It adds the SALARY column reference. It runs later. It sorts the output.

2. A query displays names and uses SALARY in WHERE. What should you expect?

It qualifies because the filter references SALARY. It is excluded because SALARY is hidden from the display. It changes salary values. It disables the policy.

3. What does the null audit condition imply for a qualifying salary query that processes no rows?

A policy record proves one salary was returned. The query is blocked. Auditing can occur even with no rows processed. The table is empty forever.

4. Does this salary audit policy prevent a reader who already has SELECT from seeing salary?

Yes, it removes SELECT. Yes, it hides salary in every result. Only if the query uses SELECT *. No. It creates audit evidence while grants and access controls govern visibility.

5. In pure unified auditing, which field identifies this FGA policy's records?

FGA_POLICY_NAME in UNIFIED_AUDIT_TRAIL. POLICY_COLUMN in every employee row. A record count equal to the employees fetched. A guessed policy name in application output.

https://www.apps-dba.com/ https://www.youtube.com/@EnhanceSkillsDaily