MySQL安全管理

安全管理：用各种方式来确保数据库的安全和数据的安全

示例

微盟的危机应对值得我们借鉴、携程的数据库被程序员删库跑路...

如果有用户管理，那么可以通过权限限制其没有权限删除
如果有数据备份，即便数据删除，也可以很快的实现数据还原，减小损失
...
为什么需要安全管理，总结就是：

1、安全管理是每一个接触数据库的人都应该考虑的问题，尤其是DBA（数据库管理员）

2、数据库安全的维度有很多

管理安全：用户、权限、备份还原等
结构安全：外键、视图、事务等
执行层：预处理
接下来我们x举例数据库的一些安全的维度

一、外键约束
目标：了解外键的概念和意义，掌握外键约束的管理和实际的应用场景

外键
外键约束
外键管理
1、外键
目标：认识外键，了解外键的构成条件

概念

外键：foreign key，表中指向外部表主键的字段定义成外键

外键必须要通过语法指定才能称之为外键
[constraint外键名] foreign key(当前表字段名) references 外部表(主键字段)
外键构成条件
外键字段必须与对应表的主键字段类型一致
外键字段本身要求是一个索引（创建外键会自动生成一个索引）
步骤

1、确定表中字段与另外一张表存在关联关系

2、使用外键明确关联外表

3、外键约束成功

示例

1、创建专业表和学生表，学生表中的专业id指向专业表id

create table t_47(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;
 
create table t_48(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int comment '指向t_46表中的id主键',
    constraint `c_id` foreign key(c_id) references t_47(id)
)charset utf8;
2、外键可以不指定名字，系统会自动生成

create table t_49(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int,
    foreign key(c_id) references t_47(id)
)charset utf8;
小结

1、外键是需要保证字段与外部连接的主键字段一致的

2、一张表可以有多个外键，但是一个字段只能产生一个外键

2、外键约束
目标：了解外键的意义，掌握外键的约束控制和约束作用

概念

外键约束：当表建立外键关系后，外键就会对主表（外键指向的表）和子表（外键所在的表）里的数据产生约束效果

外键约束的是写操作（默认操作）
新增：子表插入的数据对应的外键必须在主表存在
修改：主表的记录如果在子表存在，那么主表的主键不能修改（主键不能修改）
删除：主表的记录如果在子表存在，那么主表的主键不能删除
删除：主表的记录如果在子表存在，那么主表的主键不能删除
外键约束控制：外键可以在定义时控制外键的约束作用
控制类型
on update：父表更新时子表的表现
on delete：父表删除时子表的表现
控制方式
cascade：级联操作，父表操作后子表跟随操作
set null：置空操作，父表操作后，子表关联的外键字段置空
restrict：严格模式，不允许父表操作（默认的）
no action：子表不管
步骤

1、确定表的外键关联关系

2、确定主表的约束控制

3、明确使用相应的约束控制

4、系统自动约束

示例

1、子表不能插入主表不存在的数据

insert into t_48 values(null,'poizxc2014-1',2);    # 错误
 
insert into t_47 values(null,'English');
insert into t_48 values(null,'poizxc2014-2',1);
2、默认的外键产生后，主键不能更新被关联的主键字段或者删除被关联的主键记录

# 错误
update t_47 set id = 2;
delete from t_47 where id = 1;
3、限制外键约束，一般使用更新级联，删除置空

on update cascade：更新级联
on delete set null：删除置空
create table t_50(
    id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;
 
create table t_51(
    id int primary key auto_increment,
    name varchar(50) not null,
    c_id int, # 如果要允许置空，就不能not null
    foreign key(c_id) references t_50(id) on update cascade on delete set null
)charset utf8;
 
insert into t_50 values(null,'Chinese'),(null,'Computer');
insert into t_51 values(null,'Tony',1),(null,'Petter',2);
子表依然不允许插入父表不存在的外键
但是可以插入外键为Null的数据
# 错误
insert into t_51 values(null,'Lilei',3);
 
insert into t_51 values(null,'Lilei',NULL); # OK        
父表的更新（主键）会让关联的外键自动级联更新
update t_50 set id = 3 where id = 1;
父表的删除会让关联的外键自动自动置空
delete from t_50 where id = 3;
小结

1、外键约束对子表和父表都有约束

子表约束：子表不能插入父表不存在的外键
父表约束
更新约束（默认不允许）
删除约束（默认不允许）
一般约束
级联更新
删除置空
2、外键约束增强了数据的安全性和可靠性，但是会增加程序对于数据的不可控性，所以是实际开发中一般会通过程序逻辑控制来保证数据的完整性和安全性，外间使用较少

3、外键管理
目标：了解外键的维护

概念

外键管理：在表创建后期维护外键

新增外键
alter table 表名 add [constraint `外建名`] foreign key(外键字段) references 表名(主键) [on 外键约束]
删除外键
alter table 表名 drop foreign key 外键名;
更新外键：先删除后新增
示例

1、删除外键

alter table t_51 drop foreign key t_51_ibfk_1;    # 系统生成的外键
2、追加外键

alter table t_51 add constraint `t_51_50` foreign key(c_id) references t_50(id);
注意：追加外键需要保证外键字段里的值要么为Null，要么在父表中都能找到
小结

1、外键的使用最好的创建表结构的时候就维护好，后期的维护对子表数据有要求

二、事务安全
学习目标：了解事务安全的概念和特性，掌握事务安全的应用，能够使用事务安全解决相应问题

事务概念
事务处理
事务特点
1、事务
目标：认识事务，了解事务的原理和作用

概念

事务：要做的某个事情

计算机中的事务是指某个程序执行单元（写操作）

事务安全：当事务执行后，保障事务的执行是有效的，而不会导致数据错乱

事务安全通常针对的是一连串操作（多个事务）而产生的统一结果

MySQL中默认的写操作是直接写入的

执行写操作SQL
同步到数据表
示例

银行转账：从A账户转账到B账户

创建数据表

create table t_52(
    id int primary key auto_increment,
    name varchar(50) not null,
    account decimal(10,2) default 0.00
)charset utf8;
 
insert into t_52 values(null,'Tom',10000),(null,'Lucy',100);
转账：Tom向Lucy转账，一定是分为两步

# Tom扣钱
update t_52 set account = account - 1000 where id = 1;
 
# Lucy收钱
update t_52 set account = account + 1000 where id = 2;
以上两步必须都成功转账才能叫成功
两步操作无法确保哪一步会出问题（尤其是第二步）
为了保障两步都成功才能叫事务安全
事务安全原理

事务安全是在操作前告知系统，接下来所有的操作都暂不同步到数据表，而是记录到事务日志，指导后续所有操作都成功，再进行同步；否则取消所有操作

以上述转账为例

graph TB
A(转账开始)-->B[开启事务]
B-->C{事务1:Tom转出1000}
C-->|成功|D[记录到事务日志]
C-->|失败|G
D-->E{事务2:Lucy转入1000}
D-->|失败|G
E-->|成功|F[记录到事务日志]
F-->G[关闭事务<br>成功:提交事务 同步到数据表\清除事务日志<br>失败:回滚事务 清除事务日志]
G-->H((结束))
小结

1、事务的目的就是为了保障连续操作的一致性，保证结果的完整性

2、事务的原理是通过将操作结果暂时保存在事务日志中，等所有操作的结果都是成功的，然后一并同步到数据表