数据库系统原理（第6章：数据库安全与保护）

一、数据库完整性

数据库的安全保护：

• 完整性控制：数据库完整性是指数据库中数据的正确性和相容性
• 安全性控制：数据库防止不合法的操作而造成数据泄露、更改或破坏
• 并发控制：事务就是为了保证数据一致性而产生的一个概念和基本手段
• 数据库的备份与恢复：保证数据库的可靠性和完整性

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

SRE实战 互联网时代守护先锋，助力企业售后服务体系运筹帷幄！一键直达领取阿里云限量特价优惠。

完整性约束的作用：防止数据库中存在不符合语义的数据。

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

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

完整性约束条件是完整性控制机制的核心

定义与实现完整性约束：实体完整性 、参照完整性、 用户定义的完整性

**************************实体完整性**************************

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

主键列必须遵守的规则

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

 

 

主键约束与候选键约束的区别 

• 主键约束       一个表只能创建 一个主键     关键字 PRIMARY KEY
• 候选键约束   可以定义 若干个候选键     关键字 UNIQUE

***********************参照完整性*******************************

REFERENCES tbl_name(index_col_name,…)
 [ON DELETE reference_option]
 [ON UPDATE reference_option]

tbl_name：指定外键所参照的表名

index_col_name：指定被参照的列名

ON DELETE：指定参照动作相关的SQL语句

reference_option：指定参照完整性约束的实现策略 （RESTRICT-限制策略（默认的） | CASCADE-级联策略 | SET NULL-置空策略 | NO ACTION-不采取实施策略）

 

 

 

**********************用户定义的完整性****************************

非空约束：NOT NULL

CHECK 约束：CHECK(expr)

触发器

命名完整性约束：CONSTRAINT [symbol]    symbol：指定的约束名字

只能给基于表的完整性约束指定名字，无法给基于列的完整性约束指定名字

命名完整性约束的方法是在各种完整性约束的定义说明之前加上关键字（ CONSTRAINT ）和该约束的名字

更新完整性约束

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

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

 

二、触发器

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

*************************创建触发器******************************

使用CREATE TRIGGER语句创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event
 ON tbl_name FOR EACH ROW trigger_body

trigger_name：指定触发器的名称

trigger_time：指定触发器被触发的时刻 

trigger_event：指定触发器的触发事件

tbl_name：指定与触发器相关联的表名

FOR EACH ROW ：指定对于受触发事件影响的每一 行都要激活触发器的动作

trigger_body：指定触发器动作主体

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

 CREATE TRIGGER mysql_test.customers_insert_trigger AFTER INSERT
 -> ON mysql_test.customers FOR EACH ROW SET @str=‘one customer added!’

使用DROP语句删除触发器

语法：DROP TRIGGER [IF EXISTS][schema_name.]trigger_name

• IF EXISTS：用于避免在没有触发器的 情况下删除触发器
• schema_name：指定触发器所在的数据库的名称
• trigger_name：指定要删除的触发器名称

例如：DROP TRIGGER IF EXISTS mysql_test.customers_insert_trigger;

使用触发器

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

*******************INSERT触发器***********************

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

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

CREATE TRIGGER mysql_test.customers_insert_trigger AFTER INSERT
 -> ON mysql_test.customers FOR EACH ROW SET @str=NEW.cust_id;

********************DELETE触发器*************************

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

*******************UPDATE触发器***************************

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

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

CREATE TRIGGER mysql_test.customers_update_trigger BEFORE UPDATE
 -> ON mysql_test.customers FOR EACH ROW
 -> SET NEW.cust_address=OLD.cust_contact;

 

三、安全性与访问控制

数据库的安全性是指保护数据库以防止不合法的使用而造成数据泄露、更 改或破坏，所以安全性对于任何一个DBMS来说都是至关重要的。

安全性与访问控制：身份验证 、数据库用户权 限确认

使用CREATE USER语句创建MySQL账户

语法：CREATE USER user   [IDENTIFIED BY [PASSWORD]’password’]

• user格式：指定创建用户账号 格式：’user_name’@’host name’
• IDENTIFIED BY：可选项，指定用户账号对应的口令
• PASSWORD：可选项，指定散列口令

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

CREATE USER ‘zhangsan’@’localhost’ IDENTIFIED BY ‘123’,
 -> ‘lisi’@’localhost’ IDENTIFIED BY PASSWORD
 -> ‘*531E182E272080AB0740FE2F2D689DBE0146E04’;

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

