Privileges (GRANT/REVOKE) — MySQL

MySQL Privileges and Access Control

MySQL's privilege system determines exactly what each user account is allowed to do. Privileges can be granted at four levels of granularity — from global access to a single column. Understanding and correctly applying the least-privilege principle is one of the most important things you can do to secure a MySQL installation.

Privilege Hierarchy

Privileges flow from more specific to less specific. A more specific grant overrides a broader one. The hierarchy from broadest to narrowest:

Global (*.*) — applies to all databases on the server
Database (db.*) — applies to all tables in a specific database
Table (db.table) — applies to all columns in a specific table
Column — applies to specific columns in a table
Routine — applies to a specific stored procedure or function

Level	Scope	Example
Global	Entire MySQL server	GRANT RELOAD ON . TO ...
Database	All tables in a specific database	GRANT SELECT ON mydb.* TO ...
Table	A specific table	GRANT SELECT ON mydb.orders TO ...
Column	Specific columns in a table	GRANT SELECT (email, name) ON mydb.customers TO ...
Routine	A specific stored procedure or function	GRANT EXECUTE ON PROCEDURE mydb.process_order TO ...

All Privilege Types Reference

Privilege	Level	What it Allows
SELECT	Global/DB/Table/Column	Read rows with SELECT
INSERT	Global/DB/Table/Column	Add new rows with INSERT
UPDATE	Global/DB/Table/Column	Modify existing rows with UPDATE
DELETE	Global/DB/Table	Remove rows with DELETE
CREATE	Global/DB	Create databases and tables
DROP	Global/DB/Table	Drop databases and tables
ALTER	Global/DB/Table	Change table structure with ALTER TABLE
INDEX	Global/DB/Table	Create and drop indexes
REFERENCES	Global/DB/Table/Column	Create foreign key constraints
CREATE VIEW	Global/DB	Create views
SHOW VIEW	Global/DB	See view definitions with SHOW CREATE VIEW
TRIGGER	Global/DB/Table	Create, modify, and drop triggers
EXECUTE	Global/DB	Execute stored procedures and functions
CREATE ROUTINE	Global/DB	Create stored procedures and functions
ALTER ROUTINE	Global/DB	Modify and drop stored routines
EVENT	Global/DB	Create, modify, and drop events
LOCK TABLES	Global/DB	Use LOCK TABLES on tables with SELECT privilege
RELOAD	Global	Execute FLUSH operations
PROCESS	Global	See all threads in SHOW PROCESSLIST
REPLICATION SLAVE	Global	Read binary log events for replication
REPLICATION CLIENT	Global	Query replica and primary status
FILE	Global	Read/write files on the server with LOAD DATA, SELECT INTO OUTFILE
SUPER	Global	Many administrative operations including SET GLOBAL — use sparingly
ALL PRIVILEGES	Any	All privileges at the specified scope level

GRANT Syntax — All Forms

SQL

-- Global privileges (all databases on server)
GRANT privilege_list ON *.* TO 'user'@'host';

-- Database-level privileges
GRANT privilege_list ON database_name.* TO 'user'@'host';

-- Table-level privileges
GRANT privilege_list ON database_name.table_name TO 'user'@'host';

-- Column-level privileges
GRANT SELECT (col1, col2), UPDATE (col1) ON db.table TO 'user'@'host';

-- Routine-level privileges
GRANT EXECUTE ON PROCEDURE db.proc_name TO 'user'@'host';
GRANT EXECUTE ON FUNCTION  db.func_name TO 'user'@'host';

-- Host patterns
GRANT SELECT ON myapp.* TO 'webapp'@'%';           -- any host
GRANT SELECT ON myapp.* TO 'reporter'@'10.0.0.%';  -- subnet
GRANT SELECT ON myapp.* TO 'dba'@'localhost';       -- local only

WITH GRANT OPTION

Adding WITH GRANT OPTION allows the user to grant their own privileges to other users:

SQL

