Tales From A Lazy Fat DBA

Unified auditing is one of those features that looks simple when we enable it, but its impact can become very visible very quickly on a busy production system.

I remember once instance where we’d enabled a unified audit policy for a limited set of users. At first glance, the scope looked controlled. It was not enabled for the whole database. It was not auditing every schema. It was not auditing every user. The policy was enabled only for ten named users and only for SELECT activity on a small set of application tables. So, from a distance, it looked safe. But within a short period, the audit trail started growing heavily. In around one day, we saw millions of audit records. That immediately raised the question every DBA asks in this situation —-> “Is something wrong, or is the audit policy doing exactly what we asked it to do?”

In this case, the answer was clear after reviewing the data. Nothing was technically broken. The audit policy was behaving as configured. The issue was that the policy was too broad for the way the application was actually using these tables. The audit policy was defined like this:

CREATE AUDIT POLICY sensitive_read_audit_policy ACTIONS 
  SELECT ON app_schema.sensitive_table_1, 
  SELECT ON app_schema.sensitive_table_2, 
  SELECT ON app_schema.sensitive_table_3, 
  SELECT ON app_schema.sensitive_table_4, 
  SELECT ON app_schema.high_volume_sensitive_table 
  WHEN '1=1' EVALUATE PER STATEMENT;

The important part is this ---> WHEN '1=1' EVALUATE PER STATEMENT

Means that condition is always true. So every qualifying SELECT statement against these audited objects was getting recorded. Since the policy was evaluated per statement, this was not one audit row per user or one audit row per session. It was one audit event per qualifying statement activity. That distinction matters a lot.

On a quiet database, this may not be a concern. But on a busy application system, especially where front-end screens, packages, reports, and repeated lookups are involved, this can quickly generate a very large audit trail. In our case, the audit was enabled only for ten users, but those users were active through the application. The main client program seen in the audit records was TEST.EXE, which appeared to be the primary application front-end client. That was the first clue that the activity was coming from normal application usage, not from random ad hoc access.

The next step was to break down the audit records by object. That changed the whole direction of the analysis … A one-day audit summary showed something like this

OBJECT_SCHEMA   OBJECT_NAME                   ACTION_NAME    AUDIT_RECORDS
-------------   ---------------------------   -----------    -------------
APP_SCHEMA      HIGH_VOLUME_SENSITIVE_TABLE   SELECT           4,425,238
APP_SCHEMA      SENSITIVE_TABLE_1             SELECT              87,785
APP_SCHEMA      SENSITIVE_TABLE_2             SELECT               8,419
APP_SCHEMA      SENSITIVE_TABLE_3             SELECT               2,835
APP_SCHEMA      SENSITIVE_TABLE_4             SELECT               1,263

This changed the direction of the analysis immediately. The issue was not evenly distributed across all audited objects. Almost all of the audit records were coming from one high-volume business table. The high-volume table alone was responsible for roughly 98% of the audit records. That is a very important finding. When audit growth becomes a problem, we should not treat all audited objects equally. One table, one screen, one package, one report, or one application flow may be generating most of the activity.

In this example, the high-volume table had millions of rows and was frequently queried by the application. But table size alone was not the reason for the audit growth. A large table does not generate audit rows by itself. Audit rows are generated when audited activity happens. So the real issue was repeated read activity against that object. From the database side, we can check which client programs or hosts are generating records … something like this

SELECT dbusername,
       client_program_name,
       userhost,
       COUNT(*) AS audit_records
FROM unified_audit_trail
WHERE unified_audit_policies LIKE '%SENSITIVE_READ_AUDIT_POLICY%'
  AND event_timestamp >= SYSTIMESTAMP - INTERVAL '1' DAY
GROUP BY dbusername,
         client_program_name,
         userhost
ORDER BY audit_records DESC;

This helps confirm whether the audit records are coming from normal application usage, reporting tools, ad hoc clients, batch jobs, or unexpected access paths.

