MySQL用户权限详细汇总

最新推荐文章于 2024-05-09 00:35:13 发布
最新推荐文章于 2024-05-09 00:35:13 发布
阅读量622 收藏 2
点赞数
分类专栏： mysql/oracle/sqlserver/db2/mongdb/redis/neo4j/GreenPlum/Teradata/hsqldb/Derby/sakila 文章标签： 数据库 markdown
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tao_wei162/article/details/84772596
版权

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 表结构如下：

?
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
<code class= "hljs objectivec" >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></code>

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授予权限

创建用户：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" > GRANT SELECT , INSERT ON d3307.* TO user4@ '192.168.52' IDENTIFIED BY 'user0523' ;</code></code>

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

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

?
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
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" >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></code></code></code>

2.1.4 授予ALL权限

执行sql语句建立用户：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" > GRANT ALL ON d3307.* TO dba5@ '192.168.52.1' IDENTIFIED BY 'dba0523' ;</code></code></code></code>

建立用户的时候，如下所示，除了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 ;)

如下所示：

?
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
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" >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></code></code></code></code></code>

2.2 user表

2.2.1 表结构：

?
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
52
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" >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>
</code></code></code></code></code>

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 创建对库所有表有操作权限的普通用户

创建用户：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" > GRANT SELECT , UPDATE ON d3307.* TO user6@ '192.168.52.1' IDENTIFIED BY 'user0523' ;</code></code></code></code></code></code>

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

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

?
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
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" >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></code></code></code></code></code></code></code>

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

创建用户：

?
1
2
3
4
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" >mysql> GRANT SELECT , UPDATE ON *.* TO user7@ '%' IDENTIFIED BY 'user0523' ;
Query OK, 0 rows affected (0.00 sec)
 
mysql></code></code></code></code></code></code></code></code>

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

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

?
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
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" >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: 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></code></code></code></code></code></code></code></code></code>

2.3 tables_priv表

2.3.1 查看表结构

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" >mysql> desc mysql.tables_priv;
+ -------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                                                                                                              | Null | Key | Default           | Extra                       |
+ -------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char (60)                                                                                                                          | NO   | PRI |                   |                             |
| Db          | char (64)                                                                                                                          | NO   | PRI |                   |                             |
| User        | char (16)                                                                                                                          | NO   | PRI |                   |                             |
| Table_name  | char (64)                                                                                                                          | NO   | PRI |                   |                             |
| Grantor     | char (77)                                                                                                                          | NO   | MUL |                   |                             |
| Timestamp   | timestamp                                                                                                                         | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Table_priv  | set ( 'Select' , 'Insert' , 'Update' , 'Delete' , 'Create' , 'Drop' , 'Grant' , 'References' , 'Index' , 'Alter' , 'Create View' , 'Show view' , 'Trigger' ) | NO   |     |                   |                             |
| Column_priv | set ( 'Select' , 'Insert' , 'Update' , 'References' )                                                                                      | NO   |     |                   |                             |
+ -------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
 
mysql>
</code></code></code></code></code></code></code></code></code>

2.3.2 分析：

记录了对一个表的单独授权记录，只有执行grant insert on dbname.tablename to user1@’%’identified by ‘pwd’;类似的授权记录才会在这个表里录入授权信息；其中各个字段涵义如下：

字段存储的数据
Host字段用户的登录ip范围
User字段表所在的数据库名称
Table_name字段授权的表的名称
Grantor字段执行grant建立用户的授权者
Timestamp字段0000-00-00 00:00:00
Table_priv字段所授予的操作表的权限，比如select、udate、delete等
Column_priv字段对这个表的某个字段单独授予的权限

 


另外当赋予all在某张表上的时候，Table_priv列会多处所有关于表的授权记录，描述如下：
Select,Insert,Update,Delete,Create,Drop,References,Index,Alter,Create View,Show view,Trigger。 
 

2.3.3 创建单独操作这个表的用户

创建用户：

?
1
2
3
4
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" >mysql> GRANT INSERT , SELECT , UPDATE ON d3307.t TO user8@ '192.168.52.1' IDENTIFIED BY 'dba0523' ;
Query OK, 0 rows affected (0.00 sec)
 
mysql></code></code></code></code></code></code></code></code></code></code>

分析结果：
应该是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv是有值的，但是Column_priv没有值，因为没有单独对某一个列做了授权限制的。

