第十章 - 权限管理及安全控制

第十章 - 权限管理及安全控制

数据库的安全性是指保护数据库以防止不合法使用所造成的数据泄露、更改或破坏。系统安全保护措施是否有效是数据库系统主要的性能指标之一。数据库的安全性与计算机系统的安全性紧密联系，数据库管理系统提供的主要技术有强制存取控制、数据加密存储和加密传输等。

控制数据存取流程 ，通过用户标识和鉴定、存取控制、视图、审计和数据加密等方法，将非法用户和不具备完整性的数据进行特别处理。

在MySQL数据库管理系统中，主要是通过用户权限管理实现其安全性控制的。当在服务器上运行MySQL时，数据库管理员的职责就是要想方设法使MySQL免遭用户的非法侵入，拒绝其访问数据库，保证数据库的安全性和完整性。

10.1 MySQL权限系统的工作原理

了解MySQL数据库的安全性，首先要了解MySQL的访问控制系统，掌握MySQL权限系统的工作原理，熟悉其权限操作。

当MySQL服务启动时，首先会读取mysql中的权限表，并将表中的数据装入内存。当用户进行存取操作时，MySQL会根据这些表中的数据做相应的权限控制。

10.1.1 MySQL的权限表

通过网络连接服务器的客户对MySQL数据库的访问由权限表内容来控制。

用户登录以后，MySQL数据库系统会根据这些权限表的内容为每个用户赋予相应的权限。这些权限表中最重要的是user表、db表和host表。除此之外，还有tables_priv表、columns_priv表、proc_priv表等。

1. user表

user表是MySQL中最重要的一个权限表，记录允许连接到服务器的账号信息。user表列出可以连接服务器的用户及其口令，并且指定他们有哪种全局（超级用户）权限。

在user表启用的任何权限均是全局权限，并适用于所有数据库。MySQL 5.7中user表有45个字段，这些字段共分为4类，分别是用户列、权限列、安全列和资源控制列。

利用 mysql> desc user; 可以查看user表结构。

mysql> use mysql;
mysql> desc user;

2. db表和host表

db表和host表也是MySQL数据库中非常重要的权限表。

• db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库。

• host表中存储了某个主机对数据库的操作权限，配合db权限表对给定主机上数据库级操作权限做更细致的控制。

这个权限表不受grant和revoke语句的影响。db表比较常用，host表一般很少使用。db表和host表的字段大致可以分为两类，分别是用户列和权限列。

用户列

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

db 表的用户列如下表所示：

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机名
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名

权限列

db 表中的权限列和 user 表中的权限列大致相同，只是user 表中的权限是针对所有数据库的，而 db 表中的权限只针对指定的数据库。如果希望用户只对某个数据库有操作权限，可以先将 user 表中对应的权限设置为 N，然后在 db 表中设置对应数据库的操作权限。

3. tables_priv表和columns_priv表

• tables_priv表

tables_priv表可以对单个表进行权限设置，tables_priv表包含8个字段，分别是Host、Db、User、Table_name、Table_priv、Column_priv、Timestamp和Grantor。

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名
Table_name	char(64)	NO	无	表名
Grantor	char(93)	NO	无	修改该记录的用户
Timestamp	timestamp	NO	CURRENT_TIMESTAMP	修改该记录的时间
Table_priv	set('Select','Insert','Update','Delete', 'Create','Drop','Grant','References', 'Index','Alter','Create View', 'Show view','Trigger')	NO	无	表示对表的操作权限，包括 Select、Insert、Update、Delete、Create、Drop、Grant、References、Index 和 Alter 等
Column_priv	set('Select','Insert', 'Update','References')	NO	无	表示对表中的列的操作权限，包括 Select、Insert、Update 和 References

• columns_priv表

columns_priv表可以对单个数据列进行权限设置，包含7个字段。Columns_priv表示对表中的数据列进行操作的权限。这些权限包括Select、Insert、Update和References。

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名
Table_name	char(64)	NO	无	表名
Column_name	char(64)	NO	无	数据列名称，用来指定对哪些数据列具有操作权限
Timestamp	timestamp	NO	CURRENT_TIMESTAMP	修改该记录的时间
Column_priv	set('Select','Insert','Update','References')	NO	无	表示对表中的列的操作权限，包括 Select、Insert、Update 和 References

4. procs_priv表

procs_priv表可以存储过程和存储函数进行权限设置。

字段名	字段类型	是否为空	默认值	说明
Host	char(60)	NO	无	主机名
Db	char(64)	NO	无	数据库名
User	char(32)	NO	无	用户名
Routine_name	char(64)	NO	无	表示存储过程或函数的名称
Routine_type	enum('FUNCTION','PROCEDURE')	NO	无	表示存储过程或函数的类型，Routine_type 字段有两个值，分别是 FUNCTION 和 PROCEDURE。FUNCTION 表示这是一个函数；PROCEDURE 表示这是一个存储过程。
Grantor	char(93)	NO	无	插入或修改该记录的用户
Proc_priv	set('Execute','Alter Routine','Grant')	NO	无	表示拥有的权限，包括 Execute、Alter Routine、Grant 3种
Timestamp	timestamp	NO	CURRENT_TIMESTAMP	表示记录更新时间

10.1.2 MySQL权限系统的工作过程

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

1. 连接核实阶段