-- Grant SELECT and the ability to re-grant SELECT to others
GRANT SELECT ON myapp.* TO 'team_lead'@'%' WITH GRANT OPTION;

-- team_lead can now do:
GRANT SELECT ON myapp.* TO 'junior_dev'@'localhost';

-- Revoking WITH GRANT OPTION without revoking the underlying privilege
REVOKE GRANT OPTION FOR SELECT ON myapp.* FROM 'team_lead'@'%';

Warning

WITH GRANT OPTION can be dangerous — the grantee can give privileges to arbitrary users, potentially including accounts you did not intend to have access. Use it only for trusted administrative accounts.

Practical GRANT Examples

SQL

-- Web application: read/write on one database only
GRANT SELECT, INSERT, UPDATE, DELETE
  ON myapp.*
  TO 'webapp'@'%';

-- Read-only reporting user limited to a subnet
GRANT SELECT ON myapp.* TO 'reporter'@'10.0.0.%';

-- DBA who can change schema but not modify data
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES, CREATE VIEW
  ON myapp.*
  TO 'dba'@'localhost';

-- Backup agent: minimum needed to dump the database
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER, RELOAD
  ON *.*
  TO 'backup_agent'@'localhost';

-- Column-level: expose only public columns (hide cost, margin)
GRANT SELECT (product_id, product_name, category, price)
  ON myapp.products
  TO 'catalog_api'@'%';

-- Routine-level: run one specific procedure only
GRANT EXECUTE ON PROCEDURE myapp.process_order
  TO 'order_service'@'%';

-- Apply changes immediately (needed when editing grant tables directly)
FLUSH PRIVILEGES;

Note

In MySQL 8.0, FLUSH PRIVILEGES is generally not needed after GRANT/REVOKE — the grant tables are updated automatically. It is still needed if you directly modify rows in mysql.user or mysql.db.

REVOKE — Removing Privileges

SQL

-- Revoke a specific privilege
REVOKE DELETE ON myapp.* FROM 'webapp'@'%';

-- Revoke multiple privileges
REVOKE INSERT, UPDATE ON myapp.orders FROM 'reporter'@'10.0.0.%';

-- Revoke all privileges on a specific database
REVOKE ALL PRIVILEGES ON myapp.* FROM 'old_user'@'localhost';

-- Revoke all privileges and grant option globally
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'old_user'@'localhost';

Checking What a User Can Do

SQL

-- Show grants for a specific user
SHOW GRANTS FOR 'webapp'@'%';
-- GRANT USAGE ON *.* TO 'webapp'@'%'
-- GRANT SELECT, INSERT, UPDATE, DELETE ON 'myapp'.* TO 'webapp'@'%'

-- Show grants for the current session
SHOW GRANTS;
SHOW GRANTS FOR CURRENT_USER();

-- Check a specific privilege (MySQL 8.0+)
SELECT HAS_TABLE_PRIVILEGE('webapp'@'%', 'myapp.orders', 'DELETE');
SELECT HAS_DATABASE_PRIVILEGE('webapp'@'%', 'myapp', 'CREATE');

-- Query information_schema for full detail
SELECT GRANTEE, TABLE_SCHEMA, PRIVILEGE_TYPE, IS_GRANTABLE
FROM information_schema.SCHEMA_PRIVILEGES
WHERE GRANTEE LIKE '%webapp%';

MySQL 8.0 Roles

MySQL 8.0 introduced roles — named collections of privileges you can assign to multiple users. Roles make managing permissions for groups of users much cleaner and reduce the risk of privilege drift when onboarding new team members.

SQL

-- 1. Create roles
CREATE ROLE 'app_read', 'app_write', 'app_admin';

-- 2. Grant privileges to roles
GRANT SELECT ON myapp.* TO 'app_read';
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp.* TO 'app_write';
GRANT ALL PRIVILEGES ON myapp.* TO 'app_admin';

