MySQL用户与权限管理

用户与权限管理

1.用户管理

MySQL 用户可以分为 "普通用户" 和 "root用户"。

root用户是超级管理员：拥有所有权限，创建，删除，修改用户的密码等管理权限；
普通用户：只能拥有被授予的各种权限。
MySQL 提供了许多语句用来管理用户账号：用户的增删改查，密码管理以及权限管理等...
MySQL 数据库的安全性需要通过账户管理来保证的

1.1 登录MySQL服务器

使用 mysql 命令登录（连接） MySQL 服务。命令如下：

mysql -h[(hostname)|(hostIP)] -P(port) -u(username) -p(passwrod) DatabaseName -e "SQL语句"
# 例如
mysql -h 127.0.0.1 -P 3306 -u root -p root;

参数介绍：

-h 参数：表示主机名或者ip地址，hostname为主机，hostIP为主机IP
-P 参数：连接MySQL服务的端口号。（不写该参数默认使用：3306 端口）
-u 参数：数据库用户名，username为用户名
-p 参数：数据库用户密码，会提示输入密码。
DatabaseName 参数：用于指明登录到哪一个数据库中。在不指明的情况下，默认登录到 mysql 数据库中。（usr 可以切换数据库）
-e 参数：SQL语句。登录MySQL服务器以后即可执行这个SQL语句，然后退出MySQL服务。

举例：

mysql -p 127.0.0.1 -P 3306 -uroot -p 数据库 -e "sql语句"

1.2 创建用户

在 MySQL 数据库中，官方推荐使用 "CREATE USER" 语句创建新用户。MySQL 8.0 移除了 PASSWORD 加密方法，因此不再推荐使用 insert 语句直接操作 "mysql" 库中的 "user表" 来新增用户。

使用 "CREATE USER" 语句创建新用户时，必须拥有 "CREATE USER" 权限。每添加一个用户， "CREATE USER" 语句会在 "mysq.user"中的添加一条新纪录，但是新创建的账户没有任何权限。如果添加的账户已经存在，会报错。

注意："刷新权限" 的本质就是：事务中的提交操作。在使用（dml）操作用户信息后，则必须执行 "刷新权限" 操作，才生效。

（创建新用户）create user 基本语法形式如下：

create user 用户名@'指明连接类型' IDENTIFIED BY '密码';
# 在未指定连接类型时，默认为:'%'

例如

CREATE USER xld@'%' IDENTIFIED BY 'xld123';

查看当前数据库的所有用户的信息

SELECT * FROM mysql.user;

查看当前用户拥有的权限信息

SHOW GRANTS;

1.3 修改用户

修改用户信息的语法 - （其本质就是修改表数据）：

# 修改用户信息 - dml 操作
update mysql.user set user = '新的用户名' where user = '旧的用户' and host = '连接类型';

# 刷新权限 
flush privileges;

例如

# 修改用户信息 - dml 操作
UPDATE mysql.user SET USER = 'xld' WHERE USER = 'xld_test' and host = '%';

# 刷新权限
FLUSH PRIVILEGES;

修改用户密码的语法：

# 修改用户密码
alter user 用户名@'连接类型' IDENTIFIED BY '新密码';

# 或者 不设置密码
update mysql.user set authentication_string='' where user ='用户名';

# 刷新权限
flush privileges;

例如

# 修改用户密码
ALTER USER xld@'%' IDENTIFIED BY 'xld123456';

# 或者 不设置密码
UPDATE mysql.user SET authentication_string = '' WHERE USER = 'xld';

# 刷新权限
FLUSH PRIVILEGES;

千万一定要注意：使用（dml语言）修改用户（权限）信息后，必须执行 "刷新权限" 操作。

注意：在修改密码的时候，必须指明用户的 "连接类型（@'xx'）"。因为 "用户名" 是可以重复的

1.4 删除用户

在 MySQL 数据库中，使用 "DROP USER" 来删除普通用户，但是要拥有"DROP USER" 权限才可以。也可以直接在 mysql.user 表中删除用户。

