mysql权限层级体系_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这几张表就可以看到很多细节，接下来仔细分析这些表就可以知道权限的奥秘。

演示过程中需要建立用户来演示，先简单介绍下如何创建用户：

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)

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

2.1.4 授予ALL权限

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

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

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) | 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>

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 创建单独操作这个表的用户

创建用户：

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>

分析结果：

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

查看权限，如下所示：

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>

2.3.4 单独为某个列授权

授权语句操作：

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

2.4 columns_priv表

2.4.1 表结构如下：

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>

2.4.2 分析

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

2.4.3 实际操作

2.5 procs_priv表

2.5.1 表结构

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)

2.6.2分析：

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

3，创建用户

3.1、CREATE USER创建用户

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语句来新建普通用户

4，删除用户

4.1 drop user删除用户

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用户的密码

5.2 修改user表

5.3 使用SET语句来修改密码

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

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 使用普通账号来找回密码

6，收回用户权限

7，数据库用户划分

7.1 普通数据管理用户：

7.2 开发人员账户：

7.3 DBA人员账户

7.4 数据分析人员只读账号

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：所有权限

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

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

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