高级篇三、用户与权限管理

1、用户管理

1.1 登录MySQL服务器

mysql -h xxx -P xxx -u xxx -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服务器。

举例：

mysql -uroot -p -hlocalhost -P3306 mysql -e "select host,user from user"

1.2 创建用户

CREATE USER语句的基本语法形式如下：

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

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

举例：

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

1.3 修改用户

修改用户名：

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

1.4 删除用户

1、使用DROP方式删除（推荐）
使用DROP USER语句来删除用户时，必须用于DROP USER权限。DROP USER语句的基本语法形式如下：

DROP USER user[,user]…;

demo

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

2、使用DELETE方式删除，执行完DELETE命令后要使用FLUSH命令来使用户生效

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

demo

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

1.5 设置当前用户密码

1. 使用ALTER USER命令来修改当前用户密码
   用户可以使用ALTER命令来修改自身密码，如下语句代表修改当前登录用户的密码。基本语法如下：

ALTER USER USER() IDENTIFIED BY 'new_password';

2. 使用SET语句来修改当前用户密码
   使用root用户登录MySQL后，可以使用SET语句来修改密码，具体SQL语句如下：

SET PASSWORD='new_password';

1.6 修改其它用户密码

1. 使用ALTER语句来修改普通用户的密码

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

2. 使用SET命令来修改普通用户的密码
   使用root用户登录到MySQL服务器后，可以使用SET语句来修改普通用户的密码。SET语句的代码如下：

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

3）使用UPDATE语句修改普通用户的密码**（不推荐）**

UPDATE MySQL.user SET authentication_string=PASSWORD("123456")
WHERE User = "username" AND Host = "hostname";

1.7 MySQL8密码管理(了解)

2、权限管理

2.1 权限列表

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.2 授予权限的原则

2.3 授予权限

给用户授权的方式有 2 种，分别是通过把 角色赋予用户给用户授权 和 直接给用户授权

授权命令：

GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址 [IDENTIFIED BY ‘密码口令’];

demo

-- 给li4用户用本地命令行方式，授予atguigudb这个库下的所有表的插删改查的权限。
GRANT SELECT,INSERT,DELETE,UPDATE ON atguigudb.* TO li4@localhost ;

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

2.4 查看权限

1、查看当前用户权限

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

2、查看某用户的全局权限

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

2.5 收回权限

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

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

收回权限命令：

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

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

3、权限表

4、访问控制

4.1 连接核实阶段

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

4.2 请求核实阶段

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

5、角色管理

1、角色的理解

引入角色的目的时方便管理相同权限的用户。只需要给相同权限的用户分配角色即可，而不需要分配具体的权限

2、创建角色

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

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

如果host_name省略，默认为% ， role_name不可省略 ，不可为空。

demo：

CREATE ROLE 'manage'@'localhost';

3、给角色赋予权限

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

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

demo:

grant select,update on dbtest1.* to 'manager'@'localhost';

4、查看角色的权限

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

只要你创建了一个角色，系统就会自动给你一个“ USAGE ”权限，意思是连接登录数据库的权限 。

5、回收角色的权限

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

REVOKE privileges ON tablename FROM 'rolename';

6、删除角色

当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色

DROP ROLE role [,role2]...

7、给用户赋予角色

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

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

在上述语句中，role代表角色，user代表用户。可将多个角色同时赋予多个用户，用逗号隔开即可。

demo： 给用户zhang3赋予manager的角色

grant 'manager'@'localhost' to 'zhang3'@'%';

使用zhang3用户登录，然后查询当前角色，如果角色未激活，结果将显示NONE。SQL语句如
下。

8、为用户激活角色

1、使用set default role 命令激活角色

SET DEFAULT ROLE 角色 TO '用户'@'localhost';

demo：

set 'manager'@'localhost' to 'zhang3'@'localhost';

2、将activate_all_roles_on_login设置为ON

SET GLOBAL activate_all_roles_on_login=ON;

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

9、撤销用户的角色

撤销用户角色的SQL语法如下：

REVOKE role FROM user;

demo

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

10、设置强制角色(mandatory role)

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

方式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'; #系统重启后失效