Privileges and GRANT Statement

MySQL uses a privilege system to control what each user can do. Privileges can be granted at four levels: global, database, table, and column. The principle of least privilege dictates that users should only have the permissions they strictly need.

GRANT Syntax

The GRANT statement assigns privileges to a user. You specify the privilege type, the scope (which database/table), and the user. After granting, run FLUSH PRIVILEGES to ensure the changes take effect (though it is usually automatic with GRANT).

Common Privilege Types

SELECT — read data
INSERT — add rows
UPDATE — modify rows
DELETE — remove rows
EXECUTE — run stored procedures
ALL PRIVILEGES — everything (avoid for application users)

Example

-- Grant read-only access to a specific database
GRANT SELECT ON myapp_db.* TO 'report_user'@'%';

-- Grant DML permissions but not DDL
GRANT SELECT, INSERT, UPDATE, DELETE ON myapp_db.* TO 'app_user'@'localhost';

-- Grant execute on a specific stored procedure
GRANT EXECUTE ON PROCEDURE myapp_db.GetCustomerOrders TO 'app_user'@'localhost';

-- View a user's privileges
SHOW GRANTS FOR 'app_user'@'localhost';

-- Revoke a privilege
REVOKE DELETE ON myapp_db.* FROM 'app_user'@'localhost';

SQL

Full Editor

Pro Tip

Follow the principle of least privilege: grant only what the user actually needs, nothing more.

Related Resources

MySQL Reference

Complete tag & property list

MySQL How-To Guides

Step-by-step practical guides

MySQL Exercises

Practice what you've learned