MySQL GRANT Privileges: GRANT, REVOKE and Least Privilege

A new account can do almost nothing. You give it privileges with GRANT, and remove them with REVOKE. The golden rule is least privilege: give each account only what it needs, and no more.

query.sql

SQL

CREATE DATABASE shop;
CREATE TABLE shop.customers (id INT PRIMARY KEY, name VARCHAR(20), email VARCHAR(40), password_hash VARCHAR(20));
CREATE TABLE shop.orders (id INT PRIMARY KEY, customer_id INT, total INT);
INSERT INTO shop.customers VALUES (1, 'Maya', 'maya@example.com', 'h1'), (2, 'Rohan', 'rohan@example.com', 'h2');
INSERT INTO shop.orders VALUES (1, 1, 100), (2, 2, 50);

CREATE USER 'shop_app'@'localhost' IDENTIFIED BY 'S3cret!app#Pass';
GRANT SELECT, INSERT, UPDATE, DELETE ON shop.* TO 'shop_app'@'localhost';

SHOW GRANTS FOR 'shop_app'@'localhost';

Output

OUTPUT

+----------------------------------------------------------------------------+
| Grants for shop_app@localhost                                              |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `shop_app`@`localhost`                               |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `shop`.* TO `shop_app`@`localhost` |
+----------------------------------------------------------------------------+
2 rows in set

USAGE means “can connect, no privileges”. Below it, the privileges at the database level (shop.*).

Privilege levels

Level	Syntax	Applies to
Global	`ON .`	Every database on the server (administrative privileges live here)
Database	`ON shop.*`	Every object in one database
Table	`ON shop.orders`	One table
Column	`GRANT SELECT (id, name) ON shop.customers`	Only the listed columns
Routine	`GRANT EXECUTE ON PROCEDURE shop.place_order`	One stored procedure or function

The privileges you meet most

Privilege	Allows
`SELECT`	Read rows
`INSERT`, `UPDATE`, `DELETE`	Change rows
`CREATE`, `ALTER`, `DROP`, `INDEX`	Change structure
`REFERENCES`	Create foreign keys
`CREATE VIEW`, `SHOW VIEW`, `CREATE ROUTINE`, `ALTER ROUTINE`, `EXECUTE`, `TRIGGER`, `EVENT`	Stored objects
`LOCK TABLES`, `CREATE TEMPORARY TABLES`	Utilities used by tools and dumps
`PROCESS`, `RELOAD`, `SHUTDOWN`, `REPLICATION SLAVE`, `SUPER`-like dynamic privileges	Administration: never for applications
`FILE`	Read and write files on the server: dangerous
`ALL PRIVILEGES`	Everything at that level. Avoid it for anything but admins
`GRANT OPTION`	Pass on the privileges you have

What an application account really needs

A typical web application account needs SELECT, INSERT, UPDATE, DELETE on its own database, and nothing else. It does not need CREATE, ALTER, DROP (those belong to a separate migration account), FILE, PROCESS, or any global privilege.

These are real results for the shop_app account (run through a login as that user, on a MySQL 8.4 server):

Output

OUTPUT

SELECT CURRENT_USER() AS me;           -> shop_app@localhost
SHOW DATABASES;                         -> information_schema, performance_schema, shop    (only what it may see)
SELECT COUNT(*) FROM shop.customers;    -> 2
DELETE FROM shop.orders WHERE id = 99;  -> allowed (no row matched)
DROP TABLE shop.orders;                 -> ERROR 1142 (42000): DROP command denied to user 'shop_app'@'localhost' for table 'orders'
SELECT user FROM mysql.user;            -> ERROR 1142 (42000): SELECT command denied to user 'shop_app'@'localhost' for table 'user'
CREATE TABLE shop.x (a INT);            -> ERROR 1142 (42000): CREATE command denied to user 'shop_app'@'localhost' for table 'x'

Even if an attacker finds an SQL injection hole in the application, the damage is limited to what these four privileges allow: they cannot drop tables, create accounts or read the user list (SQL Injection).

Column-level privileges

You can allow a reporting account to read some columns of a table and not others:

query.sql

SQL

CREATE USER 'reporter'@'localhost' IDENTIFIED BY 'R3port!er#Pass';
GRANT SELECT (id, name) ON shop.customers TO 'reporter'@'localhost';
GRANT SELECT ON shop.orders TO 'reporter'@'localhost';

