Documentation Home
MySQL 9.4 Reference Manual
Related Documentation Download this Manual
PDF (US Ltr) - 41.2Mb
PDF (A4) - 41.3Mb
Man Pages (TGZ) - 262.8Kb
Man Pages (Zip) - 368.8Kb
Info (Gzip) - 4.1Mb
Info (Zip) - 4.1Mb


MySQL 9.4 Reference Manual  / ...  / Security  / Access Control and Account Management  /  Privileges Provided by MySQL

8.2.2 Privileges Provided by MySQL

The privileges granted to a MySQL account determine which operations the account can perform. MySQL privileges differ in the contexts in which they apply and at different levels of operation:

  • Administrative privileges enable users to manage operation of the MySQL server. These privileges are global because they are not specific to a particular database.

  • Database privileges apply to a database and to all objects within it. These privileges can be granted for specific databases, or globally so that they apply to all databases.

  • Privileges for database objects such as tables, indexes, views, and stored routines can be granted for specific objects within a database, for all objects of a given type within a database (for example, all tables in a database), or globally for all objects of a given type in all databases.

Privileges also differ in terms of whether they are static (built in to the server) or dynamic (defined at runtime). Whether a privilege is static or dynamic affects its availability to be granted to user accounts and roles. For information about the differences between static and dynamic privileges, seeStatic Versus Dynamic Privileges.)

Information about account privileges is stored in the grant tables in themysql system database. For a description of the structure and contents of these tables, seeSection 8.2.3, “Grant Tables”. The MySQL server reads the contents of the grant tables into memory when it starts, and reloads them under the circumstances indicated inSection 8.2.13, “When Privilege Changes Take Effect”. The server bases access-control decisions on the in-memory copies of the grant tables.

Important

Some MySQL releases introduce changes to the grant tables to add new privileges or features. To make sure that you can take advantage of any new capabilities, update your grant tables to the current structure whenever you upgrade MySQL. SeeChapter 3,Upgrading MySQL.

The following sections summarize the available privileges, provide more detailed descriptions of each privilege, and offer usage guidelines.

Summary of Available Privileges

The following table shows the static privilege names used inGRANT andREVOKE statements, along with the column name associated with each privilege in the grant tables and the context in which the privilege applies.

Table 8.2 Permissible Static Privileges for GRANT and REVOKE

PrivilegeGrant Table ColumnContext
ALL [PRIVILEGES]Synonym for“all privileges”Server administration
ALTERAlter_privTables
ALTER ROUTINEAlter_routine_privStored routines
CREATECreate_privDatabases, tables, or indexes
CREATE ROLECreate_role_privServer administration
CREATE ROUTINECreate_routine_privStored routines
CREATE TABLESPACECreate_tablespace_privServer administration
CREATE TEMPORARY TABLESCreate_tmp_table_privTables
CREATE USERCreate_user_privServer administration
CREATE VIEWCreate_view_privViews
DELETEDelete_privTables
DROPDrop_privDatabases, tables, or views
DROP ROLEDrop_role_privServer administration
EVENTEvent_privDatabases
EXECUTEExecute_privStored routines
FILEFile_privFile access on server host
GRANT OPTIONGrant_privDatabases, tables, or stored routines
INDEXIndex_privTables
INSERTInsert_privTables or columns
LOCK TABLESLock_tables_privDatabases
PROCESSProcess_privServer administration
PROXYSeeproxies_priv tableServer administration
REFERENCESReferences_privDatabases or tables
RELOADReload_privServer administration
REPLICATION CLIENTRepl_client_privServer administration
REPLICATION SLAVERepl_slave_privServer administration
SELECTSelect_privTables or columns
SHOW DATABASESShow_db_privServer administration
SHOW VIEWShow_view_privViews
SHUTDOWNShutdown_privServer administration
SUPERSuper_privServer administration
TRIGGERTrigger_privTables
UPDATEUpdate_privTables or columns
USAGESynonym for“no privileges”Server administration