-- 3. Grant roles to users
GRANT 'app_read'  TO 'reporter'@'10.0.0.%';
GRANT 'app_write' TO 'webapp'@'%';
GRANT 'app_admin' TO 'dba'@'localhost';

-- 4. Set the default role (active on login without SET ROLE)
SET DEFAULT ROLE 'app_write' TO 'webapp'@'%';
SET DEFAULT ROLE ALL TO 'dba'@'localhost';

-- 5. Activate a role in the current session
SET ROLE 'app_admin';
SELECT CURRENT_ROLE();

-- List all role assignments
SELECT FROM_USER, FROM_HOST, TO_USER, TO_HOST
FROM information_schema.ROLE_EDGES;

Mandatory Roles (MySQL 8.0)

SQL

-- Set roles that are automatically active for every user
SET PERSIST mandatory_roles = 'app_read';

-- Now every user automatically has app_read (they cannot remove it)
-- Individual grants can extend but not reduce these base privileges

Principle of Least Privilege — Real App Roles

SQL

-- Roles for a production SaaS application
CREATE ROLE 'saas_readonly', 'saas_app', 'saas_migration', 'saas_backup';

-- Privileges per role
GRANT SELECT ON saasdb.* TO 'saas_readonly';
GRANT SELECT, INSERT, UPDATE, DELETE ON saasdb.* TO 'saas_app';
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES ON saasdb.* TO 'saas_migration';
GRANT 'saas_app' TO 'saas_migration';  -- migration also needs DML
GRANT SELECT, LOCK TABLES, SHOW VIEW, EVENT, TRIGGER ON saasdb.*
  TO 'saas_backup';
GRANT RELOAD ON *.* TO 'saas_backup';  -- FLUSH TABLES needed for consistent backup

-- Users
CREATE USER 'api_service'@'%'         IDENTIFIED BY 'ApiS3rvice!';
CREATE USER 'analytics'@'10.0.0.%'   IDENTIFIED BY 'An@lytics!';
CREATE USER 'deploy_bot'@'localhost'  IDENTIFIED BY 'D3pl0y!';
CREATE USER 'backup_job'@'localhost'  IDENTIFIED BY 'B@ckupJ0b!';

-- Assign roles
GRANT 'saas_app'       TO 'api_service'@'%';
GRANT 'saas_readonly'  TO 'analytics'@'10.0.0.%';
GRANT 'saas_migration' TO 'deploy_bot'@'localhost';
GRANT 'saas_backup'    TO 'backup_job'@'localhost';

-- Default roles active on login
SET DEFAULT ROLE ALL TO
  'api_service'@'%',
  'analytics'@'10.0.0.%',
  'deploy_bot'@'localhost',
  'backup_job'@'localhost';

Privilege Audit Query

SQL

-- Full audit of all users' database-level privileges
SELECT
  GRANTEE,
  TABLE_SCHEMA AS db,
  GROUP_CONCAT(PRIVILEGE_TYPE ORDER BY PRIVILEGE_TYPE SEPARATOR ', ') AS privileges,
  IS_GRANTABLE
FROM information_schema.SCHEMA_PRIVILEGES
GROUP BY GRANTEE, TABLE_SCHEMA, IS_GRANTABLE
ORDER BY GRANTEE;

-- Find all superuser accounts (dangerous — review quarterly)
SELECT User, Host, Super_priv, Grant_priv
FROM mysql.user
WHERE Super_priv = 'Y' OR Grant_priv = 'Y';

Best Practices

Grant the minimum set of privileges needed — add more only when a specific need arises.
Use roles (MySQL 8.0+) to manage permissions for groups of users with the same access pattern.
Revoke and recreate privileges when an application access needs change rather than just adding more.
Audit all user privileges quarterly — remove stale accounts and reduce over-privileged accounts.
Never grant ALL PRIVILEGES at the global level (.) to application accounts.
Document the purpose of each account and its expected privilege set in your infrastructure runbook.
Use column-level grants to protect sensitive data (SSN, salary, credit card) from broad SELECT grants.