8.MySQL安全管理

作者: 好像在哪见过你丶 | 来源:发表于2019-05-23 15:59 被阅读0次

思考：在实际开发中，通常会需要对外提供一些数据，供其他项目去访问。这个时候就会出现一个问题，数据库怎么保证数据安全呢？

引入：当项目体量非常大的时候，的确会需要对外提供一些数据查询操作，或者项目大的时候需要多个项目共享数据。那么这个时候就会涉及到账户的安全问题以及数据的安全问题。在MySQL中，提供了多种方式来保证数据的安全。

数据备份与还原：数据出错后最后一道保障
用户权限管理：按需提供权限
外键：表内部数据安全保护
视图：虚拟表保护内部数据
事务安全：规范数据的连续操作

总结：在MySQL中提供了很多的方式来保障数据的安全，因为数据安全是软件最重要的考量因素。

一、数据备份与还原【掌握】

定义：数据备份与还原，是指在数据安全的时候，将数据进行额外的存储，以防止数据出问题；而到了数据出了问题的时候，可以利用保存的安全数据还原成原来的样子。

在MySQL中，提供了很多种方式来实现数据的备份与还原

文件备份与还原
整库数据备份与还原
单表数据备份与还原博客剖析
增量备份与还原百度百科

总结：数据库的备份还原应该根据实际项目的需求来选择合适的方式，但是一定是要用到的。

思考：如果一个项目访问量非常小，数据量也不大，应该采用什么方式备份还原呢？

引入：理论上来讲，每一种备份方式都适合小项目，不过针对小项目通常采用的备份方式就是最简单的方式，文件备份。

1. 文件备份与还原【了解】

定义：文件备份与还原，就是利用MySQL数据表的结构、索引和数据文件，直接保存备份。还原的时候也是直接使用文件覆盖即可，此备份还原方式最简单。

文件备份：将数据库表对应文件复制到安全位置即可，但是InnoDB和MyIsam存储引擎有区别

InnoDB：需要备份三种文件
- .frm文件：表结构文件
- .ibd文件：数据和索引内容
- ibdata1：InnoDB存储引擎的全部数据和索引文件
MyIsam：需要备份三种文件
- .frm文件：表结构文件
- .MYI文件：索引文件
- .MYD文件：数据文件

文件还原：直接将备份文件放到对应数据库文件夹下即可

注意：InnoDB的还原的话需要使用备份ibdata1文件覆盖mysql\data\ibdata1文件

总结

MySQL文件备份最简单，操作也最方便
文件备份会占用大量磁盘：结构、索引和数据
InnoDB存储引擎不适合使用文件备份，MyIsam存储引擎适合

思考：文件备份太过冗余，而且如果备份频次高点的话，磁盘受不了。有么有别的方式来实现备份呢？

引入：一般项目中最常用的就是整库备份，也称之为SQL备份，备份的内容是将表的结构和数据已SQL指令的形式备份。

2.整库备份与还原【掌握】

定义：整库备份与还原，是利用mysql提供的客户端mysqldump.exe将数据库中指定的表或者整个数据库以SQL指令形式保存到文件，还原的时候只要执行文件中对应的SQL指令即可。

mysqldump.exe备份：mysqldump是一种客户端，因此也需要像mysql.exe一样先进行连接认证；只是mysqldump备份的指令是直接跟在认证之后的

mysqldump.exe -h主机地址 -P端口 -u用户名 -p密码 数据库名字 [表名列表] > SQL文件路径

mysqldump可以实现多种备份方式

单表备份：数据库名字表名 > SQL文件路径
多表备份：数据库名字表名1 表名2 ...表名N > SQL文件路径
整库备份：数据库名字 > SQL文件路径

mysqldump -uroot -proot my_database my_class > D:/a.sql             #单表
mysqldump -uroot -proot my_database my_class my_student > D:/as.sql  #多表
mysqldump -uroot -proot my_database > D:/asd.sql                    #整库

还原：还原就是用备份的SQL指令执行一次，从而可以覆盖掉当前数据库内对应的表和数据

mysql客户端还原，即登录时直接还原：mysql.exe -hPup 数据库名字 < SQL文件路径
指令还原，即登录mysql后还原：source SQL文件路径

mysql -uroot -p my_database < D:/a.sql  #还原（回车后输入密码）
source D:/as.sql;                      #还原（登录mysql服务端后）

注意：SQL还原时必须指定数据库名字

总结