The following table shows the dynamic privilege names used inGRANT andREVOKE statements, along with the context in which the privilege applies.

Table 8.3 Permissible Dynamic Privileges for GRANT and REVOKE

PrivilegeContext
ALLOW_NONEXISTENT_DEFINEROrphan object protection
APPLICATION_PASSWORD_ADMINDual password administration
AUDIT_ABORT_EXEMPTAllow queries blocked by audit log filter
AUDIT_ADMINAudit log administration
AUTHENTICATION_POLICY_ADMINAuthentication administration
BACKUP_ADMINBackup administration
BINLOG_ADMINBackup and Replication administration
BINLOG_ENCRYPTION_ADMINBackup and Replication administration
CLONE_ADMINClone administration
CONNECTION_ADMINServer administration
CREATE_SPATIAL_REFERENCE_SYSTEMGIS administration
ENCRYPTION_KEY_ADMINServer administration
EXPORT_QUERY_RESULTSAllow user to export query results
FIREWALL_ADMINFirewall administration
FIREWALL_EXEMPTFirewall administration
FIREWALL_USERFirewall administration (deprecated)
FLUSH_OPTIMIZER_COSTSServer administration
FLUSH_PRIVILEGES (Deprecated)Server administration
FLUSH_STATUSServer administration
FLUSH_TABLESServer administration
FLUSH_USER_RESOURCESServer administration
GROUP_REPLICATION_ADMINReplication administration
GROUP_REPLICATION_STREAMReplication administration
INNODB_REDO_LOG_ARCHIVERedo log archiving administration
INNODB_REDO_LOG_ENABLERedo log administration
MASKING_DICTIONARIES_ADMINServer administration
NDB_STORED_USERNDB Cluster
OPTIMIZE_LOCAL_TABLEOPTIMIZE LOCAL TABLE statements
OPTION_TRACKER_OBSERVEROption Trackermysql_option.option_usage table read access
OPTION_TRACKER_UPDATEROption Trackermysql_option.option_usage table write access
PASSWORDLESS_USER_ADMINAuthentication administration
PERSIST_RO_VARIABLES_ADMINServer administration
REPLICATION_APPLIERPRIVILEGE_CHECKS_USER for a replication channel
REPLICATION_SLAVE_ADMINReplication administration
RESOURCE_GROUP_ADMINResource group administration
RESOURCE_GROUP_USERResource group administration
ROLE_ADMINServer administration
SENSITIVE_VARIABLES_OBSERVERServer administration
SESSION_VARIABLES_ADMINServer administration
SET_ANY_DEFINERServer administration
SHOW_ROUTINEServer administration
SKIP_QUERY_REWRITEServer administration
SYSTEM_USERServer administration
SYSTEM_VARIABLES_ADMINServer administration
TABLE_ENCRYPTION_ADMINServer administration
TELEMETRY_LOG_ADMINTelemetry log administration for MySQL HeatWave on AWS
TP_CONNECTION_ADMINThread pool administration
TRANSACTION_GTID_TAGReplication administration
VERSION_TOKEN_ADMIN (Deprecated)Server administration
XA_RECOVER_ADMINServer administration