当用户试图连接MySQL服务器时，服务器基于用户提供的信息来验证用户身份，如果不能通过身份验证，服务器会完全拒绝该用户的访问。如果能够通过身份验证，则服务器接受连接，然后进入第2个阶段等待用户请求。

2. 请求核实阶段

一旦连接得到许可，服务器进入请求核实阶段。在这一阶段，MySQL服务器对当前用户的每个操作都进行权限检查，判断用户是否有足够的权限来执行它。用户的权限保存在user、db、host、tables_priv或columns_priv权限表中。

在MySQL权限表的结构中，user表在最顶层，是全局级的。下面是db表和host表，它们是数据库层级的。最后才是tables_priv表和columns_priv表，它们是表级和列级的。低等级的表只能从高等级的表得到必要的范围或权限。

MySQL接受到用户的操作请求时，首先确认用户是否有权限。如果没有，则MySQL首先检查user表，即先检查全局权限表user，如果user中对应的权限为T（有），则此用户对的所有数据库的权限为T，将不再检查db、tables_priv、columns_priv；如果为F（无），则从db表中检查此用户对应的具体数据库，并得到db中的T的权限；

如果db中为F，则检查tables_priv及columns_priv表中此数据库对应的具体表，取得表中的权限T，以此类推。如果所有权限表都检查完毕，依旧没有找到允许的权限操作，MySQL服务器将返回错误信息，用户操作不能执行，操作失败。

10.2 账户管理

账户管理是MySQL用户管理的最基本的内容。账户管理包括登录和退出MySQL服务器、创建用户、删除用户、密码管理、权限管理等内容。通过账户管理，可以保证MySQL数据库的安全性。

10.2.1 普通用户的管理

MySQL用户包括普通用户和root用户。这两种用户的权限是不一样的。root用户是超级管理员，拥有所有的权限。

root用户的权限包括创建用户、删除用户、修改普通用户的密码等管理权限。而普通用户只拥有创建该用户时赋予它的权限。用户管理包括管理用户的帐户、权限等。

1. 创建新用户

在MySQL数据库中，创建新用户可以直接操作MySQL权限表，也可以使用create user语句或grant语句。

(1) 使用 create user 语句创建新用户

要使用create user语句，必须拥有mysql数据库的全局create user权限，或拥有insert权限。对于每个账户，create user会在没有权限的mysql.user表中创建一个新记录。

如果账户已经存在，则出现错误。使用自选的identified by子句，可以为账户设置一个密码。user值和密码的设置方法和grant语句一样。

执行create user或grant语句时，服务器会有相应的用户权限表，添加或修改用户及其权限。

create user语句的基本语法格式如下:

create user user[identified by [password]'password'] 
	[,user[identified by [password]'password']][,…]; 

用户名的格式为：用户名称@ 主机名。

CREATE USER用于创建新的MySQL账户。CREATE USER会在系统本身的mysql数据库的user表中添加一个新记录。要使用CREATE USER，必须拥有mysql数据库的全局CREATE USER权限或INSERT权限。如果账户已经存在，则出现错误。

【例10.1】 添加两个新用户，Hans的密码为hans131，Rose的密码为rose123

mysql> create user
    -> 'Hans'@'localhost' identified by ' hans131',
    -> 'Rose'@'localhost' identified by ' rose123‘;

说明：

在用户名的后面声明了关键字localhost。这个关键字指定了用户创建的使用MySQL的连接所来自的主机。如果一个用户名和主机名中包含特殊符号如“_”，或通配符如“%”，则需要用单引号将其括起。“%”表示一组主机。

如果两个用户具有相同的用户名但主机不同，MySQL将其视为不同的用户，允许为这两个用户分配不同的权限集合。

如果没有输入密码，那么MySQL允许相关的用户不使用密码登录。但是从安全的角度并不推荐这种做法。

刚刚创建的用户还没有很多权限。它们可以登录到MySQL，但是它们不能使用USE语句来让用户已经创建的任何数据库成为当前数据库，因此，它们无法访问那些数据库的表，只允许进行不需要权限的操作，例如，用一条SHOW语句查询所有存储引擎和字符集的列表。

【例10.2】 添加一个新用户，用户名为Pool，密码为136792，不指定明文。

操作步骤、代码和运行结果如下：

① 使用password()函数获取密码'136792'的散列值。

mysql> select password('136792');
+-------------------------------------------+
| password('136792')                             |
+-------------------------------------------+
| *BB3D238C77F04751017773C8CFFFB291BFF7427C |
+-------------------------------------------+

② 执行create user语句创建用户Pool。

mysql> create user 'Pool'@'localhost'
    -> identified by password'*BB3D238C77F04751017773C8CFFFB291BFF7427C‘;

(2) 使用 grant 语句创建新用户

grant语句不仅可创建新用户，还可以在创建的同时对用户授权。

grant语句还可以指定用户的其他特点，如安全连接、限制使用服务器资源等。使用grant语句创建新用户时必须有grant权限。

grant语句的基本语法格式如下。

grant priv_type on database.table 
to user[identified by[password] 'password'] 
 [,user[identified by[password] 'password']][,…] 
[with grant option]; 

【例10.3】 使用grant语句创建一个新用户test11，主机名为localhost，密码为test131，并授予所有数据表的select和update权限。

mysql> grant select,update on *.* to 'test11'@'localhost'
    -> identified by ‘test131’;

