Prisma 7.10.0 MySQL migrate status: Minimum Required Database Privileges

[shruti2008-debug]

Hello Prisma team,

I am using Prisma CLI and Schema Engine version 7.10.0 with MySQL 8.0.

I need to run the following command only to verify migration status:

prisma migrate status --config=prisma.stage10.config.ts

I want to use a database account with the minimum privileges required for this status check, without applying migrations or modifying application data.

Could you please clarify:

1. Is SELECT permission on the _prisma_migrations table sufficient for prisma migrate status in Prisma 7.10.0?
2. Does the command require additional MySQL privileges for schema or table discovery, metadata queries, INFORMATION_SCHEMA, or SHOW TABLES?
3. What are the exact minimum required privileges for this command with MySQL 8.0?
4. Are there any Prisma 7.10.0-specific requirements or limitations I should be aware of?

I want to confirm the required privileges before running the diagnostic. I will not apply migrations or change database permissions until the requirements are clear.

Thank you!

[brandonsdebt]

1. Is SELECT sufficient?
   Yes.

2. Are additional metadata privileges needed?
   No.

3. What is the minimum grant?
   For an existing account that can connect to the target database:
   GRANT SELECT ON your_database._prisma_migrations
   TO 'status_reader'@'your_client_host';

No database-wide SELECT, write permissions, or DDL privileges are required.

4. Any version-specific requirements or limitations?

• Prisma 7.10.0 explicitly disables shadow-database checks for this command, so shadow-database creation privileges are unnecessary.
• Ensure your config points to the intended database and local migrations directory.
• migrate status checks migration history; it does not detect manual schema drift.

[praveen-kumary]

Here is the breakdown of the exact privileges needed for prisma migrate status on MySQL 8.0:

1. Is SELECT on _prisma_migrations sufficient?

Almost, but with one caveat. If the _prisma_migrations table already exists in your database, SELECT on _prisma_migrations covers the actual rows read by the migration engine to inspect applied migrations and checksums. However, Prisma's Schema Engine also checks whether the table exists in the target database before reading it.

2. Does it require metadata queries (INFORMATION_SCHEMA / SHOW TABLES)?

In MySQL 8.0, any account granted table-level privileges automatically receives read access to INFORMATION_SCHEMA.TABLES and INFORMATION_SCHEMA.COLUMNS scoped to those permitted tables. You do not need to grant global PROCESS or global administrative privileges.

3. Exact Minimum Required Privileges for MySQL 8.0

To run prisma migrate status safely as a read-only diagnostic user without the ability to modify schema or data:

Option A: Strict Minimal (Only checking migration records)

If _prisma_migrations already exists:

CREATE USER 'prisma_status'@'%' IDENTIFIED BY 'your_secure_password';

-- Minimum required read permission for migration history
GRANT SELECT ON `your_database`._prisma_migrations TO 'prisma_status'@'%';

FLUSH PRIVILEGES;

Option B: Recommended Minimal (Allowing drift and table existence checks)

If you want prisma migrate status to also detect if the schema is empty or if unapplied migrations exist without failing on table existence checks:

CREATE USER 'prisma_status'@'%' IDENTIFIED BY 'your_secure_password';

-- Read access across the schema (read-only, zero write/DDL capability)
GRANT SELECT, SHOW VIEW ON `your_database`.* TO 'prisma_status'@'%';

FLUSH PRIVILEGES;

(Notice: Do not grant CREATE, ALTER, DROP, INSERT, UPDATE, or DELETE. prisma migrate status will run completely fine and cannot mutate your database.)

4. Prisma 7.10.0 Considerations

• No Shadow Database: prisma migrate status does not create or require a shadow database (unlike prisma migrate dev). It only queries the target database URL specified in your config.
• Strict Read-Only Guarantee: prisma migrate status never executes DDL or data mutations. If an unapplied migration is found, it simply reports the status code and exits (with exit code 0 if synced, or non-zero if pending migrations/drift are detected).

[markwbrown]

I tested Prisma CLI/Schema Engine 7.10.0 against MySQL 8.0.46 using a separate account with only this table-level grant, in addition to its implicit USAGE:

GRANT SELECT ON `your_database`.`_prisma_migrations`
TO 'status_reader'@'your_client_host';

With an existing _prisma_migrations table, prisma migrate status successfully reported all four tested history states: up to date (exit 0), pending migration (exit 1), failed migration (exit 1), and divergent local/database histories (exit 1).

The server query log showed only reads of session/version variables and _prisma_migrations for these checks. No database-wide SELECT, SHOW VIEW, application-table access, or DDL privileges were needed. Separate attempts to read an application table, insert a migration record, and create a table were denied.

One correction to the broader-grant suggestion above: migrate status is not a schema-drift check. After manually adding a column, it still returned exit 0 with unchanged migration history. The 7.10.0 implementation calls history diagnosis with optInToShadowDatabase: false.

This result is scoped to an initialized database with the history table already present; it doesn't establish the same grant procedure for a database without that table.