高级篇-第03章 用户与权限管理
1.用户管理
MySQL用户可以分为普通用户和root用户。root用户是超级管理员，拥有所有权限，包括创建用户、删除用户和修改用户的密码等管理权限;普通用户只拥有被授予的各种权限。
MySQL提供了许多语句用来管理用户账号，这些语句可以用来管理包括登录和退出MysQL服务器、创建用户、删除用户、密码管理和权限管理等内容。
MySQL数据库的安全性需要通过账户管理来保证。
1.1 登录MySQL服务器
启动MySQL服务后，可以通过mysql命令来登录MySQL服务器，命令如下:
mysql -h hostname|hostIP -P port -u username -p DatabaseName -e "sql语句”
1.2 创建用户
在MySQL数据库中，官方推荐使用CREATE USER语句创建新用户。MySQL8版本移除了PASSWORD加密方法，因此不再推荐使用INSERT语句直接操作MySQL中的user表来增加用户。
使用CREATE USER语句来创建新用户时，必须拥有CREATE USER权限。每添加一个用户，CREATE USER语句会在mysql.user表中添加一条新记录，但是新创建的账户没有任何权限。如果添加的账户已经存在，CREATE USER语句就会返回一个错误。
举例：
CREATE user 'zhangsan1'@'localhost' IDENTIFIED by '123456';
CREATE user 'zhangsan1' IDENTIFIED by '123456';# 默认是@'%'
user表是使用host与user字段作为联合主键
1.3 修改用户
1.4 删除用户
在MySQL数据库中，可以使用DROP USER语句来删除普通用户，也可以直接在mysql.user表中删除用户。
方式1：使用DROP方式删除（推荐）
使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：
DROP USER user[,user]...;
其中，user参数是需要删除的用户，由用户的用户名(User）和主机名(Host）组成。DROP USER语句可以同时删除多个用户，各用户之间用逗号隔开。
举例：
DROP USER 'li4';# 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';
方式2：使用DELETE方式删除
可以使用DELETE语句直接将用户的信息从mysql.user表中删除，但必须拥有对mysql.user表的DELETE权限，DELETE语句的基本语法形式如下：
DELETE FROM mysql.user WHERE host='hostname' AND user='username';
host字段和user字段是user表的联合主键，因此两个字段的值才能唯一确定一条记录。
执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：
FLUSH PRIVILEGES;
注意：不推荐通过DELETE进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。
1.5 设置当前用户密码
适用于root用户修改自己的密码，已经普通用户登录后修改自己的密码。
root用户拥有很高的权限，因此必须保证root用户的密码安全。root用户可以通过多种方式来修改密码，使用ALTER USER修改用户密码是MySQL官方推荐的方式。此外，也可以通过SET语句修改密码。由于MysQL 8中已移除了PASSWORD()函数，因此不再使用UPDATE语句直接操作用户表修改密码。
旧的写法如下：
# 修改当前用户的密码：（mysql5.7测试有效）
SET PASSWORD=PASSWORD('123456');
8.0：用户密码实际存储mysq会加密
1.6 修改其它用户密码
root用户不仅可以修改自己的密码，还可以修改其它普通用户的密码。root用户登录MySQL服务器后，可以通过ALTER语句和SET语句来修改普通用户的密码。由于PASSWORD()函数已移除，因此使用UPDATE直接操作用户表的方式已不再使用。
1.7 MySQL8密码管理（了解）
MySQL中记录使用过的历史密码，目前包含如下密码管理功能：
（1）密码过期：要求定期修改密码
（2）密码重用限制：不允许使用旧密码
（3）密码强度评估：要求使用高强度的密码。（第1章已讲）
提示
MySQL密码管理功能只针对使用基于MySQL授权插件的账号，这些插件有mysql_native_password、sha256_password和caching_sha2_password。
1.密码过期策略
- 在MySQL中，数据库管理员可以手动设置账号密码过期，也可以建立一个自动密码过期策略。
- 过期策略可以是全局的，也可以为每个账号设置单独的过期策略。
手动设置立马过期
手动设置账号密码过期，可使用如下语句：
ALTER USER user PASSWORD EXPIRE;
手动设置指定时间过期方式1：全局
如果密码使用的时间大于允许的时间，服务器会自动设置为过期，不需要手动设置。
MySQL使用default_password_life_time系统变量建立全局密码过期策略。
- 它的默认值是0，表示禁用自动密码过期。
- 它允许的值是正整数N，表示允许的密码生存期。密码必须每隔N天进行修改。
2.密码重用策略
MySQL限制使用已用过的密码。重用限制基于密码更改的数量和使用的时间。重用策略可以是全局的，也可以为每个账号设置单独的策略。
- 账号的历史密码包括过去该账号所使用的密码。MySQL基于以下规则来限制密码重用：
- 如果账号的密码限制基于密码更改的数量，那么新密码不能从最近限制的密码数量中选择。例如，如果密码更改的最小值为3，那么新密码不能与最近3个密码中任何一个相同。
- 如果账号密码限制基于时间，那么新密码不能从规定时间内选择。例如，如果密码重用周期为60天，那么新密码不能从最近60天内使用的密码中选择。 - MySQL使用password_history和password_reuse_interval系统变量设置密码重用策略。
- password_history：规定密码重用的数量
- password_reuse_interval：规定密码重用的周期 - 这两个值可在服务器的配置文件中维护，也可在运行期间使用SQL语句更改改变量的值并持久化。
2.权限管理
关于MysQL的权限简单的理解就是MySQL允许你做你权力以内的事情，不可以越界。比如只允许你执行SELECT操作，那么你就不能执行UPDATE操作。只允许你从某台机器上连接MySQL，那么你就不能从除那台机器以外的其他机器连接MySQL。
2.1 权限列表
MySQL到底有哪些权限呢？
SHOW PRIVILEGES;
GRANT和REVOKE语句中可以使用的权限如下:
2.2 授予权限的原则
权限控制主要是出于安全因素，因此需要遵循以下几个经验原则：
1、只授予能满足需要的最小权限，防止用户干坏事。比如用户只需要查询，那就只给select权限就可以了，不要给用户赋予update、insert或者delete权限。
2、创建用户的时候限制用户的登录主机，一般是限制成指定IP或者内网IP段。
3、为每个用户设置满足密码复杂度的密码。
4、定期清理不需要的用户，回收权限或者删除用户。
2.3 授予权限
给用户授权的方式有2种，分别是通过角色赋予用户给用户授权和直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。
授权命令：
GRANT 权限1,权限2,...权限n ON 数据库名称,表名称 TO '用户名'@'用户地址' [IDENTIFIED BY '密码口令'];
- 该权限如果发现没有改用户，则会直接新建一个用户。
比如：
- 给li4用户用本地命令行方式，授予atguigudb这个库下所有表的插删改查的权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO 'li4'@'localhost';
-
授予通过网络方式登录的joe用户，对所有库所有表的全部权限，密码设为123。注意，这里唯独不包括grant的权限
GRANT ALL PRIVILEGES ON *.* TO 'joe'@'%' IDENTIFIED BY '123';- ALL PRIVILEGES是表示所有权限，你也可以使用SELECT、UPDATE等权限。
- ON用来指定权限针对哪些库和表。
- .中前面的号用来指定数据库名，后面的号用来指定表名。这里的*表示所有的。
- TO表示将权限赋予某个用户。
- li4@'localhost'表示li4用户，@后面接限制的主机，可以是IP、IP段、域名以及%，%表示任何地方。注意:这里%有的版本不包括本地，以前碰到过给某个用户设置了%允许任何地方登录，但是在本地登录不了，这个和版本有关系，遇到这个问题再加一个localhost的用户就可以了。
-
如果需要赋予包括GRANT的权限，添加参数"WITH GRANT OPTION"这个选项即可，表示该用户可以将自己拥有的权限授权给别人。经常有人在创建操作用户的时候不指定WITH.GRANT OPTION选项导致后来该用户不能使用GRANT命令创建用户或者给其它用户授权。
-
可以使用GRANT重复给用户添加权限，权限叠加，比如你先给用户添加一个SELECT权限，然后又给用户添加一个INSERT权限，那么该用户就同时拥有了SELECT和INSERT权限。
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,...权限n ON 数据库名称.表名称 FROM '用户名'@'用户地址';
- 举例
#收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM 'joe'@'%';
#收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM 'joe'@'%';
- 注意：须用户重新登录后才能生效
3.权限表
MySQL服务器通过权限表来控制用户对数据库的访问，权限表存放在mysql数据库中。MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表。除此之外，还有table_priv表、column_priv表和proc_priv表等。在MySQL启动时，服务器将这些数据库表中权限信息的内容读入内存。
3.1 user表
user表是MySsQL中最重要的一个权限表，记录用户账号和权限信息。如下图:
这些字段可以分成4类，分别是范围列（或用户列)．权限列、安全列和资源控制列。
2.权限列
权限列的字段决定了用户的权限，描述了在全局范围内允许对数据和数据库进行的操作，包括查询权限，修改权限等，还包括关闭服务器、超级权限和加载用户等权限。普通权限用于操作数据库；高级权限用于数据库管理。
user表中的对应的权限时针对所有用户数据库的。这些字段值的类型为ENUM，可以取值只能为Y和N，Y表示该用户有对应的权限；N是表示用户没有对应的权限。从user表的结构可以看到，这些字段的值为Y和N。如果要修改权限，就可以使用GRANT语句或UPDATE语句更改user表的这些字段来修改用户对应的权限。
一个小时内用户查询或者连接数量超过资源控制限制，用户将被锁定，直到下一个小时才可以再次执行对应的操作。可以使用GRANT语句更新这些字段的值。
3.2 db表
db表是MySQL数据中非常重要的权限表。决定用户能从哪个主机存储哪个数据库。db表比较常用。
user表中的权限时针对所有数据库的，如果user表中的select_pri字段取值与Y，那么该用户可以查询所有数据库中的表。如果希望用户只对某个数据库有操作权限，那么需要将user表中对应的权限设置为N，然后在db表中设置对应数据库的操作权限。由此可知，用户先根据user表内容获取权限，然后根据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 角色的理解
角色是在MySQL8.0中引入的新功能。在MySQL中，角色是权限的集合，可以为角色添加或移除权限。用户可以被赋予角色，同时也被授予角色包含的权限。对角色进行操作需要较高的权限。并且像用户账户一样，角色可以拥有授予和撤销的权限。
引入角色的目的是方便管理拥有相同权限的用户。恰当的权限设定，可以确保数据的安全性，这是至关重要的。
5.2 创建角色
在实际应用中，为了安全性，需要给用户授予权限。当用户数量较多时，为了避免单独给每一个用户授予多个权限，可以将权限集合放入角色中，再赋予用户相应的角色。
创建角色使用CREATE ROLE语句，语法如下：
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
角色名称和命名规则和用户名类似。如果host_name省略，默认为%，role_name不可省略，不可为空。
练习：我们现在需要创建一个经理的角色，就可以用下面的代码：
CREATE ROLE 'manager'@'localhost';
这里创建了一个角色，角色名称是“manager"”，角色可以登录的主机是"localhost”，意思是只能从数据库服务器运行的这台计算机登录这个账号。你也可以不写主机名，直接创建角色"manager":
CREATE ROLE 'manager';
如果不写主机名，MySQL默认是通配符“%”，意思是这个账号可以从任何一台主机上登录数据库。
同样道理，如果我们要创建库管的角色，就可以用下面代码：
CREATE ROLE 'stocker';
还可以通过如下的指令，一次性创建3个角色：
CREATE ROLE 'app_developer','app_read','app_write';
5.3 给角色赋予权限
创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。给角色授权的语法结构是：
GRANT privileges ON table_name TO 'role_name'[@'host_name'];
上述语句中privileges代表权限的名称，多个权限以逗号隔开。
查看所有权限，下图只是部分，展示不全。
show privileges\G;
举例：
grant select,update on dbtest1.* to 'manager';
grant all privileges on *.* to 'boss';
5.4 查看角色的权限
5.5 回收角色的权限
5.6 删除角色
5.7 给用户赋予角色
5.8 激活角色
5.9 撤销用户的角色
5.10 设置强制角色（mandatory role）
5.11 小结
6.配置文件的使用
6.1 配置文件的格式
在配置文件中指定启动选项的语法类似于命令行语法，但是配置文件中指定的启动选项不允许加--前缀，并且每行只指定一个选项，而且=周围可以有空白字符（命令行中选项名、=、选项值之间不允许有空白字符）。另外，在配置文件中，我们可以使用#来添加注释，从#出现直到行尾的内容都属于注释内容，读取配置文件时会忽略这些注释内容。
6.2 启动命令与选项组
6.3 特定MySQL版本的专用选项组
我们可以在选项组的名称后加上特定的MySQL版本号，比如对于[mysqld]选项组来说，我们可以定义一个[mysqld-5.7]的选项组，它的含义和[mysqld]一样，只不过只有版本号5.7的mysqld程序才能使用这个选项组中的选项。
6.4 同一个配置文件中多个组的优先级
6.5 命令行和配置文件中启动选项的区别
7.系统变量（复习）
7.1 通过启动选项设置
通过命令行添加启动选项是临时性的，服务器重启就没有了
浙公网安备 33010602011771号