其中，*.* 表示对用户test131设置全局权限。利用 select user from user; 语句可以验证新用户的添加是否成功。

2. 删除普通用户

如果存在一个或是多个帐户被闲置，应当考虑将其删除，确保不会用于可能的违法的活动。利用 drop user 命令就能很容易地做到，它将从权限表中删除用户的所有信息，即来自所有授权表的帐户权限记录。

在MySQL数据库中，可以使用 drop user 语句来删除普通用户，也可以直接在mysql.usr表中删除用户。

(1) 使用 drop user 语句删除用户。

drop user的语法格式如下。

drop user user_name[, user_name] [,…]; 

drop user语句用于删除一个或多个MySQL账户，并取消其权限。要使用drop user，必须拥有mysql数据库的全局create user权限或delete权限。

drop user不能自动关闭任何打开的用户对话。如果用户有打开的对话，则取消用户，命令不会生效，直到用户对话被关闭后才生效。一旦对话被关闭，用户也被取消，此用户再次试图登录时将会失败。

例如，删除用户TOM1命令如下。

mysql>drop user TOM1@localhost; 

(2) 使用delete语句删除用户

delete语句的基本语法格式如下:

delete from mysql.user where host='hostname' and user='username'; 

其中，host和user为user表中的两个字段。

例如，使用delete删除用户test11的命令如下：

mysql>delete from mysql.user where host='localhost'and user='test11'; 

使用select语句查询user表中的记录，可以验证删除操作是否成功。

如果删除的用户已经创建了表、索引或其他的数据库对象，这些数据库对象将继续存在，因为MySQL并没有记录是谁创建了这些对象。

3. 修改用户名称

可以使用rename user语句来实现。如果旧账户不存在或者新账户已存在，则会出现错误。

使用rename user语句修改用户的基本语法格式如下：

rename user old_user to new_user,[,old_user to new_user] [,…]; 

例如，将用户king11和king12的名字分别修改为king1和king1。可以使用如下命令：

mysql> rename user
     -> 'king11'@'localhost' to 'king1'@'localhost', 
     -> 'king12'@'localhost' to 'king2'@'localhost';

10.2.2 mysql命令的使用

用户可以通过mysql命令来登录MySQL服务器。前面已经简单介绍过一些登录MySQL服务器的方法，但是有些参数还不全。

1. 登录MySQL服务器

启动MySQL服务后，可以通过mysql命令来登录MySQL服务器。完整的mysql命令如下：

mysql -h hostname|hostIP -P port -u username -p 
Database Name -e "SQL statements"；

2. 修改用户密码

要修改某个用户的登录密码，可以使用mysqladmin命令、update语句或set password语句来实现。

(1) root用户修改自己的密码。

root用户的安全对于保证MySQL的安全非常重要，因为root用户拥有全部权限。修改root用户密码的方式有多种。

① 使用 mysqladmin 命令

mysqladmin命令的基础语法格式如下

mysqladmin –u username  –h localhost –p  password "newpassword"; 

例如，可以使用mysqladmin命令将root用户的密码修改为“rootpwd”。

mysql>mysqladmin  –u root –p password "rootpwd"; 
Enter password: *******

② 使用 update 语句修改 mysql 数据库中的 user 表

因为所有账户信息都保存在user表中，因此可以通过直接修改user表来改变root用户的密码。root用户登录到MySQL服务器后，使用update语句修改mysql数据库中的user表的password字段值，从而修改用户密码。

使用update语句修改root用户密码的语句如下。

mysql>update mysql.user set   password=password('newpassword') 
    where user='root' and host='localhost'; 

(2) 使用set语句修改用户密码

其基本语法格式如下。

set  password [for user]= password('newpassword'); 

例如，将用户test11的密码修改为test12。

mysql>set password for  'test11'@'localhost' =password('test12');

10.3 权限管理

权限管理主要是对登录到MySQL服务器的用户进行权限验证。所有用户的权限都存储在MySQL的权限表中。合理的权限管理能够保证数据库系统的安全，不合理的权限设置会给MySQL服务器带来安全隐患。

10.3.1 MySQL的权限类型

MySQL数据库中有多种类型的权限，这些权限都存储在mysql数据库的权限表中。在MySQL启动时，服务器将这些数据库中的权限信息读入内存。

grant和revoke命令用来管理访问权限，也可以用来创建和删除用户，但在MySQL5.7中可以利用create user和drop user命令更容易地实现这些任务。

如果授权表拥有含有mixed-case数据库或表名称的权限记录，并且lower_case_table_names系统变量已设置，则不能使用revoke撤销权限，必须直接操纵授权表。（当 lower_case_table_names已设置时，grant将不会创建此类记录，但是此类记录可能已经在设置变量之前被创建了。）

授予的权限可以分为多个层级：

1. 全局层级

全局权限适用于一个给定服务器中的所有数据库。这些权限存储在mysql.user表中。grant all on .和revoke all on .只授予和撤销全局权限。

2. 数据库层级

数据库权限适用于一个给定数据库中的所有目标。这些权限存储在mysql.db和mysql.host表中。grant all on db_name.和revoke all on db_name.只授予和撤销数据库权限。

3. 表层级

表权限适用于一个给定表中的所有列。这些权限存储在mysql.tables_priv表中。grant all on db_name.tbl_name和revoke all on db_name.tbl_name只授予和撤销表权限。

4. 列层级

