8.2.2 Privileges Provided by MySQL (original) (raw)

Dynamic Privilege Descriptions

Dynamic privileges are defined at runtime, in contrast to static privileges, which are built in to the server. The following list describes each dynamic privilege available in MySQL.

Most dynamic privileges are defined at server startup. Others are defined by a particular component or plugin, as indicated in the privilege descriptions. In such cases, the privilege is unavailable unless the component or plugin that defines it is enabled.

Particular SQL statements might have more specific privilege requirements than indicated here. If so, the description for the statement in question provides the details.

• APPLICATION_PASSWORD_ADMIN (added in MySQL 8.0.14)
  For dual-password capability, this privilege enables use of the RETAIN CURRENT PASSWORD andDISCARD OLD PASSWORD clauses forALTER USER andSET PASSWORD statements that apply to your own account. This privilege is required to manipulate your own secondary password because most users require only one password.
  If an account is to be permitted to manipulate secondary passwords for all accounts, it should be granted theCREATE USER privilege rather thanAPPLICATION_PASSWORD_ADMIN.
  For more information about use of dual passwords, seeSection 8.2.15, “Password Management”.
• AUDIT_ABORT_EXEMPT (added in MySQL 8.0.28)
  Allows queries blocked by an “abort” item in the audit log filter. This privilege is defined by theaudit_log plugin; seeSection 8.4.5, “MySQL Enterprise Audit”.
  Accounts created in MySQL 8.0.28 or later with theSYSTEM_USER privilege have the AUDIT_ABORT_EXEMPT privilege assigned automatically when they are created. TheAUDIT_ABORT_EXEMPT privilege is also assigned to existing accounts with theSYSTEM_USER privilege when you carry out an upgrade procedure with MySQL 8.0.28 or later, if no existing accounts have that privilege assigned. Accounts with the SYSTEM_USER privilege can therefore be used to regain access to a system following an audit misconfiguration.
• AUDIT_ADMIN
  Enables audit log configuration. This privilege is defined by the audit_log plugin; seeSection 8.4.5, “MySQL Enterprise Audit”.
• BACKUP_ADMIN
  Enables execution of the LOCK INSTANCE FOR BACKUP statement and access to the Performance Schema log_status table.
  The BACKUP_ADMIN privilege is automatically granted to users with theRELOAD privilege when performing an in-place upgrade to MySQL 8.0 from an earlier version.
• AUTHENTICATION_POLICY_ADMIN (added in MySQL 8.0.27)
  The authentication_policy system variable places certain constraints on how the authentication-related clauses ofCREATE USER andALTER USER statements may be used. A user who has theAUTHENTICATION_POLICY_ADMIN privilege is not subject to these constraints. (A warning does occur for statements that otherwise would not be permitted.)
  For details about the constraints imposed byauthentication_policy, see the description of that variable.
• BINLOG_ADMIN
  Enables binary log control by means of thePURGE BINARY LOGS andBINLOG statements.
• BINLOG_ENCRYPTION_ADMIN
  Enables setting the system variablebinlog_encryption, which activates or deactivates encryption for binary log files and relay log files. This ability is not provided by theBINLOG_ADMIN,SYSTEM_VARIABLES_ADMIN, orSESSION_VARIABLES_ADMIN privileges. The related system variablebinlog_rotate_encryption_master_key_at_startup, which rotates the binary log master key automatically when the server is restarted, does not require this privilege.
• CLONE_ADMIN
  Enables execution of the CLONE statements. IncludesBACKUP_ADMIN andSHUTDOWN privileges.
