【MySQL高级】——用户与权限管理

一、用户管理

<1> 登录

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服务器。

<2> 创建用户

CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

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

CREATE USER 'kangshifu'@'localhost' IDENTIFIED BY '123456';
CREATE USER zhang3 IDENTIFIED BY '123123'; # 默认host是 %

<3> 修改用户

UPDATE mysql.user SET USER='li4' WHERE USER='wang5'; 
FLUSH PRIVILEGES;

<4> 删除用户

1. DROP

使用DROP USER语句来删除用户时，必须用于DROP USER权限。

DROP USER user[,user]…;

例：

DROP USER li4 ; # 默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';

2. DELECT（不推荐）

DELETE FROM mysql.user WHERE Host=’hostname’ AND User=’username’;
FLUSH PRIVILEGES;

例：

DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily';
FLUSH PRIVILEGES;

不推荐通过 DELETE FROM USER u WHERE USER=‘li4’ 进行删除，系统会有残留信息保 留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表 的相应记录都消失了。

<5> 设置当前用户的密码

1. 使用ALTER USER

使用ALTER USER命令来修改当前用户密码 用户可以使用ALTER命令来修改自身密码

ALTER USER USER() IDENTIFIED BY 'new_password';

2 使用SET

SET PASSWORD='new_password';

<6> 修改其他用户的密码

1. 使用ALTER

ALTER USER user [IDENTIFIED BY '新密码'] [,user[IDENTIFIED BY '新密码']]…;

2. 使用SET

使用root用户登录到MySQL服务器后，可以使用SET语句来修改普 通用户的密码

SET PASSWORD FOR 'username'@'hostname'='new_password';

二、权限管理

<1> 权限列表

show privileges;

1. CREATE和DROP权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将MySQL数据库中的DROP权限授予某用户，用户就可以删除MySQL访问权限保存的数据库。
2. SELECT、INSERT、UPDATE和DELETE权限 允许在一个数据库现有的表上实施操作。
3. SELECT权限只有在它们真正从一个表中检索行时才被用到。
4. INDEX权限 允许创建或删除索引，INDEX适用于已有的表。如果具有某个表的CREATE权限，就可以在CREATE TABLE语句中包括索引定义。
5. ALTER权限 可以使用ALTER TABLE来更改表的结构和重新命名表。
6. CREATE ROUTINE权限 用来创建保存的程序（函数和程序），ALTER ROUTINE权限用来更改和删除保存的程序， EXECUTE权限 用来执行保存的程序。
7. GRANT权限 允许授权给其他用户，可用于数据库、表和保存的程序。
8. FILE权限 使用户可以使用LOAD DATA INFILE和SELECT … INTO OUTFILE语句读或写服务器上的文件，任何被授予FILE权限的用户都能读或写MySQL服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

<2> 授予权限原则

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

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

<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@'%' ;

<4> 查看权限

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

<5> 收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限可以在一定程度上保证系统的安全性。MySQL中使用 REVOKE语句 取消用户的某些权限。使用REVOKE收回权限之后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存（删除user表中的账户记录使用DROP USER语句）。
命令：

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

例：
#收回全库全表的所有权限

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

#收回mysql库下的所有表的插删改查权限

REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

三、权限表

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

<1> user表

DESCRIBE mysql.db;

1. 范围列（或用户列）

2. 权限列

3. 安全列

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

4. 资源控制列

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

<2> db表

体现对某个数据库的权限。
DESCRIBE mysql.db;

1. 主键列

db表用户列有3个字段，分别是Host、User、Db。这3个字段分别表示主机名、用户名和数据库 名。表示从某个主机连接某个用户对某个数据库的操作权限，这3个字段的组合构成了db表的主键。

2. 权限列

Create_routine_priv和Alter_routine_priv这两个字段决定用户是否具有创建和修改存储过程的权限

<3> tables_priv表

tables_priv表有8个字段，分别是Host、Db、User、Table_name、Grantor、Timestamp、Table_priv和 Column_priv

<4> columns_priv表

columns_priv表只有7个字段，分别是Host、Db、User、Table_name、Column_name、Timestamp、column_priv。其中，Column_name用来指定对哪些数据列具有操作权限。

<5> procs_priv表

四、访问控制

<1> 连接核实阶段

<2> 请求核实阶段

<3> 访问控制流程

5. 角色管理

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

<1> 创建角色

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

角色名称的命名规则和用户名类似。如果 host_name省略，默认为% ， role_name不可省略 ，不可为空。
例：

CREATE ROLE 'manager'@'localhost';

<2> 给角色赋予权限

系统就会自动给你一个“ USAGE ”权限，意思是 连接登录数据库的权限

GRANT privileges ON table_name TO 'role_name'[@'host_name'];

例：

GRANT SELECT ON demo.goodsmaster TO 'manager';

<3> 查看角色权限

SHOW GRANTS FOR 'manager';

<4> 回收角色权限

REVOKE privileges ON tablename FROM 'rolename';

例：

REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';

<5> 删除角色

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

DROP ROLE role [,role2]...

例：

DROP ROLE 'school_read';

<6> 给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。给用户添加角色可使用GRANT语句

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

例：

GRANT 'school_read' TO 'kangshifu'@'localhost';

<7> 激活角色

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

例：

SET DEFAULT ROLE ALL TO 'dev1'@'localhost';
对 所有角色永久激活
SET GLOBAL activate_all_roles_on_login=ON;

<8>撤销用户的角色

REVOKE role FROM user;

例：

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

<9>设置强制角色

1. 配置文件方式

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';