MySQL数据库的用户授权_查看权限_mysql 查看用户授权

Mysql 的权限分类

分为以下四种：

1.全局权限

mysql> grant all privileges on *.* to 'test'@'%' with grant option; # 赋予全部的权限
mysql> grant select on *.* to 'test'@'%'; # 赋予查询的权限
mysql> revoke all privileges on *.* from 'test'@'%'; # 撤销全部的权限
1
2
3

说明：通过通配符 *.* 赋予权限，会往 mysql.user 表插入一条记录。

2.db权限

mysql> grant all privileges on testdb.* to 'test'@'%' with grant option;
mysql> revoke all privileges on testdb.* from 'test'@'%';
mysql> grant select on testdb.* to 'test'@'%';
mysql> revoke select on testdb.* from 'test'@'%';
1
2
3
4

说明：通过 db_name.* 授予权限，会往 mysql.db 表插入一条记录。

3.表权限

mysql> grant all privileges on testdb.student to 'test'@'%' with grant option;  
mysql> grant select on testdb.student to 'test'@'%';
1
2

说明：上述的授权语句，会往 mysql.tables_priv 表插入一条记录。

4.列权限

mysql> grant select(id), insert(id,a) on testdb.student to 'test'@'%' with grant option;
1

说明：上述的授权语句，会往 mysql.columns_priv 表插入一条记录。

权限数据的存储

每种权限的数据都会在磁盘和内存中存储，具体的存储位置为：

1.全局权限
磁盘：表 mysql.user
内存：数组 acl_user

2.db权限
磁盘：表 mysql.db
内存：数组 acl_dbs

3.表权限
磁盘：表 mysql.tables_priv
内存：和列权限组成的 hash 结构 column_priv_hash

4.列权限
磁盘：表 mysql.columns_priv
内存：和表权限组成的 hash 结构 column_priv_hash

每种权限的修改策略和作用范围

1.全局权限

策略：已存在的连接不生效，新建立连接立即生效。即已经存在的会话不会起作用，必须重新创建会话权限才会生效。

范围：当前线程，即重启服务后就失效了

2.db权限

策略：所有连接立即生效，但是已经存在的会话必须执行 use db_name 后权限才会生效。因为会话在执行 use db_name 数据库的相关权限会保存在会话变量中，即使之后别的会话 revoke 或者 grant 权限，也不会影响到该会话，在切换出该 db 之前该会话会一直持有原来的权限

范围：全局，即重启服务后依旧有效

3.表权限

策略：所有连接立即生效，即已经存在的会话也会立刻生效

范围：全局，即重启服务后依旧有效

4.列权限
策略：所有连接立即生效，即已经存在的会话也会立刻生效

范围：全局 ，即重启服务后依旧有效

刷新权限

对于全局权限，FLUSH PRIVILEGES 操作会清空 acl_user 数组，acl_dbs 数组，column_priv_hash 集合，然后从 mysql.user 表， mysql.db 表，mysql.table_priv 表，mysql.columns_priv 表读取数据到对应的内存对象中。所以说如果内存中的权限数据和磁盘表中的数据一致的话，FLUSH PRIVILEGES 其实是可以不用做的。

使用 GRANT、REVOKE、SET PASSWORD、RENAME USER 等命令来更改用户的权限，内存和磁盘中的数据都是同步更新的，即 MySQL 服务器会注意到这些变化并立即将更新后的用户数据和权限数据加载至内存中，所以执行上述这些命令后是不需要 FLUSH PRIVILEGES 的。

FLUSH PRIVILEGES 的使用场景：

如果使用 INSERT、UPDATE、DELETE 等 DML 语句直接修改权限表（mysql.user、mysql.db、mysql.tables_priv、mysql.columns_priv），内存中的权限数据是不会同步更新的，此时我们就需要重启服务器（当 MySQL 启动时，所有的权限都会被加载到内存中）或者使用 FLUSH PRIVILEGES 命令来更新内存权限数据。即权限需在重启服务器或者 FLUSH PRIVILEGES 之后方可生效。

MySQL 提供的操作权限

另请参见官方文档：https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

授权语法格式

给用户授权的语法格式：

mysql> GRANT permission1, permission2, ... ON database_name.table_name TO 'user_name'@'host';
mysql> GRANT ALL PRIVILEGES ON database_name.table_name TO 'user_name'@'host' WITH GRANT OPTION;
1
2