• CONNECTION_ADMIN
  Enables use of the KILL statement or mysqladmin kill command to kill threads belonging to other accounts. (An account can always kill its own threads.)
  Enables setting system variables related to client connections, or circumventing restrictions related to client connections. From MySQL 8.0.31,CONNECTION_ADMIN is required to activate MySQL Server’s offline mode, which is done by changing the value of theoffline_mode system variable to ON.
  The CONNECTION_ADMIN privilege enables administrators with it to bypass effects of these system variables:
  • init_connect: The server does not executeinit_connect system variable content whenCONNECTION_ADMIN clients connect.
  • max_connections: The server accepts one connection from aCONNECTION_ADMIN client even if the connection limit configured by themax_connections system variable is reached.
  • offline_mode: A server in offline mode (offline_mode enabled) does not terminateCONNECTION_ADMIN client connections at the next client request, and accepts new connections fromCONNECTION_ADMIN clients.
  • read_only: Updates fromCONNECTION_ADMIN clients can be performed even when theread_only system variable is enabled. This applies to explicit table updates, and to account management statements such asGRANT andREVOKE that update tables implicitly.
    Group Replication group members need theCONNECTION_ADMIN privilege so that Group Replication connections are not terminated if one of the servers involved is placed in offline mode. If the MySQL communication stack is in use (group_replication_communication_stack = MYSQL), without this privilege, a member that is placed in offline mode is expelled from the group.
• ENCRYPTION_KEY_ADMIN
  Enables InnoDB encryption key rotation.
• FIREWALL_ADMIN
  Enables a user to administer firewall rules for any user. This privilege is defined by theMYSQL_FIREWALL plugin; seeSection 8.4.7, “MySQL Enterprise Firewall”.
• FIREWALL_EXEMPT (added in MySQL 8.0.27)
  A user with this privilege is exempt from firewall restrictions. This privilege is defined by theMYSQL_FIREWALL plugin; seeSection 8.4.7, “MySQL Enterprise Firewall”.
• FIREWALL_USER
  Enables users to update their own firewall rules. This privilege is defined by theMYSQL_FIREWALL plugin; seeSection 8.4.7, “MySQL Enterprise Firewall”.
• FLUSH_OPTIMIZER_COSTS (added in MySQL 8.0.23)
  Enables use of the FLUSH OPTIMIZER_COSTS statement.
• FLUSH_STATUS (added in MySQL 8.0.23)
  Enables use of the FLUSH STATUS statement.
• FLUSH_TABLES (added in MySQL 8.0.23)
  Enables use of the FLUSH TABLES statement.
• FLUSH_USER_RESOURCES (added in MySQL 8.0.23)
  Enables use of the FLUSH USER_RESOURCES statement.
• GROUP_REPLICATION_ADMIN
  Enables the account to start and stop Group Replication using the START GROUP REPLICATION and STOP GROUP REPLICATION statements, to change the global setting for thegroup_replication_consistency system variable, and to use thegroup_replication_set_write_concurrency() andgroup_replication_set_communication_protocol() functions. Grant this privilege to accounts that are used to administer servers that are members of a replication group.
• GROUP_REPLICATION_STREAM
  Allows a user account to be used for establishing Group Replication's group communication connections. It must be granted to a recovery user when the MySQL communication stack is used for Group Replication (group_replication_communication_stack=MYSQL).
• INNODB_REDO_LOG_ARCHIVE
  Enables the account to activate and deactivate redo log archiving.
• INNODB_REDO_LOG_ENABLE
  Enables use of theALTER INSTANCE {ENABLE|DISABLE} INNODB REDO_LOG statement to enable or disable redo logging. Introduced in MySQL 8.0.21.
  See Disabling Redo Logging.
• MASKING_DICTIONARIES_ADMIN
  Enables the account to add and remove dictionary terms using themasking_dictionary_term_add() andmasking_dictionary_term_remove() component functions. Accounts also require this dynamic privilege to remove a full dictionary using themasking_dictionary_remove() function, which removes all of the terms associated with the named dictionary currently in themysql.masking_dictionaries table.
  See Section 8.5, “MySQL Enterprise Data Masking and De-Identification”.
