Oracle DBA Lesson 78 Part 1 — Create, Enable and Prove an Audit Policy

A reporting account already has permission to query an employee table. The task is to record its SELECT activity and connect a controlled query to an audit record with the correct actor, object, time, policy, and outcome.

Oracle DBA Lesson 78 Part 1 — Create, Enable and Prove an Audit Policy

A unified audit policy has a useful lifecycle:

1. Define the action and object with CREATE AUDIT POLICY.
2. Enable it for the intended account with AUDIT POLICY.
3. Test from a fresh reader session and match the resulting event in UNIFIED_AUDIT_TRAIL.
4. Disable and drop the policy while retaining its audit records.

Creating a policy stores its definition. Enablement determines the users and outcomes it covers. Verification requires the actual matching event.

Check the intended container

Keep the administrator, the reader, and the audit viewer in the intended pluggable database. In SQL*Plus, check SHOW USER and SHOW CON_NAME after connecting. The service alias must route to that container.

Creating, enabling, disabling, and dropping a policy requires the AUDIT SYSTEM privilege or the AUDIT_ADMIN role in that container. An AUDIT_VIEWER account inspects audit information. The reporting account still needs its own object privilege: an audit policy records activity and does not grant SELECT. Unified policies can operate in mixed or pure unified mode. Lesson 77 explains mode and destination inventory. This lesson does not change the audit mode. See CREATE AUDIT POLICY prerequisites and scope and AUDIT prerequisites.

Define the action and object

In the intended container, name the action and the qualified table:

CREATE AUDIT POLICY emp_read_aud
  ACTIONS SELECT ON app_data.employees;

EMP_READ_AUD is the policy name. ACTIONS SELECT chooses the object action. ON APP_DATA.EMPLOYEES qualifies the table with its schema, so the definition does not depend on the administrator's schema. With no CONTAINER clause, the documented default is CONTAINER = CURRENT: a local policy in the current container.

This policy covers direct reader queries against one table. A full sensitive-data requirement may need additional actions, indirect-access paths, or fine-grained auditing. An object-action policy is not a complete audit strategy.

Read the stored definition:

SELECT policy_name, audit_option, audit_option_type,
       object_schema, object_name
FROM audit_unified_policies
WHERE policy_name = 'EMP_READ_AUD';

The returned definition should name the intended action and object. See auditing object actions and CREATE AUDIT POLICY.

Enable the policy for the reader

AUDIT POLICY emp_read_aud BY report_user;

The BY clause chooses the database account whose matching actions this policy audits. Omitting BY, EXCEPT, and role applicability would enable the policy for all users, so keep the intended reader clause.

This statement omits WHENEVER SUCCESSFUL and WHENEVER NOT SUCCESSFUL, so Oracle audits applicable successful and unsuccessful actions. To select one outcome, use the corresponding documented clause and test that choice. Outcome selection does not promise a record for every malformed or unrecognized statement. For the controlled test, use a syntactically valid SELECT on the specified existing object.

Check enablement:

SELECT policy_name, enabled_option, entity_name,
       success, failure
FROM audit_unified_enabled_policies
WHERE policy_name = 'EMP_READ_AUD';

Read the actual rows together:

Column	What to check
POLICY_NAME	The policy created for this test, EMP_READ_AUD.
ENABLED_OPTION	User applicability consistent with the BY statement.
ENTITY_NAME	The intended reporting account.
SUCCESS, FAILURE	Both enabled for this statement, because no outcome clause was specified.

This confirms the configured applicability. The next step tests its effect. See AUDIT user and outcome clauses and enabled-policy columns.

Test from a fresh reader session

Policy enablement takes effect in subsequent user sessions. After enabling the policy, open a fresh reporting-user connection to the same pluggable database. Let SQL*Plus prompt for authentication. Do not embed credentials in a script or a captured command.

Record the database, container, user, and start and end time in UTC. Then run:

SELECT COUNT(*)
FROM app_data.employees;

The count is a SELECT on the audited table and does not display employee values. A successful count shows that the query ran. The audit record is the separate proof of collection. Do not assume a particular count.

Start the test interval just before this query and end it just after. Use a recorded UTC value with enough precision to identify the controlled event. Close unrelated reader activity, or record extra session identifiers if several matching queries can fall in that interval. See policy enablement and fresh sessions.

Match the audit record

As the audit viewer in the same container:

SELECT event_timestamp_utc, dbusername, action_name,
       object_schema, object_name, return_code,
       unified_audit_policies
