二.用户与权限管理(二)

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

5.3给角色赋予权限

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

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

上述语句中privileges代表权限的名称，多个权限以逗号隔开。可使用SHOW语句查询权限名称，下图列出了部分权限列表。

SHOW PRIVILEGES\G;

练习1：我们现在想给经理角色授予商品信息表、盘点表和应付账款表的只读权限，就可以用下面的代码来实现

GRANT SELECT ON demo.settlement TO 'manager'; 

GRANT SELECT ON demo.goodsmaster TO 'manager'; 

GRANT SELECT ON demo.invcount TO 'manager';

练习2:

GRANT ALL PRIVILEGES ON app_db.* TO 'app_developer'; -- 给app_db数据库中所有表的所有权限

GRANT SELECT ON app_db.* TO 'app_read'; -- app_db数据库中所有表的查询权限

GRANT INSERT,UPDATE,DELETE ON app_db.* TO 'app_write'; -- app_db数据库中所有表的修改权限

练习3:创建三个角色，分别拥有全部权限、查询权限和读写权限，步骤如下所示
(1) 使用如下SQL语句创建三个角色，角色名为school_admin、school_read、school_write。

CREATE ROLE 'school_admin','school_read','school_write';

(2)给每个角色授予对应的权限，school_admin可以对数据库school中的所有表进行任何操作，school_read只能对数据库school中的表进行查询，school_write可以对数据库school中的表进行读写操作，SQL语句如下。

GRANT ALL PRIVILEGES  ON school.* TO 'school_admin';
GRANT SELECT ON school.* TO 'school_read';
GRANT INSERT,UPDATE,DELETE ON school.* TO 'school_write';

5.4查看角色的权限

赋予角色权限之后，我们可以通过 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”赋予的权限，也就是对商品信息表、盘点表和应付账款表的只读权限。
再举例：来看看库管角色的权限：

mysql> SHOW GRANTS FOR 'stocker';
+------------------------------------------------------------------------+
| Grants for 'stocker'@'%'                                               |
| GRANT USAGE ON *.* TO 'stocker'@'%'                                    |
| GRANT SELECT ON 'demo'.'goodsmaster' To 'stocker'@'%'                  |
| GRANT SELECT,INSERT,UPDATE,DELETE ON 'demo'.'invcount' To 'stocker'@'%'|
+-------------------------
-----------------------------------------------+

结果显示，库管角色拥有商品信息表的只读权限和盘点表的增删改查权限。

5.5回收角色的权限

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

修改了角色的权限，会影响拥有该角色的账户的权限。

撤销角色权限的SQL语法如下：

REVOKE [privileges] ON tablename FROM 'rolename';

练习1：撤销school_write角色的权限。

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

（2）撤销后使用SHOW语句查看school_write对应的权限，语句如下。

SHOW GRANTS FOR 'school_write';

5.6删除角色

当我们需要对业务重新整合的时候，可能就需要对之前创建的角色进行清理，删除一些不会再使用的角色。删除角色的操作很简单，你只要掌握语法结构就行了。

DROP ROLE role [,role2]...

注意， 如果你删除了角色，那么用户也就失去了通过这个角色所获得的所有权限
练习：执行如下SQL删除角色school_read。

DROP ROLE 'school_read';

5.7给用户赋予角色

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

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

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

练习：给wang5用户添加角色school_read权限。
（1）使用GRANT语句给kangshifu添加school_read权限，SQL语句如下。

CREATE USER 'wang5' IDENTIFIED BY '123123';
GRANT 'school_read' TO 'wang5'@'%';

（2）添加完成后使用SHOW语句查看是否添加成功，SQL语句如下。

SHOW GRANTS FOR 'wang5'@'%';

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

SELECT CURRENT_ROLE();

上面结果是NONE,说明用户未具备相应的角色。

或者你用赋予了角色的用户去登录、操作，你会发现，这个账号没有任何权限。这是因为，MySQL中创建了角色之后，默认都是没有被激活，也就是不能用，必须要手动激活，激活以后用户才能拥有角色对应的权限。

5.8激活角色

方式1：使用set default role命令激活角色

SET DEFAULT ROLE 角色名 TO '用户'@'地址';

举例：

-- SET DEFAULT ROLE 'manager'@'%' TO 'wang5'@'%';   -- 单个角色
SET DEFAULT ROLE ALL TO 'wang5'@'%';   -- 所有权限

举例：使用 SET DEFAULT ROLE 为下面4个用户默认激活所有已拥有的角色如下：

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

注意：用户需要退出重新登录，才能看到赋予的角色。

方式二：将activate_all_roles_on_login设置为ON

• 默认情况

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 语句的意思是，对 所有角色永久激活 。运行这条语句之后，用户才真正拥有了赋予角色的所有权限。

5.9撤销用户角色

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

REVOKE role FROM username;

练习：撤销wang5用户的school_read角色。
（1）撤销的SQL语句如下

REVOKE 'school_read' FROM 'wang5'@'localhost';

（2）撤销后，执行如下查询语句，查看wang5用户的角色信息