列权限适用于一个给定表中的 单一列。这些权限存储在mysql.columns_priv表中。当使用revoke时，您必须指定与被授权列相同的列。采用select(col1, col2…)、insert(col1, col2…)和update(col1, col2…) 的格式实现。

5. 子程序层级

create routine，alterroutine，execute和grant等权限适用于已存储的子程序。这些权限可以被授予为全局层级和数据库层级。而且，除了create routine外，这些权限可以被授予为子程序层级，并存储在mysql.procs_priv表中。

grant和revoke命令对于谁可以操作服务器及其内容的各个方面提供了多程度的控制，从谁可以关闭服务器，到谁可以修改特定表字段中的信息都能控制。
表10-1中列出了使用这些命令可以授予或撤回的常用权限。

10.3.2 授权管理

授权就是为某个用户授予权限。在MySQL中，可以使用grant语句为用户授予权限。新创建的用户还没任何权限，不能访问数据库，不能做任何事情。针对不同用户对数据库的实际操作要求，分别授予用户对特定表的特定字段、特定表、数据库的特定权限。

1. 利用grant语句给用户授权

在MySQL中使用grant关键字来为用户设置权限。必须是拥有grant权限的用户才可以执行grant语句。

grant语句的基本语法格式如下:

grant priv_type[(column_list)][,priv_type[(column_list)]][,…n] 
on {db_name.*|*.*|database_name.*|database_name.table_name} 
to user[identified by [password] 'password'] 
[,user[identified by [password] 'password']] [,…n] 
[with grant option]; 

(1) 授予MySQL字段级别权限

GRANT 权限名称(列名[,列名,…])[, 权限名称(列名[,列名,…]),…]
  ON TABLE  数据库名.表名或视图名
  TO 用户[,用户,…]
  [WITH GRAND OPTION];

USE  mysql;
 
CREATE USER column_user@localhost   
    IDENTIFIED  BY 'password';
 
GRANT  SELECT(ename,sal,empno),UPDATE(sal)  
   ON TABLE scott1.emp 
   TO column_user@localhost  
   WITH GRANT OPTION;

(2) 授予MySQL表级别权限

GRANT 权限名称[, 权限名称,…]
  ON TABLE 数据库名.表名或视图名
  TO 用户[,用户,…]
  [WITH GRAND OPTION];

USE mysql;
 
CREATE USER table_user@localhost 
     IDENTIFIED BY 'password';
 
GRANT ALTER,SELECT,INSERT(empno,ename) 
 ON TABLE scott1.emp
 TO table_user@localhost;

-- 以table_user用户连接MySQL服务器，执行如下语句。
DESC scott1.emp;

ALTER TABLE scott1.emp
 MODIFY COLUMN  empno INT;

(3) 授予MySQL存储程序级别权限

GRANT 权限名称[, 权限名称,…]
 ON  FUNCTION|PROCEDURE 
   数据库名.函数名｜数据库名.存储过程名
 TO 用户[,用户,…]
[WITH GRAND OPTION];

USE  mysql;
 
CREATE USER proc_user@localhost  IDENTIFIED BY 'password';
 
GRANT  EXECUTE  ON PROCEDURE scott1.emp_p
 TO  proc_user@localhost;
 
GRANT  ALTER ROUTINE,EXECUTE  ON FUNCTION scott1.sum_fn  TO proc_user@localhost;

-- 以proc_user用户连接MySQL服务器，执行如下语句。
CALL scott1.emp_p;
SELECT scott1.sum_fn(3);
DROP FUNCTION scott1.sum_fn;

(4) 授予MySQL数据库级别权限

在GRANT语法格式中，授予数据库权限时ON关键字后面跟 * 和 库名.* 。* 表示当前数据库中的所有表；库名.*表示某个数据库中的所有表。

GRANT 权限名称[, 权限名称,…]
 ON  数据库名.*
 TO 用户[,用户,…]
[WITH GRAND OPTION];

USE mysql;
 
CREATE USER database_user@localhost IDENTIFIED BY 'password';
 
GRANT CREATE,SELECT,DROP ON scott1.* TO database_user@localhost;
 
SELECT * FROM db 
 WHERE host='localhost' and db='scott1';

-- 以database_user用户连接MySQL服务器，执行如下语句。
CREATE TABLE scott1.employee
( empno INT NOT NULL PRIMARY KEY,
  ename  VARCHAR(10)
);
DROP TABLE scott1.employee;

(5) 授予MySQL服务器管理员级别权限

GRANT 权限名称[, 权限名称,…]
 ON  *.*
 TO 用户[,用户,…]
[WITH GRAND OPTION];

USE mysql;
 
CREATE USER server_user@localhost IDENTIFIED BY 'password';
 
GRANT ALL PRIVILEGES 
 ON *.*
 TO server_user@localhost;
 
SELECT * FROM user
 WHERE host='localhost' and user='server_user';

-- 以sever_user用户连接MySQL服务器，执行如下语句。
CREATE DATABASE student;

【例10.4】使用grant语句创建一个新用户grantuser，密码为grantpass。用户grantuser对所有的数据有查询、插入权限，并授予grant权限。

mysql> grant select,insert on *.* to 'grantuser'@'localhost'
    -> identified by 'grantpass'
    -> with grant option;

若在TO子句中给存在的用户指定密码，则新密码将原密码覆盖。如果权限授予了一个不存在的用户，MySQL会自动执行一条CREATE USER语句来创建这个用户，但必须为该用户指定密码。

