时间：2021-07-01 10:21:17 帮助过：18人阅读
Mysql 涉及用户权限的语句有GRANT,REVOKE
通过Mysql自带的help文档可以查看相应的语法：
GRANT
GRANT priv_type，...... ON [obj_type] priv_level TO user_specification [WITH with_option ...]
priv_type对应的有:
ALL | ALTER | ALTER ROUTINE | CREATE | CREATE ROUTINE | CREATE TEMPORARY TABLES | CREATE USER | CREATE VIEW | DELETE | DROP | EXECUTE | FILE | INDEX | INSERT | LOCK TABLES | PROCESS | REFERENCES | RELOAD | REPLICATION CLIENT | REPLICATION SLAVE | SELECT | SHOW DATABASES | SHOW VIEW | SHUTDOWN | SUPER | UPDATE | USAGE
权限 | 意义 |
ALL [PRIVILEGES] | 设置除GRANT OPTION之外的所有简单权限 |
ALTER | 允许使用ALTER TABLE |
ALTER ROUTINE | 更改或取消已存储的子程序 |
CREATE | 允许使用CREATE TABLE |
CREATE ROUTINE | 创建已存储的子程序 |
CREATE TEMPORARY TABLES | 允许使用CREATE TEMPORARY TABLE |
CREATE USER | 允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。 |
CREATE VIEW | 允许使用CREATE VIEW |
DELETE | 允许使用DELETE |
DROP | 允许使用DROP TABLE |
EXECUTE | 允许用户运行已存储的子程序 |
FILE | 允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE |
INDEX | 允许使用CREATE INDEX和DROP INDEX |
INSERT | 允许使用INSERT |
LOCK TABLES | 允许对您拥有SELECT权限的表使用LOCK TABLES |
PROCESS | 允许使用SHOW FULL PROCESSLIST |
REFERENCES | 未被实施 |
RELOAD | 允许使用FLUSH |
REPLICATION CLIENT | 允许用户询问从属服务器或主服务器的地址 |
REPLICATION SLAVE | 用于复制型从属服务器（从主服务器中读取二进制日志事件） |
SELECT | 允许使用SELECT |
SHOW DATABASES | SHOW DATABASES显示所有数据库 |
SHOW VIEW | 允许使用SHOW CREATE VIEW |
SHUTDOWN | 允许使用mysqladmin shutdown |
SUPER | 允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。 |
UPDATE | 允许使用UPDATE |
USAGE | “无权限”的同义词 |
GRANT OPTION |
object_type对应的有:
TABLE | FUNCTION | PROCEDURE
priv_level对应的有:
* | *.* | db_name.* | db_name.tbl_name | tbl_name | db_name.routine_name
user_specification对应内容为:
[ IDENTIFIED BY [PASSWORD] ‘password‘ | IDENTIFIED WITH auth_plugin [AS ‘auth_string‘] ]
ssl_option对应的有:
SSL | X509 | CIPHER ‘cipher‘ | ISSUER ‘issuer‘ | SUBJECT ‘subject‘
with_option对应内容为:
GRANT OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR count | MAX_CONNECTIONS_PER_HOUR count | MAX_USER_CONNECTIONS count
举例：
mysql> create database testdb; mysql> grant all privileges on testdb.* to test@‘localhost‘ identified by ‘testdb‘;
mysql> create user test@‘localhost‘ identified by ‘testdb‘;
通过上述方式，用户被创建在mysql.user表中。
mysql> select host,user,authentication_string from mysql.user where user=‘test‘;
对于当前用户授权情况可以通过以下语句查询
mysql> show grants; mysql> show grants for test@‘localhost‘; mysql> show grants for current_user; mysql> show grants for current_user();
REVOKE
REOVKE priv_type，...... ON [obj_type] priv_level FROM user_specification
举例：
mysql> revoke all on testdb.* from test@‘127.0.0.1’
删除user表中定义的用户
mysql> drop user ‘dbd‘@‘127.0.0.1‘;
本文出自 “LinuxOracle” 博客，请务必保留此出处http://onlinekof2001.blog.51cto.com/3106724/1685454
Mysql用户授权管理
标签：grant