Most schema migrations run with more database privilege than anything else in the delivery pipeline, often under the same credential the application uses every day. A migration tool needs to create, alter and drop objects, so teams commonly hand it an owner or administrative login, store that secret in CI and let any merged change execute against production. That makes the migration path a privileged identity with a wide blast radius: one bad or malicious DDL statement, or one leaked pipeline secret, can drop tables, widen grants or disable audit. This guide covers the database side of change control: separating migration and runtime identities, reviewing DDL before it runs, guarding production with database-level controls and detecting out-of-band schema change. It is for DBAs, platform engineers and AppSec teams. General change control is in Change Management Security, and pipeline identity in the CI/CD Pipeline Identity Security Guide.
Key takeaways
- Split the identities. The application runtime account should hold only data rights; schema ownership and DDL belong to a separate migration identity that is used only by the deployment job.
- Treat the migrations folder as privileged code. Require named reviewers for it, check for destructive and grant-changing statements automatically and block self-approval.
- Release the credential only after approval. Bind the migration secret or short-lived token to a protected deployment environment so it exists only for an approved job.
- Put guardrails in the database itself. DDL triggers and event triggers can audit or block dangerous statements regardless of which tool or person sends them.
- Detect drift. Any schema or grant change that does not match the migration history table is an incident until proven otherwise.
Why migrations are a privileged access problem
Migration tools such as Alembic, Flyway, Liquibase and framework-native migrators all share one pattern: versioned scripts applied in order, with a history table in the target database recording what ran. The tool connects as a database identity and executes whatever the next script contains. Whoever can change a script, change the pipeline or obtain that identity's credential can run arbitrary DDL and DCL in production.
Three conditions turn this into real risk:
- Shared credentials: the application connects with the same login that owns the schema, so an injection flaw or a leaked application secret also grants DROP and GRANT.
- Standing secrets in CI: a long-lived owner password sits in repository or organisation secrets, readable by any workflow that can reference it. See CI/CD Security Guide.
- Developer-side shortcuts: local tools, notebooks and AI coding agents connected to production with the same rights. The Replit AI Agent Database Deletion 2025 showed how a development workflow with direct production write access, and a code freeze that existed only as an instruction, ended with a deleted production database.
In ATT&CK terms, the risks are abuse of valid accounts (T1078), account manipulation through grant changes (T1098) and data destruction (T1485). The controls below aim to make each of those require more than one compromised component.
The three-identity model
Our recommended baseline for any production database that receives schema changes is three distinct identities, each with a narrow purpose.
| Identity | Purpose | Rights | Can log in? | Credential |
|---|---|---|---|---|
| Schema owner | Owns tables, views, functions | Ownership of application schemas only; no server-wide admin | No (for example a PostgreSQL role created NOLOGIN) | None |
| Migrator | Runs approved migrations | Can act as the schema owner (PostgreSQL membership with SET, or ALTER on the schema in SQL Server); can write the migration history table | Yes, from the deployment runner only | Short-lived token or vault-issued dynamic credential, released per job |
| Application runtime | Serves traffic | SELECT, INSERT, UPDATE, DELETE (or EXECUTE on procedures) on required objects; no CREATE, ALTER, DROP or GRANT | Yes, from application hosts | Workload identity or managed secret, rotated |
Keep ownership away from humans and from the runtime account: in PostgreSQL the object owner holds all grantable privileges by default, and the owner can always change grants on its objects. Read-only reporting identities form a fourth tier where needed.
Making new objects inherit the right grants
A common break in this model is the first new table: the migrator creates it, the runtime account cannot read it, and someone "fixes" production by granting the runtime account broad rights. Avoid that with default privileges. In PostgreSQL, ALTER DEFAULT PRIVILEGES sets the privileges applied to objects created in future, and FOR ROLE limits it to objects created by a named role. PostgreSQL documents two traps: default privileges apply per creating role and are not inherited from roles the creator belongs to, and a per-schema REVOKE cannot remove a privilege granted globally. Set default privileges for the schema owner role that actually creates the objects, and test with a migration that creates a new table.
On managed services, do not use the provider's initial administrative user as the migrator. AWS, for example, recommends against using the RDS master user directly in applications. Create the migrator separately and keep the initial administrator as an emergency credential. See DBA Privileged Access.
Reviewing DDL before it runs
Code review of migration scripts is the primary preventive control. Treat it as privileged change review, not ordinary code review.
- Ownership rules: require approval from a named database reviewer group for any change under the migrations path (a CODEOWNERS file or equivalent branch protection rule).
- Automated classification: a pipeline check flags statements by risk class (table below) and adds a label or blocks merge for high-risk classes until a second approver signs.
- Immutability: applied migrations are never edited. Most tools store a checksum of each applied script and refuse to proceed when an applied script changes; keep that validation on in production.
- Preconditions: encode safety checks the tool can enforce at run time. Liquibase preconditions, for example, can check the connected user (runningAs) and the database type (dbms), and the default failure action, HALT, stops the whole changelog.
- Destructive commands off: disable commands that drop all objects in a schema, such as Flyway clean, which drops all objects in the configured schemas; keep cleanDisabled at its default of true in every configuration that can reach production.
| Statement class | Examples | Our recommended handling |
|---|---|---|
| Additive | CREATE TABLE, ADD COLUMN (nullable), CREATE INDEX | Standard review |
| Rewrite or lock-heavy | ALTER COLUMN TYPE, adding NOT NULL with a default on large tables, index builds without an online option | DBA review for performance and locking; schedule in a window |
| Destructive | DROP TABLE, DROP COLUMN, TRUNCATE, DELETE without WHERE | Two approvers; verified backup or snapshot before run; expand-and-contract preferred |
| Privilege-changing (DCL) | GRANT, REVOKE, ALTER ROLE, CREATE USER, ALTER DEFAULT PRIVILEGES | Security reviewer approval; never bundled with feature changes |
| Security-control changes | Dropping or disabling triggers, audit specifications, row-level security policies, masking | Security reviewer approval and alert on run |
| Code that runs with elevated rights | SECURITY DEFINER functions, EXECUTE AS, DEFINER clauses | Security reviewer; check search path and caller rights |
The expand-and-contract pattern (add the new structure, migrate data and code, then remove the old structure in a later release) turns many destructive changes into additive ones and keeps rollback possible.
Releasing the migration credential safely
The migrator credential should exist only for an approved job. Practical patterns:
- Protected deployment environments: in GitHub Actions, environment secrets are only available to jobs that reference the environment and only after its protection rules pass. Rules include up to six required reviewers, an option to prevent self-review, deployment branch and tag restrictions and a setting that stops administrators bypassing the rules. Other CI platforms offer equivalent approval gates.
- Short-lived database credentials: federate the runner to the cloud (OIDC) and mint a database token per job rather than storing a password. Amazon RDS IAM database authentication, for example, issues tokens valid for 15 minutes for MariaDB, MySQL and PostgreSQL. AWS notes that CloudWatch and CloudTrail do not log IAM database authentication itself, so keep database-side connection logging on. See the GitHub Actions OIDC Federation Hardening Guide and the Short-Lived Credentials Migration Guide for Legacy Applications.
- Dynamic secrets: where a secrets manager can create a database user per lease, issue the migrator a user that expires after the job.
- Network path: allow the migrator to connect only from deployment runners, not from developer networks. Self-hosted runners that reach production need their own hardening; see Self-hosted CI/CD Runners and Build Agents.
Guardrails inside the database
Pipeline controls can be bypassed by anyone with a direct connection. Database-level controls apply whoever sends the statement.
| Engine | Mechanism | What it can do | Caveats from the vendor docs |
|---|---|---|---|
| Microsoft SQL Server | DDL triggers (ON DATABASE or ON ALL SERVER) and EVENTDATA() | Audit DDL with the full statement text; roll back events such as DROP_TABLE or ALTER_TABLE; event groups such as DDL_TABLE_EVENTS | Fire only after the statement runs (no INSTEAD OF); some system stored procedures also fire them, so test; review trigger code because it can run with elevated privileges |
| PostgreSQL | Event triggers on ddl_command_start, ddl_command_end, sql_drop, table_rewrite | Audit via pg_event_trigger_ddl_commands() and pg_event_trigger_dropped_objects(); block by raising an error in ddl_command_start | Do not fire for shared objects (databases, roles, tablespaces) or ALTER SYSTEM; can be switched off with the event_triggers setting, so monitor for that |
Use these as a backstop: for example, a trigger that rejects DROP TABLE unless the session user is the migrator and a change window flag is set, and that logs every GRANT. Engine-native audit (SQL Server Audit, pgAudit, Oracle Unified Auditing) should also record DDL and DCL, written somewhere DBAs cannot alter. See the Database Audit Log Detection Guide.
Emergency changes and hotfix DDL
Production incidents sometimes need a schema change faster than the pipeline allows. Plan for it rather than leaving it to a shared admin login:
- Write the fix as a normal migration script on an emergency branch, even if it is applied by hand.
- Apply it through just-in-time elevation to the migrator or schema owner role, with session recording. See Privileged Session Management Guide.
- Record it in the migration history so the next pipeline run does not fail or reapply it (most tools support marking a change as applied).
- Review it retrospectively within [2] working days, as you would any emergency change.
Detecting drift and out-of-band change
A schema that differs from its migration history means someone changed production outside the process. Detection sources:
- Schema diff: a scheduled job compares the production catalog (tables, columns, indexes, grants, triggers, functions) with the schema built from the migration scripts in a clean environment. Grants matter as much as structure.
- DDL by unexpected principals: alert on any DDL or DCL whose session user is not the migrator, outside an approved emergency.
- Guardrail tampering: alert on disabling DDL or event triggers, changing audit settings or altering the migration history table directly.
- Runtime account drift: alert if the application account gains CREATE, ALTER, DROP or GRANT rights.
Metrics
| Metric | What good looks like (our recommendation) |
|---|---|
| Production schema changes applied by the migrator through the pipeline | Over 95%, remainder recorded emergencies |
| Drift findings unexplained after 5 working days | Zero |
| Application runtime accounts holding DDL or GRANT rights | Zero |
| Destructive migrations with a verified pre-change backup or snapshot | 100% |
| Migration credentials stored as long-lived secrets | Falling to zero |
Rolling it out
- Inventory: list every database that receives migrations, the identity each tool and each application uses, and where those secrets are stored.
- Audit mode first: turn on DDL and DCL auditing and alert-only triggers. Run for two release cycles to learn who changes what.
- Split identities: create the owner, migrator and runtime identities, set default privileges, and move one application at a time. Test with a migration that creates a new table and confirm the runtime account can use it.
- Revoke from runtime: remove DDL and GRANT rights from runtime accounts. Roll back by restoring the previous grant script if a missed dependency appears, and fix it with a proper migration.
- Gate the credential: move the migrator credential to a protected environment or short-lived token.
- Enforce: switch selected triggers from alert to block, starting with DROP and GRANT outside the migrator, and turn on drift detection.
Smaller teams can stop at steps 1 to 5 with a single required reviewer; larger estates and regulated systems (SOX, PCI DSS) should add the database guardrails and drift detection, since those produce the evidence auditors ask for. See the SOX IT General Controls (ITGC) Guide.
Framework mapping
| Framework | Reference | How this guide applies it |
|---|---|---|
| NIST SP 800-53 Rev. 5 | CM-3 Configuration Change Control; CM-4 Impact Analyses; CM-5 Access Restrictions for Change; CM-5(1) Automated Access Enforcement and Audit Records | Reviewed migrations, risk classes, migrator-only DDL, triggers and audit |
| NIST SP 800-53 Rev. 5 | AC-5 Separation of Duties; AC-6(5) Privileged Accounts; SA-10 Developer Configuration Management | Three-identity model, reviewer approval, immutable migration history |
| NIST CSF 2.0 | PR.PS-01; PR.AA-05; DE.CM-03 | Configuration management, least privilege, monitoring of DDL activity |
| MITRE ATT&CK | T1078 Valid Accounts; T1098 Account Manipulation; T1485 Data Destruction | Credential gating, DCL review, destructive-change controls |
Common failure patterns
- One login for everything: the application and the migration tool share the schema owner credential, so an application compromise includes DROP and GRANT.
- Grant fixes in production: a new table is unreadable, so someone grants the runtime account ownership or broad rights by hand and it is never reverted. Default privileges prevent this.
- Development tools with production rights: a developer laptop, notebook or AI coding agent connected to production with write and DDL rights, protected only by instructions, as the Replit AI Agent Database Deletion 2025 showed.
- Repository-wide secrets: the migrator password is an organisation or repository secret, usable by any workflow including ones triggered from branches, rather than an environment secret behind approval.
- Edited history: someone edits an applied migration or updates the history table to get past a checksum error, hiding what actually ran.
- Grants smuggled in feature migrations: a GRANT or ALTER ROLE buried inside a large feature change that only gets feature review.
Practitioner checklist
- Separate schema owner (no login), migrator and application runtime identities exist for every production database.
- Runtime accounts hold no CREATE, ALTER, DROP or GRANT rights, and this is checked automatically.
- Default privileges are set for the creating role so new objects get the right grants without manual fixes.
- The migrations path needs approval from a named database reviewer group; self-approval is blocked.
- Destructive and privilege-changing statements are detected in the pipeline and need a second approver.
- Applied migrations are immutable and checksum validation is on in production; schema-wide drop commands are disabled.
- The migrator credential is released only to approved jobs, preferably as a short-lived token.
- DDL and DCL are audited in the database, with alerts for DDL by any principal other than the migrator.
- A scheduled drift check compares production schema and grants with migration history.
- Emergency DDL uses just-in-time elevation, is recorded in migration history and reviewed afterwards.
Standards and references
- NIST: SP 800-53 Rev. 5 Security and Privacy Controls
- PostgreSQL: ALTER DEFAULT PRIVILEGES
- PostgreSQL: Overview of event trigger behaviour
- Microsoft: DDL triggers (SQL Server)
- Redgate Flyway: Clean
- Redgate Flyway: Clean Disabled setting
- Liquibase: Preconditions
- GitHub: Managing environments for deployment
- AWS: IAM database authentication for MariaDB, MySQL and PostgreSQL
- AWS: Master user account privileges (Amazon RDS)
Related NHI Mgmt Group resources: Change Management Security · Database Security Guide · DBA Privileged Access · CI/CD Pipeline Identity Security Guide · Database Privileged Access Review Template