查看权限，如下所示：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" >mysql> SELECT * FROM mysql.tables_priv where user = 'user8' \G;
*************************** 1. row ***************************
        Host: 192.168.52.1
          Db: d3307
        User : user8
  Table_name: t
     Grantor: root@localhost
   Timestamp : 0000-00-00 00:00:00
  Table_priv: Select , Insert , Update
Column_priv:
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
mysql></code></code></code></code></code></code></code></code></code></code></code>

 

2.3.4 单独为某个列授权

授权语句操作：

?
1
2
3
4
5
6
7
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" >mysql> GRANT UPDATE (created_time) ON d3307.t  TO user8@ '192.168.52.1' ;
Query OK, 0 rows affected (0.00 sec)
 
mysql> GRANT SELECT (uname)  ON d3307.t  TO user8@ '192.168.52.1' ;
Query OK, 0 rows affected (0.00 sec)
 
mysql></code></code></code></code></code></code></code></code></code></code></code></code>

分析：
单独为某个列授权，会记录在这个表的Column_priv字段里面，会记录下对单个列的授权操作记录

查看记录：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" >mysql> SELECT * FROM mysql.tables_priv where user = 'user8' \G;
*************************** 1. row ***************************
        Host: 192.168.52.1
          Db: d3307
        User : user8
  Table_name: t
     Grantor: root@localhost
   Timestamp : 0000-00-00 00:00:00
  Table_priv: Select , Insert , Update
Column_priv: Select , Update
1 row in set (0.00 sec)
 
ERROR:
No query specified
 
mysql></code></code></code></code></code></code></code></code></code></code></code></code></code>


而且还会在另外一个权限表mysql.columns_priv留下记录单独的授权记录，如下所示：

?
1
2
3
4
5
6
7
8
9
10
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" >mysql> SELECT * FROM mysql.columns_priv WHERE USER = 'user8' ;
+ --------------+-------+-------+------------+--------------+---------------------+-------------+
| Host         | Db    | User  | Table_name | Column_name  | Timestamp           | Column_priv |
+ --------------+-------+-------+------------+--------------+---------------------+-------------+
| 192.168.52.1 | d3307 | user8 | t          | created_time | 0000-00-00 00:00:00 | Update      |
| 192.168.52.1 | d3307 | user8 | t          | uname        | 0000-00-00 00:00:00 | Select      |
+ --------------+-------+-------+------------+--------------+---------------------+-------------+
2 rows in set (0.00 sec)
 
mysql></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

2.4 columns_priv表

2.4.1 表结构如下：

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" >mysql> desc mysql.columns_priv;
+ -------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Field       | Type                                         | Null | Key | Default           | Extra                       |
+ -------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
| Host        | char (60)                                     | NO   | PRI |                   |                             |
| Db          | char (64)                                     | NO   | PRI |                   |                             |
| User        | char (16)                                     | NO   | PRI |                   |                             |
| Table_name  | char (64)                                     | NO   | PRI |                   |                             |
| Column_name | char (64)                                     | NO   | PRI |                   |                             |
| Timestamp   | timestamp                                    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| Column_priv | set ( 'Select' , 'Insert' , 'Update' , 'References' ) | NO   |     |                   |                             |
+ -------------+----------------------------------------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.04 sec)
 
mysql>
</code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

2.4.2 分析

单独对某一列有操作权限的时候，会将权限信息记录在这个表里面，比如新建立一个账号GRANT UPDATE(uname) ON d3307.t TO user9@’192.168.52.%’ IDENTIFIED BY ‘user0520’; 那么就会在这个表上录入授权信息记录，重点看Column_name字段和Column_priv字段的值。 
 

2.4.3 实际操作

创建用户操作：

?
1
2
3
4
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" >mysql> GRANT UPDATE (uname) ON d3307.t TO user9@ '192.168.52.%' IDENTIFIED BY 'user0520' ;
Query OK, 0 rows affected (0.00 sec)
 
mysql></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

查看结果，会在这个columns_priv表留下一条记录：

?
1
2
3
4
5
6
7
8
9
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" >mysql> SELECT * FROM mysql.columns_priv WHERE USER = 'user9' ;
         + --------------+-------+-------+------------+-------------+---------------------+-------------+
         | Host         | Db    | User  | Table_name | Column_name | Timestamp           | Column_priv |
         + --------------+-------+-------+------------+-------------+---------------------+-------------+
         | 192.168.52.% | d3307 | user9 | t          | uname       | 0000-00-00 00:00:00 | Update      |
         + --------------+-------+-------+------------+-------------+---------------------+-------------+
         1 row in set (0.00 sec)
 
         mysql></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

