How to Grant Privileges in MySQL Database

Granting privileges is where MySQL security either holds up under pressure or quietly falls apart. I’ve inherited databases where every application account had ALL PRIVILEGES on every schema — a single compromised credential in setups like that means total data loss, not just a contained incident. In this guide, I’ll walk through how I actually design and apply privilege grants in MySQL, from the basic syntax to the layered, least-privilege model I use in production.

Understanding the Privilege Hierarchy First

Before granting anything, I always think in terms of MySQL’s privilege scope hierarchy, because a grant at one level cascades differently than at another.

graph TD
    A[Global - *.*] --> B[Database - dbname.*]
    B --> C[Table - dbname.tablename]
    C --> D[Column - dbname.tablename column-list]
    E[Stored Routine Privileges] --> F[Procedure/Function specific]

• Global (*.*) — applies across every database on the server. I use this sparingly, mostly for admin and replication accounts.
• Database-level (shop_db.*) — the most common scope for application accounts.
• Table-level (shop_db.orders) — for tightly scoped access, like a reporting tool that only needs one table.
• Column-level — for cases where even within a table, certain columns (like a hashed password field) should never be readable by a given account.

Basic Grant Syntax

GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_app'@'10.0.0.%';

This gives the application account full CRUD access to every table in shop_db, but nothing else — no DROP, no ALTER, no access to other databases.

Applying the change (only needed for direct table-based grants in older versions; modern GRANT/REVOKE apply immediately, but I run this out of habit and to be safe across versions):

FLUSH PRIVILEGES;

Granting at Different Scopes

Global Privileges (Admin Accounts Only)

GRANT ALL PRIVILEGES ON *.* TO 'admin_dba'@'10.0.0.5' WITH GRANT OPTION;

I use WITH GRANT OPTION extremely rarely — it allows the account to grant its own privileges to others, which I reserve strictly for true DBA accounts, never application service accounts.

Database-Level Privileges

GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_app'@'10.0.0.%';

Table-Level Privileges

GRANT SELECT ON shop_db.orders TO 'reporting_tool'@'10.0.1.%';
GRANT SELECT, INSERT ON shop_db.audit_log TO 'audit_service'@'10.0.2.%';

Column-Level Privileges

GRANT SELECT (id, name, email) ON shop_db.users TO 'support_agent'@'10.0.3.%';

This is one of my favorite lesser-used features — a support dashboard account can read customer names and emails to help with tickets, but never touches the password_hash or payment_token columns, even though it has SELECT on the table.

Routine (Stored Procedure/Function) Privileges

GRANT EXECUTE ON PROCEDURE shop_db.calculate_monthly_revenue TO 'reporting_tool'@'10.0.1.%';

This lets an account run a specific stored procedure without giving it any direct table access at all — useful for exposing a controlled, pre-defined query surface to less-trusted tools.

Common Privilege Types I Grant (and What They Actually Do)

In MySQL 8.0, SUPER has been broken down into more granular dynamic privileges like SYSTEM_VARIABLES_ADMIN, CONNECTION_ADMIN, and REPLICATION_APPLIER — I use these instead of the broad SUPER privilege whenever possible, since it lets me grant exactly the administrative capability needed.

GRANT SYSTEM_VARIABLES_ADMIN, CONNECTION_ADMIN ON *.* TO 'ops_engineer'@'10.0.0.10';

Designing a Least-Privilege Model for a Real App

Here’s the pattern I actually use for a typical e-commerce backend:

-- Application service — everyday CRUD, no schema changes
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'shop_app'@'10.0.0.%';

-- Migration/deploy account — schema changes only during deploys, used briefly then rotated
GRANT CREATE, ALTER, DROP, INDEX, SELECT ON shop_db.* TO 'migration_runner'@'10.0.0.20';

-- Read replica reporting account
GRANT SELECT ON shop_db.* TO 'reporting_tool'@'10.0.1.%';

-- Backup account
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON shop_db.* TO 'backup_user'@'localhost';
GRANT RELOAD, PROCESS ON *.* TO 'backup_user'@'localhost';

graph TD
    subgraph Least Privilege Layout
    A[shop_app] -->|CRUD only| DB[(shop_db)]
    B[migration_runner] -->|DDL, used only at deploy time| DB
    C[reporting_tool] -->|Read-only| DB
    D[backup_user] -->|Backup-specific privileges| DB
    end

Granting Privileges via Roles (Preferred at Scale)

