Movatterモバイル変換

Table 6.2 Permissible Privileges for GRANT and REVOKE

• 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).

• CREATE

  Enables use of statements that create new databases and tables.

• CREATE ROUTINE

  Enables use of statements that create stored routines (stored procedures and functions).

• 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 13.1.18.2, “CREATE TEMPORARY TABLE Statement”.

• CREATE USER

  Enables use of theALTER USER,CREATE USER,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.

• 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).

• 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.

  • As of MySQL 5.7.17, 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 5.1.7, “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, theINFORMATION_SCHEMA.PROCESSLIST 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 25.12.16.4, “The threads Table”.

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

• PROXY

  Enables one user to impersonate or become known as another user. SeeSection 6.2.14, “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,flush-threads,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--master-data.

  • Use of theRESET statement.

• REPLICATION CLIENT

  Enables use of theSHOW MASTER STATUS,SHOW SLAVE STATUS, andSHOW BINARY LOGS statements.

• REPLICATION SLAVE

  Enables the account to request updates that have been made to databases on the source server, using theSHOW SLAVE HOSTS,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-master. Grant this privilege to accounts that are used by replica servers to connect to the current server as their source.

• 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 a global privilege is considered a privilege for all databases,any global privilege enables a user to see all database names withSHOW DATABASES or by examining theINFORMATION_SCHEMASCHEMATA table.

• SHOW VIEW

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

• SHUTDOWN

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

• SUPER

  Affects the following operations and server behaviors:

  • Enables server configuration changes by modifying global system variables. For some system variables, setting the session value also requires theSUPER privilege. 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. See alsoSection 5.1.8.1, “System Variable Privileges”.

  • Enables changes to global transaction characteristics (seeSection 13.3.6, “SET TRANSACTION Statement”).

  • Enables the account to start and stop replication, including Group Replication.

  • Enables use of theCHANGE MASTER TO andCHANGE REPLICATION FILTER statements.

  • Enables binary log control by means of thePURGE BINARY LOGS andBINLOG statements.

  • Enables setting the effective authorization ID when executing a view or stored program. A user with this privilege can specify any account in theDEFINER attribute of a view or stored program.

  • Enables use of theCREATE SERVER,ALTER SERVER, andDROP SERVER statements.

  • Enables use of themysqladmin debug command.

  • EnablesInnoDB encryption key rotation.

  • Enables reading the DES key file by theDES_ENCRYPT() function.

  • Enables execution of Version Tokens functions.

  • Enables control over client connections not permitted to non-SUPER accounts:

    • Enables use of theKILL statement ormysqladmin kill command to kill threads belonging to other accounts. (An account can always kill its own threads.)

    • The server does not executeinit_connect system variable content whenSUPER clients connect.

    • The server accepts one connection from aSUPER client even if the connection limit configured by themax_connections system variable is reached.

    • A server in offline mode (offline_mode enabled) does not terminateSUPER client connections at the next client request, and accepts new connections fromSUPER clients.

    • Updates can be performed even when theread_only system variable is enabled. This applies to explicit table updates, and to use of account-management statements such asGRANT andREVOKE that update tables implicitly.

  You may also need theSUPER privilege to create or alter stored functions if binary logging is enabled, as described inSection 23.7, “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 modify account attributes such as resource limits or SSL characteristics without naming specific account privileges in the privilege list.SHOW GRANTS displaysUSAGE to indicate that an account has no privileges at a privilege level.

• 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.