【小菜鸟之---MySQL基础-用户管理】

1【用户管理】

​ MySQL用户可以分为普通用户和rdot用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限;普通用户只拥有被授予的各种权限。

​ MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理和权限管理等内容。

​ MySQL数据库的安全性需要通过账户管理来保证

1.2创建用户

CREATE USER 用户名 IDENTIFIED BY '密码' 

CREATE USER 'zjc'@'localhost' IDENTIFIED BY '9529';

1.3修改用户名

UPDATE mysql.user SET USER='ZJC' WHERE USER = 'zjc';

执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：
FLUSH PRIVILEGES;

1.4删除用户

【推荐】使用DROP删除;
其中，user参数是需要删除的用户，由用户的用户名(User)和主机名(Host)组成。DROP USER语句可以同时删除多个用户，各用户之间用逗号隔开。
DROP USER user1,user2

DROP USER zjc ; # 默认删除host为%的用户

DROP USER 'zjc'@'localhost';

使用DELETE 语句删除mysql.user表中的数据，即
DELETE FROM mysql.user WHERE Host = 'hostname' AND User ='username'
执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：
FLUSH PRIVILEGES;
注意：【不推荐】通过 DELETE FROM USER u WHERE USER='li4'进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

1.5设置当前用户密码

SET PASSWORD = PASSWORD('123456789');

1.6修改其他用户密码

使用ALTER USER语句来修改普通用户的密码
ALTER USER 'user' IDENTIFIED BY 'newpassword';

ALTER USER 'user'@'192.168.58.129' BY '9529';

使用SET命令来修改普通用户的密码
SET PASSWORD FOR 'user'@"%" = 'new_password';

1.7密码过期策略

手动设置账号密码过期
ALTER USER user PASSWORD EXPIRE (expire)
用户仍然可以登录进入数据库，但无法进行查询。密码过期后，只有重新设置了新密码，才能正常使用。

2【权限管理】

​ 关于MySSQL的权限简单的理解就是MysQL允许你做你权力以内的事情，不可以越界。比如只允许你执行SELECT操作，那么你就不能执行UPDATE操作。只允许你从某台机器上连接MySQL，那么你就不能从除那台机器以外的其他机器连接MySQL。

2.1权限列表

mysql> show privileges;

权限	权限级别	权限说明
CREATE	数据库、表或索引	创建数据库、表或索引权限
DROP	数据库或表	删除数据库或表权限
GRANT OPTION	数据库、表或保存的程序	赋予权限选项
REFERENCES	数据库或表
ALTER	表	更改表，比如添加字段、索引等
DELETE	表	删除数据权限
INDEX	表	索引权限
INSERT	表	插入权限
SELECT	表	查询权限
UPDATE	表	更新权限
CREATE VIEW	视图	创建视图权限
SHOW VIEW	视图	查看视图权限
ALTER ROUTINE	存储过程	更改存储过程权限
CREATE ROUTINE	存储过程	创建存储过程权限
EXECUTE	存储过程	执行存储过程权限
FILE	服务器主机上的文件访问	文件访问权限
CREATE TEMPORARY TABLES	服务器管理	创建临时表权限
LOCK TABLES	服务器管理	锁表权限
CREATE USER	服务器管理	创建用户权限
RELOAD	服务器管理	执行flush-hosts, flush-logs, flush-privileges, flush-status, flush-tables, flush-threads, refresh, reload等命令的权限
PROCESS	服务器管理	查看进程权限
REPLICATION CLIENT	服务器管理	复制权限
REPLICATION SLAVE	服务器管理	复制权限
SHOW DATABASES	服务器管理	查看数据库权限
SHUTDOWN	服务器管理	关闭数据库权限
SUPER	服务器管理	执行kill线程权限

权限分布	可能的设置的权限
表权限	‘Select’, ‘Insert’, ‘Update’, ‘Delete’, ‘Create’, ‘Drop’, ‘Grant’, ‘References’, ‘Index’, ‘Alter’
列权限	‘Select’, ‘Insert’, ‘Update’, ‘References’
过程权限	‘Execute’, ‘Alter Routine’, ‘Grant’