CREATE ROLE 'app_crud', 'app_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop_db.* TO 'app_crud';
GRANT SELECT ON shop_db.* TO 'app_readonly';

GRANT 'app_crud' TO 'shop_app'@'10.0.0.%';
GRANT 'app_readonly' TO 'analytics_dashboard'@'10.0.1.%', 'reporting_tool'@'10.0.1.%';

SET DEFAULT ROLE ALL TO 'shop_app'@'10.0.0.%';

When ten reporting tools all need the same read-only access, I only maintain that permission set in one place — the role — rather than ten separate GRANT statements that can drift apart over time.

Verifying What’s Actually Granted

SHOW GRANTS FOR 'shop_app'@'10.0.0.%';

+---------------------------------------------------------------------+
| Grants for shop_app@10.0.0.%                                        |
+---------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `shop_app`@`10.0.0.%`                         |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop_db`.* TO `shop_app`@`10.0.0.%` |
+---------------------------------------------------------------------+

I run this as part of every account provisioning checklist, and again during periodic access reviews — never trust that a grant was applied correctly without verifying it.

Security Best Practices

• Grant only what’s needed for the account’s actual function — I ask “what does this service literally do” before writing a single GRANT.
• Never grant ALL PRIVILEGES to application accounts. Reserve it for true DBA/admin roles.
• Avoid WITH GRANT OPTION except for accounts that genuinely need to delegate privileges — it’s rarely necessary for application or reporting accounts.
• Separate schema-change privileges from runtime privileges. My application’s day-to-day account almost never needs CREATE, ALTER, or DROP — those belong to a migration account used only during deploys.
• Use column-level grants for tables containing sensitive fields alongside general-purpose fields, rather than splitting into multiple tables just for access control.
• Review grants quarterly:

SELECT grantee, table_schema, privilege_type
FROM information_schema.schema_privileges
ORDER BY grantee;

Real-World Scenario: Tightening Overly Broad Grants

I once inherited a system where the application account had GRANT ALL PRIVILEGES ON *.* — including on the mysql system schema itself. During a security review, we redesigned this into the layered model above: a CRUD-only application account, a separate migration account rotated per deploy, and a read-only reporting account. The change required updating three connection strings and took about half a day of careful testing, but it meant that if the application’s credentials were ever leaked, an attacker couldn’t drop tables, create new admin users, or touch other databases on the same server — a massive reduction in blast radius for a relatively small amount of work.

Troubleshooting Common Issues

Frequently Asked Questions

What’s the difference between GRANT ALL PRIVILEGES and GRANT ALL? They’re equivalent — ALL and ALL PRIVILEGES both mean every privilege applicable at that scope, excluding GRANT OPTION unless specified separately.

Do I need FLUSH PRIVILEGES after every GRANT? Not for standard GRANT/REVOKE statements in modern MySQL — they take effect immediately. I still run it out of habit for direct edits to grant tables, which is a much rarer, lower-level operation.

Can I grant privileges on a table that doesn’t exist yet? Yes, MySQL allows this, though I generally prefer creating the schema first so I can verify grants make sense against the actual structure.

How do I grant privileges on all future tables in a database, not just current ones? Database-level grants (ON shop_db.*) automatically apply to tables created later — you don’t need to re-grant for each new table.

Interview Questions on This Topic

1. What’s the difference between granting privileges at the global, database, table, and column level?
2. Why would you use column-level SELECT privileges instead of splitting a table into two?
3. What does WITH GRANT OPTION do, and why should it be used sparingly?
4. How do dynamic privileges in MySQL 8.0 improve on the older SUPER privilege?
5. Why is it best practice to separate a migration/DDL account from a runtime application account?

Key Takeaways

• Grant the minimum privileges necessary for an account’s actual function — always start narrow and add, not the reverse.
• Use database, table, or even column-level grants depending on how tightly you need to scope access.
• Prefer roles over per-user grants once you have more than a couple of accounts needing the same permissions.
• Separate schema-changing privileges from everyday runtime privileges into different accounts.
• Verify every grant with SHOW GRANTS — don’t assume it applied the way you intended.

References

• MySQL 8.0 Reference Manual — GRANT Statement: https://dev.mysql.com/doc/refman/8.0/en/grant.html
• MySQL 8.0 Reference Manual — Privileges Provided by MySQL: https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html
• MySQL 8.0 Reference Manual — Using Roles: https://dev.mysql.com/doc/refman/8.0/en/roles.html