关于MySQL的安全管理

前言

*外键约束
*事务管理
*预处理
*视图
*数据备份与还原
*用户管理

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

一、外键约束

外键
外键约束
外键管理

1、外键

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

*外键必须要通过语法指定才能称之为外键
[constraint外键名] foreign key(当前表字段名) references 外部表(主键字段)

*外键构成条件
外键字段必须与对应表的主键字段类型一致
外键字段本身要求是一个索引（创建外键会自动生成一个索引）

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

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

3、外键约束成功

eg：创建一个学生表和一个专业表，学生表中的专业id指向专业表id

create table t_18(
	id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_19(
	id int primary key auto_increment,
    name varchar(50) not null,
    c_id int comment '指向t_17表中的id主键',
    constraint `c_id` foreign key(c_id) references t_18(id)
)charset utf8;

2、外键可以不指定名字，系统会自动生成

create table t_20(
	id int primary key auto_increment,
    name varchar(50) not null,
    c_id int,
    foreign key(c_id) references t_18(id)
)charset utf8;

2、外键约束

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

*外键约束的是写操作（默认操作）
新增：子表插入的数据对应的外键必须在主表存在
修改：主表的记录如果在子表存在，那么主表的主键不能修改（主键不能修改）
删除：主表的记录如果在子表存在，那么主表的主键不能删除
删除：主表的记录如果在子表存在，那么主表的主键不能删除

*外键约束控制：外键可以在定义时控制外键的约束作用
控制类型
&on update：父表更新时子表的表现
&on delete：父表删除时子表的表现
控制方式
&cascade：级联操作，父表操作后子表跟随操作
&set null：置空操作，父表操作后，子表关联的外键字段置空
&restrict：严格模式，不允许父表操作（默认的）
&no action：子表不管

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

2、确定主表的约束控制

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

4、系统自动约束

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

insert into t_19 values(null,'Tony',2);	# 错误

insert into t_18 values(null,'English');
insert into t_19 values(null,'Peny',1);

2、默认的外键产生后，主键不能更新被关联的主键字段或者删除被关联的主键记录

# 错误
update t_18 set id = 2;
delete from t_18 where id = 1;

3、限制外键约束，一般使用更新级联，删除置空

on update cascade：更新级联
on delete set null：删除置空

create table t_21(
	id int primary key auto_increment,
    name varchar(50) not null unique
)charset utf8;

create table t_22(
	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_21 values(null,'Chinese'),(null,'Computer');
insert into t_22 values(null,'Tony',1),(null,'Petter',2);

3、外键管理

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

新增外键

alter table 表名 add [constraint `外建名`] foreign key(外键字段) references 表名(主键) [on 外键约束]

删除外键

alter table 表名 drop foreign key 外键名;

二、事务安全

1、事务

事务：要做的某个事情

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

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

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

*MySQL中默认的写操作是直接写入的
执行写操作SQL
同步到数据表

eg：转账，从A到B账户
创建数据表

create table t_23(
	id int primary key auto_increment,
    name varchar(50) not null,
    account decimal(10,2) default 0.00
)charset utf8;

insert into t_23 values(null,'Tom',10000),(null,'Lucy',100);

转账：Tom向Lucy转账，一定是分为两步

# Tom扣钱
update t_23 set account = account - 1000 where id = 1;

# Lucy收钱
update t_23 set account = account + 1000 where id = 2;

2、事务处理

事务处理：利用自动或者手动方式实现事务管理

*自动事务处理：系统默认，操作结束直接同步到数据表（事务关闭状态）
#系统控制：变量 autocommit（值为ON，自动提交）
*手动事务处理
#开启事务： start transaction
#关闭事务
##提交事务：commit（同步到数据表同时清空日志数据）
##回滚事务：rollback（清空日志数据）
*事务回滚：在长事务执行中，可以在某个已经成功的节点处设置回滚点，后续回滚的话可以回到某个成功点
#设置回滚点：savepoint 回滚点名字
#回滚到回滚点：rollback to 回滚点名字

steps
1、确定操作需要使用到事务操作

2、开启事务

3、执行事务
如果需要回滚点设置：设置回滚点
如果需要回滚：回滚到回滚点
4、结束事务
成功提交事务：同步到数据表，清空事务日志
失败回滚事务：清空事务日志

eg：1、手动事务：启用事务转账，成功提交事务

# 开启事务
start transaction;

# Tom扣钱
update t_23 set account = account - 1000 where id  = 1;

# Lucy收钱
update t_23 set account = account + 1000 where id  = 2;

# 提交事务
commit;

2、手动事务：启用事务转账，成功提交事务（回滚点）

# 开启事务
start transaction;

# Tom扣钱
update t_23 set account = account - 1000 where id= 1;

# 设置回滚点
savepoint sp1;

# Lucy收钱
update t_23 set account = account + 10000 where id= 2;

# 操作失败回到回滚点
rollback to sp1;

# Lucy收钱
update t_23 set account = account + 1000 where id= 2;

# 提交事务
commit;

3、自动事务
Mysql默认是自动提交事务的：所以事务一旦发生就会立即写入到数据表（不能多个事务一起完成任务）

show variables like 'autocommit';

关闭自动提交事务（当前设置级别用户级：当前用户档次连接有效）

set autocommit = 0;

3、事务特点

事务特点：事务处理具有ACID四大特性

*原子性（Atomicity ）：一个事务操作是一个整体，不可拆分，要么都成功，要么都失败
*一致性（Consistency）：事务执行之前和执行之后都必须处于一致性状态，数据的完整性没有被破坏（事务逻辑的准确性）
*隔离性（Isolation ）：事务操作过程中，其他事务不可见
*持久性（Durability ）：事务一旦提交，结果不可改变

三、预处理

1、预处理

预处理：prepare statement，一种预先编译SQL指令的方式（然后命令执行）

*预处理不同于直接处理，是将要执行的SQL指令先发送给服务器编译，然后通过指令执行
发送预处理：prepare 预处理名字 from ‘要执行的SQL指令’
执行预处理：execute 预处理名字
*预处理管理
预处理属于会话级别：即当前用户当次连接有效（断开会被服务器清理掉）
删除预处理：deallocate | drop prepare 预处理名字

steps
1、要执行的SQL指令想使用预处理
重复执行的指令
涉及数据安全的指令
2、发送预处理指令

3、执行预处理

eg：查询学生的SQL指令需要重复执行多次

# 普通操作
select * from t_42;

# 预处理操作：发送预处理
prepare p1 from 'select * from t_42';

# 预处理操作：执行预处理
execute p1;

# 删除预处理
deallocate  prepare p1;

2、预处理传参

预处理传参：在执行预处理的时候传入预处理需要的可变数据

*一般预处理都不会是固定死的SQL指令，而是具有一些数据可变的执行（条件）
#可变数据的位置使用占位符 ? 占位
#prepare 预处理名字 from 预处理指令 变化部分使用?替代
*在执行预处理的时候将实际数据传进去代替占位符执行SQL
#数据存储到变量（预处理传入的值必须是变量保存的）
set @变量名 = 值
#使用using关键字传参
execute 预处理名字 using @变量名
#数据传入的顺序与预处理中占位符的顺序一致

steps
1、同样的SQL指令要执行N次，但是条件不一致

2、使用预处理占位符发送预处理指令

3、设定变量保存要传入的数据

4、执行预处理，携带变量参数

eg：向t_11中插入数据

# 准备预处理：涉及参数
prepare t_11_insert from 'insert into t_40 values(null,?,?,?,?)';

# 设置变量并传入参数
set @name = '药师兜';
set @gender = '男';
set @age = 23;
set @class_name = '木叶1班';

# 执行预处理
execute t_11_insert using @name,@gender,@age,@class_name;

四、视图

1、视图

视图：view，一种由select指令组成的虚拟表

*视图是虚拟表，可以使用表管理（结构管理）
为视图提供数据的表叫做基表

创建视图
create view 视图名字 as select指令;
访问视图：一般都是查询
select */字段名 from 视图名字;

*视图有结构，但不存储数据
结构：select选择的字段
数据：访问视图时执行的select指令

steps
1、确定需要使用视图提供数据
数据来源是多张表
对外部系统提供数据支撑（保护基表数据）
2、使用视图

eg：1、需要对外提供一个学生详情的数据，经常使用：可以利用视图实现

# 对外提供数据，要保护数据本身的安全
# 需要长期使用

# 创建视图
create view v_student_info as select * from t_16 left join t_17 using(c_id);
# 使用视图：像表一样使用
select * from v_student_info;

2、有些复杂的SQL又是经常用到的，如多张表的连表操作：可以利用视图实现

# 院系表
create table t_53(
	id int primary key auto_increment,
    name varchar(50) not null
)charset utf8;
insert into t_53 values(null,'语言系'),(null,'考古系');

# 专业表
create table t_25(
    id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '学院id'
)charset utf8;
insert into t_25 values(null,'English',1),(null,'Chinese',1);

# 学生表
create table t_26(
	id int primary key auto_increment,
    name varchar(50) not null,
    s_id int not null comment '专业Id'
)charset utf8;
insert into t_26 values(null,'Lilei',2),(null,'Mark',2),(null,'Tony',1);

# 获取所有学生的明细信息
select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_26 as stu left join t_25 sub on stu.s_id = sub.id left join t_24 sch on sub.s_id = sch.id;

# 以视图保存这类复杂指令，后续可以直接访问视图
create view v_student_detail as select stu.*,sub.name as sub_name,sub.s_id as sch_id,sch.name as sch_name from t_26 as stu left join t_25 sub on stu.s_id = sub.id left join t_24 sch on sub.s_id = sch.id;

select * from v_student_detail;

2、视图管理

视图管理：对视图结构的管理

视图查看：显示视图结构和具体视图信息

show tables;	# 查看全部视图
show create table/view 视图名字;	# 查看视图创建指令
desc 视图名字;	 # 查看视图结构

视图修改：更改视图逻辑

# 更改视图
alter view 视图名 as 新的查询指令;
create or replace view 视图名 as 新的查询指令;	# 创建新的或者替换新的

视图删除

drop view 视图名; 

3、视图数据操作

视图数据操作：直接对视图进行写操作（增删改）然后实现基表数据的变化

*视图所有的数据操作都是最终对基表的数据操作
*视图操作条件
#多基表视图：不允许操作（增删改都不行）
#单基表视图：允许增删改
##新增条件：视图的字段必须包含基表中所有不允许为空的字段
#with check option：操作检查规则
##默认不需要这个规则（创建视图时指定）：视图操作只要满足前面上述条件即可
##增加此规则：视图的数据操作后，必须要保证该视图还能把通过视图操作的数据查出来（否则失败）

steps
1、根据需求确定需要使用视图

2、确定允许视图进行数据操作（通常用户权限设定，且是单基表视图）

3、确定视图数据的操作是否需要操作检查（有where条件筛选，且只对新增和更新有影响）
需要：增加with check option
不需要
4、使用视图进行数据操作（最终数据写落点是基表

eg：1、增加一个单表视图和多表视图

create view v_student_1 as select s_id,s_name from t_16;
create view v_student_2 as select s.*,c.c_name from t_16 s left join t_17 c using(c_id);
create or replace view v_student_3 as select * from t_16 where c_id is not null with check option;

2、新增数据

insert into v_student_1 values(null,'student7');	# 正确：视图包含所有必有字段
insert into v_student_2 values(null,'student8',null,null); # 错误：不可插入
insert into v_student_3 values(null,'student8',null);	# 错误：check option，因为第三个字段c_id为NULL，不符合视图筛选条件，查不出来
insert into v_student_3 values(null,'Student9',1);	# 正确

3、更新数据

update v_student_1 set s_name = 'boy' where s_id = 8;
update v_student_2 set s_name = 'boy' where s_id = 7; # 错误：不可修改
update v_student_3 set c_id = null where s_id = 1;	  # 错误：check option，修改后c_id为null，变得不符合视图筛选条件了
update v_student_3 set s_name = 'boy' where s_id = 1; # 正确

4、删除数据

delete from v_student_1 where s_id = 2;
delete from v_student_2 where s_id = 3;	# 错误：不可删除
delete from v_student_3 where s_id = 1;	# 可以删除，说明with check option不影响删除操作

4、视图算法

视图算法：指视图在执行过程中对于内部的select指令的处理方式

*视图算法在创建视图时指定

create ALGORITHM = 算法 view 视图名字 as select指令;

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

steps
1、确定使用视图

2、确定视图算法：考虑视图内部SQL指令中的子句使用情况

3、创建视图并使用视图

eg：1、创建三种不同算法视图

create algorithm = undefined view v_student_4 as select * from t_13 order by age desc;
create algorithm = merge view v_student_5 as select * from t_13 order by age desc;
create algorithm = temptable view v_student_6 as select * from t_13 order by age desc;

2、使用视图：为了体现算法效果，给视图增加分组效果

select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_4 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_5 group by class_name;
select count(*),any_value(name),any_value(age),class_name,max(age) from v_student_6 group by class_name;

3、临时表算法的视图不能进行数据插入操作

insert into v_student_6 values(null,'冥加','男',100,'神妖1班'); # 错误：不可插入

五、数据备份与还原

备份：backup，将数据或者结构按照一定的格式存储到另外一个文件中，以保障阶段数据的完整性和安全性

将当前正确数据进行数据保存
备份通常是有固定的时间节点
还原：restore，在当前数据出问题的情况下，将之前备份的数据替换掉当前数据，保证系统的持续、正确的运行

基于备份进行数据还原
备份还原不一定能够保证所有损失挽回

1、表数据备份

表数据备份：单独针对表里的数据部分进行备份（数据导出）

*将数据从表中查出，按照一定格式存储到外部文件
#字段格式化：fields
##terminated by：字段数据结束后使用的符号，默认是空格
##enclosed by：字段数据包裹，默认什么都没有
##escaped by：特殊字符的处理，默认是转义

#行格式化：lines
##terminated by：行结束符号，默认是\n，自动换行
##starting by：行开始符号，默认没有

select 字段列表|*  into outfile 外部文件路径 
	[fields terminated by 格式 enclosed by 格式]
	[lines terminated by 格式 starting by 格式]
from 数据表;

*表数据备份不限定数据的来源是一张表还是多张表（可以连表）

steps
1、确定需要对表数据进行导出处理（备份），而且不需要考虑字段名字

2、确定导出的数据的处理
字段处理（可以默认）
行处理（可以默认）
3、执行表数据导出

eg：1、将t_11表的数据按照默认方式导出到文件

select * into outfile  'D:/t_11.csv' from t_11;

2、将t_11表的数据按照指定格式导出到文件

select name,gender,age,class_name into outfile 'D:/t_11_self.csv'
	fields terminated by '-' enclosed by '"'
	lines starting by 'GO:'
from t_11;

3、多表数据导出

select * into outfile 'D:/t_16_17.csv' from t_16 left join t_17 using(c_id);

2、表数据还原

*将一定格式的数据按照一定的解析方式解析成符合表字段格式的数据导入到数据表
字段处理
行处理

load data infile '数据文件所在路径' into table 表名
	[fields terminated by 格式 enclosed by 格式]
	[lines terminated by 格式 starting by 格式]
	[(字段列表)];	# 如果是部分表字段，那么必须将字段列表放到最后

*数据文件来源
表数据备份的数据文件
外部获取或者制作的符合格式的数据

steps
1、数据文件里的数据满足数据表的字段要求
数据类型
字段对应数（自增长id、可以为空字段除外）
2、数据文件里的数据可以通过字段加工、行加工处理满足表字段要求

3、使用数据导入

3、文件备份

文件备份：直接对数据表进行文件保留，属于物理备份

*文件备份操作简单，直接将数据表（或者数据库文件夹）进行保存迁移
*MySQL中不同表存储引擎产生的文件不一致，保存手段也不一致
###InnoDB：表结构文件在ibd文件中，数据和索引存储在外部统一的ibdata文件中(Mysql7以前话是frm后缀)
###MyIsam：每张表的数据、结构和索引都是独立文件，直接找到三个文件迁移即可

steps
1、设定备份时间节点

2、设定备份文件存储位置

3、确定备份表的存储引擎

4、根据节点进行文件备份：将文件转移（复制）到其他存储位置

eg：1、MyIsam表的文件备份：找到三个文件，复制迁移

sdi：表结构文件
MYI：索引文件
MYD：数据文件
2、InnoDB表的文件备份：找到两个文件，复制迁移

ibd：表结构文件
ibdata：所有InnoDB数据文件

4、文件还原

文件还原：利用备份的文件，替换出现问题的文件，还原到备份前的良好状态

*直接将备份的文件放到对应的位置即可

*文件还原影响
MyIsam存储引擎：单表备份，单表还原，不影响其他任何数据
InnoDB存储引擎：单表结构，整库数据，只适合整库备份还原，否则会影响其他InnoDB存储表

steps
1、找到出问题的数据文件
MyIsam：表结构、表数据、表索引三个文件（删掉即可）
InnoDB：表结构、整库数据表ibdata（删掉）
2、将备jhjk 份数据放到相应删除的文件位置

5、SQL备份

SQL备份：将数据库的数据以SQL指令的形式保存到文件当中，属于逻辑备份

*SQL备份是利用Mysqldump.exe客户端实现备份

*SQL备份是将备份目标（数据表）以SQL指令形式，从表的结构、数据和其他信息保存到文件

mysqldump.exe -h -P -u -p [备份选项] 数据库名字 [数据表列表] > SQL文件路径

*备份选项很多，常见的主要是数据库的备份多少
#全库备份：–all-databases 所有数据库的所有表，也不需要指定数据库名字
#单库备份：[–databases] 数据库 指定数据库里的所有表（后面不要给表名）
#部分表（单表）备份：数据库名字 表1[ 表2…表N]

steps
1、确定备份的时间：通常是有规则的时间备份

2、确定备份的目标级别：全库、单库、数据表

3、使用mysqldump实现备份

eg：

全库备份
mysqldump.exe -uroot -proot --all-databases > D:/mysql.sql
单库备份
mysqldump -uroot -proot --databases db_2 > D:/db_2.sql
单表备份
mysqldump -uroot -proot db_2 t_11 t_13 > D:/t_11_13.sql

6、SQL还原

SQL还原：在需要用到SQL备份数据时，想办法让SQL执行，从而实现备份数据的还原

*SQL还原可以使用Mysql.exe进行操作

mysql.exe -h -P -u -p [数据库名字] < SQL文件路径

*SQL还原可以在进入到数据库之后利用SQL指令还原

source SQL文件路径;

steps
1、确定数据库（表）需要进行数据还原
数据错乱
数据不完整
2、找到对应节点的SQL备份文件

3、SQL还原

六、用户管理

1、账号管理

账号管理：根据项目的需求设置和管理账号

*账号是权限依赖的对象，先有账号才有权限
*MySQL中账号的组成分为两个部分：用户名 @ 主机地址（root@localhost）
#用户名为用户登录时的名字
#主机地址：是允许账号所在客户端的访问的客户端IP（如上述root只能在服务器本机通过客户端访问）
*账号管理
#创建账号：create user 用户名@主机地址 identified by ‘明文密码’;
#删除账号：drop user 用户名@主机地址

steps
1、根据项目要求创建用户

2、根据项目要求删除用户

eg：1、根据项目情况，不同项目创建不同账号

# A团队只允许在公司访问服务器，公司IP为163.177.151.110
create user `admin`@`163.177.151.110` identified by 'admin123';

# B团队不限定负责数据库管理，不限定工作地点
create user `admin` identified by 'admin321';

2、开发任务结束，A团队任务完成，不需要进行数据库操作

drop user `admin`@`163.177.151.110`;

2、权限管理

权限管理：对账号进行权限的支持与回收

*账号创建之初除了登录是没有其他操作权限的

*账号的管理通常需要配合权限的使用

赋权：给账号绑定相应的权限 grant 权限列表 on 数据库|*.数据表|* to 用户名@主机地址
回收：将账号已有的权限回收 revoke 权限列表 on 数据库|*.数据表|* from 用户名@主机地址
刷新权限：flush privileges
查看权限：show grants for 用户名@主机地址

steps
1、创建新的用户账号

2、根据需求赋予/回收指定数据库（一般整库）或者指定数据表的操作权限

3、刷新权限

eg：1、给用户分配权限，db_2下所有表的所有权限

create user `admin`@`localhost` identified by '123456';
grant all privileges on db_2.* to `admin`@`localhost`;

2、给用户admin分配权限：db_2下的查看视图权限

grant select on db_2.v_student_1 to `admin1`;
grant select on db_2.v_student_2 to `admin1`;
grant select on db_2.v_student_3 to `admin1`;
grant select on db_2.v_student_4 to `admin1`;
grant select on db_2.v_student_5 to `admin1`;
grant select on db_2.v_student_6 to `admin1`;

3、回收权限

# 如果用户不要了，可以直接删除用户，保留用户不给权限，就回收全部权限
revoke all on db_2.* from `admin`@`localhost`;

# 针对单独授权表的权限回收：只能针对表进行操作
revoke select on db_2.v_student_1 from `admin1`;

3、角色管理

角色管理：role，即根据角色来分配权限，然后用户只需要关联角色即可（分配角色）：Mysql8以后才有的

*角色的存在可以更方便的用户维护多个具有相同权限的用户（核心价值）
*角色相关操作和语法

创建角色：create role 角色名字1[,角色名字2,...角色名字N]（可批量创建）
分配权限：grant 权限列表 on 数据库|*.数据表|* to 角色名字
绑定角色：grant 角色名字 to 用户名@主机地址
撤销角色：revoke 角色名字 from 用户名@主机地址
回收角色权限：revoke 权限列表 on 数据库|*.数据表|* from 角色名字
删除角色：drop role 角色名字1[,角色名字2,...角色名字N]

steps
关联角色

1、创建角色

2、确定角色的权限：给角色分配权限

3、将角色分配给用户（和第2步可以没有先后关系）

取关角色

1、权限过大：回收角色权限

2、放弃角色：删除角色

eg：1、创建用户角色，分配给具有同样权限的用户

# 创建角色（角色与用户名很相似）
create role developer,app_read,app_write;

# 给角色分配权限
grant all on db_2.* to developer;
grant select on db_2.* to app_read;
grant insert,update,delete on db_2.* to app_write;

# 创建用户，并分配角色给用户
create user 'admin1'@'%' identified by '1234';
create user 'admin2'@'%' identified by '1234';
create user 'admin3'@'%' identified by '1234';

grant developer to 'admin1'@'%';
grant app_read to 'admin2'@'%','admin1'@'%'; # 允许批量给用户分配角色
grant app_write to 'admin3'@'%';

2、回收角色权限或者角色

# 回收角色权限
revoke insert,delete on db_2.* from app_write;

# 回收角色
revoke app_read from 'admin2'@'%';

# 删除角色
drop role developer;