In the case behind this post, the majority of audit records were generated by the primary application client. So filtering by that client program would not solve the problem. It would only confirm what we already knew: the main application was the source of the audit volume. We had a few options, FGA clicked instantly, but there are multiple columns out of those tables where they want to enforce auditing, meaning auditing a full table or with FGA will be almost like same, with very less gains as they are looing to audit a wide/huge range of columns and FGA wasn’t a good choice … I mean FGA is more targeted, but If every application query selects the sensitive columns, then FGA may still generate a lot of records. It may make the audit trail more meaningful, but not necessarily small.

Now, next I thought was about one of the option which I see very less DBA discuss about, the ‘ONLY TOPLEVEL’ … Okay, so what this “ONLY TOPLEVEL” does ? … It is an option for unified audit policies that limits audit records to top-level SQL operations. In simple terms, it helps avoid recording the internal or recursive SQL activity that happens underneath a top-level operation. For example, a user may perform one action in the application. That action may call a stored procedure. The procedure may query several tables. The query may access views. The views may access base tables. Oracle may also perform recursive SQL internally. Without top-level filtering, the audit policy may record many of those lower-level operations if they match the audited action. With ONLY TOPLEVEL, the policy focuses on top-level operations instead of recording every indirect SQL statement generated underneath the user action.

ALTER AUDIT POLICY sensitive_read_audit_policy ADD ONLY TOPLEVEL;

This does not change which objects are part of the policy. It changes how much internal activity gets recorded for that policy.

That is why ONLY TOPLEVEL can be very useful when an audit policy is generating a large number of rows because of recursive or package driven SQL. It can reduce audit volume by a great extent. It can reduce pressure on audit storage. It can reduce purge workload. It can also reduce the operational overhead of managing a very large audit trail.

I mean retention is handled by purge. Generation is handled by audit policy design. ONLY TOPLEVEL helps with generation. If it reduces unnecessary internal audit rows, then fewer rows are inserted into the unified audit trail in the first place. It helps in less audit data growth, less pressure on storage, small retentions, less work for purge jobs etc. .. And this is especially important when unified audit data is growing inside system-managed areas such as the audit schema and SYSAUX-related storage. Even if old audit records are purged, physical datafiles may not automatically shrink at the filesystem level. Deleted space may become reusable inside the tablespace, but actual file shrink depends on the datafile high water mark. You can do something like this to implement this change…

-- Check current policy enablement before change

SELECT policy_name,
       enabled_option,
       entity_name,
       entity_type,
       success,
       failure
FROM audit_unified_enabled_policies
WHERE policy_name = 'SENSITIVE_READ_AUDIT_POLICY' ORDER BY entity_name;

NOAUDIT POLICY sensitive_read_audit_policy BY user_1, user_2, user_3;
ALTER AUDIT POLICY sensitive_read_audit_policy ADD ONLY TOPLEVEL;

-- Reverify the policy
SELECT policy_name,
       enabled_option,
       entity_name,
       entity_type,
       success,
       failure
FROM audit_unified_enabled_policies
WHERE policy_name = 'SENSITIVE_READ_AUDIT_POLICY' ORDER BY entity_name;

Soon after we implemented the ONLY TOPLEVEL approach, we saw huge drop in audit records generation. It was more than 90% reduction in records 🙂 And all top level information about underlying sensitive obejcts were well captured by the auditing after ignoring all recursive statements.

The biggest lesson from this exercise is that audit volume should be understood before it becomes a storage or performance issue. A policy can be logically correct and still operationally expensive. Auditing a few users and a few objects may sound small, but if one high-volume table is read millions of times through normal application activity, the unified audit trail can grow very quickly. ONLY TOPLEVEL is a very useful option when the audit policy is capturing too much internal or recursive SQL activity. WHENEVER SUCCESSFUL helps align the policy with a successful-read requirement. FGA can make the design more targeted when specific columns or conditions matter.

But the best audit design starts with a simple question –> What exact evidence do we need? … If the requirement is database object access, unified auditing may be enough. If the requirement is business record access, such as which account, transaction, investment, or bank record was viewed, then application or package-level audit logging may be needed as well.

A good audit trail is not the biggest audit trail. A good audit trail is the one that captures the right evidence, at the right level, with a volume the system can safely manage.

Hope It Helped!
Prashant Dixit