• NDB_STORED_USER
  Enables the user or role and its privileges to be shared and synchronized between all NDB-enabled MySQL servers as soon as they join a given NDB Cluster. This privilege is available only if theNDB storage engine is enabled.
  Any changes to or revocations of privileges made for the given user or role are synchronized immediately with all connected MySQL servers (SQL nodes). You should be aware that there is no guarantee that multiple statements affecting privileges originating from different SQL nodes are executed on all SQL nodes in the same order. For this reason, it is highly recommended that all user administration be done from a single designated SQL node.
  NDB_STORED_USER is a global privilege and must be granted or revoked using ON *.*. Trying to set any other scope for this privilege results in an error. This privilege can be given to most application and administrative users, but it cannot be granted to system reserved accounts such asmysql.session@localhost ormysql.infoschema@localhost.
  A user that has been granted theNDB_STORED_USER privilege is stored inNDB (and thus shared by all SQL nodes), as is a role with this privilege. A user that is merely granted a role that has NDB_STORED_USER is not stored inNDB; each NDB stored user must be granted the privilege explicitly.
  For more detailed information about how this works inNDB, seeSection 25.6.13, “Privilege Synchronization and NDB_STORED_USER”.
  The NDB_STORED_USER privilege is available beginning with NDB 8.0.18.
• PASSWORDLESS_USER_ADMIN (added in MySQL 8.0.27)
  This privilege applies to passwordless user accounts:
  • For account creation, a user who executesCREATE USER to create a passwordless account must possess thePASSWORDLESS_USER_ADMIN privilege.
  • In replication context, thePASSWORDLESS_USER_ADMIN privilege applies to replication users and enables replication ofALTER USER ... MODIFY statements for user accounts that are configured for passwordless authentication.
    For information about passwordless authentication, seeFIDO Passwordless Authentication.
• PERSIST_RO_VARIABLES_ADMIN
  For users who also haveSYSTEM_VARIABLES_ADMIN,PERSIST_RO_VARIABLES_ADMIN enables use ofSET PERSIST_ONLY to persist global system variables to the mysqld-auto.cnf option file in the data directory. This statement is similar toSET PERSIST but does not modify the runtime global system variable value. This makesSET PERSIST_ONLY suitable for configuring read-only system variables that can be set only at server startup.
  See also Section 7.1.9.1, “System Variable Privileges”.
• REPLICATION_APPLIER
  Enables the account to act as thePRIVILEGE_CHECKS_USER for a replication channel, and to execute BINLOG statements in mysqlbinlog output. Grant this privilege to accounts that are assigned usingCHANGE REPLICATION SOURCE TO (from MySQL 8.0.23) or CHANGE MASTER TO (before MySQL 8.0.23) to provide a security context for replication channels, and to handle replication errors on those channels. As well as theREPLICATION_APPLIER privilege, you must also give the account the required privileges to execute the transactions received by the replication channel or contained in the mysqlbinlog output, for example to update the affected tables. For more information, see Section 19.3.3, “Replication Privilege Checks”.
• REPLICATION_SLAVE_ADMIN
  Enables the account to connect to the replication source server, start and stop replication using theSTART REPLICA andSTOP REPLICA statements, and use theCHANGE REPLICATION SOURCE TO statement (from MySQL 8.0.23) or CHANGE MASTER TO statement (before MySQL 8.0.23) and theCHANGE REPLICATION FILTER statements. Grant this privilege to accounts that are used by replicas to connect to the current server as their replication source server. This privilege does not apply to Group Replication; useGROUP_REPLICATION_ADMIN for that.
• RESOURCE_GROUP_ADMIN
  Enables resource group management, consisting of creating, altering, and dropping resource groups, and assignment of threads and statements to resource groups. A user with this privilege can perform any operation relating to resource groups.
• RESOURCE_GROUP_USER
  Enables assigning threads and statements to resource groups. A user with this privilege can use theSET RESOURCE GROUP statement and the RESOURCE_GROUP optimizer hint.
• ROLE_ADMIN
  Enables granting and revoking roles, use of theWITH ADMIN OPTION clause of theGRANT statement, and nonempty<graphml> element content in the result from theROLES_GRAPHML() function. Required to set the value of themandatory_roles system variable.