2.2权限授予规则

权限控制主要是出于安全因素，因此需要遵循以下几个经验原则 ：

只授予能 满足需要的最小权限 ，防止用户干坏事。比如用户只是需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
创建用户的时候限制用户的登录主机 ，一般是限制成指定IP或者内网IP段。
为每个用户设置满足密码复杂度的密码 。
定期清理不需要的用户，回收权限或者删除用户。

2.3授予权限

​ 给用户授权的方式有 2 种，分别是通过把 角色赋予用户给用户授权 和 直接给用户授权 。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。

GRANT 权限1，权限2... ON  数据库名称 表名称 TO  用户名%地址 [ IDENTIFIED BY  '密码口令'];

GRANT INSERT DELETE UPDATE SELECT  ON test.* TO 'zjc'@'%'
给zjc用户用本地命令行方式，授予test这个库下的所有表的插删改查的权限。

授予通过网络方式登录的joe用户 ，对所有库所有表的全部权限，密码设为123。注意这里唯独不包括grant的权限
GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%' IDENTIFIED BY '123';

ALL PRIVILEGES是表示所有权限，你也可以使用SELECT、UPDATE等权限。
ON用来指定权限针对哪些库和表。
.中前面的*号用来指定数据库名，后面的*号用来指定表名。这里的*表示所有的。
TO表示将权限赋予某个用户。
li4@'localhost’表示li4用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意:这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。
IDENTIFIED BY指定用户的登录密码。

	
> 如果需要赋予包括GRANT的权限，添加参数"WITH GRANT OPTION”这个选项即可，表示该用户可以将自己拥有的权限授权给别人。经常有人在创建操作用户的时候不指定WITH GRANT
> OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。

2.4查看权限

SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();

SHOW GRANTS FOR 'user'@'主机地址' ;

2.5收回权限

​ 收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。

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

注意：在将用户账户从user表删除之前，应该收回相应用户的所有权限。

#收回权限命令：
REVOKE 权限1，权限2.... ON  数据库. 数据库表 FROM 用户名@地址

#收回全库全表权限
REVOKE ALL PRIVILEGES ON *.* FROM 'zjc'@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'zic'@'%';

注意： 须用户重新登录后才能生效

3【权限表】

3.1user表

user表是MySQL中最重要的一个权限表， 记录用户账号和权限信息 ，有49个字段。这些字段可以分成4类，分别是范围列（或用户列）、权限列、安全列和资源控制列。

3.2db表

db表是MySQL数据中非常重要的权限表。db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。db表比较常用。

user表中的权限是针对所有数据库的，如果user表中的Select_priv字段取值为Y，那么该用户可以查询所有数据库中的表。如果希望用户只对某个数据库有操作权限，那么需要将user表中对应的权限设置为N，然后在db表中设置对应数据库的操作权限。由此可知，用户先根据user表的内容获取权限，然后根据db表的内容获取权限。

使用DESCRIBE查看db表的基本结构：
DESCRIBE mysql.db;

3.3 tables_priv表和columns_priv表

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

3.4 procs_priv表

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

4【访问控制（了解）】

​ 正常情况下，并不希望每个用户都可以执行所有的数据库操作。当MySQL允许一个用户执行各种操作时，它将首先核实该用户向MySQL服务器发送的连接请求，然后确认用户的操作请求是否被允许。这个过程称为MySQL中的访问控制过程。MySQL的访问控制分为两个阶段:连接核实阶段和请求核实阶段。

4.1 连接核实阶段

当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信息。

服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入阶段2等待用户请求。

4.2 请求核实阶段

一旦建立了连接，服务器就进入了访问控制的阶段2，也就是请求核实阶段。对此连接上进来的每个请求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表。

确认权限时，MySQL首先 检查user表 ，如果指定的权限没有在user表中被授予，那么MySQL就会继续检查db表 ，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续 检查tables_priv表以及 columns_priv表 ，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将 返回错误信息 ，用户请求的操作不能执行，操作失败。