Static Privilege Descriptions

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

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

  • ALL,ALL PRIVILEGES

    These privilege specifiers are shorthand for“all privileges available at a given privilege level” (exceptGRANT OPTION). For example, grantingALL at the global or table level grants all global privileges or all table-level privileges, respectively.

  • ALTER

    Enables use of theALTER TABLE statement to change the structure of tables.ALTER TABLE also requires theCREATE andINSERT privileges. Renaming a table requiresALTER andDROP on the old table,CREATE, andINSERT on the new table.

  • ALTER ROUTINE

    Enables use of statements that alter or drop stored routines (stored procedures and functions). For routines that fall within the scope at which the privilege is granted and for which the user is not the user named as the routineDEFINER, also enables access to routine properties other than the routine definition.

  • CREATE

    Enables use of statements that create new databases and tables.

  • CREATE ROLE

    Enables use of theCREATE ROLE statement. (TheCREATE USER privilege also enables use of theCREATE ROLE statement.) SeeSection 8.2.10, “Using Roles”.

    TheCREATE ROLE andDROP ROLE privileges are not as powerful asCREATE USER because they can be used only to create and drop accounts. They cannot be used asCREATE USER can be modify account attributes or rename accounts. SeeUser and Role Interchangeability.

  • CREATE ROUTINE

    Enables use of statements that create stored routines (stored procedures and functions). For routines that fall within the scope at which the privilege is granted and for which the user is not the user named as the routineDEFINER, also enables access to routine properties other than the routine definition.

  • CREATE TABLESPACE

    Enables use of statements that create, alter, or drop tablespaces and log file groups.

  • CREATE TEMPORARY TABLES

    Enables the creation of temporary tables using theCREATE TEMPORARY TABLE statement.

    After a session has created a temporary table, the server performs no further privilege checks on the table. The creating session can perform any operation on the table, such asDROP TABLE,INSERT,UPDATE, orSELECT. For more information, seeSection 15.1.24.2, “CREATE TEMPORARY TABLE Statement”.

  • CREATE USER

    Enables use of theALTER USER,CREATE ROLE,CREATE USER,DROP ROLE,DROP USER,RENAME USER, andREVOKE ALL PRIVILEGES statements.

  • CREATE VIEW

    Enables use of theCREATE VIEW statement.

  • DELETE

    Enables rows to be deleted from tables in a database.

  • DROP

    Enables use of statements that drop (remove) existing databases, tables, and views. TheDROP privilege is required to use theALTER TABLE ... DROP PARTITION statement on a partitioned table. TheDROP privilege is also required forTRUNCATE TABLE.

  • DROP ROLE

    Enables use of theDROP ROLE statement. (TheCREATE USER privilege also enables use of theDROP ROLE statement.) SeeSection 8.2.10, “Using Roles”.

    TheCREATE ROLE andDROP ROLE privileges are not as powerful asCREATE USER because they can be used only to create and drop accounts. They cannot be used asCREATE USER can be modify account attributes or rename accounts. SeeUser and Role Interchangeability.

  • EVENT

    Enables use of statements that create, alter, drop, or display events for the Event Scheduler.

  • EXECUTE

    Enables use of statements that execute stored routines (stored procedures and functions). For routines that fall within the scope at which the privilege is granted and for which the user is not the user named as the routineDEFINER, also enables access to routine properties other than the routine definition.

  • FILE

    Affects the following operations and server behaviors:

    • Enables reading and writing files on the server host using theLOAD DATA andSELECT ... INTO OUTFILE statements and theLOAD_FILE() function. A user who has theFILE privilege can read any file on the server host that is either world-readable or readable by the MySQL server. (This implies the user can read any file in any database directory, because the server can access any of those files.)

    • Enables creating new files in any directory where the MySQL server has write access. This includes the server's data directory containing the files that implement the privilege tables.

    • Enables use of theDATA DIRECTORY orINDEX DIRECTORY table option for theCREATE TABLE statement.

    As a security measure, the server does not overwrite existing files.

    To limit the location in which files can be read and written, set thesecure_file_priv system variable to a specific directory. SeeSection 7.1.8, “Server System Variables”.

  • GRANT OPTION

    Enables you to grant to or revoke from other users those privileges that you yourself possess.

  • INDEX

    Enables use of statements that create or drop (remove) indexes.INDEX applies to existing tables. If you have theCREATE privilege for a table, you can include index definitions in theCREATE TABLE statement.

  • INSERT

    Enables rows to be inserted into tables in a database.INSERT is also required for theANALYZE TABLE,OPTIMIZE TABLE, andREPAIR TABLE table-maintenance statements.

  • LOCK TABLES

    Enables use of explicitLOCK TABLES statements to lock tables for which you have theSELECT privilege. This includes use of write locks, which prevents other sessions from reading the locked table.

  • PROCESS

    ThePROCESS privilege controls access to information about threads executing within the server (that is, information about statements being executed by sessions). Thread information available using theSHOW PROCESSLIST statement, themysqladmin processlist command, the Information SchemaPROCESSLIST table, and the Performance Schemaprocesslist table is accessible as follows:

    • With thePROCESS privilege, a user has access to information about all threads, even those belonging to other users.

    • Without thePROCESS privilege, nonanonymous users have access to information about their own threads but not threads for other users, and anonymous users have no access to thread information.

    Note

    The Performance Schemathreads table also provides thread information, but table access uses a different privilege model. SeeSection 29.12.22.10, “The threads Table”.

    ThePROCESS privilege also enables use of theSHOW ENGINE statement, access to theINFORMATION_SCHEMAInnoDB tables (tables with names that begin withINNODB_), and access to theINFORMATION_SCHEMAFILES table.

  • PROXY

    Enables one user to impersonate or become known as another user. SeeSection 8.2.19, “Proxy Users”.

  • REFERENCES

    Creation of a foreign key constraint requires theREFERENCES privilege for the parent table.

  • RELOAD

    TheRELOAD enables the following operations:

    • Use of theFLUSH statement.

    • Use ofmysqladmin commands that are equivalent toFLUSH operations:flush-hosts,flush-logs,flush-privileges,flush-status,flush-tables,refresh, andreload.

      Thereload command tells the server to reload the grant tables into memory.flush-privileges is a synonym forreload. Therefresh command closes and reopens the log files and flushes all tables. The otherflush-xxx commands perform functions similar torefresh, but are more specific and may be preferable in some instances. For example, if you want to flush just the log files,flush-logs is a better choice thanrefresh.

    • Use ofmysqldump options that perform variousFLUSH operations:--flush-logs and--source-data.

    • Use of theRESET BINARY LOGS AND GTIDS andRESET REPLICA statements.

  • REPLICATION CLIENT

    Enables use of theSHOW BINARY LOG STATUS,SHOW REPLICA STATUS, andSHOW BINARY LOGS statements.

  • REPLICATION SLAVE

    Enables the account to request updates that have been made to databases on the replication source server, using theSHOW REPLICAS,SHOW RELAYLOG EVENTS, andSHOW BINLOG EVENTS statements. This privilege is also required to use themysqlbinlog options--read-from-remote-server (-R) and--read-from-remote-source. Grant this privilege to accounts that are used by replicas to connect to the current server as their replication source server.

  • SELECT

    Enables rows to be selected from tables in a database.SELECT statements require theSELECT privilege only if they actually access tables. SomeSELECT statements do not access tables and can be executed without permission for any database. For example, you can useSELECT as a simple calculator to evaluate expressions that make no reference to tables:

    SELECT 1+1;SELECT PI()*2;

    TheSELECT privilege is also needed for other statements that read column values. For example,SELECT is needed for columns referenced on the right hand side ofcol_name=expr assignment inUPDATE statements or for columns named in theWHERE clause ofDELETE orUPDATE statements.

    TheSELECT privilege is needed for tables or views used withEXPLAIN, including any underlying tables in view definitions.

  • SHOW DATABASES

    Enables the account to see database names by issuing theSHOW DATABASE statement. Accounts that do not have this privilege see only databases for which they have some privileges, and cannot use the statement at all if the server was started with the--skip-show-database option.

    Caution

    Because any static global privilege is considered a privilege for all databases, any static global privilege enables a user to see all database names withSHOW DATABASES or by examining theSCHEMATA table ofINFORMATION_SCHEMA, except databases that have been restricted at the database level by partial revokes.

  • SHOW VIEW

    Enables use of theSHOW CREATE VIEW statement. This privilege is also needed for views used withEXPLAIN.

  • SHUTDOWN

    Enables use of theSHUTDOWN andRESTART statements, themysqladmin shutdown command, and themysql_shutdown() C API function.

  • SUPER

    SUPER is a powerful and far-reaching privilege and should not be granted lightly. If an account needs to perform only a subset ofSUPER operations, it may be possible to achieve the desired privilege set by instead granting one or more dynamic privileges, each of which confers more limited capabilities. SeeDynamic Privilege Descriptions.

    Note

    SUPER is deprecated, and you should expect it to be removed in a future version of MySQL. SeeMigrating Accounts from SUPER to Dynamic Privileges.

    SUPER affects the following operations and server behaviors:

    You may also need theSUPER privilege to create or alter stored functions if binary logging is enabled, as described inSection 27.9, “Stored Program Binary Logging”.

  • TRIGGER

    Enables trigger operations. You must have this privilege for a table to create, drop, execute, or display triggers for that table.

    When a trigger is activated (by a user who has privileges to executeINSERT,UPDATE, orDELETE statements for the table associated with the trigger), trigger execution requires that the user who defined the trigger still have theTRIGGER privilege for the table.

  • UPDATE

    Enables rows to be updated in tables in a database.

  • USAGE

    This privilege specifier stands for“no privileges.” It is used at the global level withGRANT to specify clauses such asWITH GRANT OPTION without naming specific account privileges in the privilege list.SHOW GRANTS displaysUSAGE to indicate that an account has no privileges at a privilege level.

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.