• SENSITIVE_VARIABLES_OBSERVER (added in MySQL 8.0.29)
  Enables a holder to view the values of sensitive system variables in the Performance Schema tablesglobal_variables,session_variables,variables_by_thread, andpersisted_variables, to issueSELECT statements to return their values, and to track changes to them in session trackers for connections. Users without this privilege cannot view or track those system variable values. SeePersisting Sensitive System Variables.
• SERVICE_CONNECTION_ADMIN
  Enables connections to the network interface that permits only administrative connections (seeSection 7.1.12.1, “Connection Interfaces”).
• SESSION_VARIABLES_ADMIN (added in MySQL 8.0.14)
  For most system variables, setting the session value requires no special privileges and can be done by any user to affect the current session. For some system variables, setting the session value can have effects outside the current session and thus is a restricted operation. For these, theSESSION_VARIABLES_ADMIN privilege enables the user to set the session value.
  If a system variable is restricted and requires a special privilege to set the session value, the variable description indicates that restriction. Examples includebinlog_format,sql_log_bin, andsql_log_off.
  Prior to MySQL 8.0.14 whenSESSION_VARIABLES_ADMIN was added, restricted session system variables can be set only by users who have theSYSTEM_VARIABLES_ADMIN orSUPER privilege.
  The SESSION_VARIABLES_ADMIN privilege is a subset of theSYSTEM_VARIABLES_ADMIN andSUPER privileges. A user who has either of those privileges is also permitted to set restricted session variables and effectively hasSESSION_VARIABLES_ADMIN by implication and need not be grantedSESSION_VARIABLES_ADMIN explicitly.
  See also Section 7.1.9.1, “System Variable Privileges”.
• SET_USER_ID
  Enables setting the effective authorization ID when executing a view or stored program. A user with this privilege can specify any account as theDEFINER attribute of a view or stored program. Stored programs execute with the privileges of the specified account, so ensure that you follow the risk minimization guidelines listed inSection 27.6, “Stored Object Access Control”.
  As of MySQL 8.0.22,SET_USER_ID also enables overriding security checks designed to prevent operations that (perhaps inadvertently) cause stored objects to become orphaned or that cause adoption of stored objects that are currently orphaned. For details, seeOrphan Stored Objects.
• SHOW_ROUTINE (added in MySQL 8.0.20)
  Enables a user to access definitions and properties of all stored routines (stored procedures and functions), even those for which the user is not named as the routineDEFINER. This access includes:
  • The contents of the Information SchemaROUTINES table.
  • The SHOW CREATE FUNCTION and SHOW CREATE PROCEDURE statements.
  • The SHOW FUNCTION CODE and SHOW PROCEDURE CODE statements.
  • The SHOW FUNCTION STATUS and SHOW PROCEDURE STATUS statements.
    Prior to MySQL 8.0.20, for a user to access definitions of routines the user did not define, the user must have the global SELECT privilege, which is very broad. As of 8.0.20,SHOW_ROUTINE may be granted instead as a privilege with a more restricted scope that permits access to routine definitions. (That is, an administrator can rescind globalSELECT from users that do not otherwise require it and grantSHOW_ROUTINE instead.) This enables an account to back up stored routines without requiring a broad privilege.
• SKIP_QUERY_REWRITE (added in MySQL 8.0.31)
  Queries issued by a user with this privilege are not subject to being rewritten by the Rewriter plugin (see Section 7.6.4, “The Rewriter Query Rewrite Plugin”).
  This privilege should be granted to users issuing administrative or control statements that should not be rewritten, as well as toPRIVILEGE_CHECKS_USER accounts (seeSection 19.3.3, “Replication Privilege Checks”) used to apply statements from a replication source.
