六、数据库安全与保护

1.数据库完整性

什么是数据库完整性

数据库完整性是指数据库中数据的正确性和相容性。

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

列级约束：包括对列的类型、取值范围、精度等的约束
元组约束：指元组中各个字段之间的相互约束
表级约束：指若干元组、关系之间的联系的约束

定义与实现完整性约束

实体完整性
参照完整性
用户定义的完整性

1.实体完整性：在MySQL中，实体完整性是通过主键约束和候选键约束实现的。

主键列必须遵守的的规则：

每个表只能定义一个主键
主键的值（键值）必须能够唯一标识表中的每一个行记录，且不能为null
复合主键不能包含不必要的多余列
一个列名在复合主键的列表中只能出现一次

约束方式	语句	关键字/区别1	区别2
主键约束	CREATE TABLE或ALTER TABLE	PRIMARY KEY	一个表只能定义一个主键
候选键约束	CREATE TABLE或ALTER TABLE	UNIQUE	可以定义若干个候选键

2.参照完整性：

设置外键：

/*
table_name：指定外键所参照的表名
column_name：指定被参照的列名
ON DELETE/UPDATE：指定参照动作相关的sql语句
reference_option：指定参照完整性约束的实现策略，
    RESTRICT：限制策略
    CASCADE：级联策略
    SET NULL：置空策略
    NO ACTION：不采取实施策略
*/
REFERENCES table_name(column_name[(length) [ASC | DESC]], ...)
ON DELETE reference_option
ON UPDATE reference_option

在数据库mysql_test中创建一个商品订单表orders，该表包含订单号order_id，商品名order_product，商品类型order_product_type，客户ID号c_id，订购时间order_date，价格order_price，数量order_amount。要求商品订单表order中的所有客户信息都已在表customers中记录在册。

mysql> create table orders 
    -> (
    -> order_id int not null auto_increment,
    -> order_product char(50) not null,
    -> order_product_type char(50) not null,
    -> c_id int not null,
    -> order_date datetime not null,
    -> order_price double not null,
    -> order_amount int not null,
    -> primary key(order_id),
    -> foreign key(c_id)
    -> references customers(c_id)
    -> on delete restrict
    -> on update restrict
    -> );

3.用户定义的完整性：

非空约束：not null
CHECK约束：CHECK(expr)

命名完整性约束

/*
symbol：指定约束的名字
    只能给基于表的完整性约束指定名字，
    无法给基于列的完整性约束指定名字
*/
CONSTRAINT [symbol]

更新完整性约束

使用ALTER TABLE语句更新与列或表有关的各种约束。

完整性约束不能直接被修改。（先删除，再增加）
使用ALTER TABLE语句，可以独立的删除完整性约束，而不会删除表本身。（DROP TABLE语句删除一个表，则表中所有的完整性约束都会被自动删除）

2.触发器

创建触发器

触发器是用户定义在关系表上的一类由事件驱动的数据对象，也是一种保证数据完整性的方法。

使用CREATE TRIGGER语句创建触发器

/*
trigger_name：指定触发器的名称
trigger_time：指定触发器被触发的时间
trigger_event：指定触发事件
table_name：指定与触发器相关联的表名
FOR EACH ROW：指定对于受触发事件影响的每一行都要激活触发器的动作
trigger_body：指定触发器动作主体
*/
CREATE TRIGGER trigger_name trigger_time trigger_event
ON table_name FOR EACH ROW trigger_body

在数据库mysql_test的表customers中创建一个触发器customers_insert_trigger，用于每次向表customers中插入一行数据后，将用户变量str的值设置为one customer added!：

mysql> create trigger mysql_test.customers_insert_trigger after insert
    -> on mysql_test.customers for each row set @str = 'one customer added!';
Query OK, 0 rows affected (0.04 sec)

mysql> insert into mysql_test.customers
    -> values (0, 'haoqin', 'm', 'jiangsu', null);
Query OK, 1 row affected (0.05 sec)

mysql> select @str;
+---------------------+
| @str                |
+---------------------+
| one customer added! |
+---------------------+
1 row in set (0.00 sec)

删除触发器

使用DROP语句删除触发器

/*
IF EXISTS：用于避免在没有触发器的情况下删除触发器
schema_name：指定触发器所在的数据库名称
trigger_name：指定要删除的触发器名称
*/
DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

删除数据库mysql_test中的触发器customers_insert_trigger:

