MySQL的权限管理

E&T: CentOS_7.4 64位; MySQL-5.6;

 

P0. 安装mysql的时候, 宜指定mysql:mysql 用户。

 

MySQL 默认有个root用户，但是这个用户权限太大，一般只在管理数据库时候才用。如果在项目中要连接 MySQL 数据库，则建议新建一个权限较小的用户来连接。

P1. 创建MySQL数据库新用户

① 进入MySQL 命令行模式:

② 创建新用户

 Mysql> create user 'USERNAME'@'HOST' identified by 'PASSWORD';

注意:

创建登录用户，MySQL的登录用户必须是’USERNAME’@’HOST’（用户名加主机名），如’mysql’@’192.168.1.10′，含义是只有在192.168.1.10这台主机上

才可以使用mysql用户登录MySQL数据库（还可以指定只允许登录那个数据库）。

HOST的表现方式：

1. IP地址，如192.168.1.10；

2. 主机名，如localhost；

3. 网络地址，如192.168.1.10

4. 通配符，如

%：匹配任意字符

_：匹配任意单个字符如192.168.1._(允许192.168.1.1-192.168.1.9)

 

P2. 用户赋权

授权就是为某个用户授予权限，合理的授权可以保证数据库的安全，MySQL中可以使用GRANT语句为用户授予权限。授权可以分为多个层次：

　　全局层级：全局权限适用于一个给定服务器中的所有数据库，这些权限存储在mysql.user表中。

　　数据库层级：数据库权限适用于一个给定数据库中的所有目标，这些权限存储在mysql.db表中。

　　表层级：表权限适用于一个给定表中的所有列，这些权限存储在mysql.tables_priv表中。

　　列层级：列权限使用于一个给定表中的单一列，这些权限存储在mysql.columns_priv表中。

　　子程序层级：CREATE ROUTINE、ALTER ROUTINE、EXECUTE和GRANT权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了CREATE ROUTINE外，这些权限可以被授予子程序层级，并存储在mysql.procs_priv表中。

MySQL中必须拥有GRANT权限的用户才可以执行GRANT语句。

① GRANT赋予用户权限:

# 定义对已经存在的用户可以操作此库下的所有表及所有权限；

　Mysql> grant all privileges on DB_NAME.* to 'USERNAME'@'HOST';

# 定义对已经存在的用户赋予insert权限对db库下的xsb表；

　Mysql> grant all privileges on db.xsb to 'USERNAME'@'HOST';

all表示赋予用户全部权限（包含存储过程、存储函数等创建和执行）。

当数据库名称、表名称被*.*代替，表示赋予用户操作服务器上所有数据库所有表的权限。

 

② REVOKE移除用户权限

# 移除tom用户对于db.xsb的权限;

　Mysql> revoke all on db.xsb from 'tom'@'localhost';

使用REVOKE收回权限之后，用户帐户的记录将从db、host、tables_priv、columns_priv表中删除，但是用户帐号记录依然在user表中保存。

 

③ FULSH刷新授权表

　Mysql> flush privileges;

 

④ SHOW查看用户的权限

　 Mysql> show grants for 'USERNAME'@'HOST';

 

P3. 数据库开发人员，创建表、索引、视图、存储过程、函数等权限授权

grant创建、修改、删除MySQL数据表结构权限

　　grant create on testdb.* to developer@'192.168.0.%';

　　grant alter on testdb.* to developer@'192.168.0.%';

　　grant drop on testdb.* to developer@'192.168.0.%';

 

grant操作MySQL外键权限

 　　grant references on testdb.* to developer@'192.168.0.%';

 

grant操作MySQL临时表权限。

 　　grant create temporary tables on testdb.* to developer@'192.168.0.%';

 

grant操作MySQL索引权限

 　　grant index on testdb.* to developer@'192.168.0.%';

 

grant操作MySQL视图、查看视图源代码 权限

　　grant create view on testdb.* to developer@'192.168.0.%';

　　grant show view on testdb.* to developer@'192.168.0.%';

 

grant操作MySQL存储过程、存储函数权限

　　grant create routine on testdb.* to developer@'192.168.0.%';

　　grant alter routine on testdb.* to developer@'192.168.0.%';

　　grant execute on testdb.* to developer@'192.168.0.%';

 更多参见原博文: https://www.cnblogs.com/softidea/p/7451375.html

 

MySQL用户权限表

MySQL访问控制两阶段   (参考: https://www.cnblogs.com/softidea/p/7451375.html)

阶段1：客户端连接核实阶段

阶段2：客户端操作核实阶段

客户端连接核实阶段，当连接MySQL服务器时，服务器基于用户的身份以及用户是否能通过正确的密码身份验证，来接受或拒绝连接。即客户端用户连接请求中会提供用户名称、主机地址和密码，MySQL使用user表中的三个字段（Host、User、Password）执行身份检查，服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的面貌时才接受连接。如果连接核实没有通过，服务器完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

客户端操作核实阶段，当客户端的连接请求被MySQL服务器端通过其身份认证后。那么接下来就可以发送数据库的操作命令给服务器端处理，服务器检查用户要执行的操作，在确认权限时，MySQL首先检查user表，如果指定的权限没有在user表中被授权；MySQL将检查db表，db表时下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。其过程大概如下图：