SHOW GRANTS FOR 'reporter'@'localhost';

Output

OUTPUT

+-----------------------------------------------------------------------------+
| Grants for reporter@localhost                                               |
+-----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `reporter`@`localhost`                                |
| GRANT SELECT (`id`, `name`) ON `shop`.`customers` TO `reporter`@`localhost` |
| GRANT SELECT ON `shop`.`orders` TO `reporter`@`localhost`                   |
+-----------------------------------------------------------------------------+
3 rows in set

As reporter, a real session gives:

Output

OUTPUT

SELECT id, name FROM shop.customers;   -> 2 rows
SELECT email FROM shop.customers;      -> ERROR 1143 (42000): SELECT command denied to user 'reporter'@'localhost' for column 'email' in table 'customers'
SELECT * FROM shop.customers;          -> ERROR 1142 (42000): SELECT command denied to user 'reporter'@'localhost' for table 'customers'
UPDATE shop.orders SET total = 0;      -> ERROR 1142 (42000): UPDATE command denied to user 'reporter'@'localhost' for table 'orders'

Column privileges work, but they are fiddly: SELECT * fails, and every new column needs a decision. A view that exposes only the safe columns, with the privilege granted on the view, is often easier to manage (Views).

REVOKE

query.sql

SQL

1
2

REVOKE DELETE ON shop.* FROM 'shop_app'@'localhost';
SHOW GRANTS FOR 'shop_app'@'localhost';

Output

OUTPUT

+--------------------------------------------------------------------+
| Grants for shop_app@localhost                                      |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `shop_app`@`localhost`                       |
| GRANT SELECT, INSERT, UPDATE ON `shop`.* TO `shop_app`@`localhost` |
+--------------------------------------------------------------------+
2 rows in set

REVOKE removes privileges exactly as granted. A privilege granted on shop.* cannot be taken away for one table with REVOKE ... ON shop.orders. If you need “everything except one table”, grant at the table level instead, or use partial revokes (a MySQL 8.0.16 feature controlled by partial_revokes, for global privileges on some databases).

WITH GRANT OPTION

WITH GRANT OPTION lets the account pass its own privileges to others. Give it only to administrators. An application account must never have it.

Seeing privileges

query.sql

SQL

1
2
3

SELECT grantee, table_schema, privilege_type FROM information_schema.schema_privileges WHERE grantee LIKE '%shop_app%' ORDER BY privilege_type;
SELECT grantee, table_name, privilege_type FROM information_schema.table_privileges WHERE grantee LIKE '%reporter%' ORDER BY table_name;
SELECT grantee, table_name, column_name, privilege_type FROM information_schema.column_privileges WHERE grantee LIKE '%reporter%' ORDER BY column_name;

Output

OUTPUT

+------------------------+--------------+----------------+
| grantee                | table_schema | privilege_type |
+------------------------+--------------+----------------+
| 'shop_app'@'localhost' | shop         | INSERT         |
| 'shop_app'@'localhost' | shop         | SELECT         |
| 'shop_app'@'localhost' | shop         | UPDATE         |
+------------------------+--------------+----------------+
3 rows in set

+------------------------+------------+----------------+
| grantee                | table_name | privilege_type |
+------------------------+------------+----------------+
| 'reporter'@'localhost' | orders     | SELECT         |
+------------------------+------------+----------------+
1 row in set

+------------------------+------------+-------------+----------------+
| grantee                | table_name | column_name | privilege_type |
+------------------------+------------+-------------+----------------+
| 'reporter'@'localhost' | customers  | id          | SELECT         |
| 'reporter'@'localhost' | customers  | name        | SELECT         |
+------------------------+------------+-------------+----------------+
2 rows in set

Changes take effect…

GRANT and REVOKE take effect immediately for new statements (and for new connections for some privilege levels). Never edit the grant tables directly, and never run FLUSH PRIVILEGES after GRANT (it is only needed after manual table edits).

A checklist

Start from no privileges, and add the least that works.
Database-level, not global, for application accounts.
Separate accounts for application, migrations, reporting and backups.
Review with SHOW GRANTS every few months, and after an incident.
Never GRANT ALL ON . to anything but a named administrator.
Use roles to manage groups of privileges (Roles).

dangerous

ALL PRIVILEGES is almost never right

Hosting panels and quick-start tutorials often run GRANT ALL PRIVILEGES ON . TO 'app'@'%'. That makes any bug in the application a full server compromise.