mysql> drop trigger if exists mysql_test.customers_insert_trigger;

使用触发器

INSERT触发器
DELETE触发器
UPDATE触发器

INSERT触发器

在INSERT触发器代码中，可引用名为NEW（不区分大小写）的虚拟表，来访问被插入的行。
在BEFOR INSERT触发器中，NEW中的值可以被更新。

在数据库mysql_test的表customers中重新创建一个触发器customers_insert_trigger，用于每次向表customers中插入一行数据后，将用户变量str的值设置为新插入客户的id号：

mysql> create trigger mysql_test.customers_insert_trigger after insert
    -> on mysql_test.customers for each row set @str = new.c_id;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mysql_test.customers 
    -> values (3, 'zhangming', 'm', 'jiaozuo', 'haicun');
Query OK, 1 row affected (0.00 sec)

mysql> select @str;
+------+
| @str |
+------+
|    3 |
+------+
1 row in set (0.00 sec)

DELETE触发器

在DELETE触发器代码中，可引用名为OLD（不区分大小写）的虚拟表，来访问被删除的行。
OLD中的值不能被更新。

UPDATE触发器

在UPDATE触发器代码中，可引用名为OLD（不区分大小写）的虚拟表，来访问UPDATE语句执行前的值，也可以引用一个名为NEW（不区分大小写）的虚拟表来访问更新后的值。

在数据库mysql_test的表customers中创建一个触发器customers_update_trigger，用于每次更新表customers时，将该表中c_address列的值设置为c_contact列的值：

mysql> select * from mysql_test.customers;
+------+-----------+-------+-----------+-----------+
| c_id | c_name    | c_sex | c_address | c_contact |
+------+-----------+-------+-----------+-----------+
|    1 | liuhai    | m     | NULL      | chongyi   |
+------+-----------+-------+-----------+-----------+
7 rows in set (0.00 sec)

mysql> create trigger mysql_test.customers_update_trigger before update 
    -> on mysql_test.customers for each row set new.c_address = old.c_contact;
Query OK, 0 rows affected (0.02 sec)

mysql> update mysql_test.customers
    -> set c_contact = 'jinzhong'
    -> where c_id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select c_address, c_contact from mysql_test.customers 
    -> where c_id = 1;
+-----------+-----------+
| c_address | c_contact |
+-----------+-----------+
| chongyi   | jinzhong  |
+-----------+-----------+
1 row in set (0.00 sec)

3.安全性与访问控制

数据库的安全性是指保护数据库以防止不合法的使用而造成数据泄露、更改或破坏，所以安全性对于任何一个DBMS来说都是至关重要的。
数据库的安全性和访问控制分为：
- 身份验证
- 数据库用户权限确认

用户账号管理

root用户

mysql> select user from mysql.user;
+---------------+
| user          |
+---------------+
| mysql.session |
| mysql.sys     |
| root          |
+---------------+
3 rows in set (0.00 sec)

使用CREATE USER语句创建MySQL用户

/*
user：指定创建用户账号。
    格式：‘username’@‘hostname’，本机的hostname默认为‘%’
IDENTIFIED BY：可选项，指定用户账号对应的口令
PASSWORD：可选项，指定散列口令
password：指定用户账号的口令
*/
CREATE USER user [IDENTIFIED BY [PASSWORD] 'password']

在MySQL服务器中添加两个新的账户，其用户名分别为zhangshan和lisi，他们的主机名均为localhost，用户zhangshan的口令为明文123，用户lisi的口令为对明文使用PASSWORD()函数加密返回的散列值：

mysql> select password('456');
+-------------------------------------------+
| password('456')                           |
+-------------------------------------------+
| *531E182E2F72080AB0740FE2F2D689DBE0146E04 |
+-------------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> create user 'zhangshan'@'localhost' identified by '123',
    -> 'lisi'@'localhost' identified by password '*531E182E2F72080AB0740FE2F2D689DBE0146E04';
Query OK, 0 rows affected, 1 warning (0.02 sec)

使用DROP USER语句删除用户账号

DROP USER user[, user, ...]

删除前面例子中的lisi用户：

mysql> drop user lisi@localhost;

使用RENAME USER语句修改用户账号

/*
old_user：指定系统中已经存在的用户账号
new_user：指定新的用户账号
*/
RENAME USER old_user TO new_user[, old_user TO new_user]

将前面例子中用户zhangshan的名字修改成wangwu：

