LEARN. PRACTICE. GROW.Restoring data does not recreate every account
The dump selected database lab without the users option. After restore, the dr_runtime account present on the source was absent from the target. The script created it separately and first observed 1142 when trying to read accounts without grants. Only then did it assign permissions used by the example. Documentation provides account-information export options, but those were not executed in this block. The point is explicit scope: data, accounts and privileges are related recovery parts, and none should be presumed merely because tables reappeared. Also record which identity performed restore and which performed application tests.
Test object execution context
The view and procedure use SQL SECURITY INVOKER. The runtime account needs permissions to reference them and read data used by their definitions. The trigger instead executes in its definer’s context, which is local root in this lab. A runtime-account insert created an audit row although that account received 1142 when trying to read audit directly. Do not turn the experiment’s privileged definer into a production recommendation. Acceptance should review definer dependencies, existence and privileges, distinguishing direct access from effects executed by a stored object. Successful administrator queries alone do not establish that these contexts work for the intended application identity.
Confirm reads, writes and associated effects
After grants, the runtime account read the view with two accounts and total 100, and the procedure returned the same total. It then inserted NEW=10, received id3 and observed total 110. An administrative query found three audit rows, showing the restored trigger also participated in the workflow. These checks exercise more than object presence in the catalogue. Nevertheless, they are local SQL calls with small data; they do not represent the whole application, pool, remote authentication or expected load. Use the results to define concrete criteria and add actual service tests outside the experiment’s coverage.
Expected rejection is also an acceptance criterion
Attempting to reuse code A returned 1062, and a BAD account with negative balance returned 3819 through CHECK violation. Three accounts and three audit rows remained. A useful restore preserves both allowed operations and constraints preventing invalid data. Do not end review merely because one valid write succeeded. Define invalid inputs relevant to the contract and confirm rejection without unexpected side effects. The lab checked counts after both failures; it did not measure every possible application invariant. Functional acceptance still needs representative cases agreed with data owners. A constraint’s presence in the schema is weaker evidence than observing its relevant behavior.
A failed import can leave work behind
The failure file inserts before-error, references a missing table and only then attempts to insert after-error. Executed by mysql in batch mode without force or a transaction enclosing the file, it ends with 1146. The first note committed and the last was not executed. Therefore, stopping at an error does not make the file atomically reversible. Before retrying, identify what already applied and choose an appropriate strategy. In the lab, the disposable target database was recreated and received the original dump again; this recovered two accounts and two audit rows, deliberately losing the NEW write made on the test target.
Close the experiment and state what remains
The sixteen groups use two disposable MySQL 9.7.2 servers, private sockets, four persistent connections and additional CLI clients. One runtime account without administrative privileges executed allowed tests and expected denials. Empty passwords belong only to this disposable local environment; they do not validate real authentication configuration. Clients and servers stopped and temporary directories were removed. There was no production database, TCP, TLS, replica, binary-log recovery, concurrent DDL, crash or benchmark. Handover should preserve these boundaries and require outstanding operational evidence, including backup protection, recovery point, load and independent review. Successful local execution is useful evidence within that stated scope.
-- Target-only synthetic lab account; empty password is not a production pattern.
CREATE USER 'dr_runtime'@'localhost' IDENTIFIED BY ''
GRANT SELECT,INSERT ON lab.accounts TO 'dr_runtime'@'localhost'
GRANT SELECT ON lab.totals TO 'dr_runtime'@'localhost'
GRANT EXECUTE ON PROCEDURE lab.read_total TO 'dr_runtime'@'localhost'
-- Verify allowed operations and expected denials using that account.The runtime account received 1142 before grants; afterwards it read the total, inserted id3 and triggered auditing without direct audit-table access.
Common pitfalls
Superuser restore as a working application; import error as full rollback; a hash as evidence of business recovery.
Related topics: Logical backup and object inventory · Permissions and recovery acceptance
Accept restore through observed outcomes and explicit boundaries, with a plan for partial work and changes after the backup.
Reference: Stored object access control · MySQL 9.7 LTS with InnoDB reference semantics