语法：DROP USER user [,user]…

例如：DROP USER lisi@localhost

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

 

 

 例如：RENAME USER ‘zhangsan’@’localhost’ TO ‘wangwu’@’localhost’;

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

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

例如：SET PASSWORD FOR 'username'@'localhost' = PASSWORD('pass');

使用GRANT语句为用户授权

GRANT
 priv_type [(column_list)]
 [,priv_type [(column_list)]] …
 ON [object_type] priv_level
 TO user_specification [,user_specification] …
 [WITH GRANT OPTION]

• priv_type：用于指定权限的名称
• column_list：用于指定权限要授予给表中哪些具体的列
• object_type：用于指定权限授予的对象类型
• priv_level：用于指定权限授予的级别
• TO：用于设定用户的口令，以及指定 被授予权限的用户user
• user_specification：user[IDENTIFIED BY [PASSWORD]’password’]
• WITH ：可选项，用于实现权限的转移或限制

例如：授予用户zhangsan在数据库mysql_test的表customers上拥有对 列cust_id和列cust_name的SELECT权限。

 GRANT SELECT(cust_id,cust_name)
 -> ON mysql_test.customers
 -> TO’zhangsan’@’localhost’;

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

 GRANT SELECT,UPDATE
-> ON mysql_test.customers
-> TO 'liming'@'localhost' IDENTIFIED BY '123',
-> 'huang'@'localhost' IDENTIFIED BY '789';

授予系统中已存在的wangwu可以在数据库mysql_test中执行所有 数据库操作的权限

 GRANT ALL
 -> ON mysql_test.*
 -> TO ‘wangwu’@’localhost’;

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

GRANT CREATE USER
 -> ON *.*
 -> TO ‘wangwu’@’localhost’;

权限的转移

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

 GRANT SELECT,UPDATE
 -> ON mysql_test.customers
 -> TO ‘zhou’@’localhost’ IDENTIFIED BY ‘123’
 -> WITH GRANT OPTION;

使用REVOKE语句撤销用户权限

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

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

 REVOKE SELECT
 -> ON mysql_test.customers
 -> FROM ‘zhou’@’localhost’;

 

四、事务与并发控制

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

 

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

事务的特征（ACID）：

• 原子性 Atomicity：事务是不可分割的最小工作单位
• 一致性 Consistenc y：
• 隔离性 Isolation：
• 持续性（永久性） Durability

例题：依据事务的ACID特征，分析并编写银行数据库系统中的转账事务T：从账 户A转账S金额资金到账户B

BEGIN TRANSACTION
read(A);
A=A-S;
write(A);
If(A<0)ROLLBACK;
else read(B);
 B=B+S
 write(B);
 COMMIT;}

并发操作问题

• 丢失更新 • 事务T1,T2同时读入同一数据并加以修改，T2的提交结果会破坏T1提交的结果

• 不可重复读 • 事务T1读取数据后，事务T2执行更新操作，使T1无法再现前一次读取结果
• 读“脏”数据 • 事务T1修改数据后撤销，使得T2读取的数据与数据库中不一致

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

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

基本的封锁类型：

• 1、排他锁（X锁），用于写操作
• 2、共享锁（S锁），用于读操作

封锁的工作原理：

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

封锁的粒度

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

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

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

死锁和活锁

• 活锁——活锁的处理方案： 先来先服务
• 死锁——活锁的预防方案： （1）一次性锁请求 （2）锁请求排序 （3）序列化处理 （4）资源剥夺  ；死锁检测：图论的方法检测死锁，并以正在执行的事务为节点

可串行性

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

两段封锁法

• 1.发展（Growing）或加锁阶段
• 2.收缩（Shrinking）或释放锁阶段

五、备份与恢复 

数据丢失的途径：

1.计算机硬件故障

2.计算机软件故障

3.病毒

4.人为误操作

5.自然灾害

6.盗窃

数据库备份与恢复的概念

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

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

SELECT *INTO OUTFILE ‘file_name’ export_options
 | INTO DUMPFILE ‘file_name’

file_name：指定数据备份文件的名称

 

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

 

简述恢复数据的方法?

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

SELECT * FROM mysql_test.customers
INTO OUTFILE ‘C:/BACKUP/backupfile.txt’
FIELDS TERMINATED BY ’,’
OPTIONALLY ENCLOSED BY “”
LINES TERMINATED BY ‘?’;

 

扫码关注我们

微信号：SRE实战

拒绝背锅 运筹帷幄