用户管理

登录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服务器

注：顺序可以打乱。

创建用户

CREATE USER 'username'[@'hostname'] [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']] --即可以重复添加
-- @'host'不写默认为 localhost
CREATE USER 'zhangsan' IDENTIFIED BY '123123'; --默认host是 % ，可以从任意远程主机登陆
CREATE USER 'bailong'@'localhost' IDENTIFIED BY '123456';

“[ ]” 表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用IDENTIFIED BY指定明文密码值。
CREATE USER语句可以同时创建多个用户。

修改用户

UPDATE mysql.user SET USER = 'Lee' WHERE USER = 'wang'; --把 'wang' 改成 'Lee'
FLUSH PRIVILEGES; --刷新

删除用户

使用DROP方式(推荐)

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

DROP USER 'username'@'hostname'[,user]; 
DROP USER 'li4';  --默认删除host为%的用户
DROP USER 'kangshifu'@'localhost';

使用delete方式

可以使用DELETE语句，直接将用户的信息从 mysql.user表中删除，但必须拥有对 mysql.user表的 DELETE权限，DELETE语句的基本语法形式如下：

DELETE FROM mysql.user WHERE Host='hostname' AND User='username';
--执行完DELETE命令后要使用FLUSH命令来使用户生效，命令如下：
FLUSH PRIVILEGES;
--举例
DELETE FROM mysql.user WHERE Host='localhost' AND User='Emily';
FLUSH PRIVILEGES;

注：1. Host字段和User字段是user表的联合主键，因此两个字段的值才能唯一确定一条记录。
2. 不推荐通过 DELETE FROM mysql.user WHERE USER=’?’ 进行删除，系统会有残留信息保留。而drop user命令会删除用户以及对应的权限，执行命令后你会发现mysql.user表和mysql.db表的相应记录都消失了。

设置用户的密码

适用于root用户修改自己的密码，以及普通用户登录后修改自己的密码。root用户拥有很高的权限，因此必须保证root用户的密码安全。由于MySQL 8中已经移除了 PASSWORD()函数，因此不再使用 UPDATE语句直接操作用户表修改密码。

使用ALTER语句

使用 ALTER USER命令来修改当前用户密码

ALTER USER user IDENTIFIED BY 'new_password';
--user是账户，包括用户名和主机名
ALTER USER 'qingyan'@'localhost' IDENTIFIED BY 'hello_world';
-- 修改主机号为localhost的用户qingyan的密码为 'hello_world'

使用SET语句

登录MySQL后，可以使用SET语句来修改密码，该语句会自动将密码加密后再赋给当前用户：

SET PASSWORD = 'new_password';

修改其他用户的密码

root用户登录MySQL服务器后，可以用 ALTER语句和 SET语句来修改普通用户的密码。由于PASSWROD()函数已经移除，因此使用UPDATE直接操作用户表的方式已不再使用，原因是：

UPDATE MySQL.user SET authentication_string=PASSWORD("123456")
WHERE User = "username" AND Host = "hostname";
--PASSWORD()函数已经被删除，因此不再使用
FLUSH PRIVILEGES;

更换为如下方式：

--使用ALTER语句
ALTER USER user [IDENTIFIED BY '新密码'][,user[IDENTIFIED BY '新密码']]…;
--此处的user 就是 账户，包括'username'@'hostname' ，由用户名和主机名组成
ALTER USER 'yinhe'@'localhost' IDENTIFIED BY 'hello_world'
--使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码
SET PASSWORD FOR 'username'@'hostname' = 'new_password';
SET PASSWORD FOR 'yinhe'@'localhost' = 'hello_world';

MySQL8密码管理

密码过期策略

在MySQL中，数据库管理员可以手动设置账号密码过期，也可以建立一个自动密码过期策略。过期策略可以是全局的，也可以为每个账号设置单独的过期策略。

手动设置

ALTER USER user PASSWORD EXPIRE; --手动设置账号密码立马过期
ALTER USER 'bailong'@'localhost' PASSWORD EXPIRE;
--该用户仍然可以登录进入数据库，但是无法进行查询。只有重新设置了密码才能正常使用

自动设置

--方式一：使用SQL语句更改该变量的值并持久化
SET PERSIST default_password_lifetime = 180; # 建立全局策略，设置密码每隔180天过期
--方式二：配置文件my.cnf (windows下是my.ini)中进行维护
[mysqld]
default_password_lifetime=180 #建立全局策略，设置密码每隔180天过期

手动设置指定时间过期方式2：单独设置
每个账号既可用全局密码过期策略，也可单独设置策略。在 CREATE USER 和 ALTER USER 语句上加入 PASSWORD EXPIRE 选项可实现单独设置策略。下面是一些语句示例：

#设置kangshifu账号密码每90天过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE INTERVAL 90 DAY;
#设置密码永不过期：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE NEVER;
#延用全局密码过期策略：
CREATE USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;
ALTER USER 'kangshifu'@'localhost' PASSWORD EXPIRE DEFAULT;

密码重用策略

全局

方式一：使用SQL语句
SET PERSIST password_history = 6; #设置不能选择最近使用过的6个密码
SET PERSIST password_reuse_interval = 365; #设置不能选择最近一年内的密码
方式二：my.cnf配置文件
[mysqld]
password_history=6
password_reuse_interval=365

单独设置

#不能使用最近5个密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
ALTER USER 'kangshifu'@'localhost' PASSWORD HISTORY 5;
#不能使用最近365天内的密码：
CREATE USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost' PASSWORD REUSE INTERVAL 365 DAY;
#既不能使用最近5个密码，也不能使用365天内的密码
CREATE USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;
ALTER USER 'kangshifu'@'localhost'
PASSWORD HISTORY 5
PASSWORD REUSE INTERVAL 365 DAY;

权限管理

各种权限

mysql> 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 种，分别是通过把角色赋予用户给用户授权和直接给用户授权。用户是数据库的使用者，我们可以通过给用户授予访问数据库中资源的权限，来控制使用者对数据库的访问，消除安全隐患。

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY '密码口令'];
--该权限如果发现没有该用户，则会直接新建一个用户。
GRANT SELECT,INSERT,DELETE,UPDATE ON yy.* TO li4@localhost ;
--给li4用户用本地命令行方式，授予yy这个库下的所有表的插删改查的权限。
GRANT ALL PRIVILEGES ON *.* TO joe@'%' IDENTIFIED BY '123';
--授予通过网络方式登录的joe用户对所有库所有表的全部权限，密码设为123。
--注意这里唯独不包括grant的权限

