Database audit facilities usually let you configure statement-level auditing (capture every statement in a class, such as all DDL or all reads) or object-level auditing (capture only access to named tables or columns). Compare the two and describe when you would use each.
answer
- Class selector vs object selector
- Complete-but-loud vs focused-but-list-rot
- Audit privileged sessions wholesale
- Bind parameters leak the protected data
- Engine audit records failures and rolled-back statements
basics
~20 sStatement-level captures a whole class of statements regardless of target — complete but high volume. Object-level captures only access to named tables or columns — cheap and focused, but blind to anything outside the list. Typical policy: statement-level for DDL, role and privileged sessions; object-level for sensitive tables.
solid answer
~50 s**Statement-level (class-based)** auditing says "record every statement of class X" — all DDL, all role/privilege statements, all writes, all reads. It is complete within the class: nothing new escapes it, including tables created tomorrow and ad-hoc queries nobody predicted. The cost is volume, especially for the READ class on a busy OLTP database. **Object-level** auditing says "record any access to `patients.ssn`". Volume tracks the sensitivity of the object rather than the traffic of the system, and the records are dense with signal. The weakness is that it is a maintained list: a new table, a copy into a staging table, or an export path added next quarter is invisible until someone updates the policy. The usual production shape is both: statement-level for the cheap high-value classes (DDL, GRANT/role changes, logins, and anything a superuser or break-glass session does), plus object-level on the handful of regulated tables. Statement-level READ on everything is the configuration that ends up disabled after the first outage.
go deeper
Define the two selectors and give one example of each; knowing that read auditing everything is expensive is enough.
Explain completeness versus volume both ways, name list-rot as the object-level failure mode, and propose the combined default policy.
Add scoping rules by role so privileged sessions are captured wholesale, the parameter-leakage problem, nested/dynamic statement coverage, and auditing the audit configuration.
Drive the policy from regulatory scope and control objectives, own the decision about statement text versus parameter redaction with the data-protection owner, and budget the volume against the SIEM ingest cost.
## Two different selectors over the same event stream Every audit facility sits on a hook in the engine that fires when a statement is parsed or executed, and then asks one question: *do I record this?* The two mainstream ways to answer are by **statement class** and by **object**. **Statement-level (class-based)** rules are written in terms of what kind of statement it is: READ (SELECT and read-only CTEs), WRITE (INSERT/UPDATE/DELETE/MERGE/TRUNCATE), DDL (CREATE/ALTER/DROP), ROLE (grants, revokes, role membership), FUNCTION (routine execution), and a miscellaneous bucket. Turning on a class captures every statement in it regardless of what it touched. **Object-level** rules are written in terms of the target: audit any access to this table, or to this column, sometimes qualified by action ("reads of `card.pan`, writes to `accounts.balance`"). The engine records only statements whose resolved objects intersect the policy. These are not competitors so much as two knobs applied to the same stream, and mature configurations set both. ## Trade-off: completeness versus volume Statement-level auditing is **complete within its class**, which is precisely its value for compliance. A new table added by a migration is covered on the day it is created; an ad-hoc query from a laptop is covered; a path nobody documented is covered. You do not have to have predicted anything. But volume is proportional to traffic, and the READ class on an OLTP system means one audit record per query at whatever your QPS is — routinely gigabytes per hour, mostly recording that the application read its own rows for its own users. Object-level auditing inverts both properties. Volume is proportional to how often the *sensitive* objects are touched, which on a well-designed schema is a small fraction of traffic, and every record is worth reading. But it is a **maintained allowlist of things worth watching**, and lists rot. Classic failure modes: an analyst copies `customers` into `customers_tmp` for a migration and the copy is unaudited; a new column carrying a national id is added to an already-audited table but the policy names columns explicitly; a report view is created over the sensitive table and, depending on the engine, access through the view may be attributed to the view rather than the base table. ## Details that separate a good answer **Statement text and parameters are sensitive.** Whatever the selector, the recorded statement — and especially its bind parameters — can contain the very data you are protecting. Auditing reads of a table of national ids can produce an audit log full of national ids. Some facilities log the parameterised statement without values; that reduces exposure but also reduces forensic value, and the choice belongs to the data-protection owner, not the DBA. **Failed and rolled-back statements.** Engine-side auditing generally fires on execution, so a statement that raises an error or whose transaction later rolls back is still recorded. That is a feature — attempted access is the signal you want — and it is also the sharpest behavioural difference from trigger-based audit tables, whose rows disappear with the rollback because they are written inside the same transaction. **Nested and dynamic statements.** Statements executed inside stored routines, or assembled as dynamic SQL, may be recorded at the outer call, at each inner statement, or both, depending on the facility. Under-auditing here is a real bypass: "call the procedure" tells an investigator nothing about which rows it touched. Test it rather than assuming. **Views and privilege indirection.** Object rules are evaluated against the objects the engine actually resolves. A view that reads a sensitive base table may or may not trigger the base table's rule; interviewers like candidates who say "I would verify this on the specific engine" instead of asserting. **The auditor is auditable.** Whoever can change audit configuration can silence it. Changes to audit policy must themselves be in the ROLE/misc class and shipped off-box, or the whole scheme rests on the honesty of the person most worth watching. ## A workable default policy 1. Statement-level for the cheap classes: DDL, ROLE/privilege, logins and logouts (including failures). Volume is near zero; value is high. 2. Statement-level for *sessions*, not for everything: many facilities let you scope rules to a role or user, so you can capture **everything** a superuser, break-glass, or human DBA session does while leaving the application's pooled login on a narrow policy. This is the single highest-leverage setting. 3. Object-level on the regulated tables and columns, including read access where the regulation requires it. 4. Explicitly *not* statement-level READ across the whole database — decide that deliberately and write down why, because it is the setting that gets silently disabled at 3 a.m. during a load spike and never re-enabled. ## How to pitch it Define both selectors in one sentence each, contrast completeness against volume, then give the combined policy — and mention that audit-configuration changes are themselves audited.
- You must audit reads of one sensitive column on a table that receives thousands of queries per second. How do you keep the volume manageable?Use an object/column-scoped rule rather than the READ class, so only statements that actually resolve that column are recorded. Then reduce the population of statements that touch it: restrict the column to a narrow role, keep it out of the application's default projection, and route bulk consumers to a derived dataset without it. If volume is still high, the real finding is usually architectural — a hot path is reading data it does not need.
- Why do engine-level audit records exist for statements whose transaction rolled back, while a trigger-written audit table has no row for them?Engine auditing fires on statement execution, outside the transaction's own data lifecycle, so the record survives regardless of commit outcome. A trigger writes its audit row inside the same transaction as the change, so a rollback discards the audit row along with the change. For detecting attempted access — the security question — the engine behaviour is what you want; for reconstructing committed data history, the trigger behaviour is fine.
saying these in an interview costs you the question
- Treating object-level auditing as strictly better because it is cheaper, ignoring that it only sees objects someone remembered to list.
- Enabling READ-class auditing database-wide on an OLTP system and being surprised by the disk and latency cost.
- Assuming statements inside stored routines and dynamic SQL are audited without verifying.
- Forgetting that captured statement text and bind parameters carry the sensitive data itself.
- Not auditing changes to the audit configuration, so it can be turned off silently.