FROM unified_audit_trail
WHERE dbusername = 'REPORT_USER'
  AND object_schema = 'APP_DATA'
  AND object_name = 'EMPLOYEES'
  AND action_name = 'SELECT'
  AND event_timestamp_utc >= TIMESTAMP '2026-10-22 09:00:00'
  AND event_timestamp_utc <  TIMESTAMP '2026-10-22 09:05:00'
ORDER BY event_timestamp_utc;

Replace these timestamp bounds with the test's UTC interval. Explicit TIMESTAMP literals avoid implicit string conversion. The video uses :start_utc and :end_utc as typed timestamp bind placeholders. Supply typed values through the client, or use the explicit literals above in SQL*Plus. The start is included and the end is excluded, so adjacent intervals do not count the same boundary twice.

Check the actual row:

Field	Required match or interpretation
DBUSERNAME	The database user that performed the controlled action.
OBJECT_SCHEMA	APP_DATA.
OBJECT_NAME	EMPLOYEES.
ACTION_NAME	SELECT, interpreted in the applicable audit type.
EVENT_TIMESTAMP_UTC	Inside the actual UTC test interval.
UNIFIED_AUDIT_POLICIES	Contains the exact policy name EMP_READ_AUD among the policies that caused the record.
RETURN_CODE	Zero for a successful action. A nonzero code identifies an Oracle error.

One action can satisfy several enabled policies. Oracle can write one unified record and list their names in the comma-separated UNIFIED_AUDIT_POLICIES column. Check list membership. A whole-column equality test misses a multi-policy record. A loose LIKE '%EMP_READ_AUD%' is not exact membership: underscore is a LIKE wildcard, and a substring can match a different name. The user, object, action, and time filters locate candidate events. The policy list shows which policy caused collection.

A nonzero return code can describe an audited failed action. It does not show that a query returned employee data. When several people share one database account, individual identity needs application context. This test uses one controlled database reader. See record creation and multiple policies and UNIFIED_AUDIT_TRAIL field definitions.

When the expected record is missing

Check the database and container, the reader's fresh session, the policy definition and enablement, the selected outcomes, the exact object, action, and user, and the actual UTC bounds. Confirm that the viewer can read the trail and that the trail is available under the collection and retention procedure. Do not change collection settings or purge records to force a match.

An empty filtered result needs investigation. A policy-inventory row, or a higher total trail count, does not attribute the controlled query.

Disable and drop the policy

After recording the result, close the reporting-user session. In the same container:

NOAUDIT POLICY emp_read_aud BY report_user;

Match the user applicability used for enablement. For a policy enabled with BY, omitting the matching user list can leave the policy enabled. When the policy was enabled only for REPORT_USER, confirm that no enablement remains:

SELECT policy_name, enabled_option, entity_name
FROM audit_unified_enabled_policies
WHERE policy_name = 'EMP_READ_AUD';

Once it is disabled for every applicable user, remove the definition:

DROP AUDIT POLICY emp_read_aud;

Oracle rejects dropping an enabled policy. Confirm that the name is gone from AUDIT_UNIFIED_POLICIES. NOAUDIT takes effect in subsequent sessions, and the policy-removal guide also describes object-related behavior. Closing the test session first avoids reliance on a running session's cached audit state.

Keep the generated audit records under the retention procedure. Dropping the policy does not purge the trail, and it leaves the reader's existing object privileges in place. See NOAUDIT user applicability, DROP restrictions, and policy removal and session behavior.

Recap

A focused policy makes the intended test action attributable in the unified trail. After that match, disable and drop only that policy, and keep its audit records.

Quiz

1. Does creating the policy alone guarantee that the next SELECT is audited by it?

Yes. Creation audits every user immediately. No. Enable it for the intended user and verify a fresh-session test. Only if the table has exactly one row. Only after committing the SELECT.

2. What outcomes does AUDIT POLICY emp_read_aud BY report_user; select?

Successful actions only. Failed actions only. Applicable successful and unsuccessful actions. No actions until WHENEVER is added.

3. Which result best verifies the controlled query?

The total trail count increased. The policy name exists in the definition view. Any logon event for the reporting account. A record matching the reader, object, action, policy, test interval, and outcome.

4. What does RETURN_CODE = 0 mean?

The audited action succeeded. The table contained zero rows. Auditing was disabled. No policy applied.

5. Which cleanup fits a new policy enabled only for REPORT_USER?

Delete its rows from the unified trail. Drop it while it is enabled. Close the test session, disable it for that user, verify no enablement remains, then drop it. Disable every policy in the database.

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