Privilege-Granting Guidelines

It is a good idea to grant to an account only those privileges that it needs. You should exercise particular caution in granting theFILE and administrative privileges:

  • FILE can be abused to read into a database table any files that the MySQL server can read on the server host. This includes all world-readable files and files in the server's data directory. The table can then be accessed usingSELECT to transfer its contents to the client host.

  • GRANT OPTION enables users to give their privileges to other users. Two users that have different privileges and with theGRANT OPTION privilege are able to combine privileges.

  • ALTER may be used to subvert the privilege system by renaming tables.

  • SHUTDOWN can be abused to deny service to other users entirely by terminating the server.

  • PROCESS can be used to view the plain text of currently executing statements, including statements that set or change passwords.

  • SUPER can be used to terminate other sessions or change how the server operates.

  • Privileges granted for themysql system database itself can be used to change passwords and other access privilege information:

    • Passwords are stored encrypted, so a malicious user cannot simply read them to know the plain text password. However, a user with write access to themysql.user system tableauthentication_string column can change an account's password, and then connect to the MySQL server using that account.

    • INSERT orUPDATE granted for themysql system database enable a user to add privileges or modify existing privileges, respectively.

    • DROP for themysql system database enables a user to remote privilege tables, or even the database itself.

Static Versus Dynamic Privileges