SHOW GRANTS FOR 'wang5'@'localhost';

执行发现，用户wang5之前的school_read角色已被撤销。

5.10设置强制角色(mandatory role)

强制角色是给每个创建账户的默认角色，不需要手动设置。强制角色无法被REVOKE或者DROP
方式1：服务启动前设置

[mysqld] 
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'; #系统重启后失效

6.配置文件的使用

6.1配置文件格式

与在命令行中指定启动选项不同的是，配置文件中的启动选项被划分为若干个组，每个组有一个组名，用中括号[]扩起来，像这样：

[server]
(具体的启动选项...)

[mysqld]
(具体的启动选项...)

[mysqld_safe]
(具体的启动选项...)

[client]
(具体的启动选项...)

[mysql]
(具体的启动选项...)

[mysqladmin]
(具体的启动选项...)

像这个配置文件里就定义了许多个组，组名分别是server、mysqld、mysqld_safe、client、mysql、mysqladmin。每个组下边可以定义若干个启动选项，我们以[server]组为例来看一下填写启动选项的形式(其他组中启动选项的形式是一样的）:

[server]
option1          #这是option1.该选项不需要选项值
option2-value2   #这是option2.该选项需要选项值

在配置文件中指定启动选项的语法类似于命令行语法，但是配置文件中指定的启动选项不允以加 - -前缀。并且每行只指定一个选项，而且=周围可以有空白字符（命令行中选项名、=、选项值之间不允许有空白字符）。另外，在配置文件中，我们可以使用#来添加注释，从#出现直到行尾的内容都属于注释内容，读取配置文件时会忽略这些注释内容。

6.2 启动命令与选项组

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

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

下面是启动命令能读取的选项组都有哪些：

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

比如,在/etc/mysql/my.cnf这个配置文件中添加一些内容：

[server]
skip-networking
default-storage-engine=MyISAM

然后直接用mysqld启动服务器程序：

mysqld

虽然在命令行没有添加启动选项，但是在程序启动的时候，就会默认的到我们上边提到的配置文件路径下查找配置文件，其中就包括/etc/my.cnf。又由于mysqld命令可以读取[server]选项组的内容，所以skip-networking和default-storage-engine=MyISAM这两个选项是生效的。你可以把这些启动选项放在[client]组里再试试用mysqld启动服务器程序，就不生效。

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

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

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

我们说同一个命令可以访问配置文件中的多个组，比如mysqld可以访问[mysqld]、[server]组，如果在同一个配置文件中，比如~/.my.cnf,在这些组里出现了同样的配置项，比如这样：

[server]
default-storage-engine=InnoDB

[mysqld]
default-storage-engine=MyISAM

那么，将以最后一个出现的组中的启动选项为准，比方说例子中default-storage-engine既出现在[mysqld]组也出现在[server]组，因为[mysqld]组在[server]组后边，就以[mysqld]组中的配置项为准。

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

在命令行上指定的绝大部分启动选项都可以放到配置文件中，但是有一些选项是专门为命令行设计的，比方说defaults-extra-file、defaults-file这样的选项本身就是为归指定配置文件路径的，再放在配置文件中使用就没啥意义了。

如果同一个启动选项既出现在命令行中，又出现在配置文件中，那么以命令行中的启动选项为准！比如我们在配置文件中写了：

[server]
default-storage-engine=InnoDB

而我们的启动命令是：

mysq1.server start --default-storage-engine=MyISAM

那最后default-storage-engine的值就是MyISAM

7.系统变量

7.1系统变量简介

MySQL服务器程序运行过程中会用到许多影响程序行为的变量，它们被称为MySQL系统变量。比如：

max_connections:允许同时连入的客户端数量
fault_storage_engine:表的默认存储引擎用系统变量
query_cache_size:查询缓存的大小
MySQL服务器程序的系统变量有好几百条，就不一 一列举了。

7.2查看系统变量

查看MySQL服务器程序支持的系统变量以及它们的当前值：

#查看所有全局变量
SHOW GLOBAL VARIABLES;

#查看所有会话变量
SHOW SESSION VARIABLES ;
#或
SHOW VARIABLES;

7.3 设置系统变量

7.3.1 通过启动选项设置

大部分的系统变量都可以通过启动服务器时传送启动选项的方式来进行设置。如何填写启动选项我们总结一下
主要是两种方式：

• 通过命令行添加启动选项
  比方说我们在启动服务器程序时用这个命令：

mysqld --default-storage-engine=MyISAM --max-connections=10

• 通过配置文件添加启动选
  我们可以这样填写配置文件：

[server]
default-storage-engine=MyISAM
max-connections=10

当使用上边两种方式中的任意一种启动服务器程序后，我们再来查看一下系统变量的值：

mysql> show variables like 'default_storge_engine';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| default_storge_engine | MyISAM|
+-----------------------+-------+

mysql> show variables like 'max-connections';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| max-connections       |  10  |
+-----------------------+-------+

可以看到default_storage_engine和max_connections这两个系统变量的值已经被修改了。有一点需要注意的是，对于启动选项来说，如果启动选项名由多个单词组成，各个单词之间用短划线-或者下划线_连接起来都可以，但是它对应的系统变量的单词之间必须使用下划线_连接起来。

7.3.2 服务器程序运行过程中设置

对于大部分系统变量来说，它们的值可以在服务器程序运行过程中进行动态修改而无需停止并重启服务器。但是，系统变量有作用范围之分。

设置不同作用范围的系统变量

多个客户端程序可以同时连接到一个服务器程序。对于同一个系统变量，我们有时想让不同的客户端有不同的值。比方说客户端A,他想让当前客户端对应的默认存储引擎为InnoDB,所以他可以把系统变量default_storage_engine的值设置为InnoDB;客户端B,他想让当前客户端对应的默认存储引擎为MyISAM,所以他可以把系统变量defaull_storage_engine的值设置为MyISAM。这样两个客户端拥有不同的默认存储引擎，使用时互不影响，十分方便。但是这样各个客户端都私有一份系统变量会产生这么两个问题：

• 有一些系统变量并不是针对单个客户端的，比如允许同时连接到服务器的客户端数量max_connections,查询缓存的大小query_cache_size,这些公有的系统变量让某个客户端私有显然不合适。
• 一个新连接到服务器的客户端对应的系统变量的值该怎么设置？

为了解决这两个问题，MySQL提出了系统变量作用范围的概念，具体来说作用范围分为这两种：

• GLOBAL 全局变量，影响服务器的整体操作。
• SESSION:会话变量，影响某个客户端连接的操作。（注：SESSION有个别名叫LOCAL)