查看权限

#查看当前用户的权限
SHOW GRANTS;
# 或
SHOW GRANTS FOR CURRENT_USER;
# 或
SHOW GRANTS FOR CURRENT_USER();
#查看某用户的权限
SHOW GRANTS FOR 'username'@'主机地址' ;

收回权限

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

REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;
#收回joe用户在全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
#收回joe用户在mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

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

访问控制

连接核实阶段

当用户试图连接MySQL服务器时，服务器基于用户的身份以及用户是否能提供正确的密码验证身份来确定接受或者拒绝连接。即客户端用户会在连接请求中提供用户名、主机地址、用户密码，MySQL服务器接收到用户请求后，会使用user表中的host、user和authentication_string这3个字段匹配客户端提供信息(验证账户)。
服务器只有在user表记录的Host和User字段匹配客户端主机名和用户名，并且提供正确的密码时才接受连接。如果连接核实没有通过，服务器就完全拒绝访问；否则，服务器接受连接，然后进入请求核实阶段(验证权限)。

请求核实阶段

一旦建立了连接，服务器就进入了请求核实阶段。对此连接上进来的每个请求，服务器检查该请求要执行什么操作、是否有足够的权限来执行它，这正是需要授权表中的权限列发挥作用的地方。这些权限可以来自user、db、table_priv和column_priv表。
确认权限时，MySQL首先检查 user 表，如果指定的权限没有在user表中被授予，那么MySQL就会继续检查db表，db表是下一安全层级，其中的权限限定于数据库层级，在该层级的SELECT权限允许用户查看指定数据库的所有表中的数据；如果在该层级没有找到限定的权限，则MySQL继续检查tables_priv表以及 columns_priv表，如果所有权限表都检查完毕，但还是没有找到允许的权限操作，MySQL将返回错误信息，用户请求的操作不能执行，操作失败。
提示： MySQL通过向下层级的顺序（从user表到columns_priv表）检查权限表，但并不是所有的权限都要执行该过程。例如，一个用户登录到MySQL服务器之后只执行对MySQL的管理操作，此时只涉及管理权限，因此MySQL只检查user表。另外，如果请求的权限操作不被允许，MySQL也不会继续检查下一层级的表。