说明:
1.GRANT 表示授予的含义
2.permission1, permission2, ... 表示授予的具体权限，如 SELECT、INSERT、UPDATE 等，权限之间使用逗号分隔；如果要授予所的权限则使用 ALL PRIVILEGES 或者 ALL
3.ON 表示这些权限对哪些数据库和表生效，格式：【数据库名.表名】，这里写 *.* 表示所有数据库的所有表。如果要指定将权限应用到test 库的 user 表中，可以这么写：test.user
4.database_name 数据库名
5.table_name 表名
6.TO 将权限授予哪个用户。格式：【用户名@登录IP或域名】
7.user_name 是用户名；host 是主机名，主机名可以指定为 %，表示没有限制，在任何主机都可以登录；也可以指定为 192.168.0.%，表示在 192.168.0.0 ~ 192.168.0.255 这个网段的主机上远程登录数据库服务器
8.user_name 和 host 可以使用单引号或者双引号引起来，也可以直接省略掉
9.identified by 用来指定用户的登录密码
10.with grant option 表示允许用户将自己的权限授权给其它用户
11.对同一个用户多次添加权限时，会和已有权限合并，不会覆盖已有权限。即权限会自动叠加，不会覆盖之前授予的权限，比如你先给用户添加一个 SELECT 权限，后来又给用户添加了一个 INSERT 权限，那么该用户就同时拥有了 SELECT 和 INSERT 权限。

参考示例

授予用户拥有某个数据库的全部权限

mysql> GRANT ALL PRIVILEGES ON database_name.* TO 'user_name'@'host';
1

注：
1.星号 * 是通配符，表示匹配全部的表
2.PRIVILEGES 关键词可以省略

授予用户拥有所有数据库的全部权限

mysql> GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'host';
1

注：星号是通配符。第一个星号表示匹配任意的数据库，第二个星号表示匹配任意的表。

授予用户拥有某个数据库的部分权限

mysql> GRANT SELECT, INSERT, DELETE ON database_name.* TO 'user_name'@'host';
mysql> grant select,update on test.* to lwx@37.114.28.114;
1
2

注：用户表中虽然 user 和 host 是两个字段，但是授权的时候必须通过 user@host 来指定被授权的用户，因为这个两个字段是联合主键，两个字段联合起来必须唯一。

授予用户拥有某个数据库中的某个表的部分权限

例如，授予用户 pig@% 拥有数据库 test 中的表 user 的查询、插入权限：

mysql> GRANT SELECT, INSERT ON test.user TO 'pig'@'%'; 
1

授予用户也具有给其它用户授权的权限

语法格式：

mysql> GRANT permission1, permission2, ... ON database_name.table_name TO 'user_name'@'host' WITH GRANT OPTION; 
1

例如，授予用户 liaowenxiong@% 可以给其它用户授予针对数据库 test 中的表 student 的查询、插入的权限的权限：

mysql> GRANT select,insert ON test.student TO 'liaowenxiong'@'%' WITH GRANT OPTION;
1

授予用户操作表外键的权限

mysql> grant references on qydpw.* to ‘lwx’@‘192.168.0.%’;
1

授予用户创建临时表的权限

mysql> grant create temporary tables on qydpw.* to ‘lwx’@‘192.168.0.%’;
1

授予用户操作索引的权限

mysql> grant index on qydpw.* to ‘developer’@‘192.168.0.%’;
1

授予用户创建视图的权限

mysql> grant create view on qydpw.* to ‘developer’@‘192.168.0.%’;
1

授予用户查看视图源代码的权限

mysql> grant show view on qydpw.* to ‘developer’@‘192.168.0.%’;
1

授予创建存储过程和函数的权限

mysql> grant create routine on qydpw.* to ‘developer’@‘192.168.0.%’;
mysql> grant create routine on qydpw.* to ‘lwx’@‘127.0.0.1’;
1
2

授予用户 file 权限

拥有 file 权限才可以执行 select into outfile 和 load data infile 语句。

mysql> grant file on *.* to ‘lwx’@‘localhost’;
1

授予用户 super 权限

拥有 super 权限，用户可以终止任何查询，使用 set 语句修改系统变量，使用 CHANGE MASTER 和 PURGE MASTER LOGS。

mysql> grant super on *.* to ‘lwx’@‘localhost’;
1

授予用户 process 权限

通过这个权限，用户可以执行 SHOW PROCESSLIST 和 KILL 命令。默认情况下，每个用户都可以执行 SHOW PROCESSLIST 命令，但是只能查询本用户的进程。