在服务器启动时，会将每个全局变量初始化为其默认值（可以通过命令行或选项文件中指定的选项更改这些默认值）。然后服务器还为每个连接的客户端维护一组会话变量，客户端的会话变量在连接时使用相应全局变量的当前值初始化。

以default_storage_engine举例，在服务器启动时会初始化一个名为default_storage_engine,作用范围为GLOBAL的系统变量。之后每当有一个客户端连接到该服务器时，服务器都会单独为该客户端分配一个名为default_storage_engine,作用范围为SESSION的系统变量，该作用范围为SESSION的系统变量值按照当前作用范围为GLOBAL的同名系统变量值进行初始化。
很显然，通过启动选项设置的系统变量的作用范围都是GLOBAL的，也就是对所有客户端都有效的，因为在系统启动的时候还没有客户端程序连接进来呢。了解了系统变量的GLOBAL和SESSION作用范围之后，我们再看一下在服务器程序运行期间通过客户端程序设置系统变量的语法：

SET [GLOBAL|SESSION] 系统变量名 = 值；

或者写成这样也行：

SET  [@@(GLOBAL|SESSION).] var_name = XXX ;

比如，服务器运行过程中把作用范围为GLOBAL的系统变量default_storage_engine的值修改为MyISAM,也就是想让之后新连接到服务器的客户端都用MyISAM作为默认的存储引擎，那我们可以选择下边两条语句中的任意一条来进行设置：

#方式一:
SET GLOBAL default_storage_engine = MyISAM;

#方式二：
SET @@GLOBAL.default_storage_engine = MyISAM;

如果只想对本客户端生效，也可以选择下边三条语句中的任意一条来进行设置：

#方式一
SET SESSION default_storage_engine = MyISAM;

#方式二：
SET @@SESSION.default_storage_engine = MyISAM;

#方式三：
SET default_storage_engine = MyISAM;

从上边的方式三可以看出，如果在设置系统变量的语句中省略了作用范围，默认的作用范围就是SESSION。也就是说SET系统变量名 = 值和SET SESSION 系统变量名 = 值是等价的。

查看不同作用范围的系统变量
既然系统变量有作用范围之分，那我们的SHOW VARIABLES语句查看的是什么作用范围的系统变量呢？
答：默认查看的是SESSION作用范围的系统变量。

SHOW [GLOBAL|SESSION] VARIABLES [LIKE 匹配的模式];

小贴士：如果某个客户端改变了某个系统变量在GLOBAL作用范围的值，并不会影响该系统变量在当前已经连接的客户端作用范围为SESSION的值，只会影响后续连入的客户端在作用范围为SESSION的值。

注意事项

并不是所有系统变量都具有GLOBAL和SESSION的作用范围。

• 有一些系统变量只具有GLOBAL作用范围，比方说max_connections,表示服务器程序支持同时最多有多少个客户端程序进行连接。
• 有一些系统变量只具有SESSION作用范围，比如insert_id,表示在对某个包含AUTO_INCREMENT列的表进行插入时，该列初始的值。
• 有一些系统变量的值既具有GLOBAL作用范围，也具有SESSION作用范围，比如我们前边用到的default_storage_engine,而且其实大部分的系统变量都是这样的
• 有些系统变量是只读的，并不能设置值。比方说version,表示当前MySQL的版本，我们客户端是不能设置它的值的，只能在SHOW VARIABLES语句里查看。