2.5 procs_priv表

2.5.1 表结构

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" >mysql> desc proxies_priv;
+ --------------+------------+------+-----+-------------------+-----------------------------+
| Field        | Type       | Null | Key | Default           | Extra                       |
+ --------------+------------+------+-----+-------------------+-----------------------------+
| Host         | char (60)   | NO   | PRI |                   |                             |
| User         | char (16)   | NO   | PRI |                   |                             |
| Proxied_host | char (60)   | NO   | PRI |                   |                             |
| Proxied_user | char (16)   | NO   | PRI |                   |                             |
| With_grant   | tinyint(1) | NO   |     | 0                 |                             |
| Grantor      | char (77)   | NO   | MUL |                   |                             |
| Timestamp    | timestamp  | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+ --------------+------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.04 sec)
 
mysql></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

2.6.2分析：

procs_priv表可以对存储过程和存储函数进行权限设置。主要字段：proc_priv。

 

3，创建用户

3.1、CREATE USER创建用户

使用CREATE USER语句创建用户，必须要拥有CREATE USER权限。其格式如下：

?
1
2
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" > CREATE USER user [IDENTIFIED BY [ PASSWORD ] 'password' ],
[ user [IDENTIFIED BY [ PASSWORD ] 'password' ]]...</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

　　其中，user参数表示新建用户的账户，user由用户名(User)和主机名(Host)构成；IDENTIFIED BY关键字用来设置用户的密码；password参数表示用户的密码；如果密码是一个普通的字符串，就不需要使用PASSWORD关键字。可以没有初始密码。

例如

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" > CREATE USER 'sys' @ '%' IDENTIFIED BY 'sys' ;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

执行之后user表会增加一行记录，但权限暂时全部为‘N’。 
 

3.2、用INSERT语句新建普通用户

可以使用INSERT语句直接将用户的信息添加到mysql.user表。但必须拥有mysql.user表的INSERT权限。

另外，ssl_cipher、x509_issuer、x509_subject等必须要设置值，否则INSERT语句无法执行。

示例：
INSERT INTO mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_subject) VALUES(‘%’,’newuser1’,PASSWORD(‘123456’),”,”,”)
执行INSERT之后，要使用命令：FLUSH PRIVILEGES;命令来使用户生效。

 

3.3、用GRANT语句来新建普通用户

　　用GRANT来创建新的用户时，能够在创建用户时为用户授权。但需要拥有GRANT权限。

　　语法如下：

?
1
2
3
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" > GRANT priv_type ON database . table
TO user [IDENTIFIED BY [ PASSWORD ] 'password' ]
[, user [IDENTIFIED BY [ PASSWORD ] 'password' ]...]</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

priv_type：参数表示新yoghurt的权限；
databse.table：参数表示新用户的权限范围；
user：参数新用户的账户，由用户名和主机构成；
IDENTIFIED BY关键字用来设置密码；
password：新用户密码；
PS：GRANT语句可以同时创建多个用户。.与db.*的区别在于。.对所有数据库生效，所以user表的SELECT会变为Y。而db.*user表为’N’，更改的是Db表。

 

4，删除用户

4.1 drop user删除用户

DROP USER语句删除普通用户，需要拥有DROP USER权限。
语法如下：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" > DROP USER user [, user ]...</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

user是需要删除的用户，由用户名(User)和主机名(Host)构成。

 

4.2 DELETE语句删除普通用户

可以使用DELETE语句直接将用户的信息从mysql.user表中删除。但必须拥有对mysql.user表的DELETE权限。DELETE FROM mysql.user WHERE Host = ‘%’ AND User = ‘admin’; 删除完成后，一样要FLUSH PRIVILEGES才生效。 

 

5，修改用户密码

5.1 使用mysqladmin命令来修改root用户的密码

语法：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" >mysqladmin -u -username -p password "new_password" 　</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

新密码(new_password)必须用括号括起来，单引号会报错。

示例，修改中要输入旧的密码来验证：