mysql> rename user 'zhangshan'@'localhost' to 'wangwu'@'localhost';

使用SET PASSWORD语句修改用户登录口令

/*
FOR user：指定与修改的口令的用户
PASSWORD('new_pwd')：使用PASSWORD()函数设置新口令new_pwd
encrypted pwd：表示已被函数PASSWORD()加密后的口令值
*/
SET PASSWORD [FOR user] = {
    PASSWORD('new_pwd') | 'encrypted pwd'
}

设置用户wangwu的登录口令为pass：

mysql> set password for 'wangwu'@'localhost' = 'pass';

账号权限管理

使用GRANT语句为用户授权

/*
priv_type：用于指定权限的名称
column_list：用于指定权限要授予给哪些具体的列
ON：用于指定权限授予的对象类型和级别
object_type：用于指定权限授予的对象类型
priv_level：用于指定权限授予的对象级别
TO：用于设定用户的口令，以及指定被授予权限的用户user
user_specification：指定用户，格式：‘username’@‘hostname’ [identified by [password] 'password']
WITH：可选项，用于实现权限的转移或限制
*/
GRANT priv_type [(column_list)][, priv_type [col_list], ...]
ON [object_type] priv_level
TO user_specification[, user_specification, ...]
[WITH GRANT OPTION]

授予用户wangwu在数据库mysql_test的表customers上拥有对列c_id和c_name的SELECT权限：

mysql> grant select (c_id, c_name)
    -> on mysql_test.customers
    -> to 'wangwu'@'localhost';

授予当前系统中一个不存在的用户liming和用户huang，要求创建这两个用户，并设置对应的系统登录口令，同时授予他们在数据库mysql_test中customers表拥有SELECT和UPDATE权限：

mysql> grant select, update
    -> on mysql_test.customers
    -> to 'liming'@'localhost' identified by '123456',
    -> 'huang'@'localhost' identified by '123456';

授予已存在的用户wangwu在数据库mysql_test中拥有所有的数据库操作权限：

mysql> grant all
    -> on mysql_test.*
    -> to 'wangwu'@'localhost';

授予系统中已存在的用户wangwu拥有创建用户的权限：

mysql> grant create user
    -> on *.*
    -> to 'wangwu'@'localhost';

权限的转移

授予当前系统中不存在的用户zhou在数据库mysql_test的表customers上拥有SELECT和UPDATE权限，并允许其可以将这些权限授予给其他用户：

mysql> grant select, update
    -> on mysql_test.customers
    -> to 'zhou'@'localhost' identified by '123456'
    -> with grant option;

使用REVOKE语句撤销用户权限

REVOKE priv_type [(column_list)][, priv_type [col_list], ...]
ON [object_type] priv_level
FROM user[, user, ...]

回收系统中已存在的用户zhou在数据库mysql_test中的表customers上的SELECT权限：

mysql> revoke select
    -> on mysql_test.customers
    -> from zhou@localhost;

4.事务与并发控制

事务的概念

所谓事务是用户定义的一个数据操作序列，这些操作可以作为一个完整的工作单元，要么全部执行，要不全部不执行，是一个不可分割的工作单位。事务中的操作一般是对数据的更新操作，包括增、删、改。

事务的概念

以BEGIN TRANSACTION语句开始
以COMMIT语句或ROLLBACK语句结束

事务的特征

事务是不可分割的最小工作单位。

ACID

ACID

根据事务的ACID特征，分析并编写银行数据库系统中转账事务T：从账户A转账S金额资金到B账户中。
此事务应包含的操作如下（伪代码）：

begin transaction
read(A);
A = A - S;
write(A);
if A < 0 then
    rollback;
else
    read(B);
    B = B + S;
    write(B);
    commit;
end if;

并发操作问题

丢失更新：事务T1，T2同时读入同一数据并加以修改，T2的提交结果会破坏T1的提交结果。
不可重复读：事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果。
读‘脏’数据：事务T1修改数据后撤销，使得T2读取的数据与数据库中不一致。

封锁

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

锁实质上是就是允许或阻止一个事务对一个数据对象的存取特权。
基本的封锁类型：

排它锁（X锁），用于写操作。
共享锁（S锁），用于读操作。

用封锁进行并发控制

封锁的工作原理：