【例10.5】 使用grant语句将teaching数据库中student表的delete权限授予用户grantuser。

mysql> grant delete on teaching.student 
    -> to 'grantuser'@'localhost‘;

【例10.6】授予grantuser在student表上的studentno列和sname列的update权限。

mysql> grant  update(studentno, sname)
    -> on  student
    -> to  grantuser@localhost;

【例10.7】授予用户grantuser为teaching数据库创建存储过程和存储函数权限。

mysql> grant create routine  on  teaching.*  
    -> to grantuser@localhost;

2. 利用grant语句实现权限转移

grant语句的最后可以指定为with grant option，则表示子句中指定的所有用户都有把自己所拥有的权限授予其他用户的权利，而不管其他用户是否拥有该权限。

【例10.8】授予grantuser用户select, insert, update, delete, create,drop权限，同时允许将其本身权限转移给其他用户

mysql> grant select,insert,update,delete,create,drop
    -> on  teaching.*  to grantuser@localhost
    -> with  grant option;

10.3.3 收回权限

收回权限就是取消已经赋予用户的某些权限。收回用户不必要的权限在一定程度上可以保证数据的安全性。权限收回后，用户账户的记录将从db、host、tables_priv和columns_priv表中删除，但是用户账户记录仍然在user表中保存。收回权限利用revoke语句来实现，语法格式有两种，一种是收回用户指定的权限，另一种是收回用户的所有权限。

1. 收回指定权限

收回用户指定权限的基本语法如下:

revoke priv_type[(column_list)][,priv_type[(column_list)]][,…n]        on{table_name|*|*.*|database_name.*|database_name.table_name} 
       from 'username'@'hostname'[,'username'@'hostname'][,…n]; 

REVOKE 权限名称[(列名[,列名,…])]
                 [,权限名称[(列名[,列名,…])],…]
 ON  *.*|数据库名.*|数据库名.表名或视图名
 FROM  用户[,用户,…];

【例10.9】 收回grantuser用户对teaching数据库中student表的update权限。

mysql> revoke update on teaching.student 
    -> from grantuser@localhost; 

(2) 撤消所有权限

REVOKE ALL PRIVILEGES,GRANT OPTION
    FROM 用户[,用户,…];

10.3.4 查看权限

show grants语句可以显示指定用户的权限信息。

使用show grants查看账户权限信息的基本语法格式如下。

show grants for 'username'@'hostname'; 

【例10.10】例如，使用show grants语句查看grantuser用户的权限信息。

mysql>show grants for grantuser@localhost;    

10.3.5 限制权限

with子句也可以通过下列参数实现对一个用户授予使用限制，其中，count表示次数。

（1）max_queries_per_hour count表示每小时可以查询数据库的次数。

（2）max_connections_per_hour count表示每小时可以连接数据库的次数。

（3）max_updates_per_hour count表示每小时可以修改数据库的次数。

（4）MAX_USER_CONNECTIONS count 表示同时连接MySQL的最大用户数。

count是一个数值，对于前三个指定，次数如果为0则表示不起限制作用。

【例10.11】 授予grantuser每小时只能处理一条select语句的权限。

mysql> grant select
    -> on teaching.student
    -> to grantuser@localhost
    -> with max_queries_per_hour 1;

【例10.12】授予grantuser每小时可以发出的查询数20次，每小时可以连接数据库5次，每小时可以发出的更新数为10次。

mysql> grant all on *.* to grantuser@localhost
    -> identified by 'grantpass'
    -> with max_queries_per_hour 20
    -> max_updateS_per_hour 10
    -> max_connections_per_hour 5;

10.4 MySQL数据库安全常见问题

10.4.1 权限更改何时生效

MySQL服务器启动时以及使用grant和revoke语句时，服务器会自动读取grant表。这就为当手动更新这些权限表提供了方法。当手动更新权限表时，MySQL服务器将不会自动监测到这些修改的权限。

有3种方法可以实现让服务器完善这些修改权限，使之生效。最常使用的更新权限的方法。

在MySQL命令提示符下（以管理员的身份登录进入）键入如下命令：

flush privileges; 

或者，还可以在操作系统中运行：

mysqladmin flush-privileges 

或者是：

mysqladmin reload 

此后，当用户下次再连接的时候，系统将检查全局级别权限；当下一个命令被执行时，将检查数据库级别的权限；而表级别和列级别权限将在用户下次请求的时候被检查。

10.4.2 设置账户密码

1. 在DOS命令窗口中指定密码

利用mysqladmin命令重设服务器为host_name，且用户名为user_name的用户的密码，新密码为newpassword。

mysqladmin -u user_name -h host_name password  'newpassword'

2. 通过set password命令设置用户的密码

只有以root用户或有更新mysql数据库权限的用户的身份登录，才可以更改其他用户的密码。

mysql> set password for 'pool'@'%' = password('base123456'); 

如果你没有以匿名用户连接，省略for子句便可以更改自己的密码：

mysql> set password = password('base123456'); 

3. 使用grant usage语句指定账户密码

在全局层级下使用grant usage语句(在.)指定某个账户的密码，而不影响账户当前的权限：

mysql> grant usage on *.* to 'pool'@'%' identified by 'base123456'; 

4. 在创建新账户时建立密码，要为password列提供一个具体值