方式1（推荐）："drop user"

drop user 用户名@'连接类型',用户名@'连接类型',...

例如

DROP USER 'xld'@'%';

注意：在不指明 "@'连接类型'" 时，默认删除 "连接类型" 为 " @'%' "的用户

方式2（不推荐）：delete 方式删除

delete from mysql.user where host = '连接类型' AND user = '用户名';
# 刷新权限
FLUSH PRIVILEGES;

例如

DELETE FROM mysql.user WHERE HOST = '%' AND USER = 'xld';
# 刷新权限
FLUSH PRIVILEGES;

注意：通过 delete 方式删除用户后，必须执行 "刷新权限" 操作，才会生效。

1.5 设置当前用户密码

适用于"root" 以及 "普通用户" 登录后修改自己的密码。

MySQL 中除了可以使用 "ALTER USER" 修改用户密码（官方推荐）方式外，也可以通过 "set" 的方法修改密码。但是 8.0 中移除了 PASSWORD() 函数，因此不再能使用 update 的方法修改用户密码了。

方式1：”alter user“

alter user user() identified by '新密码';
# 例如
ALTER USER USER() IDENTIFIED BY 'xld123456';

方式2：”set“

set password = '新密码';
# 例如
SET PASSWORD = 'xld123';

注意：以上的所有方式都无需执行 "刷新权限" 操作。因为都是（ddl）操作。

1.6 修改其他用户密码

"root" 用户可以通过 "alter user 和 set "来修改 "普通用户" 的密码。但是 8.0 中移除了 PASSWORD() 函数，因此使用 update 直接操作用户表的方式不再能使用了。

注意：如果"普通用户"也想修改其他用户的密码的话，则必须要拥有 alter user 的权限。

方式1：”alter user“

alter user 用户名@'连接类型' IDENTIFIED BY '新密码';
# 例如
ALTER USER xld@'%' IDENTIFIED BY 'xld123456';

方式2："set"

set password for '用户名'@'连接类型' = '新密码';
# 例如
SET PASSWORD FOR 'xld'@'%' = 'xld123456';

注意：在修改密码的时候，必须指明用户的连接类型（@'xx'）。因为用户名是可以重复的

1.7 MySQL 8.0 密码管理

1.7.1 密码过期策略

哈啊哈哈.....

1.7.2 密码重用策略

哈啊哈哈.....

2.权限管理

关于 MySQL 的权限简单的理解：就是 MySQL 允许你做你拥有权力以内的事情，不可越界。

例如：只允许你执行 select 操作，那么你就不能执行 update 操作。只允许你从某台机器上连接 MySQL ，那么你就不能从其他机器上连接 MySQL。这就是权限控制

2.1 权限列表 - 查看

SHOW PRIVILEGES;

"GRANT" 和 "REVOKE"语句中可以使用的权限如下：

权限标识	user 表中对应的列	权限的范围
create	Create_priv	数据库，表或索引
drop	Drop_priv	数据库，表或视图
grant option	Grant_priv	数据库，表或存储过程
references	References_priv	数据库或表
event	Event_priv	数据库
alter	Alter_priv	数据库
delete	Delete_priv	表
index	Index_priv	表
insert	Insert_priv	表
select	Select_priv	表或列
update	Update_priv	表或列
create temporary tables	Create_tmp_table_priv	表
lock_tables	Lock_tables_priv	表
trigger	Trigger_priv	表
create_view	Create_view_priv	视图
show_view	Show_view_priv	视图
alter routne	Alter_routine_priv	存储过程和函数
create routne	Create_routine_priv	存储过程和函数
execute	Execute_priv	存储过程和函数
file	File_priv	访问服务器上的文件
create tablespace	Create_tablespace_priv	服务器管理
create_user	Create_user_priv	服务器管理
process	Process_priv	存储过程和函数
reload	Reload_priv	访问服务器上的文件
replication client	Repl_client_priv	服务器管理
replication slave	Repl_slave_priv	服务器管理
show databases	Show_db_priv	服务器管理
shutdown	Shutdown_priv	服务器管理
super	Super_priv	服务器管理

