阮胜昌的技术记录站-LinuxMySQL数据库运维

1，MySQL权限体系

MySQL 的权限体系大致分为5个层级:
全局层级：
全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。GRANT ALL ON .和REVOKE ALL ON .只授予和撤销全局权限。
数据库层级：
数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db表中。GRANT ALL ON db_name.和REVOKE ALL ONdb_name.只授予和撤销数据库权限。
表层级：
表权限适用于一个给定表中的所有列。这些权限存储在mysql.talbes_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ONdb_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这几张表就可以看到很多细节，接下来仔细分析这些表就可以知道权限的奥秘。

演示过程中需要建立用户来演示，先简单介绍下如何创建用户：
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@’%’ IDENTIFIEDBY ‘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>

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>

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>

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';

分析结果：存储在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>

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

创建用户：

mysql> GRANT SELECT,UPDATE ON *.* TO user7@'%'IDENTIFIED BY 'user0523';

Query OK, 0 rows affected (0.00 sec)

mysql>

分析：
基本的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: 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>

2.3 tables_priv表

2.3.1 查看表结构

mysql> desc mysql.tables_priv;

+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+

| Field       |Type                                                                                                                              |Null | Key | Default           |Extra                       |

+-------------+-----------------------------------------------------------------------------------------------------------------------------------+------+-----+-------------------+-----------------------------+

| Host        |char(60)                                                                                                                         | NO   | PRI |                   |                             |

| Db          |char(64)                                             &n