5【角色管理】

5.1角色的理解

​ 角色是在MySQL 8.0 中引入的新功能。在MysQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以拥有授予和撤销的权限。

​ 引入角色的目的是 方便管理拥有相同权限的用户 。恰当的权限设定，可以确保数据的安全性，这是至关重要的。

5.2创建角色

​ 在实际应用中，为了安全性，需要给用户授予权限。当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以先将权限集合放入角色中，再赋予用户相应的角色。

CREATE ROLE 'role_name'@'%';

CREATE ROLE 'manager'@'%';
#如果不写主机名，MySQL默认是通配符“%”，意思是这个账号可以从任何一台主机上登录数据库

CREATE ROLE 'student','teacher','master';

5.3给角色赋权

GRANT PRIVILEGES ON  table_name TO 'student'

#privileges代表权限的名称，多个权限以逗号隔开
SHOW PRIVILEGES\G;

#练习一：给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码来实现：

GRANT SELECT ON demo.settlement TO 'manager';

GRANT SELECT ON demo.goodsmaster TO 'manager';

GRANT SELECT ON demo.invcount TO 'manager';

GRANT ALL PRIVILEGES ON app_db.* TO 'app_developer'; -- 给app_db数据库中所有表的所有权限
GRANT SELECT ON app_db.* TO 'app_read'; -- app_db数据库中所有表的查询权限
GRANT INSERT，UPDATE，DELETE ON app_db.* TO 'app_write'; --app_db数据库中所有表的修改权限

#练习3:创建三个角色，分别拥有全部权限、查询权限和读写权限，步骤如下所示。
CREATE ROLE 'student','teacher','master';

GRANT ALL PRIVILEGES ON table TO 'master';
GRANT SELECT ON table TO 'student';
GRANT INSERT ,SELECT ,UPDATE ON table TO 'teacher';

5.4查看角色权限

mysql> SHOW GRANTS FOR 'teacher';

5.5 回收角色的权限

​ 角色授权后，可以对角色的权限进行维护，对权限进行添加或撤销。添加权限使用GRANT语句，与角色授权相同。撤销角色或角色权限使用REVOKE语句。

REVOKE privileges ON tablename FROM 'rolename';

#收回教师的UPDATE权限
REVOKE UPDATE ON table from 'teacher'

5.6删除角色

DROP ROLE  student，teacher；
#如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限 。

5.7给用户赋予角色

#角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。
GRANT role [,role2,...] TO user [,user2,...];

#练习
GRANT student,master TO zjc,root;

#使用zjc用户登录，然后查询当前角色
SELECT CURRENT_ROLE();

上面结果是NONE，说明用户未具备相应的角色。

	或者你用赋予了角色的用户去登录、操作，你会发现，这个账号没有任何权限。
	
	这是因为，MySQL中创建了角色之后，默认都是没有被激活，也就是不能用，必须要手动激活，激活以后用户才能拥有角色对应的权限。

5.8激活角色

方式一：使用set default role 命令激活用户已拥有的角色

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

方式二：将activate_all_roles_on_login设置为ON

mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (0.00 sec)

SET GLOBAL activate_all_roles_on_login=ON;
#这条 SQL 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

#查看当前会话已激活的角色:
SELECT CURRENT_ROLE();

5.9撤销用户角色

SET GLOBAL activate_all_roles_on_login=ON;

REVOKE 'school_read' FROM 'kangshifu'@'localhost';

#撤销后，执行如下查询语句，查看kangshifu用户的角色信息
SHOW GRANTS FOR 'kangshifu'@'localhost';

5.10设置强制角色

强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被 REVOKE或者DROP 。
方式1：服务启动前设置

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'


方式2：运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效

5.10设置强制角色

强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被 REVOKE或者DROP 。
方式1：服务启动前设置

[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com'


方式2：运行时设置
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后仍然有效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; #系统重启后失效

仅作个人学习笔记使用。