SQL文件备份是利用mysqldump.exe客户端实现
客户端备份需要先连接认证服务器
SQL备份可以是单表、多表或者整库
SQL还原可以使用mysql登录时还原，也可以在登录后使用source指令
SQL还原必须指定好数据库

二、外键约束【掌握】

定义：外键foreign key，只在一张表中有一个字段指向另外一张表的主键字段，并且通过外键关联会有一些约束效果

思考：我们在学习表关系的时候，在一对多或者多对多的时候，都会在一张表中增加一个字段来指向另外一张表的主键，但是此时其实指向没有任何实际含义，需要人为的去记住，这样有啥价值呢？

引入：如果只是需要人为的去记住对应的关系，没有任何数据库本身的控制的话，那样的存在没有价值。Mysql提供了一种外键机制来实现约束关系。

1. 外键【掌握】

定义：外键就是在设定了字段属于其他表的主键后，使用foreign key关键字让表字段与另外表的主键产生内在关联关系。

创建表时增加外键：在所有字段后使用constraint `外键名字` foreign key (外键字段) references 表名(主键字段)

create table my_foreign(
    id int primary key auto_increment,
    name varchar(10) not null,
    age tinyint unsigned not null,
    c_id int comment '指向my_class的主键',
    constraint `my_class_id` foreign key(c_id) references my_class(id)
)charset utf8;

外键要求：字段本身必须是一个普通索引，如果不是，外键会自动加上，因此对应的外键字段的key为mul

mysql> desc my_foreign;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type                | Null | Key | Default | Extra          |
+-------+---------------------+------+-----+---------+----------------+
| id    | int(11)             | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)         | NO   |     | NULL    |                |
| age   | tinyint(3) unsigned | NO   |     | NULL    |                |
| c_id  | int(11)             | YES  | MUL | NULL    |                |
+-------+---------------------+------+-----+---------+----------------+

删除外键：如果一张表明确不需要使用对应的外键，可以使用修改表结构删除：alter table 表名 drop foreign key `外键名字`;

alter table my_foreign drop foreign key `my_class_id`;

注意：删除外键对应的表字段索引依然是存在的

追加外键：即在创建表后增加外键字段：alter table 表名 add constraint `外键名字` foreign key(外键字段) references 外键表(主键);

alter table my_foreign add constraint `my_class_id` foreign key(c_id) references my_class(id);

总结

外键可以在新建表时和创建表后增加foreign key
外键对应字段会自动创建成一个普通索引
外键删除后不会清除外键字段的普通索引关系

思考：外键就是为了增加个字段吗？没有任何效果？

引入：既然已经关联了另外一张表的主键，说明外键必然是有效果的。这种效果称之为外键约束，会在数据操作时产生相应效果。

2. 外键约束【掌握】

定义：外键约束，即外键的增加之后对应的父表（主键表）和子表（外键所在表）都有相应的约束关系

外键增加后默认字表插入的数据对应的外键字段必须在父表存在，否则会报错

insert into my_foreign values(null,'犬夜叉',200,5);          #外键值5在主表中存在：OK

insert into my_foreign values(null,'神无',3,100);           #外键值100在主表不存在，报错
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`my_database`.`my_foreign`, CONSTRAINT `my_class_id` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`))

外键增加后默认父表主键如果在外键值有使用，那么不能更新主键值，也不能删除主键所在记录

update my_class set id = 11 where id = 5;               #5在外键中存在，不能更新
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`my_database`.`my_foreign`, CONSTRAINT `my_class_id` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`))

delete from my_class where id = 5;                      #5在外键中存在，不能删除
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`my_database`.`my_foreign`, CONSTRAINT `my_class_id` FOREIGN KEY (`c_id`) REFERENCES `my_class` (`id`))

因此，外键的作用就明确了

限定子表（外键所在表）不能插入主表中不存在外键值（不能更新）
限定父表（主键被引用）不能删除或者更新子表有外键引用的主键信息

可以在创建外键的之后指定外键的约束效果：即控制父表的操作对子表的影响

控制情况
- on update：父表更新与子表有关联的主键时
- on delete：父表删除与子表有关联的主键时
控制效果
- cascade：级联操作，即父表怎么样，子表相有对应关系的记录就怎么样
- set null：置空操作，即父表变化，子表关联的记录对应的外键字段置空（注意：能够使用的前提是外键对应的字段允许为空）
- restrict/no action：严格模式，即不允许父表操作
通常的搭配规则如下
- on update cascade：父表更新，子表级联更新
- on delete set null：父表删除，子表对应外键字段置空