?
1
2
3
4
5
6
7
8
9
10
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" >[root@data02 ~]# mysqladmin -u timman -p password "tim" --socket=/usr/local/mysql3307/mysql.sock
Enter password :
[root@data02 ~]#
[root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -utimman -ptim -e "select @@port";
+ --------+
| @@port |
+ --------+
|   3307 |
+ --------+
[root@data02 ~]#</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

5.2 修改user表

UPDATE user表的passwor字段的值，也可以达到修改密码的目的；

?
1
2
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" > UPDATE user SET Password = PASSWORD ( '123' ) WHERE USER = 'myuser' ;
FLUSH PRIVILEGES ;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

刷新后生效。

 

5.3 使用SET语句来修改密码

使用root用户登录到MySQL服务器后，可以使用SET语句来修改密码：
修改自己的密码，不需要用户名

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" > SET PASSWORD = PASSWORD ( "123" );</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

修改其他用户密码：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" > SET PASSWORD FOR 'myuser' @ '%' = PASSWORD ( "123456" ) FOR 用户名@主机名</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

5.4 GRANT语句来修改普通用户的密码

使用GRANT语句修改普通用户的密码，必须拥有GRANT权限。

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" > GRANT priv_type ON database . table TO user [IDENTIFIED BY [ PASSWORD ] 'password' ]</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

示例：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT SELECT ON *.* TO 'user10' @ '%' IDENTIFIED BY '123' </code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

5.5 忘记用户密码的解决办法

普通用户，直接用root超级管理员登录进去修改密码就可以了，但是如果root密码丢失了，怎么办呢？ 
 

5.5.1 msyqld_saft方式找回密码

停止mysql：service mysqld stop;
安全模式启动：mysqld_safe –skip-grant-tables &
无密码回车键登录：mysql -uroot –p
重置密码：use mysql; update user set password=password(“”) where user=’root’ and host=’localhost’; flush privileges;
正常启动：service mysql restart
再使用mysqladmin： mysqladmin password ‘123456’ 
 

5.5.2 使用普通账号来找回密码

–>(1)：有一个修改test库的用户：grant create,delete,update,insert,select on d3307.* to test@’%’ identified by ‘t1’;

–>(2)：复制user表文件到test库下并且赋予mysql用户访问权限：
cp /home/data/mysql/data/mysql/user.* /home/data/mysql/data/test/;chown mysql.mysql /home/data/mysql/data/test/user.*

–>(3)：mysql -utest -pt1登录修改root密码： 

–>(4)：将test库的user表文件覆盖 mysql库的user表文件
cp /home/data/mysql/data/mysql/user.* /tmp/; mv /home/data/mysql/data/test/user.* /home/data/mysql/data/mysql/ ; chown mysql.mysql /home/data/mysql/data/mysql/user.*;

–>(5):查找mysql进程号，并且发送SIGHUP信号，重新加载权限表。
pgrep -n mysql; kill -SIGHUP 12234;

–>(6):无密码登录，再使用mysqladmin重新设置密码。

PS：请参考第20课的视频，那里有详细的记录整个过修改密码的过程。

 

6，收回用户权限

查看权限：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" >SHOW GRANTS;  SHOW GRANTS FOR user10@ '%' ; </code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

或者直接执行sql命令去mysql数据库下的user表中查看存储着用户的基本权限：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > SELECT * FROM mysql. user WHERE USER = 'user10' AND HOST= '%' ; </code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

使用revoke关键字来收回权限：

?
1
2
3
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" > REVOKE priv_type[(column_list)]
ON database . table
FROM user [, user ]</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

示例：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" > REVOKE EXECUTE ON d3307.* FROM user10@ '%' ;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

7，数据库用户划分

7.1 普通数据管理用户：

赋予对业务表的查询维护权限即可，授权sql如下：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" > GRANT SELECT , INSERT , UPDATE , DELETE ON d3307.* TO zengxiaoteng@ '%' IDENTIFIED BY '0523' ;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

7.2 开发人员账户：

赋予增删改查的权限，授权sql如下：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT SELECT , INSERT , DELETE , UPDATE ON d3307.* TO huyan@ '%' IDENTIFIED BY '0523' ; </code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

授予创建、修改、删除 MySQL 数据表结构权限。

?
1
2
3
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT CREATE ON d3307.*  TO huyan@’192.168.52.11’;
GRANT ALTER  ON d3307.* TO huyan@’192.168.52.11’;
GRANT DROP   ON d3307.* TO huyan@’192.168.52.11’;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

授予操作 MySQL 外键权限：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT REFERENCES ON d3307.* TO huyan@’192.168.52.11’;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

授予操作 MySQL 临时表权限:

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT CREATE TEMPORARY TABLES ON d3307.* TO huyan@’192.168.52.11’;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

授予操作 MySQL 索引权限:

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT INDEX ON d3307.* TO huyan@’192.168.52.11’;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

授予操作 MySQL 视图、查看视图源代码 权限：

?
1
2
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT CREATE VIEW ON d3307.* TO huyan@’192.168.52.11’;
GRANT SHOW   VIEW ON d3307.* TO huyan@’192.168.52.11’;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

授予操作 MySQL 存储过程、函数 权限：

?
1
2
3
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT CREATE ROUTINE ON d3307.* TO huyan@’192.168.52.11’;
GRANT ALTER ROUTINE ON d3307.* TO huyan@’192.168.52.11’;
GRANT EXECUTE        ON d3307.* TO huyan@’192.168.52.11’;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

7.3 DBA人员账户

授予普通DBA管理某个MySQL数据库(test)的权限：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT ALL PRIVILEGES ON test TO sysdba@ '192.168.52.%' ;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

授予高级 DBA 管理 MySQL 中所有数据库的权限：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT ALL ON *.* TO sysdba@ '192.168.52.%' ;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

7.4 数据分析人员只读账号

只需要分配只读的权限：

?
1
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" > GRANT SELECT ON d3307.* TO dataquery@ '192.168.52.129' IDENTIFIED BY '20150523' ;</code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

甚至有些用户，可以只分配读取某些表列的权限，如下所示：

GRANT SELECT ON test.* TO dataquery@’192.168.52.%’ IDENTIFIED BY ‘20150523’;
GRANT SELECT(id,uname) ON d3307.t TO dataquery@’192.168.52.%’ ;

示列权限登录操作：

?
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
<code class= "hljs objectivec" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs sql" ><code class= "hljs markdown" ><code class= "hljs sql" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs mathematica" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs lasso" ><code class= "hljs markdown" ><code class= "hljs asciidoc" ><code class= "hljs lasso" ><code class= "hljs smalltalk" ><code class= "hljs asciidoc" ><code class= "hljs r" ><code class= "hljs sql" ><code class= "hljs r" ><code class= "hljs r" ><code class= "hljs lasso" ><code class= "hljs asciidoc" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs ruleslanguage" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs css" ><code class= "hljs vbnet" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs sql" ><code class= "hljs vhdl" >[root@data02 ~]# mysql --socket=/usr/local/mysql3307/mysql.sock -u dataquery -p20150523 -h192.168.52.130 -P3307
Welcome TO the MySQL monitor.  Commands END WITH ; OR \g.
Your MySQL CONNECTION id IS 18
SERVER VERSION: 5.6.12-LOG Source distribution
 
Copyright (c) 2000, 2013, Oracle AND / OR its affiliates. ALL rights reserved.
 
Oracle IS a registered trademark of Oracle Corporation AND / OR its
affiliates. Other NAMES may be trademarks of their respective
owners.
 
TYPE 'help;' OR '\h' FOR help. TYPE '\c' TO clear the current input statement.
 
mysql> SELECT * FROM d3307.t;
ERROR 1142 (42000): SELECT command denied TO USER 'dataquery' @ 'data02' FOR TABLE 't'
mysql>
mysql> SELECT id,uname FROM d3307.t;
+ ----+-------+
| id | uname |
+ ----+-------+
|  1 | a     |
+ ----+-------+
1 ROW IN SET (0.00 sec)
 
mysql></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code></code>

 

8，权限划分一般原则

数据库一般划分为线上库，测试库，开发库。 
 

8.1对于线上库：

DBA：有所有权限，超级管理员权限
应用程序：分配insert、delete、update、select、execute、events、jobs权限。
测试人员：select某些业务表权限
开发人员：select某些业务表权限
原则：所有对线上表的操作，除了应用程序之外，都必须经由DBA来决定是否执行、已经什么时候执行等。 
 

8.2 测试库

DBA：所有权限。
测试人员：有insert、delete、update、select、execute、jobs权限。
数据分析人员：只有select查询权限
开发人员：有select权限。

原则：DBA有所有权限，而且严格控制表结构的变更，不允许除了dba之外的人对测试环境的库环境进行修改，以免影响测试人员测试。所有对测试库的表结构进行的修改必须由测试人员和DBA一起审核过后才能操作。 
 

8.3 开发库

DBA：所有权限
测试人员：有库表结构以及数据的所有操作权限。
开发人员：有库表结构以及数据的所有操作权限。
数据分析人员：有库表结构以及数据的所有操作权限。

tao_wei162
关注
专栏目录
MySQL 用户和权限管理命令汇总
-
09-04 1828
一、创建用户 CREATE USER ‘user_name’@‘host’ IDENTIFIED BY ‘password’； 各项参数解析： 各项参数解析 例子展示 user_name:用户名 create user "newuser"@"localhost" indentifed by "abc123" 创建只能在本机登录的用户名为newuser的用户账号，这个用户的密码为abc123 host：这个用户在哪台机中登陆，只允许从本机登录则填写“localhost”，如允许远程
MySQL 用户权限详细汇总
chengxuyuanyonghu的专栏
07-21 1101
1，MySQL权限体系 mysql 的权限体系大致分为5个层级:  全局层级：  全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。  数据库层级：  数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANT ALL ON db_nam
如何查看MySQL用户权限
weixin_34416649的博客
01-19 173
如果需要查看MySQL用户权限，应该如何实现呢？下面就为您介绍查看MySQL用户权限的方法，并对授予MySQL用户权限的语句进行介绍，供您参考。 查看MySQL用户权限： show grants for 你的用户 比如： show grants for root@'localhost'; Grant 用法 GRANT USAGE ON *.* TO 'discuz...
MySQL用户权限管理详解
luyaran的博客
06-02 909
一、查看MYSQL数据库中所有用户及拥有权限  1.查看MYSQL数据库中所有用户 mysql> SELECT DISTINCT CONCAT('User: ''',user,'''@''',host,''';') AS query FROM mysql.user; +---------------------------------+ | query
mysql用户权限管理
rojanzhang的专栏
12-24 186
用户权限管理http://blog.itpub.net/29320885/viewspace-1716164/ 创建用户 命令：CREATE USER 'username'@'host' IDENTIFIED BY 'password'; 说明： Username所创建的用户名 host 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,...
MySQL权限详解：全局到列的权限控制
"MySQL用户权限详细汇总" MySQL的权限系统是其安全管理的核心部分，它允许管理员精细控制用户对数据库系统的访问。权限系统分为五个主要层级，确保了数据的保护和访问控制。 1. **全局层级**： 全局权限影响到...
MySQL语句整理及汇总介绍
09-09
本文将详细介绍MySQL中的几种主要语句类型，包括DCL、DDL、DML以及查询语句和事务控制语句。 首先，DCL（Data Control Language，数据控制语言）主要用于管理数据库的访问权限。`GRANT`和`REVOKE`是DCL的核心，`...
一文看尽MySQL用户权限管理，真香！
最新发布
2401_84831625的博客
05-09 3451
自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。深知大多数Java工程师，想要提升技能，往往是自己摸索成长，自己不成体系的自学效果低效漫长且无助。因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。
MySQL用户权限
m0_60181979的博客
08-31 387
mysql用户管理，mysql权限分配
mysql 用户权限
zin5970的专栏
12-07 580
一、前言 今天在服务器端更新程序后，mysql提示select command denied to user XX的错误，在网上找了很多方法，随后又衍生出了更多问题： access denied for user 'root'@  错误； 忘记root密码； mysql无法启动，日志文件显示“Ignoring the redo log”。 试了网上很多方法，所以整理一下自己的解决流程，算是...
mysql 用户 权限
zj7758的专栏
12-20 546
1、创建用户：      首先 进入mysql 用户库当中        mysql> insert into mysql.user(Host,User,Password) values("localhost","test",password("1234"));   2、添加权限 grant all on *.* to 'aaa'@'%' identified by '密码' with g
mysql用户权限
mimanse的博客
03-10 1109
mysql一些权限相关操作，数据库可以远程连接或者说用IP地址可以访问
weixin_34126215的博客
08-28 105
转载：https://www.cnblogs.com/lemon-flm/p/7597879.html 1、添加用户 　　1.1 登录MYSQL： 　　@>mysql -u root -p 　　@>密码 　　1.2 创建用户： 格式:grant select on 数据库.* to 用户名@登录主机 identified by '密码' 　　举...
MySQL 用户的权限
qq_40880022的博客
12-20 976
mysql> show grants for 'test_user'@'192.168.10.11'; +------------------------------------------------------------+ | Grants for test_user@192.168.10.11 | +------------------------------------------------------------+ | GRANT ALL.
MySQL权限详解：全局到列的控制
"MySQL用户权限详细汇总" MySQL的权限管理系统是其核心功能之一，它允许管理员精细控制用户对数据库系统的访问。以下是对标题和描述中提到的知识点的详细说明： 1. **全局层级权限**： 全局权限适用于服务器内的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值