Was the SQL That Ran the SQL That Was Approved?

A schema change was made on your production database. The ticket exists, the approval exists, and the database audit log shows the statement that ran. Then the auditor asks one question: was the statement that ran the same one that was approved? In most organisations there is an answer to that question but no evidence for it. The ticket says what was asked for, the database log says what ran, and the link between the two is usually somebody's memory.

This article is about how that link is built and what can genuinely be proven. The subject is not how to write a SQL statement. It is how the trail of a change stays intact from request to execution.

The Question the Auditor Actually Asks

In an audit interview the question is rarely "do you have logs". Most organisations have logs. The question asks you to join three parts:

• Authority: who asked for this change and who approved it? Was the approver entitled to approve it?
• Content: what exactly was approved? Did the content change after the approval?
• Outcome: what happened in production? When did it run, who ran it, and with what result?

If one of these three is missing the answer does not convince. The one most often missing is the middle one: the record showing that the approved content and the executed content were the same.

Why the Two Records Do Not Meet

Because the two records live in two different systems and exist for two different purposes. Each does its own job correctly. The problem is the gap between them.

The database's own auditing features (SQL Server Audit and Extended Events, Oracle Unified Auditing, pgaudit on the PostgreSQL side) are the second row in this table. They tell you reliably what ran. They do not know why it ran or who approved it, because that information never enters the database.

This is why "our audit log is on" does not on its own reassure an auditor. The issue is not coverage. It is linkage.

The Four Links of a Provable Chain

The way to build the link is to keep the change inside a single lifecycle and to tie every step to the same identity. Four links are enough.

1. One identity

The request, the scripts, the approval steps, the execution result and the audit entries should all hang off the same request identity. The ticket number is carried as a separate field, but it cannot be the carrier of the chain: a ticket usually covers more than one change.

2. A rule set frozen at approval time

Whatever rules a request was evaluated against should be recorded onto the request. Otherwise someone looking six months later sees today's rules and misreads the decision made back then. Loosening a control afterwards is itself a change and belongs on the record too.

3. A record of the moment of execution

The person who started the execution, the time, the target server and database, the result, the duration, the affected row count and any error message should be recorded. A common mistake here is to record the background service as the executor. The person who pressed the button and the service that carried out the work are different things, and the auditor asks about the first.

4. A fingerprint of the content

This is the link most often skipped, and it is the one that answers the actual question. At execution time a digest of each script's content should be computed and written into the audit entry. Later, the digest of today's stored script is recomputed and the two values are compared.

Why the Script Needs a Digest

Writing the script text into the audit entry verbatim looks sufficient at first. It has two practical problems: long scripts bloat the audit trail, and because the text sits inside the record, the question "was this text edited afterwards" is still left unanswered.

A digest solves both. It is fixed in size, and a single character changed in the content changes the digest completely. If the digest of the script stored today matches the digest recorded at execution time, the script has not changed since. If it differs, it has, and that is a question mark. It is not an offence in itself, but it does require an explanation.

The same logic can be applied to the audit trail itself: entries are chained together and the integrity of the chain can be verified later. We covered that separately in how an audit trail becomes tamper evident.

The Limits of This Method

Saying what a control does not do matters as much as saying what it does. A control that promises more than it delivers loses trust at the first review.

• A digest comparison shows that the stored script has not changed since execution. It does not, independently of the engine's own log, prove that the database engine received that exact text. Used together, the two complete the picture.
• Work done outside the application, by connecting directly to the database, does not enter this chain. Covering that is a separate matter and requires narrowing privileges on the database side.
• A digest tells you what the content is, not what it does. A harmful but approved script also passes the integrity check. Judging the content is the job of the validation and risk steps.

Checklist

Ask these questions in order in your own environment. If you can answer all of them with a document, the chain is intact.

The last item is answered with "we collect screenshots" in most organisations. That is where the real cost of an audit period sits: not the absence of control, but the scattering of the evidence.

Frequently Asked Questions

Is the database's own auditing not enough to answer this?

No, because it answers a different question. SQL Server Audit, Oracle Unified Auditing and pgaudit record reliably which statement ran. Who approved it and under which policy never enters the database, so it cannot be found there. The two records complete the picture when used together.

Why use a digest instead of writing the whole script into the audit entry?

A digest is fixed in size and does not bloat the audit trail. More importantly it makes comparison possible: the digest of today's script can be recomputed and compared with the value recorded at execution. If the text itself sat in the record, the question of whether that text was later edited would still be open.

Is the ticket number not enough to carry the chain?

No, because a ticket usually covers several changes and does not show which script ran on which server. The ticket number should be carried, but the carrier of the chain should be the request's own identity.

Does this chain break during an emergency change?

It should not. An emergency change is not a bypass of approval; it is a shorter, pre defined approval path being applied. The request, the justification, the policy applied and the execution record are kept the same way in an emergency. We covered this in detail in emergency database change.