mysql -u root mysql insert into user (Host,User,Password)
            values('%','pool33',password('base123456')); 

mysql> flush privileges; 

5. 更改已有账户的密码，要应用update语句来设置password列值

mysql -u root mysql 
update user set Password = password('base123456') 
where Host ='%' and User = 'test11'; 
mysql> flush privileges;

10.4.3 使密码更安全

MySQL授权user表定义初始MySQL用户账户和访问权限。在Windows中， MySQL一般创建了两个root账户。一个root账户用来从本机连接MySQL服务器，具有所有权限；另一个允许从任何主机连接，具有test数据库或其它以test开始的数据库的所有权限。

初始账户均没有密码，因此任何人可以用root账户不用任何密码来连接MySQL服务器。

一般情况下，MySQL创建了两个匿名用户账户，每个账户的用户名均为空。匿名账户没有密码，因此任何人可以使用匿名账户来连接MySQL服务器。

在Windows中，一个匿名账户用来从本机进行连接。也具有所有权限，同root账户一样。另一个可以从任何主机上连接，具有test数据库或其它以test开始的数据库的所有权限。为了更好的保证MySQL数据库的安全，可以采取如下措施。

1. 为root账户指定密码，使用set password语句为匿名账户指定密码

使用set password语句指定root账户密码的命令如下：

mysql> set password for root@localhost = password('123456');

mysql> set password for ''@'localhost' = password('1234546'); 

或者使用update语句修改root账户密码，要注意使用password()函数为密码加密的限制。使用 update 更新密码后，必须让服务器用 flush privileges 重新读授权表。命令如下：

mysql> update  mysql.user  set Password = password('123456')
    -> where User = 'root';

mysql> update  mysql.user  set Password = password('123456')
    -> where User = '';

mysql> flush privileges; 

2. 直接删除匿名账户，可以避免匿名访问用户的危害

命令如下：

mysql> delete  from  mysql.user where  User = ''; 

10.4.4 要确保MySQL的安全的注意事项

（1）管理员在管理用户级别时，切忌不能将mysql.user表的访问权限授予任何一般账户。

（2）如果从非交互式方式下运行一个脚本调用一个客户端，就没有从终端输入密码的机会。其最安全的方法是让客户端程序提示输入密码或在适当保护的选项文件中指定密码。

（3）可以采用下面的命令模式来连接服务器，以此来隐藏你的密码。命令如下：

mysql -u laisone  -p  db_name 
Enter password: ******** 

（4）审计服务器的用户帐户。当已有的服务器作为公司的数据库主机时，要确保禁用所有非特权用户，或者最好是全部删除。虽然MySQL用户和操作系统用户完全无关，但他们都要访问服务器环境，仅凭这一点就可能会有意地破坏数据库服务器及其内容。

（5）设置MySQL的root用户密码。对所有MySQL用户使用密码。客户端程序不需要验证运行它的人员的身份。对于客户端/服务器应用程序，用户可以指定客户端程序的用户名。例如，如果abc_user没有密码，任何人可以简单地用mysql -u other_user db_name冒充他人调用mysql程序进行连接。如果所有用户帐户均存在密码，使用其它用户的账户进行连接将困难得多。

（6）及时下载安装补丁软件。为操作系统和安装软件下载安装补丁软件是屏蔽恶意用户攻击的常用方法，否则，及时恶意用户没有多少攻击经验，也可以毫无阻碍地攻击未打补丁的服务器。

（7）禁用所有不使用的系统服务。始终要注意在将服务器放入网络之前，已经消除所有不必要的潜在服务器攻击途径

（8）关闭未使用的端口。虽然关闭未使用的系统服务是减少成功攻击可能性的好方法，不过还可以通过关闭未使用的端口来添加第二层安全。对于专用的数据库服务器，如果不希望在指定端口有数据通信，就关闭这个端口。

10.5 角色管理

我们介绍了 MySQL 权限管理相关知识。当数据库实例中存在大量的库或用户时，权限管理将会变得越来越繁琐，可能要频繁进行权限变更。MySQL 8.0 新增了 role 功能，使得权限管理更加方便，本篇文章我们来看下 8.0 下的 role 功能。

10.5.1 role 简介

role 角色功能对于 Oracle 数据库来说不算是什么特殊，在 Oracle 中经常被用到。MySQL 8.0 版本终于新增了 role 功能，为数据库用户权限管理提供了一种新思路。

role 可以看做一个权限的集合，这个集合有一个统一的名字 role 名。可以给多个数据库用户授予同个 role 的权限，权限变更可直接通过修改 role 来实现，不需要每个用户一个一个的去变更，方便运维和管理。role 可以创建、删除、修改并作用到它管理的用户上。

角色是具有名称的一组相关权限的组合，即将不同的权限集合在一起就形成了角色。

可以使用角色为用户授权，同样也可以撤销角色。由于角色集合了多种权限，所以当为用户授予角色时，相当于为用户授予了多种权限。

这样就避免了向用户逐一授权，从而简化了用户权限的管理。

下面我们具体来体验下 role 角色功能：

1．创建角色

CREATE  ROLE   角色;

【例5-13】分别在本地主机上创建应用程序角色app、运维人员角色ops、开发人员读角色dev_read、开发人员写角色dev_write。

USE mysql;
 
CREATE ROLE 'app'@'localhost','ops'@'localhost',
                            'dev_read'@'localhost','dev_write'@'localhost';

