数据库（六）—— 数据库安全与保护

一、数据库完整性

        数据完整性约束是为了防止数据库中存在不符合语义的数据，为了维护数据的完整性，加在数据库数据之上的语义约束条件就是数据完整性约束，而DBMS检查是否满足完整性约束条件的机制就称为完整性检查。

1、完整性约束条件的作用对象

        完整性检查是围绕完整性约束条件进行的，因而完整性约束条件是完整性检查机制的核心。完整性约束条件的作用对象可以是列、元组和表。

⑴ 列级约束

        列级约束主要是指对列的类型、取值范围、精度等的约束，具体包括以下内容：

① 对数据类型的约束；

② 对数据格式的约束；

③ 对取值范围或取值集合的约束；

④ 对空值的约束。

⑵ 元组约束

        元组约束指无级中各个字段之间的相互约束。

⑶ 表级约束

        表级约束指若干元组之间、关系之间的联系的约束。

2、定义与实现完整性约束

        关系模型的完整性规则是对关系的某种约束条件，关系模型中可以有三类完整性约束，分别是实体完整性、参照完整性和用户定义的完整性。

⑴ 实体完整性

       在MYSQL中，实体完整性是通过主键约束和候选键约束来实现的。

① 主键约束

        主键可以是表中某一列，也可以是表中多个列所构成的一个组合，多个列组合而成的主键也被称为复合主键。主键必须遵守以下规则：

a. 每一个表只能定义一个主键；

b. 键值必须能够唯一标识表中的每一行记录，且不能为NULL（唯一性原则）；

c. 复合主键不能包含不必要的多余列（最小化原则）；

d. 一个列名在复合主键的列表中只能出现一次。

        主键约束使用关键字PRIMARY KEY来实现，其实现方式有两种：一种是作为列的完整性约束，直接在列定义后面加上关键字；一种是作用表的完整性约束，在所有列定义语句之后添加一条主键约束语句。复合主键只能使用第二种方式。定义主键约束后，MYSQL会自动为主键创建一个唯一性索引，该索引名默认为PRIMARY，也可以重命名。

        示例如下：

/*列级主键约束，在列定义之后添加主键约束关键字*/
create table user
(
	oid int not null auto_increment primary key comment '自增主键',
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码'
)ENGINE=INNODB;
/*表级主键约束，在所有列定义之后添加主键约束语句*/
create table user
(
	oid int not null auto_increment comment '自增主键',
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码',
  primary key (oid)
)ENGINE=INNODB;
/*表级主键约束，添加复合主键约束语句*/
create table user
(
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码',
	sex char(2) not null default '男' comment '用户性别',
  primary key (username,sex)
)ENGINE=INNODB;
/*查看创建主键约束后的user表索引情况*/
show index from user;

② 候选键约束

        候选键与主键差不多，它使用关键字UNIQUE，它与主键的区别是：

a. 一个表中只能定义一个主键，但可以定义多个候选键；

b. 定义主键时，系统会自动产生PRIMARY KEY索引，而定义候选键时，会自动产生UNIQUE索引。

        示例如下：

/*列级约束，添加候选键约束关键字*/
create table user
(
	oid int not null auto_increment primary key comment '自增主键',
	username varchar(50) not null unique comment '用户名',
	pwd varchar(100) not null comment '用户密码'
)ENGINE=INNODB;
/*表级约束，添加候选键约束语句*/
create table user
(
	oid int not null auto_increment primary key comment '自增主键',
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码',
  unique (username)
)ENGINE=INNODB;
/*查看创建主键约束后的user表索引情况*/
show index from user;

⑵ 参照完整性

        参照完整性是通过在创建表或更新表的同时定义一个外键声明来实现的。外键声明有两种方式：一种是直接在列定义之后加上reference_definition语法项；一种是在所有列的属性定义后面添加FOREIGN KEY (index_column_name,...) reference_definition子句的语法项。在MYSQL中只能表级的外键约束才会生效。

        其中，reference_definition语法项的定义如下：

