转 MySQL 用户权限详细汇总

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了转 MySQL 用户权限详细汇总，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含14264字，纯文字阅读大概需要21分钟。

内容图文

1，MySQL权限体系

MySQL 的权限体系大致分为5个层级: 
全局层级： 
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。 
数据库层级： 
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANT ALL ON db_name.和REVOKE ALL ON db_name.只授予和撤销数据库权限。 
表层级： 
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。 
列层级： 
列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。 
子程序层级： 
CREATE ROUTINE, ALTER ROUTINE, EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

这些权限信息存储在下面的系统表中: 
mysql.user 
mysql.db 
mysql.host 
mysql.table_priv 
mysql.column_priv 
mysql. procs_priv 
当用户连接进来，mysqld会通过上面的这些表对用户权限进行验证！

2， 千里追踪之5表

相对于oracle来说，mysql的特性是可以限制ip，用户user、ip地址host、密码passwd这3个是用户管理的基础，权限的细节基本在mysql.user、mysql.db、mysql.host、mysql.table_priv、mysql.column_priv这几张表就可以看到很多细节，接下来仔细分析这些表就可以知道权限的奥秘。

---

<版权所有，文章允许转载，但必须以链接方式注明源地址，否则追究法律责任!> 
原博客地址： http://blog.csdn.net/mchdba/article/details/45921045 
原作者：黄杉 (mchdba)

---

演示过程中需要建立用户来演示，先简单介绍下如何创建用户： 
GRANT priv_type ON database.table 
TO user[IDENTIFIED BY [PASSWORD] ‘password’] 
[,user [IDENTIFIED BY [PASSWORD] ‘password’]…]

示例： 
GRANT SELECT, INSERT, UPDATE, DELETE ON d3307.* TO zengxiaoteng@’%’ IDENTIFIED BY ‘0523’;

2.1db表

2.1.1 表结构如下：

mysql> desc mysql.db;
+-----------------------+---------------+------+-----+---------+-------+
| Field                 | Type          | Null | Key | Default | Extra |
+-----------------------+---------------+------+-----+---------+-------+
| Host                  | char(60)      | NO   | PRI |         |       |
| Db                    | char(64)      | NO   | PRI |         |       |
| User                  | char(16)      | NO   | PRI |         |       |
| Select_priv           | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Insert_priv           | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Update_priv           | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Delete_priv           | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Create_priv           | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Drop_priv             | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Grant_priv            | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| References_priv       | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Index_priv            | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Alter_priv            | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Create_tmp_table_priv | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Lock_tables_priv      | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Create_view_priv      | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Show_view_priv        | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Create_routine_priv   | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Alter_routine_priv    | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Execute_priv          | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Event_priv            | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
| Trigger_priv          | enum(‘N‘,‘Y‘) | NO   |     | N       |       |
+-----------------------+---------------+------+-----+---------+-------+
22 rows in set (0.02 sec)

mysql>
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
27
28
29
30
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
27
28
29
30

2.1.2分析如下：

db表存储了所有对一个数据库的所有操作权限。创建用户的时候，都会往Host字段，User字段，Password字段录入用户信息； 
而当执行　GRANT SELECT,INSERT ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的授权语句的话，Select_priv和Insert_priv字段的值会变成Y其它字段仍然是N； 
当你执行了GRANT ALL ON d3307.* TO u4@’%’ IDENTIFIED BY ‘u40523’;类似的复制语句的话，后面的字段都会变成Y的值；

2.1.3 创建单个select、insert授予权限

创建用户：

GRANT SELECT,INSERT ON d3307.* TO user4@‘192.168.52‘ IDENTIFIED BY ‘user0523‘;
1
1

应该除了Host、db、user字段有值，除了Select_priv、Insert_priv值为Y外，其它的都是N。

查看mysql.db表的记录正是如此，如下所示：

mysql> SELECT * FROM mysql.`db` where user=‘user4‘\G;
*************************** 1. row ***************************
                 Host: 192.168.52
                   Db: d3307
                 User: user4
          Select_priv: Y
          Insert_priv: Y
          Update_priv: N
          Delete_priv: N
          Create_priv: N
            Drop_priv: N
           Grant_priv: N
      References_priv: N
           Index_priv: N
           Alter_priv: N
Create_tmp_table_priv: N
     Lock_tables_priv: N
     Create_view_priv: N
       Show_view_priv: N
  Create_routine_priv: N
   Alter_routine_priv: N
         Execute_priv: N
           Event_priv: N
         Trigger_priv: N
1 row in set (0.01 sec)

ERROR: 
No query specified

mysql>
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
27
28
29
30
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
27
28
29
30

2.1.4 授予ALL权限

执行sql语句建立用户：

GRANT ALL ON d3307.* TO dba5@‘192.168.52.1‘ IDENTIFIED BY ‘dba0523‘;
1
1

建立用户的时候，如下所示，除了Host、db、user字段外，所有的*_priv字段记录都会变成Y值，(Grant_priv仍然是N值除非加了WITH* GRANT OPTION执行GRANT ALL ON d3307.* TO dba5@’192.168.52.1’ IDENTIFIED BY ‘dba0523’ WITH GRANT OPTION ;)

如下所示：