create，drop 和 alter 权限，可以创建新的库，表，或删除和修改已有的库，表。
select，insert，update和delete 权限，允许对一个库中的表实施操作。
index 权限，允许创建或删除索引，index 适用于已有的表。如果具有某个表的 create 权限，就可以在创建表定义索引。
create_view 和 show_view 权限，允许创建和使用视图。
create roution 权限，允许创建保存的程序（函数和程序）。
execute 权限，允许执行保存的程序。
grant 权限，允许授权给其他用户。
file 权限，允许用户访问服务器上任务文件。

MySQL 的权限分布：

权限分布	可能的设置的权限
表权限	select，insert，update，delete，create，drop，grant，references，index，alter
列权限	select，insert，update，references
过程权限	execute，alter routine，grant

2.2 授予权限的原则

只授予能满足需要的最小权限，防止出现问题！
创建用户的时候限制用户的登录主机，一般是限制成指的IP或者内网IP段。
定期清理不需要的用户，回收权限或删除用户。

2.3 授予权限

给用户授权的方式有2种：

直接给用户授权。
通过给角色赋予权限的方式，间接给用户授权。

授权命令：

grant 权限1,权限2,... on 数据库名.表名称 TO 用户名@'连接类型';

例如：

为 xld 用户设置对 xld 库中所有表的查询新增和修改的权限。

GRANT SELECT,INSERT,UPDATE ON xld.* TO xld@'%';

为 xld 用户设置全部的权限

GRANT ALL PRIVILEGES ON *.* TO xld@'%';

注意：该授权方式，不包括 grant 的权限

授权语法解析：

grant：授权关键字。
all privileges ：表示所有的权限（但不包括 grant 权限）。也可以使用 select，insert，...。
on：用来指定授权的数据库和表（通配符）。
to：表示将权限授予的用户（用户名@'连接类型'）。
identified by：指定用户的登录密码。
如果需要赋予包括 grant 的权限，添加参数 "WITH GRANT OPTION" 这个选择即可，表示该用户可以将自己拥有的权限授权给别人。
可以使用 grant 重复授予权限，且是权限叠加的。

2.4 查看权限

查看当前用户权限

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

查看某用户的全局权限

SHOW GRANTS FOR 'xld'@'%';

2.5 收回权限

收回权限就是：取消已经赋予用户的某些权限。

MySQL 中使用 "REVOKE" 取消用户的某些权限。执行 revoke 收回权限之后，用户权限信息将从 db，host，tables_priv和 columns_priv表中删除，但是用户信息仍然在 user表中保存（需要手动删除用户）

收回权限命令：

revoke 权限1,权限2,... on 数据库名.表名称 from 用户名@'连接类型';

例如

REVOKE SELECT,UPDATE ON xld.* FROM xld@'%';
# 或
REVOKE ALL PRIVILEGES ON *.* FROM xld@'%';

可能出现的错误：

[Err] 1227 - Access denied; you need (at least one of) the SYSTEM_USER privilege(s) for this operation
# 访问被拒绝：执行此操作需要拥有 SYSTEM_USER 的权限

解决办法：为当前用户授予 SYSTEM_USER 权限

GRANT SYSTEM_USER ON *.* TO root@'%'

注意：当收回权限后，需用户重新登录后才能生效。

3. 权限表

MySQL 服务器是通过"权限表" 来控制用户对数据库的访问，"权限表" 存放在 "mysql库" 中。MySQL 数据库系统会根据 "权限表" 中的内容为每个用户赋予相应的权限。这些"权限表"中最重要的是 user，db。还有 tables_priv，column_priv和proc_priv表等。在 MySQL 启动时，服务器将这些数据库表中权限信息的内容读入内存中。

