GRANT and REVOKE

GRANT and REVOKE

GRANT, REVOKE, and Privilege Levels

GRANT ... ON level TO account adds rights and REVOKE ... FROM removes them at five levels, each in its own grant table: global *.* (mysql.user), database shop.* (mysql.db), table (tables_priv), column (columns_priv) and routine (procs_priv). Both take effect at once, without FLUSH PRIVILEGES. GRANT no longer creates a missing account, so a typo fails, and with partial_revokes on, a global grant can exclude one schema:

Column and global grants, a typo, and a partial revokeSQL

CREATE USER 'support'@'localhost' IDENTIFIED BY 'Help#Desk-2026';
GRANT SELECT (id, name, country) ON shop.customers TO 'support'@'localhost';
GRANT SELECT ON shop.orders TO 'suport'@'localhost';
SET PERSIST partial_revokes = ON;
CREATE USER 'auditor'@'localhost' IDENTIFIED BY 'Audit#Read-2026';
GRANT SELECT ON *.* TO 'auditor'@'localhost';
REVOKE SELECT ON mysql.* FROM 'auditor'@'localhost';
SHOW GRANTS FOR 'auditor'@'localhost';

Output

ERROR 1410 (42000) at line 3: You are not allowed to create a user with GRANT
+-------------------------------------------------------+
| Grants for auditor@localhost                          |
+-------------------------------------------------------+
| GRANT SELECT ON *.* TO `auditor`@`localhost`          |
| REVOKE SELECT ON `mysql`.* FROM `auditor`@`localhost` |
+-------------------------------------------------------+

support may now read three columns of customers and gets error 1143 for email. A REVOKE must name the level of the grant it undoes, or it fails with error 1141; REVOKE ALL PRIVILEGES, GRANT OPTION FROM ... clears every level.

MENU