玩转Mysql 三（权限管理）

一路走来，所有遇到的人，帮助过我的、伤害过我的都是朋友，没有一个是敌人。

一、用户管理

1、登录MySQL服务器

语法示例：

mysql –h hostname|hostIP –P port –u username –p DatabaseName –e "SQL语句"
详细命令中参数说明：

-h参数 后面接主机名或者主机IP，hostname为主机，hostIP为主机IP。
-P参数 后面接MySQL服务的端口，通过该参数连接到指定的端口。MySQL服务的默认端口是? ? ? ? ? ?3306，不使用该参数时自动连接到3306端口，port为连接的端口号。
-u参数 后面接用户名，username为用户名。
-p参数 会提示输入密码。DatabaseName参数 指明登录到哪一个数据库中。如果没有该参数，就? ? ? 会直接登录到MySQL数据库中，然后可以使用USE命令来选择数据库。
-e参数 后面可以直接加SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出? ? ? ? ? ? ?MySQL服务器。

输入密码登录并查询：

[root@mysql5 mysql]# mysql -uroot -p -h192.168.10.210 -P3306 mysql -e "select host,user from user"

[root@mysql5 mysql]# mysql -uroot -pMysJzh_1A -h192.168.10.210 -P3306 mysql -e "select host,user from user"

2、MySQL创建/删除用户及用户密码管理

创建用户

CREATE USER语句的基本语法形式如下：
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];
用户名参数表示新建用户的账户，由 用户（User） 和 主机名（Host） 构成；
“[ ]”表示可选，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户
可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用
IDENTIFIED BY指定明文密码值。
CREATE USER语句可以同时创建多个用户。
示例1：创建单个用户，指定密码。

mysql> CREATE USER test1 IDENTIFIED BY '123456';

示例2：创建多个用户指定密码

mysql> CREATE USER test2,test3 IDENTIFIED BY '123456';

修改用户名：将test1用户名修改为wangming

查看user表

mysql> ?UPDATE user SET USER='xiaoyu' WHERE USER='test1';

删除用户:删除用户有两种方式使用drop语句或者delete语句

DROP 语法：DROP USER user[,user]…;

使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：
示例：

?mysql> drop user test2;

Delete语法:DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;

执行完DELETE命令后要使用FLUSH命令来使用户生效

示例：

mysql> delete from user where host='%' and user='test3';

mysql> flush privileges;

注意：推荐通过drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都会清空。

二、用户密码管理

1、修改用户密码的语句

SET PASSWORD = PASSWORD('123456'); （5.7版本）
ALTER USER USER() IDENTIFIED BY 'new_password';
SET PASSWORD='new_password';

示例：

修改自己密码推荐用：SET PASSWORD='new_password';

2、使用root修改普通用户密码

ALTER USER user [IDENTIFIED BY '新密码']
SET PASSWORD FOR 'username'@'hostname'='new_password';
示例：修改普通用户xiaoyu的密码

mysql> ALTER USER xiaoyu IDENTIFIED BY '54321';

三、权限管理

1、MySQL权限分层

全局层级 :全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user记录用户账号和权限信息。表中GRANT ALL ON *.*和REVOKE ALL ON *.*只授予和撤销全局权限。

mysql.user字段可以分成4类，分别是范围列（或用户列）、权限列、安全列和资源控制列。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

1.1、范围列（或用户列）
host ： 表示连接类型
% 表示所有远程通过 TCP方式的连接
IP 地址 如 (192.168.1.2、127.0.0.1) 通过制定ip地址进行的TCP方式的连接机器名 通过制定网络? ? ? ? ?中的机器名进行的TCP方式的连接
::1 IPv6的本地ip地址，等同于IPv4的 127.0.0.1
localhost 本地方式通过命令行方式的连接 ，比如mysql -u xxx -p xxx 方式的连接。
user ： 表示用户名，同一用户通过不同方式链接的权限是不一样的。
password ： 密码
所有密码串通过 password(明文字符串) 生成的密文字符串。MySQL 8.0 在用户管理方面增加了
角色管理，默认的密码加密方式也做了调整，由之前的 SHA1 改为了 SHA2 ，不可逆 。同时
加上 MySQL 5.7 的禁用用户和用户过期的功能，MySQL 在用户管理方面的功能和安全性都较之
前版本大大的增强了。mysql 5.7 及之后版本的密码保存到 authentication_string 字段中不再使用password 字段。

1.2. 权限列
Grant_priv字段
表示是否拥有GRANT权限
Shutdown_priv字段
表示是否拥有停止MySQL服务的权限
Super_priv字段
表示是否拥有超级权限
Execute_priv字段
表示是否拥有EXECUTE权限。拥有EXECUTE权限，可以执行存储过程和函数。
Select_priv , Insert_priv等
为该用户所拥有的权限。

1.3. 安全列 安全列只有6个字段，其中两个是ssl相关的（ssl_type、ssl_cipher），用于 加密 ；两个是x509
相关的（x509_issuer、x509_subject），用于 标识用户 ；另外两个Plugin字段用于 验证用户身份 的插件，该字段不能为空。如果该字段为空，服务器就使用内建授权验证机制验证用户身份。