mysql> SELECT * FROM mysql.`db` where user=‘dba5‘\G;
*************************** 1. row ***************************
                 Host: 192.168.52.1
                   Db: d3307
                 User: dba5
          Select_priv: Y
          Insert_priv: Y
          Update_priv: Y
          Delete_priv: Y
          Create_priv: Y
            Drop_priv: Y
           Grant_priv: N
      References_priv: Y
           Index_priv: Y
           Alter_priv: Y
Create_tmp_table_priv: Y
     Lock_tables_priv: Y
     Create_view_priv: Y
       Show_view_priv: Y
  Create_routine_priv: Y
   Alter_routine_priv: Y
         Execute_priv: Y
           Event_priv: Y
         Trigger_priv: Y
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>
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
27
28
29
30
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
27
28
29
30

2.2 user表

2.2.1 表结构：

mysql> desc mysql.user;
    +------------------------+-----------------------------------+------+-----+---------+-------+
    | Field                  | Type                              | Null | Key | Default | Extra |
    +------------------------+-----------------------------------+------+-----+---------+-------+
    | Host                   | char(60)                          | NO   | PRI |         |       |
    | User                   | char(16)                          | NO   | PRI |         |       |
    | Password               | char(41)                          | NO   |     |         |       |
    | Select_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Insert_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Update_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Delete_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Create_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Drop_priv              | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Reload_priv            | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Shutdown_priv          | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Process_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | File_priv              | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Grant_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | References_priv        | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Index_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Alter_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Show_db_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Super_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Create_tmp_table_priv  | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Lock_tables_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Execute_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Repl_slave_priv        | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Repl_client_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Create_view_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Show_view_priv         | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Create_routine_priv    | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Alter_routine_priv     | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Create_user_priv       | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Event_priv             | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Trigger_priv           | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | Create_tablespace_priv | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    | ssl_type               | enum(‘‘,‘ANY‘,‘X509‘,‘SPECIFIED‘) | NO   |     |         |       |
    | ssl_cipher             | blob                              | NO   |     | NULL    |       |
    | x509_issuer            | blob                              | NO   |     | NULL    |       |
    | x509_subject           | blob                              | NO   |     | NULL    |       |
    | max_questions          | int(11) unsigned                  | NO   |     | 0       |       |
    | max_updates            | int(11) unsigned                  | NO   |     | 0       |       |
    | max_connections        | int(11) unsigned                  | NO   |     | 0       |       |
    | max_user_connections   | int(11) unsigned                  | NO   |     | 0       |       |
    | plugin                 | char(64)                          | YES  |     |         |       |
    | authentication_string  | text                              | YES  |     | NULL    |       |
    | password_expired       | enum(‘N‘,‘Y‘)                     | NO   |     | N       |       |
    +------------------------+-----------------------------------+------+-----+---------+-------+
    43 rows in set (0.10 sec)

mysql>

2.2.2 分析

存储用户记录的表，存储了用户的信息，每一次创建用户的时候，都会往这个表里录入记录，当你执行了，都会往Host字段，User字段，Password字段录入数据，但是后面的Select_priv、Insert_priv、Update_priv等字段的值，只有赋予GRANT ALL ON . TO timdba@’192.%’ IDENTIFIED BY ‘timdba0523’;类似的对所有库的操作权限的时候才会被记录成Y，否则都记录成N。

2.2.3 创建对库所有表有操作权限的普通用户

创建用户：

GRANT SELECT,UPDATE ON d3307.* TO user6@‘192.168.52.1‘ IDENTIFIED BY ‘user0523‘;
1
1

分析结果：存储在mysql.user表里面的记录当中，Host、User、Password是有值的，但是其它的Select_priv等*_priv字段值都是N。

验证结果，去查看表里的存储记录，如下所示：

mysql> SELECT * FROM mysql.user where user=‘user6‘\G;
*************************** 1. row ***************************
                  Host: 192.168.52.1
                  User: user6
              Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A
           Select_priv: N
           Insert_priv: N
           Update_priv: N
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: N
            Alter_priv: N
          Show_db_priv: N
            Super_priv: N
 Create_tmp_table_priv: N
      Lock_tables_priv: N
          Execute_priv: N
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: N
        Show_view_priv: N
   Create_routine_priv: N
    Alter_routine_priv: N
      Create_user_priv: N
            Event_priv: N
          Trigger_priv: N
Create_tablespace_priv: N
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: 
      password_expired: N
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql>
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
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
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51

2.2.4 创建对于所有表有操作权限的用户

创建用户：

mysql> GRANT SELECT,UPDATE ON *.* TO user7@‘%‘ IDENTIFIED BY ‘user0523‘;
Query OK, 0 rows affected (0.00 sec)

mysql>
1
2
3
4
1
2
3
4

分析： 
基本的Host、User、Password字段有记录值，然后grant了select和update所以关于*_priv字段中select和update字段有值为Y，其它*_priv字段值应该是N。

查看记录结果，分享正确，如下所示：

mysql> SELECT * FROM mysql.user where user=‘user7‘\G;
*************************** 1. row ***************************
                  Host: %
                  User: user7
              Password: *A4D1F6ACEBC5D3EB0F6D33C7DCC629E8BE55B75A
           Select_priv: Y
           Insert_priv: N
           Update_priv: Y
           Delete_priv: N
           Create_priv: N
             Drop_priv: N
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: N
            Index_priv: 


           
                本文系统来源：http://www.cnblogs.com/wangdaijun/p/5714685.html