• SYSTEM_USER (added in MySQL 8.0.16)
  The SYSTEM_USER privilege distinguishes system users from regular users:
  • A user with theSYSTEM_USER privilege is a system user.
  • A user without theSYSTEM_USER privilege is a regular user.
    The SYSTEM_USER privilege has an effect on the accounts to which a given user can apply its other privileges, as well as whether the user is protected from other accounts:
  • A system user can modify both system and regular accounts. That is, a user who has the appropriate privileges to perform a given operation on regular accounts is enabled by possession ofSYSTEM_USER to also perform the operation on system accounts. A system account can be modified only by system users with appropriate privileges, not by regular users.
  • A regular user with appropriate privileges can modify regular accounts, but not system accounts. A regular account can be modified by both system and regular users with appropriate privileges.
    This also means that database objects created by users with the SYSTEM_USER privilege cannot be modified or dropped by users without the privilege. This also applies to routines for which the definer has this privilege.
    For more information, seeSection 8.2.11, “Account Categories”.
    The protection against modification by regular accounts that is afforded to system accounts by theSYSTEM_USER privilege does not apply to regular accounts that have privileges on themysql system schema and thus can directly modify the grant tables in that schema. For full protection, do not grant mysql schema privileges to regular accounts. SeeProtecting System Accounts Against Manipulation by Regular Accounts.
    If the audit_log plugin is in use (seeSection 8.4.5, “MySQL Enterprise Audit”), from MySQL 8.0.28, accounts with the SYSTEM_USER privilege are automatically assigned theAUDIT_ABORT_EXEMPT privilege, which permits their queries to be executed even if an“abort” item configured in the filter would block them. Accounts with theSYSTEM_USER privilege can therefore be used to regain access to a system following an audit misconfiguration.
• SYSTEM_VARIABLES_ADMIN
  Affects the following operations and server behaviors:
  • Enables system variable changes at runtime:
    * Enables server configuration changes to global system variables withSET GLOBAL andSET PERSIST.
    * Enables server configuration changes to global system variables withSET PERSIST_ONLY, if the user also hasPERSIST_RO_VARIABLES_ADMIN.
    * Enables setting restricted session system variables that require a special privilege. In effect,SYSTEM_VARIABLES_ADMIN impliesSESSION_VARIABLES_ADMIN without explicitly grantingSESSION_VARIABLES_ADMIN.

  See also Section 7.1.9.1, “System Variable Privileges”.

  • Enables changes to global transaction characteristics (see Section 15.3.7, “SET TRANSACTION Statement”).
• TABLE_ENCRYPTION_ADMIN (added in MySQL 8.0.16)
  Enables a user to override default encryption settings whentable_encryption_privilege_check is enabled; seeDefining an Encryption Default for Schemas and General Tablespaces.
• TELEMETRY_LOG_ADMIN
  Enables telemetry log configuration. This privilege is defined by the telemetry_log plugin, which is deployed through HeatWave on AWS.
• TP_CONNECTION_ADMIN
  Enables connecting to the server with a privileged connection. When the limit defined bythread_pool_max_transactions_limit has been reached, new connections are not permitted. A privileged connection ignores the transaction limit and permits connecting to the server to increase the transaction limit, remove the limit, or kill running transactions. This privilege is not granted to any user by default. To establish a privileged connection, the user initiating a connection must have theTP_CONNECTION_ADMIN privilege.
  A privileged connection can execute statements and start transactions when the limit defined bythread_pool_max_transactions_limit has been reached. A privileged connection is placed in theAdmin thread group. SeePrivileged Connections.
• VERSION_TOKEN_ADMIN
  Enables execution of Version Tokens functions. This privilege is defined by theversion_tokens plugin; seeSection 7.6.6, “Version Tokens”.
• XA_RECOVER_ADMIN
  Enables execution of theXA RECOVER statement; seeSection 15.3.8.1, “XA Transaction SQL Statements”.
  Prior to MySQL 8.0, any user could execute theXA RECOVER statement to discover the XID values for outstanding prepared XA transactions, possibly leading to commit or rollback of an XA transaction by a user other than the one who started it. In MySQL 8.0,XA RECOVER is permitted only to users who have theXA_RECOVER_ADMIN privilege, which is expected to be granted only to administrative users who have need for it. This might be the case, for example, for administrators of an XA application if it has crashed and it is necessary to find outstanding transactions started by the application so they can be rolled back. This privilege requirement prevents users from discovering the XID values for outstanding prepared XA transactions other than their own. It does not affect normal commit or rollback of an XA transaction because the user who started it knows its XID.