权限表说明：

表名	描述
user	用户账号信息
db	数据库层级权限
tables_priv	表层级权限
column_priv	列层级权限
proc_priv	存储过程和函数权限
proxies_priv	代理用户的权限
global_grants	动态全局授权
default_roles	账号连接并认证后默认授予的角色
role_edges	角色子图的边界
password_history	密码更改信息

user 表

该表是用于存储数据库用户信息的表。其中重要的字段有 Host，User，authentication_string，等。其中 User 和 Host 为联合主键。

当用户和服务器之间建立连接时，登录时输入的用户名，主机名和密码必须匹配 User 表中对应的字段，只有这3个字段的值都匹配的时候，才允许连接。

host：表示连接类型（连接类型）。ip地址，机器名，%,等...
user：表示用户名。同一个用户，不同连接类型的权限也是不一样的
authentication_string（password）：密码

db表

该表是用于存储用户对某个数据库的操作权限，决定用户能存取那些数据库。其中 hots，user，db 为联合主键。

user表中的权限是针对所有数据库的，如果user表中权限字段为 Y ，那么用户就具有所有的数据库的权限（权限字段）。

如果希望用户只对某个数据库有操作权限，按摩需要将 user表中对应的权限设置为 N，然后在 db表中设置对应的数据库的操作权限。

host：表示连接类型
user：表示用户名
db：表示数据库

tables_priv和columns_priv表

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

host：表示连接类型
user：表示用户名
db：表示数据库
table_name：表示表名
grantor：表示修改该记录的用户
timestamp：表示修改该记录的时间

tables==========================

table_priv：表示对表的操作权限
column_priv：表示对表中列的操作权限

columns========================

column_name：表示列名
column_priv：表示对列的操作权

procs_priv表

该表是用于存储用户对存储过程和函数的操作权限。其中 hots，user，db，routine_name，routine_type 为联合主键。

host：表示连接类型
user：表示用户名
db：表示数据库
routine_name：程序（存储过程和函数）名称
routine_type：程序（存储过程和函数）类型
proc_priv：程序（存储过程和函数）的操作权限
grantor：表示修改该记录的用户
timestamp：表示修改该记录的时间

4. 访问控制

在正常情况下，并不希望每个用户都可以执行所有的数据库操作（权限控制）。

当 MySQL 允许一个用户执行各种操作时，它将首先核实该用户向 MySQL 服务器发送的连接请求，然后确认用户的操作请求是否被允许。这个过程称为 MySQL 中的访问控制过程。MySQL 的访问控制分为两个阶段：连接核实阶段和请求核实阶段。

连接核实阶段：服务器先核实该用户向 MySQL 服务器发送的连接请求，然后确认用户的操作请求是否被允许。

4.1 连接核实阶段

当用户连接 MySQL 服务器时，进入连接核实阶段。服务器基于用户登录时输入的用户信息和密码来确定接受或者拒绝连接。

验证方式：服务器会使用 mysql.user表中的 host，user以及authentication_string 这3个字段和客户端提供信息进行匹配验证。
验证结果：如果验证成功，服务器接受连接，然后进入下一个阶段等待用户请求。反之服务器拒绝访问。

4.2 请求核实阶段

请求核实阶段，是在建立连接成功后才会有的一个阶段。

进入该阶段后，服务器会对客户端发送的每个请求，进行检查。检查请求要执行的操作，是否有权限执行该操作。

检查请求要执行的操作 - 或者说是确认权限：

首先检查 mysql.user表，是否被授予该权限（全局权限），有执行，没有则进入下一张权限表。
进入 mysql.db表，检查"数据库层级 "是否授予该权限，有执行，没有则再次进入下一张权限表。
进入 mysql.tables_priv表以及 columns_priv表，检查"表级和列级"是否授予该权限，有执行，没有仅需进入下一张权限表。
直至所有权限表都检查完毕，以就没有找到允许该操作的权限，那么服务器将返回错误信息，该操作失败。