create table my_foreign2(
    id int primary key auto_increment,
    name varchar(10) not null,
    age tinyint unsigned not null,
    c_id int comment '外键字段',
    constraint `my_class_id2` foreign key(c_id) references my_class(id) on update cascade on delete set null
)charset utf8;
insert into my_foreign2 values(null,'七宝',2,7);

注意：表中外键名字是整个数据库唯一的，不能重复，因此可以在创建表的时候自己不指定，即直接使用foreign key及以后的内容

更新父表效果

mysql> update my_class set id = 8 where id = 7;
Query OK, 1 row affected (0.17 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from my_foreign2;
+----+------+-----+------+
| id | name | age | c_id |
+----+------+-----+------+
|  1 | 七宝 |   2 |    8 |
+----+------+-----+------+
1 row in set (0.00 sec)

删除父表效果

mysql> select * from my_foreign2;
+----+------+-----+------+
| id | name | age | c_id |
+----+------+-----+------+
|  1 | 七宝 |   2 |    8 |
+----+------+-----+------+
1 row in set (0.00 sec)

mysql> delete from my_class where id = 8;
Query OK, 1 row affected (0.08 sec)

mysql> select * from my_foreign2;
+----+------+-----+------+
| id | name | age | c_id |
+----+------+-----+------+
|  1 | 七宝 |   2 | NULL |
+----+------+-----+------+
1 row in set (0.00 sec)

总结

外键约束分为对父表的约束和对子表的约束，其中子表约束是固定的不能插入父表不存在的外键值
父表外键约束可以通过设定on update和on delete事件来控制，控制方式有cascade、set null和restrict三种
外键的强大约束作用可以保证数据的完整性和有效性
外键的强大约束有可能操作负面影响：数据维护变得困难，所以实际开发中要根据需求选择使用
外键只有InnoDB存储引擎支持，MyIsam不支持

三、事务安全【掌握】

思考：MySQL如果出现用户的一次操作，在数据库中要操作两张表：如用户下单，对应商品表的数量要先减少，然后再在订单表中增加一条记录。如果在操作的过程中，只完成了一半就出现理论问题，这种数据该怎么办？

引入：很多时候数据操作都会存在步骤性，就是多个步骤完成才是一个统一的操作。这个时候如果不是人为控制的话，就很难保证。MySQL提供了一种事务安全，可以自动管理这种需求。

1. 事务安全【掌握】

定义：事务安全transaction safe是指有些操作是需要多次对表进行写操作（增删改），但是需要最后等所有结果都成功了才提交，其中一步错了，整个操作就无效，从而保证数据的整体性。

默认的MySQL是关闭事务的，即用户一提交操作，系统就自动将数据写入到数据表

# 在一个客户端写入数据，然后去另外一个客户端查看
insert into my_foreign2 values(null,'蜘蛛',188,5);

如果想要开启事务可以使用两种方式

自动开启：修改系统事务控制变量autocommit
手动事务：使用事务命令来控制要执行的事务操作

自动事务

mysql> show variables like 'autocommit';            #查看系统事务控制，默认是自动提交的
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit    | ON    |
+---------------+-------+
1 row in set, 1 warning (0.01 sec)

# 修改自动提交
mysql> set autocommit = off;
Query OK, 0 rows affected (0.35 sec)

# 此时所有的写操作就不会自动写入到数据表了
mysql> insert into my_foreign2 values(null,'自来也',50,2);
#需要使用指令commit
mysql> commit;
Query OK, 0 rows affected (0.10 sec)

注意：当前修改只是当前客户端当次连接有效，关闭退出就失效了。

手动事务：即通过指令来控制事务的开启和结束

开启事务：start transaction;
执行事务：各类SQL写操作
关闭事务：
- commit：操作成功全部提交
- rollback：操作失败全部回退

# 开启事务
start transaction;

# 执行事务
insert into my_class values(null,'神童1班','4001');    #执行成功后执行第二天，此时其他客户端看不到
insert into my_foreign2 values(null,'阿童木',0,8);     #使用第一条的结果

# 结束事务：判断前面是否成功，如果有失败则回退
rollback;                                           #前面所有操作都失败

# 注意:如果前面新增数据的时候用到了自增长,而最后选择了rollback,那么对自增长的触发是不可逆的.

事务回退：指事务操作过程中，某个节点成功了，但是后续未必会成功，那么可以设置节点，以后返回到该位置

设置节点：savepoint 名字;
回退到节点：rollback to 节点名字;

# 开启事务
start transaction;

# 执行事务
insert into my_class values(null,'神童1班','4001');    #执行成功后执行第二天，此时其他客户端看不到

# 设置节点
savepoint sp1;

# 继续执行
insert into my_foreign2 values(null,'阿童木',0,6);     #使用第一条的结果

# 假设执行失败，回退到成功节点
rollback to sp1;                                    #第二天执行无效

# 继续执行
insert into my_foreign2 values(null,'阿童木',0,9);

# 提交事务
commit;

事务特点：ACID

原子性（Atomicity ）：一个事务操作是一个整体，不可拆分
一致性（Consistency）：事务操作要么全成功，要么全失败
隔离性（Isolation ）：事务操作过程中，其他事务不可见
持久性（Durability ）：事务一旦提交，结果不可改变
InnoDB支持，MyIsam不支持

事务锁：一个事务在操作某个数据的时候，会形成锁，其他事务不可操作（写操作，可读）
事务安全原理

默认事务关闭：SQL写操作一旦执行，DBMS就会写入到数据表（其他客户立即可见）
开启事务：系统将当前事务的操作记录到事务日志，当前用户自己查看的是DBMS加工结果数据
提交事务：事务成功提交会一次性同步到表，而如果失败提交就会全部清空当前事务记录

总结

事务安全是一种保障连续操作完整性的机制
事务安全可以是自动事务（默认关闭）和手动事务，一般使用手动事务
事务处理过程中可以设置回滚点，从而实现部分回退
事务处理有四个特点：ACID
MySQL中InnoDB支持事务，MyIsam不支持

四、视图【掌握】

思考：有这么一种需求，需要将我们数据库的数据提交给其他系统访问（仅允许访问），对方想要看到所有的班级和学生信息，该如何实现？

引入：最简单的一种方式是给数据库服务器的账号给对方，对方自己去查看。但是此时对方可以看到数据库里面的所有该看的和不该看的数据，容易造成安全隐患。此时可以使用视图机制来实现

1. 视图【掌握】

定义：视图View，是一种虚拟表结构，由select查询语句的结果组成。

定义视图：create view 视图名字 as select查询语句;

create view student_class 
as 
select s.*,c.name class_name,c.room class_room
from my_student2 s left join my_class c on s.class_id = c.id;

视图创建后可以使用表查看的所有方式进行查看

show tables;
desc student_class;
show create table/view student_class;

注意：视图是一种虚拟表，会产生表结构文件，但是没有数据和索引文件

视图结构文件.png

修改视图：即修改视图的创建语句，修改有两种方式

修改结构：alter view修改
创建替换：create or replace view

alter view 视图名字 as 新select语句

# 创建替换
create or replace view student_view as select * from my_student2; #没有就新增，有就替换

删除视图：drop view 视图名字;
视图使用：视图有select语句组成结果，是提供数据的，可以像查询表一样查询数据

select * from student_class;

总结

视图可以通过create [or replace] view 视图名字 as select语句组成；
视图可以像表一样管理（查看）
视图主要目的是为了提供数据（通常是多表连接数据）

思考：视图是像表一样管理，那么视图可以进行数据的写操作吗？

引入：视图天生是为了数据查看的，一般不建议对视图进行数据操作，但是如果非要操作，视图也是允许的。

2. 视图数据操作【了解】

定义：视图数据操作，即通过视图进行增删改操作，视图本身不存储数据，数据来源于基表，所以视图操作是直接针对基表进行操作。

对视图进行新增数据操作，利用insert插入指令：视图分为单基表视图和多基表视图，多基表视图不能新增数据。

# 多基表视图
insert into student_class values(null,'阿童木','男',0,7,'神童1班','4001'); #错误

# 单表视图
insert into student_view values(null,'阿童木','男',0,7);    #单表视图可以插入数据
Query OK, 1 row affected (0.08 sec)

注意：单表视图插入数据的前提，是视图所包含的字段有基表中全部不能为空且没有默认值的字段，如果基表中有必填字段没有出现在视图了，就会报错失败！

更新数据，利用update更新指令：同样，多基表视图不能更新，单基表视图可以

# 多基表视图
update student_class set age = 30 where id = 1;             #错误，不可更新

# 单基表视图
update student_view set age = 30 where id = 1;              #可以

删除数据，利用delete删除指令：多基表视图不能删除，单基表视图可以

# 多基表视图
delete from student_class where id = 10;                    #错误，不可删除

# 单基表视图
delete from student_view where id = 10;                     #可以

总结

视图数据写操作要看视图的基表数量：多基表视图不可增删改，单基表视图可以
实际开发中，一般不会允许对视图进行数据写操作，只是用来提供数据查询

思考：视图本身是可以理解为一条select语句，那么查询视图的时候又是一条select语句，本质不就是一条子查询吗？

引入：视图查询的本质的确可以理解为就是一条子查询操作，而且是表子查询跟在from之后。但是视图还有深一层的内容，就是视图算法，能够与子查询有一些不一样。

3. 视图算法【了解】

定义：视图算法，就是在视图创建的时候采用指定的算法，系统在进行视图调用时会根据算法来优化视图的运算逻辑，从而也能达到不同的效果。

视图算法定义：create ALGORITHM = 算法 view 视图名字 as select查询语句;，MySQL中提供了三种视图算法

undefined：默认的，未定义算法，即系统自动选择算法
merge：合并算法，就是将视图外部查询语句跟视图内部select语句合并后执行，效率高（系统优先选择）
temptable：临时表算法，即系统将视图的select语句查出来先得出一张临时表，然后外部再查询（temptable算法视图不允许写操作）

# 创建三个视图，指定三种不同算法，内容一样
create ALGORITHM = undefined view student_v1 as select * from my_student2 order by age desc;
create ALGORITHM = merge view student_v2 as select * from my_student2 order by age desc;
create ALGORITHM = temptable view student_v3 as select * from my_student2 order by age desc;

组织查询：对视图数据进行分组统计

select count(*),class_id,max(age),any_value(name) from student_v1 group by class_id;
select count(*),class_id,max(age),any_value(name) from student_v2 group by class_id;
select count(*),class_id,max(age),any_value(name) from student_v3 group by class_id;

三组结果说明：算法不同，MySQL内部执行的方式不一样，如果要用到排序和分组，那么要考虑算法带来的影响

总结

MySQL中视图有三种算法，在定义的时候确定，默认是undefined让系统自己选
视图算法主要是MySQL内部执行时的方式，系统喜欢选择merge（执行效率高）

五、用户权限管理【了解】

思考：MySQL安装的时候，给客户端预留了一个账号root，一个数据库中有很多项目，如果都用一个账号，岂不是没有数据安全可言？

引入：如果数据库只有一个账号，那肯定该账号不是公共账号，否则太危险。凡是较大项目中，都会根据不同人的权限，来分配不同的账号，以保障数据安全。

1. 用户管理【了解】

定义：用户管理，即在数据库中利用当前用户的权限去管理用户以及用户权限。

root用户是超级管理员账户，拥有所有权限，也最不安全：用户在mysql中都包含两个部分，即用户名和主机地址（host）：可以通过show grants for root@localhost; 来查看root用户权限

mysql> show grants for root@localhost;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.35 sec)

