Mysql 简明教程
MySQL - Show Privileges
MySQL 中的用户必须拥有足够的权限才能与服务器交互。可以通过向用户分配身份验证详情（如密码）来实现此操作。此外，如果用户想要交互并操作数据，则会单独授予操作或管理权限。
The users in MySQL must have enough privileges to interact with the server. This is possible by assigning authentication details, like passwords to the users. In addition to this, operational or administrative privileges are granted separately if a user wants to interact with and operate on the data.
The MySQL SHOW Privileges
MySQL SHOW PRIVILEGES 语句显示 MySQL 服务器支持的权限列表。显示的列表包括所有静态和当前注册的动态权限。
The MySQL SHOW PRIVILEGES Statement displays the list of privileges that are supported by the MYSQL server. The displayed list includes all static and currently registered dynamic privileges.
信息（返回的列表）包含三列 -
The information (returned list) contains three columns −
-
Privilege − Name of the privilege
-
Context − Name of the MySQL object for which the privilege is applicable.
-
Comment − A string value describing the purpose of the privilege.
Syntax
以下是列出 MySQL 服务器中所有权限的语法 -
Following is the syntax to list out all privileges in a MySQL Server −
SHOW PRIVILEGES;Example
以下查询列出了 MySQL 服务器支持的所有权限 -
Following query lists out all the privileges supported by the MySQL server −
SHOW PRIVILEGESOutput
执行上面的代码后，我们得到以下输出： -
After executing the above code, we get the following output −
Privilege |
Context |
Comment |
Alter |
Tables |
To alter the table |
Alter routine |
Functions, Procedures |
To alter or drop stored functions/procedures |
Create |
Databases, Tables, Indexes |
To create new databases and tables |
Create routine |
Databases |
To use CREATE FUNCTION/PROCEDURE |
Create role |
Server Admin |
To create new roles |
Create temporary tables |
Databases |
To use CREATE TEMPORARY TABLE |
Create view |
Tables |
To create new views |
Create user |
Server Admin |
To create new users |
Delete |
Tables |
To delete existing rows |
Drop |
Databases, Tables |
To drop databases, tables, and views |
Drop role |
Server Admin |
To drop roles |
Event |
Server Admin |
To create, alter, drop and execute events |
Execute |
Functions, Procedures |
To execute stored routines |
File |
File access on server |
To read and write files on the server |
Grant option |
Databases, Tables, Funcs, Procedures |
To give to other users those privileges you possess |
Index |
Tables |
To create or drop indexes |
Insert |
Tables |
To insert data into tables |
Lock tables |
Databases |
To use LOCK TABLES (together with SELECT privilege) |
Process |
Server Admin |
To view the plain text of currently executing queries |
Proxy |
Server Admin |
To make proxy user possible |
References |
Databases,Tables |
To have references on tables |
Reload |
Server Admin |
To reload or refresh tables, logs and privileges |
Replication client |
Server Admin |
To ask where the slave or master servers are |
Replication slave |
Server Admin |
To read binary log events from the master |
Select |
Tables |
To retrieve rows from table |
Show databases |
Server Admin |
To see all databases with SHOW DATABASES |
Show view |
Tables |
To see views with SHOW CREATE VIEW |
Shutdown |
Server Admin |
To shut down the server |
Super |
Server Admin |
To use KILL thread, SET GLOBAL, CHANGE MASTER, etc. |
Trigger |
Tables |
To use triggers |
Create tablespace |
Server Admin |
To create/alter/drop tablespaces |
Update |
Tables |
To update existing rows |
Usage |
Server Admin |
No privileges - allow connect only |
BINLOG_ENCRYPTION_ADMIN |
Server Admin |
|
AUDIT_ADMIN |
Server Admin |
|
ENCRYPTION_KEY_ADMIN |
Server Admin |
|
INNODB_REDO_LOG_ARCHIVE |
Server Admin |
|
APPLICATION_PASSWORD_ADMIN |
Server Admin |
|
SHOW_ROUTINE |
Server Admin |
|
BACKUP_ADMIN |
Server Admin |
|
BINLOG_ADMIN |
Server Admin |
|
CLONE_ADMIN |
Server Admin |
|
CONNECTION_ADMIN |
Server Admin |
|
SET_USER_ID |
Server Admin |
|
SERVICE_CONNECTION_ADMIN |
Server Admin |
|
GROUP_REPLICATION_ADMIN |
Server Admin |
|
REPLICATION_APPLIER |
Server Admin |
|
INNODB_REDO_LOG_ENABLE |
Server Admin |
|
PERSIST_RO_VARIABLES_ADMIN |
Server Admin |
|
TABLE_ENCRYPTION_ADMIN |
Server Admin |
|
ROLE_ADMIN |
Server Admin |
|
REPLICATION_SLAVE_ADMIN |
Server Admin |
|
SESSION_VARIABLES_ADMIN |
Server Admin |
|
RESOURCE_GROUP_ADMIN |
Server Admin |
|
RESOURCE_GROUP_USER |
Server Admin |
|
SYSTEM_USER |
Server Admin |
|
SYSTEM_VARIABLES_ADMIN |
Server Admin |
|
XA_RECOVER_ADMIN |
Server Admin |
Listing Privileges Using a Client Program
现在，让我们看看如何使用 Java、PHP、Python、JavaScript、C++ 等编程语言中的客户端程序来检索/列出授予当前 MySQL 用户的所有权限。
Now, let us see how to retrieve/list all the privileges granted to the current MySQL user using a client program in programming languages like Java, PHP, Python, JavaScript, C++ etc.