1.4. 资源控制列 资源控制列的字段用来 限制用户使用的资源 ，包含4个字段，分别为：
①max_questions，用户每小时允许执行的查询操作次数； ②max_updates，用户每小时允许执行的更新操作次数； ③max_connections，用户每小时允许执行的连接操作次数； ④max_user_connections，用户允许同时建立的连接次数。

查看用户, 以列的方式显示数据：
SELECT * FROM mysql.user \G ;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

数据库层级:数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。GRANT ALL ON db_name.*和REVOKE ALL ON db_name.*只授予和撤销数据库权限。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++?用户列 db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库
名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。
权限列
Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

表层级:表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。GRANT ALL ON db_name.tbl_name和REVOKE ALL ON db_name.tbl_name只授予和撤销表权限。

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

tables_priv表用来 对表设置操作权限 ，columns_priv表用来对表的 某一列设置权限 。

tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和
Column_priv，各个字段说明如下：
Host 、 Db 、 User 和 Table_name 四个字段分别表示主机名、数据库名、用户名和表名。
Grantor表示修改该记录的用户。
Timestamp表示修改该记录的时间。
Table_priv 表示对象的操作权限。包括Select、Insert、Update、Delete、Create、Drop、Grant、
References、Index和Alter。
Column_priv字段表示对表中的列的操作权限，包括Select、Insert、Update和References。
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

列层级:列权限适用于一个给定表中的单一列。这些权限存储在mysql.columns_priv表中。当使用REVOKE时，您必须指定与被授权列相同的列。

参考表层级

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

procs_priv表可以对 存储过程和存储函数设置操作权限

2、授予权限的原则
?权限控制主要是出于安全因素，因此需要遵循以下几个 经验原则 ：
?只授予能 满足需要的最小权限 .比如用户只是需要查询，那就只给select权限.
?创建用户的时候 限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
?为每个用户 设置满足密码复杂度的密码 。
?定期清理不需要的用户 ，回收权限或者删除用户。

3、授权语句：

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

示例：将增删改查权限授予小雨用户，并查看全局权限
mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON mysql.* TO xiaoyu@'%';

mysql> SHOW GRANTS FOR 'xiaoyu'@'%';

使用xiaoyu用户登录，查看当前拥有的权限

SHOW GRANTS;

SHOW GRANTS FOR CURRENT_USER;

SHOW GRANTS FOR CURRENT_USER();

查看xiaoyu用户全局权限

SHOW GRANTS FOR 'xiaoyu'@'%' ;

4、权限回收

MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。

权限收回语句：

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
示例：收回xiaoyu用户delete权限，并查看xiaoyu用户当前的权限

mysql> REVOKE DELETE ON mysql.* from xiaoyu@'%';

mysql> SHOW GRANTS FOR 'xiaoyu'@'%';

示例：收回xiaoyu用户在MySQL库中全部的权限

REVOKE ALL PRIVILEGES ON *.* FROM xiaoyu@'%';

提示：xiaoyu用户重新登录后授权或收回权限才能生效。

四、角色管理：角色就是一组权限的集合，方便权限管理的授予与回收；

在MySQL8版本新增特性，MySQL5.7版本会创建失败。

++++++以下操作是在MySQL8版本进行++++++++++++++++

1、创建角色?

创建角色使用 CREATE ROLE 语句，语法如下：

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

示例：创建role_vip2的角色

mysql> select version();

mysql> CREATE ROLE 'vip2'@'%';

给角色授权。给角色授权的语法结构是：
GRANT privileges ON table_name TO 'role_name'[@'host_name'];

mysql> GRANT SELECT,INSERT,DELETE,UPDATE ON mysql.* TO vip2@'%';

mysql> SHOW GRANTS FOR 'vip2';

2、回收/删除角色

撤销角色权限的SQL语法如下：
REVOKE privileges ON tablename FROM 'rolename';

mysql> REVOKE ?SELECT,INSERT,DELETE,UPDATE ON ?mysql.* FROM vip2@'%';

mysql> SHOW GRANTS FOR 'vip2'@'%';

3、将角色赋与用户SQL语法如下：(MySQL8中给用户赋予角色需要激活操作)

GRANT role [,role2,...] TO user [,user2,...];

mysql> SHOW GRANTS FOR 'yonghu1'@'%';

mysql> GRANT ?vip2 to yonghu1;

mysql> SHOW GRANTS FOR 'yonghu1'@'%';

4、删除角色的SQL语法如下：
DROP ROLE role [,role2]

mysql> show grants for 'vip2'@'%';

mysql> DROP ROLE vip2;

5、使角色生效（激活）SQL语法如下：

SET DEFAULT ROLE ALL TO 'user'@'localhost';

[root@mysql8 ~]# mysql -uyonghu1 -p123456 -h192.168.10.211 -P3306

mysql> SET DEFAULT ROLE ALL TO 'yonghu1'@'%';

6、撤销用户的角色SQL语法如下：
REVOKE role FROM user;

mysql> REVOKE vip2 FROM yonghu1;

mysql> SHOW GRANTS FOR 'yonghu1'@'%';

7、设置强制角色(mandatory role)

服务启动前设置，修改配置文件(不推荐)
[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.xxx'
运行时设置

#永久#SET PERSIST defalt_roles = 'role1,role2@localhost,r3@%.xxx;?

#临时#SET GLOBAL defalt_roles= 'role1,role2@localhost,r3@%.xxx'; 、