所有权限都在mysql数据库下的user表中

# root用户全是Y
mysql> select * from mysql.user\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y

一般我们项目中都不大会直接都使用root用户访问操作数据库（生产环境），这个时候会创建不同的账号给不同的团队，创建账号语法：create user 用户名@主机地址 identified by password '明文密码';

create user 'user1'@'%' identified by '123456'; #创建一个允许所有人访问的user1账户，密码123456
create user 'user2'@'localhost' identified by '123456'; #仅允许本机访问

赋予权限：默认的用户没有任何权限

C:\Users\DELL>mysql -uuser1 -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.21 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

赋予权限的语法：grant 权限列表 on 数据库.表名 to user

# 给user1 my_database中视图查看权限
grant select on my_database.student_view to 'user1'@'%';

# 给user2 全部权限
grant all privileges on *.* to 'user2'@'localhost';

取消权限：revoke 权限列表 on 数据库.表名 from user;

# 收回user1的查看权限
revoke select on my_database.student_view from 'user1'@'%';

如果用户密码忘了，可以帮助修改用户密码：set password for '用户名'@'主机地址'="新密码";

set password for 'user1'@'%'='654321';

回收账户即删除账户：drop user 用户名;

drop user 'user1'@'%';

总结

root用户是超级管理员，拥有全部权限
项目实施中需要给不同的小组创建不同的账户，分配不同的权限
账号的权限要及时跟进，不用的权限要迅速回收，不用的账户要尽快删除