2．授予角色权限

【例5-14】分别授予角色app数据读写权限、角色ops访问数据库权限、角色dev_read读取权限、角色dev_write写权限。

GRANT SELECT,INSERT,UPDATE,DELETE
 ON  SCOTT1.*  TO 'app'@'localhost';
 
GRANT ALL PRIVILEGES
 ON  SCOTT1.*  TO 'ops'@'localhost';
 
GRANT SELECT
 ON  SCOTT1.*  TO 'dev_read'@'localhost';
 
GRANT INSERT,UPDATE,DELETE
 ON  SCOTT1.*  TO 'dev_write'@'localhost';

3．授予用户角色

GRANT 角色[,角色,…] TO 用户[,用户,…];

【例5-15】分别将角色授予新用户app01、ops01、dev01、dev02、dev03。

-- 创建新的用户账号
CREATE USER 'app01'@'%'  IDENTIFIED BY  '000000';
 
CREATE USER 'ops01'@'%'  IDENTIFIED BY  '000000'; 
 
CREATE USER 'dev01'@'%'  IDENTIFIED BY  '000000'; 
 
CREATE USER 'dev02'@'%'  IDENTIFIED BY  '000000';
 
CREATE USER 'dev03'@'%'  IDENTIFIED BY  '000000';
 
-- 给用户账号分配角色
GRANT 'app'@'localhost' TO 'app01'@'%';
 
GRANT 'ops'@'localhost' TO 'ops01'@'%';
 
GRANT 'dev_read'@'localhost' TO 'dev01'@'%';
 
GRANT 'dev_read'@'localhost','dev_write'@'localhost'
  TO  'dev02'@'%','dev03'@'%';
  
-- 验证角色是否正确分配，可使用SHOW GRANTS语句
SHOW GRANTS FOR 'dev01'@'%' USING 'dev_read'@'localhost';

4．撤消用户角色

REVOKE 角色[,角色,…] FROM 用户[,用户,…];

【例5-16】撤消用户app01的角色app。

REVOKE 'app'@'localhost' FROM 'app01'@'%';

SHOW GRANTS FOR 'app01'@'%' USING 'app'@'localhost';

5．删除角色

DROP USER 角色[,角色,…];

【例5-17】删除角色app和ops。

DROP ROLE 'app'@'localhost','ops'@'localhost';

10.5.2 激活角色

赋予用户某个角色权限后，该用户并没有获得相应权限。

出现上述情况的原因是，在用户会话中，授予该用户的角色处于非活动状态。只有授予的角色在会话中处于活动状态时，该用户才拥有此角色的权限，要确定当前会话中哪些角色处于活动状态，可以使用 CURRENT_ROLE() 函数。

# 使用 set default role 命令激活角色
mysql> SET DEFAULT ROLE ALL TO dev1;
Query OK, 0 rows affected (0.77 sec)
 
# 重新登录 发现权限正常
[root@localhost ~]# mysql -udev1 -p123456
 
mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `dev_role`@`%` |
+----------------+
1 row in set (0.57 sec)

除了使用 set default role 命令激活角色外，还可以修改系统变量 activate_all_roles_on_login ，该变量决定是否自动激活 role ，默认为 OFF 即不自动激活，建议将该变量改为 ON ，这样以后赋予角色给新用户后就不需要再手动激活了。

# 查看 activate_all_roles_on_login 变量
mysql> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF   |
+-----------------------------+-------+
1 row in set (1.53 sec)
 
# 启用该变量 先动态启用 之后可以将此参数加入my.cnf配置文件中
mysql> set global activate_all_roles_on_login = on;
Query OK, 0 rows affected (0.50 sec)
 
# 之后角色就会自动激活
mysql> create user 'dev2'@'%' identified by '123456';
Query OK, 0 rows affected (0.68 sec)
 
mysql> grant 'dev_role' to 'dev2'@'%';
Query OK, 0 rows affected (0.38 sec)
 
root@localhost ~]# mysql -udev2 -p123456
 
mysql> select CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| `dev_role`@`%` |
+----------------+
1 row in set (0.57 sec)

10.5.3 role 相关操作

上面我们介绍了创建角色及给用户授予角色权限，关于 role 相关操作还有很多，我们接着来看下。

# 变更角色权限
mysql> grant select on db2.* to 'dev_role'@'%';
Query OK, 0 rows affected (0.33 sec)
 
# 拥有该角色的用户 重新登录后权限也会对应变化
root@localhost ~]# mysql -udev1 -p123456
 
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| db1                |
| db2                |
| information_schema |
+--------------------+
3 rows in set (2.01 sec)
 
# 回收角色权限
mysql> revoke SELECT ON db2.* from 'dev_role'@'%';
Query OK, 0 rows affected (0.31 sec)
 
# 撤销用户的角色
mysql> revoke 'dev_role'@'%' from 'dev1'@'%';
Query OK, 0 rows affected (0.72 sec)
 
mysql> show grants for 'dev1'@'%';
+----------------------------------+
| Grants for dev1@%                |
+----------------------------------+
| GRANT USAGE ON *.* TO `dev1`@`%` |
+----------------------------------+
1 row in set (1.06 sec)
 
# 删除角色 (删除角色后 对应的用户也会失去该角色的权限)
mysql> drop role dev_role;
Query OK, 0 rows affected (0.89 sec)