mysql> SHOW PROCESSLIST;
+------+-------+---------------------+------------+---------+------+----------+------------------+
| Id   | User  | Host                | db         | Command | Time | State    | Info             |
+------+-------+---------------------+------------+---------+------+----------+------------------+
| 2515 | root  | localhost           | qydpw      | Query   |    0 | starting | SHOW PROCESSLIST |
| 2596 | qydpw | 47.114.59.224:46182 | qydpw      | Sleep   | 1779 |          | NULL             |
| 2597 | qydpw | 47.114.59.224:46192 | qydpw      | Sleep   | 1689 |          | NULL             |
| 2598 | qydpw | 47.114.59.224:46194 | qydpw      | Sleep   | 1686 |          | NULL             |
| 2599 | qydpw | 47.114.59.224:46196 | production | Sleep   | 1648 |          | NULL             |
| 2600 | qydpw | 47.114.59.224:46208 | production | Sleep   | 1505 |          | NULL             |
| 2601 | qydpw | 47.114.59.224:46212 | qydpw      | Sleep   | 1444 |          | NULL             |
| 2602 | qydpw | 47.114.59.224:46218 | qydpw      | Sleep   | 1333 |          | NULL             |
| 2603 | qydpw | 47.114.59.224:46230 | production | Sleep   | 1104 |          | NULL             |
| 2604 | qydpw | 47.114.59.224:46234 | production | Sleep   | 1066 |          | NULL             |
| 2605 | qydpw | 47.114.59.224:46244 | qydpw      | Sleep   |  833 |          | NULL             |
| 2606 | qydpw | 47.114.59.224:46246 | qydpw      | Sleep   |  814 |          | NULL             |
| 2607 | qydpw | 47.114.59.224:46250 | production | Sleep   |  792 |          | NULL             |
| 2608 | qydpw | 47.114.59.224:46252 | production | Sleep   |  788 |          | NULL             |
| 2609 | qydpw | 47.114.59.224:46256 | production | Sleep   |  727 |          | NULL             |
| 2610 | qydpw | 47.114.59.224:46258 | qydpw      | Sleep   |  692 |          | NULL             |
| 2611 | qydpw | 47.114.59.224:46268 | production | Sleep   |  489 |          | NULL             |
| 2612 | qydpw | 47.114.59.224:46270 | qydpw      | Sleep   |  480 |          | NULL             |
| 2613 | qydpw | 47.114.59.224:46274 | qydpw      | Sleep   |  416 |          | NULL             |
| 2614 | qydpw | 47.114.59.224:46280 | production | Sleep   |  323 |          | NULL             |
| 2615 | qydpw | 47.114.59.224:46290 | production | Sleep   |   90 |          | NULL             |
+------+-------+---------------------+------------+---------+------+----------+------------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

注意：super、process、file 等权限不能够指定某个数据库，on 后面必须跟 *.*，如下示例：

mysql> grant super on pyt.* to lwx@localhost;
ERROR 1221 (HY000): Incorrect usage of DB GRANT and GLOBAL PRIVILEGES
mysql> grant super on *.* to lwx@localhost;
Query OK, 0 rows affected (0.01 sec)
1
2
3
4

授予修改/删除存储过程和函数的权限

mysql> grant alter routine on qydpw.* to developer@192.168.0.%; 
1

授予执行/调用存储过程和函数的权限

mysql> grant execute on qydpw.* to developer@192.168.0.%;
1

授予用户只能查询某个表的特定列

mysql> grant select(advice_id,user_id) on qydpw.tf_advice to lwx@localhost;
1

授予用户执行指定存储过程的权限

例如，授予用户执行存储过程 sys.diagnostics 的权限：

mysql> grant execute on procedure sys.diagnostics to lwx@127.0.0.1;
1

授予用户执行指定函数的权限

例如，授予用户执行函数 sys.ps_thread_account 的权限：

mysql> grant execute on function sys.ps_thread_account to lwx@127.0.0.1;
1

授予某个用户拥有所有数据库的全部权限，且拥有授权给其它账户的权限

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'host' WITH GRANT OPTION; 
1

查看用户所拥有的权限

查看其它用户的权限：

mysql> SHOW GRANTS FOR 'user_name'@'host';
1

例如，查看用户 liaowenxiong@% 所拥有的权限：

mysql> show grants for 'liaowenxiong'@'%';
+--------------------------------------------------------------------------+
| Grants for liaowenxiong@%                                                |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'liaowenxiong'@'%'                                 |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `testdb`.* TO 'liaowenxiong'@'%' |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)
1
2
3
4
5
6
7
8

查看当前登录用户自己的权限：

mysql> show grants;
1