What should a database audit trail record, and why is logging inside the application not considered sufficient for auditing database access?
answer
- Who / what / which object / when / where / outcome
- Four buckets: logins, DDL, privileges, sensitive data
- Failures matter as much as successes
- App is inside the audited trust boundary
- Pool hides the end user — propagate identity
basics
~20 sRecord who, what, which object, when, from where, and the outcome: logins and failed logins, DDL, privilege changes, and DML or reads on sensitive tables. App logs miss DBAs, tools and jobs that connect directly, and can be bypassed.
solid answer
~50 sAn audit trail answers **who did what, to which object, when, from where, and did it succeed**. A practical baseline has four buckets: 1. **Authentication events** — successful and failed logins, client host. Cheap, high value. 2. **DDL** — CREATE/ALTER/DROP. Low volume; unexplained schema change is an incident. 3. **Privilege and role changes** — grants, role membership, ownership, password changes. This is how an attacker persists. 4. **Access to sensitive data** — DML, and reads where regulation requires it. Application logging alone fails for three reasons: the app is not the only client (DBAs, migrations, ETL, BI, break-glass, stolen credentials all bypass it); the app sits *inside* the trust boundary being audited, so its own logs are not independent evidence; and app logs record intent, not the statements that actually ran. One caveat: with a connection pool the engine sees only the pool's login, so propagate the end-user identity into the session.
go deeper
Name the record shape (who/what/when/where/outcome) and the four buckets, and say auditing happens in the engine because not every client is your application.
Add why the four buckets differ in volume-versus-value, why failures are logged, and the difference between an audit trail and the general query log.
Bring the trust-boundary argument, the pooled-identity fix, correlation with application logs by request id, and the fact that audit output is itself sensitive data that must leave the host.
Frame scope from the control objectives and regulatory scope rather than from what the engine can emit, and talk about who owns the audit pipeline versus who administers the database.
## What database auditing is Auditing is the practice of producing a durable, tamper-evident record of security-relevant activity inside the database engine, so that afterwards you can answer: **who** did **what**, to **which object**, **when**, **from where**, and **with what outcome**. It is routinely confused with three neighbours it is not: application logging (business events for product analytics and debugging), monitoring and metrics (aggregate health, no per-actor detail), and the general query log (a performance-debugging tool that is normally sampled, truncated, or switched off under load — exactly the moment an auditor cares about). ## Anatomy of an audit record A useful record carries, at minimum: - a timestamp with time zone plus an ordering key (sequence number or transaction id); - the session id, the authenticated database login, and the *effective* role if the session switched roles; - the end-user identity carried down from the application, when the app uses a shared pooled login; - client host/IP and the client program or application name; - the object touched — schema and table, and column set where the engine supports it; - the action class — LOGIN, LOGOUT, DDL, READ, WRITE, ROLE/privilege change; - the statement text and/or its bind parameters; - the **outcome**: success or failure, error code, rows affected where available. Outcome matters as much as success. Failed logins and permission-denied errors are the primary signal for credential stuffing and privilege probing; an audit design that logs only successful actions is blind to the reconnaissance phase of an attack. ## What belongs in scope A sensible baseline is four buckets, ordered by value-per-byte: 1. **Authentication events.** Volume is tiny (one record per connection), value is high. Failed-login bursts, logins from new hosts, and out-of-hours logins by human accounts are the classic detections. 2. **DDL.** CREATE/ALTER/DROP of tables, indexes, views, functions. Volume is near zero outside deploy windows. Every schema change that does not map to a known migration is an incident until proven otherwise. 3. **Privilege and role changes.** Grants, role membership, ownership transfers, password/credential changes. This is how an intruder converts a foothold into persistence, and how an insider quietly widens their own access. 4. **Access to sensitive data.** DML on regulated tables, and — where health, payment or personal-data rules demand it — *read* access too. This is the only bucket with serious volume, so it is the one to scope by object rather than turning on globally. ## Why application logging is not enough Three structural reasons, and interviewers want all three: **Bypass paths.** The application is not the only client of the database. DBAs with a SQL shell, schema-migration tools, ETL and reverse-ETL jobs, BI tools, break-glass accounts, restore-from-backup procedures, and an attacker holding stolen credentials all reach the data without passing through application code. Only the engine sits on every path. **Trust boundary.** If the subject of an investigation is the application itself — a bug, an injection, or an engineer who can deploy code — then logs emitted by that application are evidence produced by the accused. Engine-side auditing is generated by a different component, ideally administered by different people, which is what makes it usable as evidence. **Fidelity.** Application logs record *intent* ("user 42 updated their profile"). They do not record the statement that actually executed. An ORM cascade, a missing WHERE clause, or an injected predicate can touch far more rows than the intent implies. Engine auditing records what really happened. None of this makes application logs worthless — they carry business context the engine cannot know. The right answer is both, correlated by a request/trace id present in each. ## The connection-pool identity problem In practice almost every application connects through a pool using a single database login, so engine audit records show `app_user` for every action and attribution stops there. The fix is to push the end-user identity into the session at checkout: set a session context variable or the client application name per request, or switch to a per-tenant role for the duration of the request, so that the engine's own records carry the human identity. Without it, you are stuck joining engine records to application records by timestamp — fragile, and useless under concurrency. Mentioning this detail signals you have actually operated an audited system rather than read about one. ## Handling and retention Audit output should leave the database host quickly for a collector or SIEM, be append-only where it lands, and carry a retention period long enough for the applicable regulation but not indefinite — statement text and parameters frequently contain personal data, so the audit stream inherits the sensitivity of the data it describes and becomes a target in its own right. ## How to pitch the answer Lead with the record shape (who/what/which/when/where/outcome), give the four buckets, then make the bypass-and-trust-boundary argument for auditing at the engine, and close with the pooled-identity caveat.
- Your application connects through a pool as a single database login. How do you get the end-user identity into the engine's audit records?Set a per-request session attribute at pool checkout — a session context variable, GUC, or the client application_name — carrying the authenticated end-user id and a request/trace id, and reset it on return so identities never leak between borrowers. Where roles are per-tenant, SET ROLE for the request instead, which also tightens authorization. If the engine's audit format cannot carry it, log a correlation id in both the engine record and the application log and join on that.
- Why audit failed statements and failed logins, not just successful ones?Failures are the reconnaissance signal. Credential stuffing shows up as bursts of failed logins from few hosts; privilege probing shows up as permission-denied errors on tables an account has no business touching. An audit trail of successes only tells you what an attacker achieved, never that they were trying, so detection arrives after the damage instead of before.
- How is an audit log different from the general query log the DBAs already have?The query log exists for performance debugging: it is often sampled, truncated, rotated aggressively, disabled under load, and freely readable and deletable by the same administrators it would incriminate. An audit log has defined scope, guaranteed capture for in-scope events, an integrity and retention policy, and separation of duties over its storage. They can share plumbing, but a query log is not evidence.
Application logs are the shop's own sales notes; engine auditing is the CCTV on the door. If you suspect the shopkeeper, you want the camera, not their notebook.
saying these in an interview costs you the question
- "We log everything in the application, so the database is covered" — ignores DBAs, migrations, ETL and stolen-credential access.
- Auditing only successful operations and ignoring failed logins and permission-denied errors.
- Treating the engine's general query/slow log as an audit trail.
- Accepting `app_user` as the actor because the pool hides the human identity.
- Believing auditing enforces anything — it is detective, not preventive, control.