我们还可以通过 mandatory_roles 变量来配置强制性角色。使用强制性角色，服务器会为全部的用户户默认赋予该角色，而不需要显示执行授予角色。可以使用 my.cnf 文件或者使用 SET PERSIST 进行配置，例如：

# my.cnf 配置
[mysqld]
mandatory_roles='dev_role'
    
# set 更改变量
SET PERSIST mandatory_roles = 'dev_role';

需要注意的是，配置在 mandatory_roles 中的角色不能撤销其权限，也不能删除。

10.6 数据库安全性

10.6.1 数据库安全性概述

数据库的安全性（Security）是指保护数据库，防止不合法的使用，以免数据的泄露、更改或破坏。

1. 数据库的“安全性”和“完整性” 区别：

• 安全性。保护数据以防止非法用户故意造成的破坏，确保合法用户做其想做的事情。

• 完整性。保护数据以防止合法用户无意中造成的破坏，确保用户所做的事情是正确的。

两者的不同关键在于“合法”与“非法”、“故意”与“无意”。

2. 5个级别上设置各种安全措施

(1) 物理控制

计算机系统的机房和设备应加以保护，通过像加锁或专门监护以防止系统场地被非法进入，进行物理破坏。

(2) 法律保护

通过立法、规章制度防止授权用户以非法的形式将其访问数据库的权限转授给非法者。

(3) 操作系统（OS）支持

无论数据库系统是多么安全，操作系统的安全弱点均可能成为入侵数据库的手段，应防止未经授权的用户从OS处着手访问数据库。

(4) 网络管理

由于大多数DBS都允许用户通过网络进行远程访问，因此网络软件内部的安全性是很重要的。

(5) DBMS实现

DBMS的安全机制的职责是检查用户的身份是否合法及使用数据库的权限是否正确。

3. 要实现数据库安全，DBMS必须提供下列支持：

• 安全策略说明。即安全性说明语言。如支持授权的SQL语言。

• 安全策略管理。即安全约束目录的存储结构、存取控制方法和维护机制。如自主存取控制方法和强制存取控制方法。

• 安全性检查。执行“授权”及其检验，认可“他能做他想做的事情吗？”。
  用户识别。即标识和确认用户，确定“他就是他说的那个人吗？”。

10.6.2 数据库安全性控制

安全措施是一级级层层设置的，其安全控制模型如图 ：

（1）当用户进入计算机系统时，系统首先根据输入的用户标识（如用户名）进行身份的鉴定，只有合法的用户才准许进入系统。

（2）对已进入计算机系统的用户，DBMS还要进行存取控制，只允许用户在所授予的权限之内进行合法的操作。

（3）DBMS是建立在操作系统之上的，安全的操作系统是数据库安全的前提。操作系统应能保证数据库中的数据必须由DBMS访问，而不允许用户越过DBMS，直接通过操作系统或其他方式访问。

（4）数据最后通过加密的方式存储到数据库中，即便非法者得到了已加密的数据，也无法识别数据内容。

10.6.3 存取控制策略

存取控制策略主要包括 ：

（1）定义用户权限，并将用户权限登记到数据字典中。

（2）合法权限检查。每当用户发出存取数据库的操作请求后，DBMS查找数据字典，根据安全规则进行合法权限检查，若用户的操作请求超出了定义的权限，系统将拒绝执行此操作。

用户权限定义和合法权限检查策略一起组成了DBMS的安全子系统。

大多数DBMS所采取的存取控制策略主要是两种：自主存取控制和强制存取控制。

（1）自主存取控制（DAC）

在自主存取控制方法中，用户对于不同的数据库对象有不同的存取权限，不同的用户对同一对象也有不同的权限，而且用户还可将其拥有的存取权限转授给其他用户。

（2）强制存取控制（MAC）

在强制存取控制方法中，每一个数据库对象被标以一定的密级，每一个用户也被授予某一个级别的许可证。

10.6.4 自主存取控制（DAC）

用户使用数据库的方式称为“授权”。权限有两种：访问数据的权限和修改数据库结构的权限。

（1）访问数据的权限有4个。

• 读（Select）权限：允许用户读数据，但不能修改数据。

• 插入（Insert）权限：允许用户插入新的数据，但不能修改数据。

• 修改（Update）权限：允许用户修改数据，但不能删除数据。

• 删除（Delete）权限：允许用户删除数据。

（2）修改数据库结构的权限也有4个。

• 索引（Index）权限：允许用户创建和删除索引。

• 资源（Resourse）权限：允许用户创建新的关系。

• 修改（Alteration）权限：允许用户在关系结构中加入或删除属性。

• 撤销（Drop）权限：允许用户撤消关系。

10.6.5 强制存取控制（MAC）

强制存取控制方法的基本思想在于为每个数据对象（文件、记录或字段等）赋予一定的密级，级别从高到低为：绝密级（TS，top secret）、机密级（S，secret）、秘密级（C，confidential）、公用级（U，unclassified）。

每个用户也具有相应的级别，称为许可证级别。

密级和许可证级别都是严格有序的。

 如：绝密>机密>秘密>公用。

在系统运行时，采用如下两条简单规则：

（1）用户i只能查看比它级别低或同级的数据；

（2）用户i只能修改和它同级的数据。

注意，这种方法在通用数据库系统中不十分有用，只是在某些专用系统中才有用，例如军事部门或政府部门。