/*参照完整性的reference_definition语法项*/
REFERENCES tb_name(index_column_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

         index_column_name的语法格式如下：

column_name [(length)] [ASC|DESC]

        reference_option的语法格式如下：

RESTRICT|CASCADE|SET NULL

         在上面的语法中，语法项reference_option用于指定参照完整性约束的实现策略，当没有明确指出参照完整性的实现策略时，默认使用RESTRICT，它表示当删除或更新被参照表中的被参照列时，参照表中的参照列拒绝删除或更新；CASCADE表示级联策略，当被参照表中删除或更新记录时，自动删除或更新参照表中匹配的记录行；SET NULL表示置空策略，当被参照表中删除或更新记录时，参照表中相关的记录置空。指定外键时还要遵守以下规则：

① 被参照表必须已经创建（也可以是同一个表，这样的表被称为自参照表，这种结构被称为自参照结构）；

② 必须为被参照表定义主键；

③ 外键允许出现一个空值，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的；

④ 外键中列的数目必须和被参照表中的主键列的数目相同；

⑤ 外键中列的数据类型必须和被参照表的主键中对应的数据类型相同。

        示例如下：

/*表级参照完整性约束示例，且设置更新删除时使用级联策略*/
create table user
(
	oid int not null auto_increment primary key comment '自增主键',
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码',
  ref_oid int not null comment '外键参照emp表的主键',
  foreign key(ref_oid) references emp(empno) on delete cascade on update cascade
)ENGINE=INNODB;
/*查看表的所有外键*/
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
where REFERENCED_TABLE_NAME = 'emp';

⑶ 用户定义的完整性

        MYSQL支持用户自定义完整性约束，分别是非空约束、CHECK约束和触发器。

① 非空约束

        非空约束是在列定义的后面加上NOT NULL作为限定词，来约束该列的取值不能为空。

② CHECK约束

        CHECK约束允许使用复杂的表达式作为限定条件，比如子查询，CHECK约束支持列级和表级的限定。但在MYSQL中并不支持CHECK约束的使用，MYSQL存储引擎会对CHECK语句进行分析但会忽略CHECK语句，这只是为了提高兼容性。可以使用enum或触发器来解决这个问题。使用enum示例如下：

/*使用enum来代替CHECK检查约束*/
create table user
(
	oid int not null auto_increment primary key comment '自增主键',
	sex enum('男','女') not null default '男' comment '用户性别'
)ENGINE=INNODB;

3、命名完整性约束

       为了删除和修改完整性约束，首先需要在定义约束的同时对其进行命名，命名完整性约束的方法是在各种完整性约束的定义说明之前加上关键字CONSTRAINT和该约束的名字，这个名字在数据库中必须是唯一的，如果没有给出该名字，则MYSQL会自动创建一个。示例如下：

/*命名完整性约束示例*/
create table user
(
	oid int not null auto_increment primary key comment '自增主键',
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码',
  ref_oid int not null comment '外键参照emp表的主键',
  constraint user_ref_emp foreign key(ref_oid) references emp(empno) on delete cascade on update cascade
)ENGINE=INNODB;

4、更新完整性约束 

        完整性约束不可以直接被修改，若要修改某个完整性约束，实际上是先用ALTER TABLE语句删除该约束，然后再新增一个与该约束同名的新约束。使用ALTER TABLE语句可以独立的删除完整性约束，而不会删除表本身，若使用DROP TABLE语句删除一个表，则这个表中的所有完整性约束都会自动被删除。删除完整性约束示例如下：

/*删除外键*/
alter table user drop foreign key user_ref_emp
/*添加外键*/
alter table user
add constraint user_ref_emp foreign key(ref_oid) references emp(empno) on delete cascade on update cascade

二、触发器

        触发器是用户定义在关系表上的一类由事件驱动的数据库对象，也是一种保证数据完整性的方法。它的作用主要是实现主键和外键不能保证的复杂的参照完整性和数据的一致性，从而有效的保护表中的数据。

1、创建触发器

        创建触发器的语法如下：

CREATE TRIGGER trigger_name trigger_time trigger_event
ON tb_name FOR EACH ROW trigger_body

         在上面的语法中，trigger_time有两个可选项，即关键字BEFORE和关键字AFTER，用于表示触发器是在激活它的之前或之后触发；trigger_event用于指定触发事件，可以是INSERT、UPDATE、DELETE中的一个。

        注意：每个表中每个事件每次只允许创建一个触发器，因此每个表最多支持创建6个触发器。

        示例如下：

create table user
(
	oid int not null auto_increment comment '自增主键',
	username varchar(50) not null comment '用户名',
	pwd varchar(100) not null comment '用户密码',
	sex char(2) not null default '男' comment '用户性别',
    age int comment '用户年龄',
	address varchar(200) comment '用户住址',
    phone varchar(20) comment '电话',
    primary key (oid)
)ENGINE=INNODB;

/*创建触发器的示例*/
create trigger tri_user_insert after insert on user for each row set @str = '一个小测试';
/*向user表中插入一条数据*/
insert into user values(null,'zhangsan','pwd_zhangsan','男',20,null,null);
/*查询用户变量验证触发器*/
select @str

2、删除触发器

        删除触发器的语法如下：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

       示例如下：

drop trigger if exists demo.tri_user_insert

注意：当删除一个表的同时，也会删除这个表上的所有触发器，如果要修改一个触发器，必须先删除它，再重新创建。 

 3、使用触发器

⑴ INSERT触发器

        在INSERT触发器代码内，可以引用一个名为NEW的虚拟表，来访问被插入的行。在BEFORE INSERT触发器中，NEW中的值也可以被更新，即允许更改被插入的值。示例如下：

/*创建INSERT触发器*/
create trigger tri_user_insert after insert on user for each row set @str = new.username;
/*向user表中插入一条数据*/
insert into user values(null,'zhangsan','pwd_zhangsan','男',20,null,null);
/*查询用户变量验证触发器*/
select @str

⑵ DELETE触发器

        在DELETE触发器代码内，可以引用一个名为OID的虚拟表，来访问被删除的行，OLD中的值全部是只读的，不能被更新。示例如下：

/*创建DELETE触发器*/
create trigger tri_user_delete after delete on user for each row set @str = old.oid;
/*删除user表中的一条数据*/
delete from user where username = 'zhangsan';
/*查询用户变量验证触发器*/
select @str

⑶ UPDATE触发器

        在UPDATE触发器代码内，可以引用一个名为OLD的虚拟表访问以前的值，也可以引用一个名为NEW的虚拟表来访问更新的值。在BEFORE UPDATE触发器中，NEW中的值可能也被更新，即允许更改将要用于UPDATE语句中的值。当触发器涉及对触发表自身的更新操作时，只能使用BEFORE UPDATE触发器。示例如下：

/*创建UPDATE触发器*/
create trigger tri_user_update before update on user for each row set new.pwd = old.username
/*更新user表中的数据*/
update user set pwd = 'wangwu123' where username = 'zhangsan'

三、安全性与访问控制

1、用户账号管理

        查看MYSQL中现有的用户，如下：

select user from mysql.user

⑴ 创建用户账号

        创建用户账号的语法格式如下：

CREATE USER user[IDENTIFIED BY [PASSWORD]'password']

          上面的语法格式中，user指定创建用户账号，其格式为'user_name'@'host_name'，user_name表示用户名，host_name表示主机名，即连接MYSQL时所在的主机名字。如果没有指定主机名，则默认表示为“%”。关键字PASSWORD用于指定散列口令（函数PASSWORD()可以返回密码的散列值），若使用明文设置口令，需忽略PASSWORD关键字。示例如下：

/*查询密码的散列值*/
select password('123');
/*创建两个用户，一个使用明文密码，一个使用散列密码*/
create user 'zhangsan1'@'localhost' identified by '123',
'zhangsan2'@'localhost' identified by password '*23AE809DDACAF96AF0FD78ED04B6A265E05AA257';

注意：如果两个用户具有相同的用户名和不同的主机名，MYSQL会将它们视为不同的用户，并允许为这两个不同的用户分配不同的权限集合。 

⑵ 更改用户账号

        其语法格式如下：

RENAME USER old_user TO new_user [,old_user TO new_user]...

         使用示例如下：

rename user 'zhangsan1'@'localhost' to 'wangwu'@'localhost' 

⑶ 修改用户口令

        其语法格式如下：

SET PASSWORD [FOR user]={PASSWORD('new_password')|'encrypted password'} 

        如果要修改用户的口令 ，必须将新口令传递到PASSWORD()函数中进行加密或者直接使用加密后的口令。可选项FOR子句如果不加，表示修改当前用户的口令，如果加上的话表示修改指定用户的口令。示例如下：

set password for 'wangwu'@'localhost' = '*531E182E2F72080AB0740FE2F2D689DBE0146E04'

⑷ 删除用户账号

        删除用户账号的语法格式如下：

DROP USER user [,user]...

       示例如下：

drop user 'wangwu'@'localhost'

2、用户权限管理

        创建用户后，需要为用户分配适当的权限，新创建的账号只能登录MYSQL服务器，不能执行任何数据库操作。查看用户的授权表如下：

show grants for 'zhangsan2'@'localhost'

⑴ 权限的授予

        常用的语法格式如下：

GRANT privi_type [(column_list)] [,privi_type [(column_list)]] ...
ON [object_type] privi_level TO user_specification [,user_specification] ...
[WITH GRANT OPTION]

        在上面的语法格式中，语法项privi_type用来指定权限的名称，比如SELECT、UPDATE、DELETE等数据库操作；ON子句用于指定权限授予的对象和级别； 可选项object_type用于指定权限授予的对象类型，包括表、函数和存储过程，分别用关键字TABLE、FUNCTION、PROCEDURE来进行标识；语法项privi_level用于指定权限的级别，*表示当前数据库中的所有表，*.*表示所有数据库中的所有表；TO子句用来指定授予权限的的用户和口令，已经指定口令的会覆盖，如果是一个不存在的用户，则会自动创建用户；WITH子句用于实现权限的转移或限制。

        授予用户在表中的某些列上进行查询的权限，示例如下：

/*授予用户在表中的某些列上进行查询的权限示例*/
grant select(empno,ename) on demo.emp to 'zhangsan2'@'localhost'
/*在zhangsan2账号下查询emp中的empno和ename列*/
select empno,ename from demo.emp

        创建一个新的用户并设置登录口令，同时授予他们在emp表上拥有SELECT和UPDATE的权限，示例如下：

grant select,update on demo.emp to 'zhangsan3'@'localhost' identified by '123'

        授予用户可以在数据库中执行所有操作的权限，示例如下：

grant all on demo.* to 'zhangsan3'@'localhost'

         授予用户创建用户的权限，示例如下：

grant create user on *.* to 'zhangsan3'@'localhost'

        此外，关于GRANT语句中语法项privi_type的使用， 可以参考MYSQL官网：Privileges Provided by MySQL，或者参考相关博客的总结：mysql grant 用户权限总结。

⑵ 权限的转移

        WITH子句可以实现权限的转移，如果将WITH子句指定为WITH GRANT OPTION，则表示TO子句中所指定的用户都具有把自己所拥有的权限授予给其他用户的权利。示例如下：

/*创建一个新的用户给其相关的权限，并允许其将自身的权限授予其他用户*/
grant select,update on demo.emp to 'zhangsan4'@'localhost' identified by '123' with grant option

⑶ 权限的撤销 

         回收权限的语法是格式如下：

REVOKE privi_type [(column__list)] [,privi_type[(column_list)]]...
ON [object_type] privi_level FROM user [,user]...

         回收所有权限的语法格式如下：

REVOKE ALL PRIVILEGES,GRANT OPTION FROM user [,user]...

         回收用户的SELECT权限的示例如下：

revoke select on demo.emp from 'zhangsan4'@'localhost'

注意：要使用REVOKE语句，必须要拥有数据库的全局CREATE USER权限或UPDATE权限。 

四、事务与并发控制

        数据库中的数据是共享资源，因此数据库系统通常都是多用户系统，即支持多个不同程序或同一程序并发地存取数据库中相同的数据，为了防止它们彼此干扰，从而保证数据库的正确性不被破坏，避免数据的不一致性，这种机制就被称为并发机制。其中，事务就是为保证数据的一致性而产生的一个概念和基本手段。

1、事务的概念

        事务是用户定义的一个数据操作序列，这些操作可作为一个完整的工作单元，要么全部执行，要么全部不执行，是一个不可分割的工作单位。用户显式定义事务的语句一般有三条：BEGIN TRANSACTION 、COMMIT和ROLLBACK。

2、事务的特征

        事务具有四个特征：原子性、一致性、隔离性、持久性。

⑴ 原子性

        事务的原子性保证事务包含的一组操作是原子不可分的，即事务是不可分割的最小工作单元，所包含的这些操作是一个整体，事务在执行时要么全部成功，要么全部失败回滚。

⑵ 一致性

        一致性要求事务必须满足数据库的完整性约束，且事务执行完毕后将数据库由一个一致性状态转变到另一个一致性状态。

⑶ 隔离性

        隔离性要求事务是彼此独立的、隔离的，即一个事务的执行不能被其他事务所干扰，一个事务对数据库变更的结果必须在它提交之后，另一个事务才能存取。

        多个事务并发执行时，其结果应该等价于它们的一种顺序执行的结果，就如同串行调度执行事务一样，这一特性也被称为可串行性，即系统执行的任何交错操作调度实质上是一个串行调度，而串行调度是指每当调度一个事务，在该事务的所有操作没有结束之前其他的事务不能被执行。

⑷ 持久性

        持久性是指一个事务一旦提交，它对数据库中数据的改变应该是永久性的，且接下来的其他操作或故障不应该对其执行结果有任何影响。

3、并发操作问题

        事务是并发控制的基本单位，并发控制机制就是要用正确的方式调度并发操作，使一个用户事务的执行不受其他事务的干扰，从而避免造成数据的不一致性。

⑴ 脏读

        脏读是指一个事务处理中读取了另一个事务处理中未提交的数据。

⑵ 不可重复读

        不可重复读是指对于数据库中某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔中，被另一个事务修改并提交了，从而无法再重现前一次读取的结果。

⑶ 幻读

        幻读是事务非独立执行时发生的一种现象，如果一个事务对数据表的中每一行的某个数据项都进行了修改并提交，而另一个事务插入一条未修改的新数据，如果第一事务的用户查看刚刚修改的数据，就会发现有一条未修改的数据，好像幻觉一样，这就是幻读。

        幻读和不可重复读都是读取了另一个已经提交的事务，不同的是幻读读到的是其他事务的新增数据，而不可重复读读到的是其他事务的更改数据。

4、四种隔离级别

⑴ Serializable（串行化）：可避免脏读、不可重复读、幻读的发生；

⑵ Repeatable read（可重复读）：可避免脏读、不可重复读的发生；

⑶ Read commited（读已提交）：可避免脏读的发生；

⑷ Read uncommited（读未提交）：最低级别，任何情况都无法保证。

        以上四种隔离级别最高的是Serializable级别，最低的是Read uncommitted级别，当然级别越高，执行效率就越低。像Serializable这样的级别，就是以锁表的方式(类似于Java多线程中的锁)使得其他的线程只能在锁外等待，所以平时选用何种隔离级别应该根据实际情况。

　　在MySQL数据库中，默认的为Repeatable read (可重复读)；而在Oracle数据库中，只支持Serializable (串行化)级别和Read committed (读已提交)这两种级别，其中默认的为Read committed级别。

关于隔离性还可以参考：

① 数据库事务的四大特性以及事务的隔离级别

② 数据库并发事务存在的问题（脏读、不可重复读、幻读等）

5、封锁

        封锁是最常用的并发控制技术，它的基本思想是：需要时，事务通过向系统请求对它所希望的数据对象进行加锁，以确保它不会被非预期改变。

⑴ 锁

        一个锁实质上就是允许或阻止一个事务对一个数据对象的存取特权。一个事务对一个数据对象加锁的结果就是将别的事务封锁在该对象之外，防止了其他事务对该对象的变更，而加锁的事务则可执行它所希望的处理并维持该对象的正确状态。

        基本的锁类型有两种：排他锁和共享锁。一般写操作要求排他锁，读操作要求共享锁。

⑵ 封锁的粒度

        通常以粒度来描述封锁的数据单元的大小。DBMS可以决定不同粒度的锁，锁住整个数据库，DBMS的管理和控制最简单，只需要设置和测试一个锁，故系统开销也最小，然而对数据的存取只能顺序进行，因而系统的总体系统大大下降；反之，数据元素锁将提供最多的并发性，但DBMS要设置大量的锁装置来标识那些当前被封锁的数据元素，同时还要大量的锁检测，影响了每一个事务的服务性能，系统总体性能也因此而下降。所以，大多高性能的系统都选择折中的锁粒度。

⑶ 活锁与死锁

        在并发事务处理过程中，由于锁会使一事务处于等待状态而调度其他事务处理，因而该事务可能会因为优先级低而永远等待下去，这种现象称为“活锁”；而两个以上事务循环等待被同组中的另一事务锁住的数据单元的情形，称为“死锁”。

        预防死锁的办法有以下几种：

① 一次性锁请求

        每一事务在处理时一次提出所有的锁请求，仅当这些请求全部满足时事务处理才会进行，否则让其等待。

② 锁请求排序

        将每个数据单元标以线性顺序，然后要求每一事务都按此顺序提出锁请求。

③ 序列化处理

        通过应用设计为每一数据单元建立一个主程序，对给定数据单元的所有请求都发送给主程序，而主程序以单道的形式运行，系统以多道形式运行。

④ 资源剥夺

        每当事务因锁请求不能满足而受阻时，强行令两个冲突的事务中的一个ROLLBACK，释放所有的锁，以后再重新运行（有可能会出现活锁）。

⑤ 死锁检测

        一旦检测到系统已发生的死锁再进行解除处理，死锁检测可以以图论的方法实现，并以正在执行的事务为结点。

⑷ 两段封锁法

        两段封锁法是事务遵循两段锁协议的调度方法，它规定在任何一个事务中，所有加锁操作都必须在所有释放锁操作之前。其中，事务划分为以下两个阶段：

① 发展或加锁阶段

        在此期间，对任一数据对象进行任一操作之前，事务都要获得对该对象的一个相应的锁。

② 收缩或释放锁阶段

        一旦事务释放了一个锁，则标明它已经进入了此阶段，此后它就不能再请求任何另外的锁。

关于两段锁有如下定理：

        遵循两段锁协议的事务的任何并发调度都是可串行化的。

五、备份与恢复

        数据库备份是指通过导出数据或者复制文件的方式来制作数据库的复本；数据库恢复则是当数据库出现故障或遭到破坏时，将备份的数据库加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。

Mysql数据库的备份与恢复Java示例程序可以参考：https://blog.csdn.net/Alexshi5/article/details/82795716