MENU
Privileges
Privileges determine what an authenticated account is allowed to do. MySQL distinguishes between a fixed set of static privileges (built into the server, such as SELECT or DROP) and an extensible set of dynamic privileges (registered by the server and by components/plugins, such as CONNECTION_ADMIN). Both kinds are assigned with GRANT and removed with REVOKE. Privileges can be granted directly to an account, or bundled into a role and granted to accounts as a unit.5.2.1. GRANT
Assigns one or more privileges, a role, or proxy rights to an account or role.| GRANT priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_or_role [, user_or_role] ... [WITH GRANT OPTION] [AS user [WITH ROLE DEFAULT | NONE | ALL | ALL EXCEPT role [, role] ... | role [, role] ...]]; GRANT PROXY ON user_or_role TO user_or_role [, user_or_role] ... [WITH GRANT OPTION]; GRANT role [, role] ... TO user_or_role [, user_or_role] ... [WITH ADMIN OPTION]; |
The first form grants static or dynamic privileges. The second form (GRANT PROXY) lets one account impersonate another. The third form grants an existing role to a user or to another role; see Roles for the full role workflow.
object_type is TABLE, FUNCTION, or PROCEDURE. priv_level sets the scope a privilege applies to:- * or *.* — the whole server (global).
- db_name.* — every object in a database.
- db_name.tbl_name or tbl_name — a single table.
- db_name.routine_name — a single stored routine (with object_type set to FUNCTION or PROCEDURE).
| GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl TO 'someuser'@'somehost'; GRANT EXECUTE ON PROCEDURE mydb.myproc TO 'someuser'@'somehost'; GRANT ALL PRIVILEGES ON test.* TO 'root'@'localhost' IDENTIFIED BY 'goodsecret' REQUIRE SUBJECT '/C=EE/ST=Some-State/L=Tallinn/O=MySQL demo client certificate/CN=Tonu Samuel/emailAddress=tonu@example.com' AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/O=MySQL Finland AB/CN=Tonu Samuel/emailAddress=tonu@example.com' AND CIPHER 'EDH-RSA-DES-CBC3-SHA'; |
Column-level privileges are scoped with a column list in parentheses. EXECUTE lets the grantee call a stored procedure without table-level access to the objects it touches inside. The last example ties ALL PRIVILEGES on a database to a client certificate matching a specific subject, issuer, and cipher.
5.2.2. REVOKE
Removes privileges, roles, or proxy rights previously granted.| REVOKE [IF EXISTS] priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level FROM user_or_role [, user_or_role] ... [IGNORE UNKNOWN USER]; REVOKE [IF EXISTS] ALL [PRIVILEGES], GRANT OPTION FROM user_or_role [, user_or_role] ... [IGNORE UNKNOWN USER]; REVOKE [IF EXISTS] PROXY ON user_or_role FROM user_or_role [, user_or_role] ... [IGNORE UNKNOWN USER]; REVOKE [IF EXISTS] role [, role] ... FROM user_or_role [, user_or_role] ... [IGNORE UNKNOWN USER]; |
The four forms mirror GRANT's four forms: specific privileges, all privileges together with the GRANT OPTION, proxy rights, and roles. IF EXISTS and IGNORE UNKNOWN USER suppress errors when the named privilege or account is not actually present, which is useful in idempotent setup scripts.
| REVOKE INSERT, UPDATE, DELETE ON app_db.* FROM 'app_write'; |
Removes the three listed privileges on app_db from the app_write role or user, leaving any other privileges it holds untouched.
5.2.3. Static Privileges
Static privileges are built into the server. Each entry below lists the scopes it can be granted at (global, database, table, column, procedure, or proxy) and what it enables.| ALL [PRIVILEGES] | Grants all privileges except GRANT OPTION. |
| ALTER (global, database, table) | Enables the use of ALTER TABLE. |
| ALTER ROUTINE (global, database, table) | Enables stored routines to be altered or dropped. |
| CREATE (global, database, table) | Enables database and table creation. |
| CREATE ROLE (global) | Enables role creation. |
| CREATE ROUTINE (global, database, table) | Enables stored routine creation. |
| CREATE TABLESPACE (global) | Enables tablespaces and log file groups to be created, altered, or dropped. |
| CREATE TEMPORARY TABLES (global, database) | Enables the use of CREATE TEMPORARY TABLE. |
| CREATE USER (global) | Enables the use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES. |
| CREATE VIEW (global, database, table) | Enables views to be created and altered. |
| DELETE (global, database, table) | Enables the use of DELETE. |
| DROP (global, database, table) | Enables databases, tables, and views to be dropped. |
| DROP ROLE (global) | Enables roles to be dropped. |
| EVENT (global, database) | Enables the use of events for the Event Scheduler. |
| EXECUTE (global, database, table) | Enables the user to execute stored routines. |
| FILE (global) | Enables the user to cause the server to read or write files. |
| GRANT_OPTION (global, database, table, procedure, proxy) | Enables privileges to be granted to or removed from other accounts. |
| INDEX (global, database, table) | Enables indexes to be created or dropped. |
| INSERT (global, database, table, column) | Enables the use of INSERT. |
| LOCK TABLES (global, database) | Enables the use of LOCK TABLES on tables for which the account has the SELECT privilege. |
| PROCESS (global) | Enables the user to see all processes with SHOW PROCESSLIST. |
| PROXY (from user to user) | Enables user proxying. |
| REFERENCES (global, database, table, column) | Has no effect in the current privilege system; retained for compatibility. |
| RELOAD (global) | Enables the use of FLUSH operations. |
| REPLICATION CLIENT (global) | Enables the user to ask where the master or slave servers are. |
| REPLICATION SLAVE (global) | Enables replication slaves to read binary log events from the master. |
| SELECT (global, database, table, column) | Enables the use of SELECT. |
| SHOW DATABASES (global) | Enables the use of SHOW DATABASES to show all databases. |
| SHOW VIEW (global, database, table) | Enables the use of SHOW CREATE VIEW. |
| SHUTDOWN (global) | Enables the use of mysqladmin shutdown. |
| SUPER (global) | Enables administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and the mysqladmin debug command. |
| TRIGGER (global, database, table) | Enables trigger operations. |
| UPDATE (global, database, table, column) | Enables the use of UPDATE. |
| USAGE | No privileges; represents "no privileges granted". |
5.2.4. Dynamic Privileges
Dynamic privileges are registered at runtime by the server and by installed components or plugins, so the exact set available on a given server can vary. Common ones include:| ALLOW_NONEXISTENT_DEFINER | Combined with SET_ANY_DEFINER, allows a nonexistent account to be named as the DEFINER of a view, trigger, event, or stored routine; without it, SET_ANY_DEFINER permits only existing accounts as definer. |
| APPLICATION_PASSWORD_ADMIN | Enables use of the RETAIN CURRENT PASSWORD and DISCARD OLD PASSWORD clauses of ALTER USER and SET PASSWORD for the account's own password. |
| AUDIT_ABORT_EXEMPT | Allows queries that would otherwise be blocked by an "abort" item in the audit log filter. |
| AUDIT_ADMIN | Enables audit log configuration. |
| AUTHENTICATION_POLICY_ADMIN | Places constraints on how the authentication-related clauses of CREATE USER and ALTER USER may be used. |
| BACKUP_ADMIN | Enables execution of LOCK INSTANCE FOR BACKUP and access to the Performance Schema log_status table. |
| BINLOG_ADMIN | Enables binary log control via PURGE BINARY LOGS and BINLOG statements. |
| BINLOG_ENCRYPTION_ADMIN | Enables setting the binlog_encryption system variable, which turns encryption of binary and relay log files on or off. |
| CLONE_ADMIN | Enables execution of CLONE statements. |
| CONNECTION_ADMIN | Enables use of KILL or mysqladmin kill to terminate threads belonging to other accounts. |
| ENCRYPTION_KEY_ADMIN | Enables InnoDB encryption key rotation. |
| FIREWALL_ADMIN | Enables administering firewall rules for any user. |
| FIREWALL_EXEMPT | Exempts the holder from firewall restrictions. |
| FIREWALL_USER | Enables users to update their own firewall rules. |
| FLUSH_OPTIMIZER_COSTS | Enables use of FLUSH OPTIMIZER_COSTS. |
| FLUSH_STATUS | Enables use of FLUSH STATUS. |
| FLUSH_TABLES | Enables use of FLUSH TABLES. |
| FLUSH_USER_RESOURCES | Enables use of FLUSH USER_RESOURCES. |
| GROUP_REPLICATION_ADMIN | Enables starting/stopping Group Replication, changing group_replication_consistency, and using the group_replication_set_write_concurrency() and group_replication_set_communication_protocol() functions. |
| GROUP_REPLICATION_STREAM | Allows an account to establish Group Replication's group communication connections. |
| INNODB_REDO_LOG_ARCHIVE | Enables activating and deactivating redo log archiving. |
| INNODB_REDO_LOG_ENABLE | Enables ALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG to turn redo logging on or off. |
| MASKING_DICTIONARIES_ADMIN | Enables adding/removing dictionary terms with the masking_dictionary_term_add() and masking_dictionary_term_remove() functions. |
| NDB_STORED_USER | Lets the user or role and its privileges be shared and synchronized across all NDB-enabled servers in an NDB Cluster. |
| PASSWORDLESS_USER_ADMIN | Applies to accounts configured without a password. |
| PERSIST_RO_VARIABLES_ADMIN | Combined with SYSTEM_VARIABLES_ADMIN, enables SET PERSIST_ONLY to persist global system variables to mysqld-auto.cnf without changing the runtime value — useful for read-only variables settable only at startup. |
| REPLICATION_APPLIER | Enables acting as the PRIVILEGE_CHECKS_USER for a replication channel and executing BINLOG statements found in mysqlbinlog output. |
| REPLICATION_SLAVE_ADMIN | Enables connecting to the replication source, starting/stopping replication with START REPLICA/STOP REPLICA, and using CHANGE REPLICATION SOURCE TO (or CHANGE MASTER TO) and CHANGE REPLICATION FILTER. |
| RESOURCE_GROUP_ADMIN | Enables creating, altering, and dropping resource groups, and assigning threads/statements to them. |
| RESOURCE_GROUP_USER | Enables assigning threads and statements to resource groups. |
| ROLE_ADMIN | Enables granting and revoking roles, use of the WITH ADMIN OPTION clause of GRANT, and nonempty <graphml> content from the ROLES_GRAPHML() function. |
| SENSITIVE_VARIABLES_OBSERVER | Enables viewing sensitive system variable values in the Performance Schema (global_variables, session_variables, variables_by_thread, persisted_variables), selecting them, and tracking their changes via session trackers. |
| SERVICE_CONNECTION_ADMIN | Enables connecting via the network interface reserved for administrative connections. |
| SESSION_VARIABLES_ADMIN | Enables setting the session value of system variables whose session-level change can affect more than the current session. |
| SET_ANY_DEFINER | Enables setting an arbitrary account (not just the current user) as the DEFINER of a view, trigger, event, or stored routine, without requiring the broad SUPER privilege. |
| SET_USER_ID | Removed as of MySQL 8.4 (deprecated in 8.2) – granting it now raises a syntax error. It formerly enabled setting the effective authorization ID when executing a view or stored program; that role is now split between SET_ANY_DEFINER and ALLOW_NONEXISTENT_DEFINER below. |
| SHOW_ROUTINE | Enables viewing the definitions and properties of all stored routines, including those not owned by the user. |
| SKIP_QUERY_REWRITE | Exempts queries issued by the holder from rewriting by the Rewriter plugin. |
| SYSTEM_USER | Marks the account as a system user rather than a regular user. |
| SYSTEM_VARIABLES_ADMIN | Enables runtime system variable changes and changes to global transaction characteristics. |
| TABLE_ENCRYPTION_ADMIN | Enables overriding default encryption settings when table_encryption_privilege_check is enabled. |
| TP_CONNECTION_ADMIN | Enables a privileged connection that ignores the thread_pool_max_transactions_limit, allowing the account to connect and raise/remove the limit or kill running transactions even once the limit is reached. Not granted to any account by default. |
| VERSION_TOKEN_ADMIN | Enables execution of Version Tokens functions. |
| XA_RECOVER_ADMIN | Enables execution of the XA RECOVER statement. |
-- Column-level privileges
GRANT SELECT (col1), INSERT (col1, col2) ON mydb.mytbl
TO 'someuser'@'somehost';
-- Routine-level privilege
GRANT EXECUTE ON PROCEDURE mydb.myproc
TO 'someuser'@'somehost';
-- Database-level privilege tied to a specific client certificate
GRANT ALL PRIVILEGES ON test.* TO 'cert_admin'@'localhost'
REQUIRE
SUBJECT '/C=EE/ST=Some-State/L=Tallinn/O=MySQL demo client certificate/CN=Tonu Samuel/emailAddress=tonu@example.com'
AND ISSUER '/C=FI/ST=Some-State/L=Helsinki/O=MySQL Finland AB/CN=Tonu Samuel/emailAddress=tonu@example.com'
AND CIPHER 'EDH-RSA-DES-CBC3-SHA';
-- A dynamic privilege, and letting the grantee re-grant it
GRANT CONNECTION_ADMIN ON *.* TO 'ops_admin'@'localhost' WITH GRANT OPTION;
-- Dynamic privileges for setting an object's DEFINER without SUPER (MySQL 8.2+)
GRANT SET_ANY_DEFINER, ALLOW_NONEXISTENT_DEFINER ON *.* TO 'app_deploy'@'localhost';
-- Revoking specific privileges without touching the rest
REVOKE INSERT, UPDATE, DELETE ON mydb.mytbl FROM 'someuser'@'somehost';
-- Revoking everything, including the GRANT OPTION itself
REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'someuser'@'somehost';