資料庫系統原理（第6章：資料庫安全與保護）

一、資料庫完整性

資料庫的安全保護：

• 完整性控制：資料庫完整性是指資料庫中資料的正確性和相容性
• 安全性控制：資料庫防止不合法的操作而造成資料泄露、更改或破壞
• 并發控制：事務就是為了保證資料一致性而產生的一個概念和基本手段
• 資料庫的備份與恢復：保證資料庫的可靠性和完整性

資料庫完整性是指資料庫中資料的正確性和相容性，

完整性約束的作用：防止資料庫中存在不符合語意的資料，

完整性約束條件的作用物件

• 列級約束： 包括對列的型別、取值范圍、精度等的約束
• 元組約束： 指元組中各個欄位之間的相互約束
• 表級約束： 指若干元組、關系之間的聯系的約束

完整性約束條件是完整性控制機制的核心

定義與實作完整性約束：物體完整性 、參照完整性、 用戶定義的完整性

**************************物體完整性**************************

物體完整性 ：在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 ‘?’;

標籤：MySQL

上一篇：MySql自定義函式-關于保留小數位的特殊需求

下一篇：mysql索引