MySQL supports static and dynamic privileges:

  • Static privileges are built in to the server. They are always available to be granted to user accounts and cannot be unregistered.

  • Dynamic privileges can be registered and unregistered at runtime. This affects their availability: A dynamic privilege that has not been registered cannot be granted.

For example, theSELECT andINSERT privileges are static and always available, whereas a dynamic privilege becomes available only if the component that implements it has been enabled.

The remainder of this section describes how dynamic privileges work in MySQL. The discussion uses the term“components” but applies equally to plugins.

Note

Server administrators should be aware of which server components define dynamic privileges. For MySQL distributions, documentation of components that define dynamic privileges describes those privileges.

Third-party components may also define dynamic privileges; an administrator should understand those privileges and not install components that might conflict or compromise server operation. For example, one component conflicts with another if both define a privilege with the same name. Component developers can reduce the likelihood of this occurrence by choosing privilege names having a prefix based on the component name.

The server maintains the set of registered dynamic privileges internally in memory. Unregistration occurs at server shutdown.

Normally, a component that defines dynamic privileges registers them when it is installed, during its initialization sequence. When uninstalled, a component does not unregister its registered dynamic privileges. (This is current practice, not a requirement. That is, components could, but do not, unregister at any time privileges they register.)

No warning or error occurs for attempts to register an already registered dynamic privilege. Consider the following sequence of statements:

INSTALL COMPONENT 'my_component';UNINSTALL COMPONENT 'my_component';INSTALL COMPONENT 'my_component';

The firstINSTALL COMPONENT statement registers any privileges defined by componentmy_component, butUNINSTALL COMPONENT does not unregister them. For the secondINSTALL COMPONENT statement, the component privileges it registers are found to be already registered, but no warnings or errors occur.

Dynamic privileges apply only at the global level. The server stores information about current assignments of dynamic privileges to user accounts in themysql.global_grants system table:

  • The server automatically registers privileges named inglobal_grants during server startup (unless the--skip-grant-tables option is given).

  • TheGRANT andREVOKE statements modify the contents ofglobal_grants.

  • Dynamic privilege assignments listed inglobal_grants are persistent. They are not removed at server shutdown.

Example: The following statement grants to useru1 the privileges required to control replication (including Group Replication) on a replica, and to modify system variables:

GRANT REPLICATION_SLAVE_ADMIN, GROUP_REPLICATION_ADMIN, BINLOG_ADMINON *.* TO 'u1'@'localhost';

Granted dynamic privileges appear in the output from theSHOW GRANTS statement and theINFORMATION_SCHEMAUSER_PRIVILEGES table.

ForGRANT andREVOKE at the global level, any named privileges not recognized as static are checked against the current set of registered dynamic privileges and granted if found. Otherwise, an error occurs to indicate an unknown privilege identifier.

ForGRANT andREVOKE the meaning ofALL [PRIVILEGES] at the global level includes all static global privileges, as well as all currently registered dynamic privileges:

  • GRANT ALL at the global level grants all static global privileges and all currently registered dynamic privileges. A dynamic privilege registered subsequent to execution of theGRANT statement is not granted retroactively to any account.

  • REVOKE ALL at the global level revokes all granted static global privileges and all granted dynamic privileges.

TheFLUSH PRIVILEGES statement reads theglobal_grants table for dynamic privilege assignments and registers any unregistered privileges found there.

For descriptions of the dynamic privileges provided by MySQL Server and components included in MySQL distributions, seeSection 8.2.2, “Privileges Provided by MySQL”.

Migrating Accounts from SUPER to Dynamic Privileges

In MySQL 9.4, many operations that previously required theSUPER privilege are also associated with a dynamic privilege of more limited scope. (For descriptions of these privileges, seeSection 8.2.2, “Privileges Provided by MySQL”.) Each such operation can be permitted to an account by granting the associated dynamic privilege rather thanSUPER. This change improves security by enabling DBAs to avoid grantingSUPER and tailor user privileges more closely to the operations permitted.SUPER is now deprecated; expect it to be removed in a future version of MySQL.

When removal ofSUPER occurs, operations that formerly requiredSUPER fail unless accounts grantedSUPER are migrated to the appropriate dynamic privileges. Use the following instructions to accomplish that goal so that accounts are ready prior toSUPER removal:

  1. Execute this query to identify accounts that are grantedSUPER:

    SELECT GRANTEE FROM INFORMATION_SCHEMA.USER_PRIVILEGESWHERE PRIVILEGE_TYPE = 'SUPER';

  2. For each account identified by the preceding query, determine the operations for which it needsSUPER. Then grant the dynamic privileges corresponding to those operations, and revokeSUPER.

    For example, if'u1'@'localhost' requiresSUPER for binary log purging and system variable modification, these statements make the required changes to the account:

    GRANT BINLOG_ADMIN, SYSTEM_VARIABLES_ADMIN ON *.* TO 'u1'@'localhost';REVOKE SUPER ON *.* FROM 'u1'@'localhost';

    After you have modified all applicable accounts, theINFORMATION_SCHEMA query in the first step should produce an empty result set.