5. 角色管理

角色是在 MySQL 8.0 中引入的新功能。在 MySQL 中，角色是权限的集合。可以为角色添加或移除权限。用户可以被赋予角色，同时也被赋予角色包含的权限。

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

5.1 创建角色

在 MySQL 中创建了角色之后，默认都是没有被激活的，也就是不能用，必须要手动激活，激活以后用户才能拥有角色对应的权限。

先将权限集合放入角色中，再为角色分配相应的用户。这样可以更好管理用户权限分配。

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

create role 角色名@'连接类型';

例如

CREATE ROLE 'xld_role'@'%';

注意：角色名称的命名规则和用户名类似。如果不指定 '连接类型'，默认使用:'%'，不可为空。

5.2 给角色赋予权限

为角色授予权限使用 GRANT 语句，语法如下：

grant 权限1,权限2,权限3,权限4,... on 数据库.表 to 角色名@'连接类型';

例如

GRANT SELECT,UPDATE,DELETE ON xld.* TO 'xld_role'@'%';
# 或者
GRANT ALL PRIVILEGES ON *.* TO 'xld_role'@'%'; # 授予全部权限，但不包括 grant 权限

5.3 查看角色的权限

查看角色的权限使用 SHOW GRANTS FOR 语句，语法如下：

show grants for 角色名@'连接类型';

例如

SHOW GRANTS FOR 'xld_role'@'%';

查看当前用户拥有的角色：

SELECT CURRENT_ROLE();

5.4 回收角色的权限

回收角色权限使用 REVOKE 语句，语法如下：

revoke 权限1,权限2,权限3,... on 数据库.表 from 角色名@'连接类型';

例如

REVOKE DELETE,SELECT ON xld.* FROM 'xld_role'@'%'; # 回收多个或单个权限
# 或
REVOKE ALL PRIVILEGES ON *.* FROM 'xld_role'@'%'; # 回收所有的权限

5.5 删除角色

删除角色使用 DROP ROLE 语句，语法如下：

drop role 角色名@'连接类型',角色名@'连接类型';

例如

DROP ROLE 'xld_role'@'%';

注意：在不指明 ” @'连接类型' “时，默认删除为 " @'%' "的角色。

5.6 给角色分配用户

为角色分配用户使用 GRANT 语句，语法如下：

grant 角色名@'连接类型'[,角色名@'连接类型',...] to 用户名@'连接类型'[,用户名@'连接类型',...];

例如

GRANT 'xld_role'@'%' TO 'zhangsan'@'%','lisi'@'%';

注意：此时被分配到角色的用户，被赋予了角色的权限。但是权限还未生效，必须激活角色。

注意：在 MySQL 中创建了角色之后，默认都是没有被激活的，也就是不能用，必须要手动激活，激活以后用户才能拥有角色对应的权限。

5.7 激活角色

激活角色有两种方式：

方式1：使用 SET DEFAULT ROLE 命令激活角色，语法如下

set default role 角色名@'连接类型' to 用户名@'连接类型'[,用户名@'连接类型',...];

例如

SET DEFAULT ROLE 'xld_role'@'%' TO 'zhangsan'@'%','lisi'@'%';

注意：用户需要退出重新登录，权限才能生效。

方式2：将系统变量 "activate_all_roles_on_login" 设置为 ON 。（或者在配置文件中设置）

SET GLOBAL activate_all_roles_on_login=ON;

该方式是一个永久的方式，对所有角色永久激活。运行这条语句之后，用户才真正拥有了角色的所有权限。
可以认为该方式是将创建角色时，默认不会被激活，修改为了默认被激活。

5.8 撤销用户的角色

撤销用户角色使用语句，语法如下：

revoke 角色名@'连接类型' from 用户名@'连接类型'[,用户名@'连接类型',...];

例如

REVOKE 'xld_role'@'%' FROM  'zhangsan'@'%','lisi'@'%';