若事务T对数据D加了X锁，则所有别的事务对数据D 的锁请求都必须等待直到事务T释放锁。
若事务T对数据D加了S锁，则别的事务还可以对数据D请求S锁，而对数据D的X锁请求还是要等待直到事务T释放锁。
事务执行数据库操作时都要先请求相应的锁，即对读取请求S锁，对更新请求X锁。这个过程一般是由DBMS在执行操作时自动隐含的进行。
事务一直占有获得的锁直到结束时释放。

锁的粒度

我们通常以粒度来描述封锁的数据单元大小。
DBMS可以决定不同粒度的锁。
粒度越细，并发性就越大，但软件复杂性和系统开销也就越大。

封锁的级别

封锁的级别又称为一致性级别或隔离度。

0级封锁：不重写其他非0级封锁事务的未提交的更新数据。（实用价值低）
1级封锁：不允许重写未提交的更新数据。（防止了丢失更新的发生）
2级封锁：既不重写，也不读取未提交的更新数据。（防止了读脏数据）
3级封锁：不读未提交的更新数据，不写任何（包括读操作）未提交的数据。

死锁和活锁

活锁---处理方案--->先来先服务

死锁---处理方案--->预防

一次性锁请求
锁请求排序
序列化处理
资源剥夺

可串行性

一组事务的一个调度就是它们的基本操作的一种排序。
在数据库系统中，可串行性就是并发执行的正确性准则，即当一组事务的并发执行调度是可串行化的，才认为它们是正确的。

两段封锁法

发展（Growing）或加锁阶段
收缩（Shrinking）或释放锁阶段

5.备份与恢复

数据库备份与恢复的概念

应用场景：

计算机硬件故障
计算机软件故障
病毒
人为误操作
自然灾害
盗窃

数据备份是指通过导出数据或复制表文件的方式来制作数据库的复本；
数据库恢复则是当数据库出现故障或遭到破坏时，将备份的数据加载到系统，从而使数据库从错误状态恢复到备份时的正确状态。
数据库的恢复是以备份为基础的，它是与备份相对应的系统维护和管理操作。

备份数据的方法

使用SELECT...INTO OUTFILE语句备份数据

/*
OUTFILE：导出语句关键字
file_name：指定数据备份文件的名称
DUMPFILE：导出的备份文件里面所有的数据行都会彼此紧挨着放置
FIELDS/LINES：决定数据行在备份文件中的存储格式
TERMINATED：指定字段之间的符号
ENCLOSED：指定包裹文件中字符值的符号
ESCAPED BY：指定转义字符
TERMINATED BY：指定数据行结束的标志
*/
SELECT * INTO OUTFILE 'file_name' exprot_options
    | INTO DUMPFILE 'file_name' 

[
FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[LINES TERMINATED BY 'string']

恢复数据的方法

使用LOAD DATA...INFILE语句恢复数据

/*
file_name.txt：指定待导入的数据库备份文件名称
table_name：指定需要导入数据的表名
FIELDS：判断字段之间和数据行之间的符号
STARTING BY：指定一个前缀
TERMINATED BY：指定一行结束的标志
*/
LOAD DATA INFILE ‘file_name.txt’
INTO TABLE table_name
[
FIELDS
    [TERMINATED BY 'string']
    [[OPTIONALLY] ENCLOSED BY 'char']
    [ESCAPED BY 'char']
]
[
LINES 
    [STARTING BY 'string']
    [TERMINATED BY 'string']
]

备份数据库mysql_test中表customers的全部数据到backupfile.txt的文件中，要求字段值如果是字符则用双引号标注，字段值之间用逗号隔开，每行以问号为结束标志。然后，将备份后的数据导入到一个和customers表结构相同的空表customers_copy中：

mysql> select * from mysql_test.customers
    -> into outfile '/usr/local/mysql-files/backupfile.txt'
    -> fields terminated by ','
    -> optionally enclosed by ""
    -> lines terminated by '?';
Query OK, 7 rows affected (0.00 sec)

mysql> create table mysql_test.customers_copy
    -> (
    -> c_id int not null auto_increment,
    -> c_name char(20) not null,
    -> c_sex char(1) not null,
    -> c_address char(10) null,
    -> c_contact char(10) null,
    -> primary key(c_id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> load data infile '/usr/local/mysql-files/backupfile.txt'
    -> into table mysql_test.customers_copy
    -> fields terminated by ','
    -> optionally enclosed by ""
    -> lines terminated by '?';
Query OK, 7 rows affected (0.01 sec)
Records: 7  Deleted: 0  Skipped: 0  Warnings: 0