角色管理

对角色的理解

创建角色

CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...
-- host_name为空默认为 %
CREATE ROLE 'manager'@'localhost';
CREATE ROLE 'manager','employee','boss';

给角色赋予权限

创建角色之后，默认这个角色是没有任何权限的，我们需要给角色授权。

GRANT privileges ON 数据库名.表名 TO 'role_name'[@'host_name'];
#现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码来实现：
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 INSERT,DELETE,UPDATE ON app_db.* TO 'app_read'; --app_db数据库中所有表的修改权限

查看角色的权限

赋予角色权限之后，我们可以通过 SHOW GRANTS 语句，来查看权限是否创建成功了。

mysql> SHOW GRANTS FOR 'manager';
+-------------------------------------------------------+
| Grants for manager@% |
+-------------------------------------------------------+
| GRANT USAGE ON *.* TO `manager`@`%` |
| GRANT SELECT ON `demo`.`goodsmaster` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`invcount` TO `manager`@`%` |
| GRANT SELECT ON `demo`.`settlement` TO `manager`@`%` |
+-------------------------------------------------------+

只要创建了一个角色，系统就会自动默认给你一个 “USAGE”权限，意思是连接登录数据库的权限。代码的最后三行代表了我们给角色 “manager” 赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权限。

回收角色权限

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

REVOKE privileges ON tablename FROM 'rolename';
REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';
SHOW GRANTS FOR 'school_write'; #查看回收后 school_write角色的权限

删除角色

当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。 如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限。

DROP ROLE role [,role2]...;
DROP ROLE 'school_read','school_writer';

给用户赋予角色

角色创建并授权后，要赋给用户并处于激活状态才能发挥作用。MySQL创建角色后，默认是未激活的状态，需要手动激活。

GRANT role [,role2,...] TO user [,user2,...];
--role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可
GRANT 'school_read' TO 'dianzi '@'localhost';
SHOW GRANTS FOR 'dianzi'@'localhost'; #查看角色是否赋予成功
SELECT CURRENT_ROLE(); 
--使用dianzi用户登录，然后查询当前角色，如果角色未激活，结果将显示NONE

激活角色

使用set default role 命令激活角色 ```sql SET DEFAULT role_name[,role_name] TO ‘kangshifu’@’localhost’;

SET DEFAULT ROLE ALL TO ‘dev1’@’localhost’, ‘read_user1’@’localhost’, ‘read_user2’@’localhost’, ‘rw_user1’@’localhost’;

SET DEFAULT ROLE administrator, developer TO ‘joe’@’10.0.0.1’;

**DEFAULT ROLE 关键字 后面的子句允许这些值：<br />NONE：将默认设置为 NONE（无角色）。<br />ALL：将默认设置设为授予该帐户的所有角色。**
2. **将activate_all_roles_on_login设置为ON**
```sql
# 默认情况
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 语句的意思是，对 所有角色永久激活

撤销用户的角色

REVOKE role FROM user;
REVOKE 'school_read' FROM 'kangshifu'@'localhost'; #撤销角色
SHOW GRANTS FOR 'kangshifu'@'localhost'; #查看是否撤销

设置强制角色

强制角色是给当前账户的默认角色，不需要手动设置。强制角色无法被 REVOKE或者 DROP。

#服务启动前设置,对所有账户都有效
[mysqld]
mandatory_roles='role1,role2@localhost,r3@%.atguigu.com';
#运行时设置,对当前账户有效
#系统重启后仍然有效
SET PERSIST mandatory_roles = 'role1,role2@localhost,r3@%.example.com'; 
#系统重启后失效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,r3@%.example.com';

MySQL管理