注意：用户需要退出重新登录，撤销才能生效。

5.9 设置强制角色 - （mandatory role）

强制角色是给每个创建用户的默认角色，不需要手动设置。强制角色无法被 REVOKE 和 DROP。

方式1：服务启动前设置

[mysqld]
mandatory_roles='角色名@连接类型,角色名@连接类型,...'

方式2：服务运行时设置

SET PERSIST mandatory_roles = '角色名@连接类型,角色名@连接类型,...'; # 系统重启后仍然有效
SET GLOBAL mandatory_roles = '角色名@连接类型,角色名@连接类型,...'; # 系统重启后失效

5.10 小结

MySQL 主要管理角色的语句如下：

语句	作用
CREATE ROLE and DROP ROLE	创建和删除角色
GRANT and REVOKE	给角色或者用户（赋予或回收）权限。以及为角色（分配或撤销）用户
SHOW GRANTS	显示（用户或角色）所拥有的权限或用户所拥有的角色
SET DEFAULT ROLE	激活用户所使用角色的权限
SET ROLE	设置当前会话的角色 - （切换用户下不同角色的权限）
CURRENT_ROLE() 函数	显示当前会话的角色
mandatory_roles 和 activate_all_roles_on_login	设置创建用户时的强制的角色和设置默认角色是否激活的状态

6. 配置文件的使用

6.1 配置文件格式

配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号 [] 扩起来。像这样：

[server]
# 具体的启动选择...

[mysqld]
# 具体的启动选择...

[mysqld_safe]
# 具体的启动选择...

[client]
# 具体的启动选择...

[mysql]
# 具体的启动选择...

[mysqladmin]
# 具体的启动选择...

配置文件中定义了许多个组，组名分别是 server，mysqld，mysqld_safe，client，mysql，mysqladmin。

每个组下边可以定义若干个启动选项，以 server为例，看一下填写启动选项的形式（其他组启动选项的形式是一样的）

[server]
option1  		# 选项1，不需要选项值。	true 或 false 类型
option2=value2  # 选项2，该选项需要填写值。键值对 类型
...

6.2 启动命令与选项组

配置文件中不同的选项组是给不同的启动命令使用的。不过有两个选项组比较特别：

[server] 组下边的启动选项将作用于所有服务器程序。
[client] 组下边的启动选项将作用于所有客户端程序。

下面是使用不同启动命令所读取的选择组列表：

启动命令	类别	读取的组
mysqld	启动服务器	[mysqld]，[server]
mysqld_safe	启动服务器	[mysqld]，[server]，[mysqld_safe]
mysql.server	启动服务器	[mysqld]，[server]，[mysql.server]
mysql	启动客户端	[mysql]，[client]
mysqladmin	启动客户端	[mysqladmin]，[client]
mysqldump	启动客户端	[mysqldump]，[client]

6.3 特定 MySQL 版本的专用选项组

我们可以在选项组的名称后加上特定的 MySQL 版本号，比如对于 [mysqld] 选项组来说，我们可以定义一个 [mysqld-5.7] 的选项组，它的含义和 [mysqld] 一样，只不过只有版本号为 5.7 的 mysqld 程序才能使用这个选项组中的选项。

6.4 同一个配置文件中多个组的优先级

一个启动命令可以访问配置文件中的多个组，比如 mysqld 命令可以访问 [mysqld]，[server]组。那么那个组的优先级大呢！。比如这样配置：

[server]
default-storage-engine=InnoDB

[mysqld]
default-storage-engine=MyISAM

答：按照声明顺序，选最后一个组中的启动选项为准。以上配置中使用 [mysqld] 组中的启动选项。

6.5 命令行和配置文件中启动选项的区别

在命令行上指定的绝大部分启动选项都可以放到配置文件中，但有些选项是专门为命令行设计的。比如：defaults-extra-file，defaults-file 这样的选项本身就是用于指定配置文件路径的。无法放在配置文件中。

如若同一个启动选项即出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准！