資料庫系統原理（第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 ‘?’;