This is a ready-to-use qualification protocol. Replace every <<FILL: ...>> placeholder with your own specifics, execute in a qualified test environment that mirrors production configuration, and route through your normal validation review and approval. A worked filled specimen follows the template. Verify each cited regulation against the current source before you rely on it.
Approval page
| Field | Entry |
|---|---|
| Protocol title | Native Database Audit Logging and Point-in-Time Recovery Qualification |
| Protocol number | <<FILL>> |
| System / database under test | <<FILL>> |
| Version | <<FILL>> |
| Effective date | <<FILL>> |
| Role | Name | Signature | Date |
|---|---|---|---|
| Author | <<FILL>> | ||
| Validation reviewer | <<FILL>> | ||
| QA approver | <<FILL>> | ||
| System owner | <<FILL>> |
1. Objective
To demonstrate, with documented test evidence, that (a) native database auditing on <<FILL: system>> captures a direct data-manipulation statement made outside the application, including any attempt to disable the audit configuration itself, and (b) point-in-time recovery can restore the database to a specific past moment and that the restored copy accurately reflects the data as it existed at that moment.
2. Scope
This protocol covers the database instance(s) supporting <<FILL: system name>> in <<FILL: environment, e.g. a qualified test/staging environment configured identically to production>>. It does not cover application-layer audit trail testing, which is addressed under <<FILL: reference to the application validation protocol>>.
3. System description
<<FILL: brief description of the database platform, version, and its role in the validated system>>. Native auditing configuration and recovery-model settings applied for this test: <<FILL>>.
4. Prerequisites
- The test environment database is configured identically to production for auditing and recovery settings.
- Native database auditing is enabled per the audit policy defined in
<<FILL: reference>>. - The database is in a recovery mode that retains transaction logs (not a simple/minimal recovery mode).
- A qualified test account with the same privilege profile as a production DBA account is available for test execution.
5. Roles
| Role | Responsibility |
|---|---|
| Tester (DBA or validation engineer) | Executes test cases, records actual results |
| Validation reviewer | Confirms test execution and evidence against expected results |
| QA | Approves the protocol and the final report; dispositions any deviation |
6. Test cases
6.1 Native audit logging
| ID | Step | Expected result | Actual result | Pass/Fail | Tester | Date |
|---|---|---|---|---|---|---|
| AUD-01 | Connect directly to the test database using a non-application client with a named test account | Connection succeeds (this is expected; the control under test is detection, not prevention) | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| AUD-02 | Execute an UPDATE against one row of a GxP test table | Statement executes | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| AUD-03 | Query the native database audit log (from the off-box destination, not the database itself) for the statement in AUD-02 | An entry exists showing account, timestamp, statement, and table, matching AUD-02 | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| AUD-04 | Confirm the audit log entry is stored where the test DBA account cannot modify or delete it | Attempt to alter or delete the entry using the DBA account fails | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| AUD-05 | Attempt to disable native auditing using the DBA account | Either the action is blocked by privilege restriction, or, if permitted, the disablement itself generates an audit entry | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| AUD-06 | Execute a DELETE against one row of a GxP test table and confirm capture | An entry exists for the DELETE, matching account, timestamp, statement | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
6.2 Point-in-time recovery
| ID | Step | Expected result | Actual result | Pass/Fail | Tester | Date |
|---|---|---|---|---|---|---|
| PITR-01 | Record the current value of a designated test row (baseline value) and the exact system time | Baseline value and timestamp recorded | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| PITR-02 | Update the designated test row to a new value; record the exact time of the change | New value and change timestamp recorded | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| PITR-03 | Restore a copy of the database, in an isolated environment, to a target time one minute before the change in PITR-02 | Restore completes successfully | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| PITR-04 | Query the designated test row in the restored copy | Value matches the baseline value from PITR-01, confirming accurate restoration to the target moment | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| PITR-05 | Confirm the recovery point objective (RPO) is met by comparing transaction log backup frequency to the documented RPO | Log backup interval is at or below the documented RPO | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
| PITR-06 | Confirm the restored database (not just the target row) opens and functions normally | Restored instance starts and is queryable without corruption | <<FILL>> | <<FILL>> | <<FILL>> | <<FILL>> |
7. Acceptance criteria
All test cases in section 6 pass. Any deviation is documented per <<FILL: SOP-ID for deviations>>, assessed for impact on the qualification conclusion, and either resolved with a re-test or justified with a documented risk rationale accepted by QA before the protocol is closed.
8. Deviation handling
Record any unexpected result, investigation, and disposition here or by reference to the deviation record number. A deviation does not automatically fail the protocol; the disposition and its rationale determine the outcome.
9. Summary and conclusion
<<FILL: overall pass/fail conclusion, summary of any deviations and their resolution, and a statement of whether native auditing and point-in-time recovery are qualified for GxP use on this system>>.
10. Attachments
<<FILL: list attached evidence, e.g. audit log exports, restore logs, screenshots>>.
References
21 CFR Part 11.10(c) (accurate and ready retrieval throughout the retention period) and 11.10(e) (audit trails). EU GMP Annex 11, clauses 9 (audit trails) and 17 (archiving).
Confirm the current version and clause numbers of each reference before issue.
Filled specimen (excerpt)
| ID | Step | Expected result | Actual result | Pass/Fail |
|---|---|---|---|---|
| AUD-03 | Query the native audit log for the AUD-02 UPDATE | Entry matching account, timestamp, statement, table | Entry found: account test_dba, 2026-07-02 14:03:11, UPDATE TEST_RESULT SET VALUE=99.9 WHERE ID=8801, table TEST_RESULT | Pass |
| AUD-05 | Attempt to disable native auditing | Disablement blocked, or itself logged | Disablement was permitted at the platform level but generated audit entry ID 88231 recording the configuration change, account, and timestamp | Pass |
| PITR-04 | Query the restored row at target time | Matches PITR-01 baseline | Baseline was 96.1; restored copy at target time (13:58:00, one minute before the 13:59:04 change) showed 96.1, matching | Pass |
Summary and conclusion (excerpt): All 12 test cases passed on first execution with no deviations. Native database auditing on DB-LIMS-01 was demonstrated to capture direct data manipulation, including a configuration-disablement event, and point-in-time recovery was demonstrated to restore accurately to a specific past moment consistent with the documented 15-minute recovery point objective. The system is qualified for GxP use of these two database-tier controls.
Common inspection findings this protocol prevents
- A claim that native auditing “should” catch a direct edit, with no test evidence it was ever actually verified to do so.
- A disaster-recovery test that only ever exercised a full restore, never a point-in-time restore to a specific moment.
- Audit-configuration disablement never tested, so no one actually knows whether turning auditing off leaves a trace.
How to adapt this protocol
- Add test cases specific to your platform’s native auditing implementation (see the platform comparison table in the database integrity article for what each major platform provides).
- Set the recovery point objective in PITR-05 from your documented business requirement, not an arbitrary number.
- Re-execute this protocol after any change to the database platform, version, or recovery configuration.