PermissiblePrivilegesforGRANTandREVOKE
Table 6.2 Permissible Privileges for GRANT and REVOKE
成都创新互联是一家专注于成都网站建设、做网站与策划设计,萍乡网站建设哪家好?成都创新互联做网站,专注于网站建设十多年,网设计领域的专业建站公司;建站业务涵盖:萍乡等地区。萍乡做网站价格咨询:18980820575
Privilege | Column | Context |
---|---|---|
ALL [PRIVILEGES] | Synonym for “all privileges” | Server administration |
ALTER | Alter_priv | Tables |
ALTER
ROUTINE | Alter_routine_priv | Stored routines |
CREATE | Create_priv | Databases, tables, or indexes |
CREATE
ROUTINE | Create_routine_priv | Stored routines |
CREATE TABLESPACE | Create_tablespace_priv | Server administration |
CREATE TEMPORARY TABLES | Create_tmp_table_priv | Tables |
CREATE
USER | Create_user_priv | Server administration |
CREATE
VIEW | Create_view_priv | Views |
DELETE | Delete_priv | Tables |
DROP | Drop_priv | Databases, tables, or views |
EVENT | Event_priv | Databases |
EXECUTE | Execute_priv | Stored routines |
FILE | File_priv | File access on server host |
GRANT
OPTION | Grant_priv | Databases, tables, or stored routines |
INDEX | Index_priv | Tables |
INSERT | Insert_priv | Tables or columns |
LOCK
TABLES | Lock_tables_priv | Databases |
PROCESS | Process_priv | Server administration |
PROXY | See proxies_priv table | Server administration |
REFERENCES | References_priv | Databases or tables |
RELOAD | Reload_priv | Server administration |
REPLICATION CLIENT | Repl_client_priv | Server administration |
REPLICATION SLAVE | Repl_slave_priv | Server administration |
SELECT | Select_priv | Tables or columns |
SHOW
DATABASES | Show_db_priv | Server administration |
SHOW VIEW | Show_view_priv | Views |
SHUTDOWN | Shutdown_priv | Server administration |
SUPER | Super_priv | Server administration |
TRIGGER | Trigger_priv | Tables |
UPDATE | Update_priv | Tables or columns |
USAGE | Synonym for “no privileges” | Server administration |
The following list provides general descriptions of the privileges 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.
The
ALL
orALL PRIVILEGES
privilege specifier is shorthand. It stands 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.The
ALTER
privilege 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.The
ALTER ROUTINE
privilege is needed to alter or drop stored routines (procedures and functions).The
CREATE
privilege enables creation of new databases and tables.The
CREATE ROUTINE
privilege is needed to create stored routines (procedures and functions).The
CREATE TABLESPACE
privilege is needed to create, alter, or drop tablespaces and log file groups.The
CREATE TEMPORARY TABLES
privilege 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 as
DROP TABLE
,INSERT
,UPDATE
, orSELECT
. For more information, see Section 13.1.18.3, “CREATE TEMPORARY TABLE Syntax”.The
CREATE USER
privilege enables use of theALTER USER
,CREATE USER
,DROP USER
,RENAME USER
, andREVOKE ALL PRIVILEGES
statements.The
CREATE VIEW
privilege enables use of theCREATE VIEW
statement.The
DELETE
privilege enables rows to be deleted from tables in a database.The
DROP
privilege enables you to drop (remove) existing databases, tables, and views. TheDROP
privilege is required in order to use the statementALTER TABLE ... DROP PARTITION
on a partitioned table. TheDROP
privilege is also required forTRUNCATE TABLE
. If you grant theDROP
privilege for themysql
database to a user, that user can drop the database in which the MySQL access privileges are stored.The
EVENT
privilege is required to create, alter, drop, or see events for the Event Scheduler.The
EXECUTE
privilege is required to execute stored routines (procedures and functions).The
FILE
privilege gives you permission to read and write files on the server host using theLOAD DATA INFILE
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.) TheFILE
privilege also enables the user to create 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 a security measure, the server will not overwrite existing files. As of MySQL 5.7.17, theFILE
privilege is required to use theDATA DIRECTORY
orINDEX DIRECTORY
table option for theCREATE TABLE
statement.To limit the location in which files can be read and written, set the
secure_file_priv
system to a specific directory. See Section 5.1.5, “Server System Variables”.The
GRANT OPTION
privilege enables you to give to other users or remove from other users those privileges that you yourself possess.The
INDEX
privilege enables you to 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.The
INSERT
privilege enables rows to be inserted into tables in a database.INSERT
is also required for theANALYZE TABLE
,OPTIMIZE TABLE
, andREPAIR TABLE
table-maintenance statements.The
LOCK TABLES
privilege enables the use of explicitLOCK TABLES
statements to lock tables for which you have theSELECT
privilege. This includes the use of write locks, which prevents other sessions from reading the locked table.The
PROCESS
privilege pertains to display of information about the threads executing within the server (that is, information about the statements being executed by sessions). The privilege enables use ofSHOW PROCESSLIST
or mysqladmin processlist to see threads belonging to other accounts; you can always see your own threads. ThePROCESS
privilege also enables use ofSHOW ENGINE
.The
PROXY
privilege enables a user to impersonate or become known as another user. See Section 6.3.9, “Proxy Users”.The
REFERENCES
privilege is unused before MySQL 5.7.6. As of 5.7.6, creation of a foreign key constraint requires theREFERENCES
privilege for the parent table.The
RELOAD
privilege enables use of theFLUSH
statement. It also enables mysqladmin commands that are equivalent toFLUSH
operations:flush-hosts
,flush-logs
,flush-privileges
,flush-status
,flush-tables
,flush-threads
,refresh
, andreload
.The
reload
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-
commands perform functions similar toxxx
refresh
, 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
.The
REPLICATION CLIENT
privilege enables the use of theSHOW MASTER STATUS
,SHOW SLAVE STATUS
, andSHOW BINARY LOGS
statements.The
REPLICATION SLAVE
privilege should be granted to accounts that are used by slave servers to connect to the current server as their master. Without this privilege, the slave cannot request updates that have been made to databases on the master server.The
SELECT
privilege enables you to select rows from tables in a database.SELECT
statements require theSELECT
privilege only if they actually retrieve rows from a table. 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;
The
SELECT
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.The
SELECT
privilege is also needed for tables or views being used withEXPLAIN
, including any underlying tables of views.The
SHOW DATABASES
privilege 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. Note that any global privilege is a privilege for the database.The
SHOW VIEW
privilege enables use of theSHOW CREATE VIEW
statement. This privilege is also needed for views being used withEXPLAIN
.The
SHUTDOWN
privilege enables use of theSHUTDOWN
statement, the mysqladmin shutdown command, and themysql_shutdown()
C API function.The
SUPER
privilege enables these operations and server behaviors:You may also need the
SUPER
privilege to create or alter stored functions if binary logging is enabled, as described in Section 23.7, “Binary Logging of Stored Programs”.Enables use of the
KILL
statement or mysqladmin kill command to kill threads belonging to other accounts. (You can always kill your own threads.)The server accepts one connection from a
SUPER
client even if the connection limit controlled by themax_connections
system variable is reached.Updates can be performed even when the
read_only
system variable is enabled. This applies to table updates and use of account-management statements such asGRANT
andREVOKE
.The server does not execute
init_connect
system variable content whenSUPER
clients connect.A server in offline mode (
offline_mode
enabled) does not terminateSUPER
client connections at the next client request, and accepts new connections fromSUPER
clients.Enables configuration changes by modifying global system variables. For some system variables, setting the session value also requires the
SUPER
privilege; if so, it is indicated in the variable description. Examples includebinlog_format
,sql_log_bin
, andsql_log_off
.Enables starting and stopping replication on slave servers, including Group Replication.
Enables use of the
CHANGE MASTER TO
andCHANGE REPLICATION FILTER
statements.Enables binary log control by means of the
PURGE 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 the
DEFINER
attribute of a view or stored program.Enables use of the
CREATE SERVER
,ALTER SERVER
, andDROP SERVER
statements.Enables use of the mysqladmin debug command.
Enables
InnoDB
key rotation.Enables reading the DES key file by the
DES_ENCRYPT()
function.Enables execution of Version Tokens user-defined functions.
Enables control over client connections not permitted to non-
SUPER
accounts:
The
TRIGGER
privilege 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 execute
INSERT
,UPDATE
, orDELETE
statements for the table associated with the trigger), trigger execution requires that the user who defined the trigger still have theTRIGGER
privilege.The
UPDATE
privilege enables rows to be updated in tables in a database.The
USAGE
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.SHOW GRANTS
displaysUSAGE
to indicate that an account has no privileges at a privilege level.
It is a good idea to grant to an account only those privileges that it needs.
You should exercise particular caution in granting the FILE
and administrative privileges:
The
FILE
privilege 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.The
GRANT OPTION
privilege 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.The
ALTER
privilege may be used to subvert the privilege system by renaming tables.The
SHUTDOWN
privilege can be abused to deny service to other users entirely by terminating the server.The
PROCESS
privilege can be used to view the plain text of currently executing statements, including statements that set or change passwords.The
SUPER
privilege can be used to terminate other sessions or change how the server operates.Privileges granted for the
mysql
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 theuser
tableauthentication_string
column can change an account's password, and then connect to the MySQL server using that account.
新闻标题:PermissiblePrivilegesforGRANTandREVOKE
本文地址:http://pwwzsj.com/article/jcgjip.html