據(jù)庫安全性與完整性：從權(quán)限控制到觸發(fā)器，一次理清基礎(chǔ)篇（四）)
很多自學(xué)《數(shù)據(jù)庫系統(tǒng)概論》第五版的同學(xué)基本都會卡在同一個地方基礎(chǔ)篇一學(xué)數(shù)據(jù)模型還行二學(xué)關(guān)系代數(shù)、關(guān)系演算也還行三學(xué)SQL更是信心滿滿結(jié)果到了基礎(chǔ)篇四突然感覺知識點(diǎn)變碎。自主存取控制、強(qiáng)制存取控制、審計(jì)、加密、實(shí)體完整性、參照完整性、觸發(fā)器……每個名詞單獨(dú)看都能理解合上書卻說不清它們各自到底在解決什么問題更別提考試時那些需要自己寫SQL授權(quán)的題目了。這種“斷層感”其實(shí)非常正常。因?yàn)榍鞍氩糠謱W(xué)的是“數(shù)據(jù)怎么組織、怎么查詢、怎么寫”后半部分學(xué)的是“數(shù)據(jù)怎么防、怎么守、怎么保證不出錯”。一個是功能邏輯一個是安全與質(zhì)量邏輯思維方式完全不同。這篇文章我就圍繞《數(shù)據(jù)庫系統(tǒng)概論》第五版基礎(chǔ)篇四的核心內(nèi)容把安全性與完整性這兩大塊拆開揉碎它們各自的邊界在哪、教材里的機(jī)制是怎么設(shè)計(jì)的、放在真實(shí)數(shù)據(jù)庫里又該怎么用、考試?yán)镉心男└哳l場景一次性捋清楚。如果你正在準(zhǔn)備專業(yè)課考試、考研復(fù)試或者只是想把數(shù)據(jù)庫的“防護(hù)體系”真正搞懂這篇文章就是按我的復(fù)習(xí)思路整理出來的“圖解式”筆記可以直接照著用。1. 基礎(chǔ)篇四到底學(xué)什么安全性與完整性的作用邊界1.1 為什么這兩章會讓人越學(xué)越散先說說這兩章在整本教材里的位置?；A(chǔ)篇前三章解決的是數(shù)據(jù)庫“怎么用”數(shù)據(jù)用什么結(jié)構(gòu)存、關(guān)系怎么表達(dá)、SQL怎么寫。到了第四章數(shù)據(jù)庫安全性和第五章數(shù)據(jù)庫完整性教材的視角從“你會不會操作”切換到了“數(shù)據(jù)能不能被信任、被合法訪問”。你會發(fā)現(xiàn)這兩章有一個共同點(diǎn)它們的知識點(diǎn)不是一條主線下來的而是圍繞“用戶訪問數(shù)據(jù)”這條路徑上的多個關(guān)卡展開的。安全性的主線可以理解為一條訪問鏈路用戶發(fā)起連接 → 身份鑒別 → 存取控制判斷權(quán)限 → 視圖進(jìn)一步隔離 → 審計(jì)記錄行為 → 加密保護(hù)存儲和傳輸完整性則是另一條獨(dú)立路線數(shù)據(jù)寫入數(shù)據(jù)庫之前數(shù)據(jù)庫系統(tǒng)自己先當(dāng)一次“質(zhì)檢員”檢查這條記錄合不合規(guī)則不合規(guī)則就不讓進(jìn)。理解了這個結(jié)構(gòu)你再去看那些名詞就不會覺得它們是一盤散沙。它們其實(shí)是守在不同關(guān)卡上的“保安”和“質(zhì)檢員”各自負(fù)責(zé)一段。1.2 安全性不等于完整性一道經(jīng)典辨析題很多同學(xué)一開始會混淆這兩個概念總覺得“安全了數(shù)據(jù)自然就完整了”。這屬于把兩個維度的問題疊在一起了。我在復(fù)習(xí)時反復(fù)給自己強(qiáng)調(diào)一句話安全性管的是“誰”能訪問、能做什么操作。重點(diǎn)在“人”和“權(quán)限”。防止的是非法用戶、越權(quán)訪問、惡意篡改。完整性管的是“數(shù)據(jù)本身”對不對、合不合語義。重點(diǎn)在“數(shù)據(jù)質(zhì)量”。防止的是不合法、不正確、不相容的數(shù)據(jù)混進(jìn)數(shù)據(jù)庫。舉個很直觀的例子某同學(xué)通過身份鑒別和授權(quán)合法登錄了教務(wù)系統(tǒng)他有權(quán)限查看成績——這是安全性在放行。但是成績列里被寫入了“-30”這種物理上不可能存在的負(fù)數(shù)數(shù)據(jù)庫系統(tǒng)必須拒絕這條記錄——這是完整性在攔截。所以二者是兩條平行且互補(bǔ)的防線。安全性再強(qiáng)也攔不住一個合法用戶誤錄入了錯誤數(shù)據(jù)完整性約束再多也管不了有人用合法賬號導(dǎo)出整張表。這也是教材為什么把這兩章放在一起的原因它們合在一起才構(gòu)成數(shù)據(jù)庫基礎(chǔ)層面的“可信數(shù)據(jù)環(huán)境”。2. 存取控制機(jī)制從GRANT/REVOKE到強(qiáng)制存取控制2.1 自主存取控制DAC權(quán)限像鑰匙而且可以轉(zhuǎn)贈自主存取控制Discretionary Access ControlDAC是教材重點(diǎn)介紹的第一種控制方式也是主流關(guān)系數(shù)據(jù)庫默認(rèn)采用的方式。它的核心思想非常樸素?cái)?shù)據(jù)對象的所有者自己決定把訪問權(quán)限給誰而且收了權(quán)限的人如果被授權(quán)了“轉(zhuǎn)贈資格”還可以繼續(xù)往下傳。這就是“自主”二字的含義——授權(quán)行為是用戶可以自己主導(dǎo)的。對應(yīng)到SQL上最核心的兩個語句就是-- 把 Student 表的查詢權(quán)限授予 U1 GRANT SELECT ON TABLE Student TO U1; -- 把 Student 表的查詢和 Sage 列的修改權(quán)限授予 U2并允許 U2 再轉(zhuǎn)授給別人 GRANT SELECT, UPDATE(Sage) ON TABLE Student TO U2 WITH GRANT OPTION; -- 回收 U1 的查詢權(quán)限 REVOKE SELECT ON TABLE Student FROM U1;這里有幾個細(xì)節(jié)值得展開說。第一權(quán)限粒度是可以到列的。UPDATE(Sage)這種方式表示只允許用戶修改Sage這一個列甚至還可以通過后面的視圖機(jī)制實(shí)現(xiàn)行級權(quán)限。很多初學(xué)者以為授權(quán)的最小單位是表其實(shí)教材里已經(jīng)給出了列級授權(quán)的寫法這在考試簡答題里是常考點(diǎn)。第二WITH GRANT OPTION是真正的“大坑”。它意味著 U2 不僅自己能查還能把SELECT權(quán)限繼續(xù)授給 U3、U4……一旦 U1 的權(quán)限被回收U2 先前授予出去的權(quán)限怎么辦教材明確給出了結(jié)論默認(rèn)情況下回收是級聯(lián)CASCADE的U3、U4 的權(quán)限也會一并被收回。雖然標(biāo)準(zhǔn)SQL里還區(qū)分了CASCADE和RESTRICT兩種回收方式但DAC體系下的級聯(lián)回收才是你答題時需要優(yōu)先掌握的默認(rèn)邏輯。第三從實(shí)踐角度看主流數(shù)據(jù)庫對GRANT的支持和教材有一點(diǎn)差異。比如 MySQL 8.0 之后建用戶和授權(quán)已經(jīng)分離了你得先CREATE USER再GRANT而且GRANT語句不能再順帶創(chuàng)建用戶。PostgreSQL 里則統(tǒng)一叫“角色ROLE”用戶和角色在概念上是打通的。這個差異不影響你理解教材的DAC模型但如果你在真實(shí)環(huán)境里敲命令發(fā)現(xiàn)報錯先檢查版本和語法差異。2.2 強(qiáng)制存取控制MAC用安全標(biāo)簽鎖死數(shù)據(jù)通路自主存取控制的缺點(diǎn)很明顯權(quán)限在用戶手里流通一個用戶可以把權(quán)限轉(zhuǎn)給別人系統(tǒng)對這種“流通”的控制不夠強(qiáng)。如果數(shù)據(jù)庫存儲的是絕密級或高度敏感的數(shù)據(jù)這種自由度就不是優(yōu)點(diǎn)而是隱患了。強(qiáng)制存取控制Mandatory Access ControlMAC的思路完全不同權(quán)限不再由用戶“自主”決定而是由系統(tǒng)強(qiáng)制規(guī)則決定。每一個數(shù)據(jù)對象表、行、文件都被標(biāo)上密級比如絕密、機(jī)密、可信、公開每一個主體用戶進(jìn)程也都有一個許可證級別。能不能操作不看你“想要什么權(quán)限”而看你的級別和數(shù)據(jù)密級之間的支配關(guān)系。教材給出的兩條核心規(guī)則可以記成一張表操作規(guī)則直觀理解讀主體的許可證級別 ≥ 客體的密級向下讀高級別能讀低級別低級別不能讀高級別寫主體的許可證級別 ≤ 客體的密級向上寫只能往同級或更高級別里寫禁止向低密級對象寫這兩條規(guī)則合起來就是經(jīng)典的“不向下寫”。為什么要禁止高級別主體向低密級客體寫因?yàn)橐坏└呒墑e數(shù)據(jù)被寫入了一個低密級的文件低密級用戶就可以從那個文件里讀到他本不該看到的高敏感內(nèi)容——這是典型的信息泄露路徑。MAC 的實(shí)現(xiàn)機(jī)制比DAC要復(fù)雜真實(shí)數(shù)據(jù)庫系統(tǒng)里通常不會對普通業(yè)務(wù)表全面啟用MAC但在軍方、政務(wù)、高保密要求的行業(yè)系統(tǒng)里它是一種基礎(chǔ)能力。舉個例子Oracle 的 Label Security、SELinux 的強(qiáng)制訪問控制策略走的都是類似思路。理解MAC的關(guān)鍵不在于你會不會用某個具體產(chǎn)品而在于掌握“主體級別、客體密級、讀規(guī)則、寫規(guī)則”這四個要素的排列組合考試?yán)锝o你一個新場景你能判斷這個訪問“放行還是不放行”。2.3 角色機(jī)制給批量授權(quán)做“中間層”DAC和MAC是兩種控制模型但在實(shí)際管理數(shù)據(jù)庫時還有一個非常實(shí)用的機(jī)制角色ROLE。教材里對角色機(jī)制的描述是“權(quán)限的集合”。我覺得更好的類比是快遞柜里的“團(tuán)長”你不需要給每個用戶逐一派發(fā)權(quán)限而是先定義一個“角色”把權(quán)限一次性授予角色再把角色授予用戶。SQL里的大致流程是-- 1. 創(chuàng)建一個角色 CREATE ROLE R1; -- 2. 把表 Student 的查詢權(quán)限授予角色 GRANT SELECT ON TABLE Student TO R1; -- 3. 把角色授予用戶用戶就自動擁有了角色的權(quán)限 GRANT R1 TO U1, U2, U3; -- 4. 還可以回收 REVOKE R1 FROM U1;這個機(jī)制最大的價值是“降維管理”。想想看如果公司有二十個實(shí)習(xí)生都需要只讀權(quán)限你挨個授權(quán)的話離職時需要挨個回收。有了角色你只需要維護(hù)“實(shí)習(xí)生”這一個角色入職就授予、離職就回收權(quán)限的粒度完全由角色統(tǒng)一控制。在 PostgreSQL 里這種用戶和角色打通的設(shè)計(jì)尤其明顯整個權(quán)限體系就是圍繞“角色”構(gòu)建的。3. 安全性的三道補(bǔ)充防線視圖、審計(jì)與加密3.1 視圖從邏輯層隔離敏感列很多同學(xué)在學(xué)SQL章節(jié)時把視圖當(dāng)成“查著方便的臨時表”學(xué)到安全性章節(jié)才發(fā)現(xiàn)視圖其實(shí)是天然的權(quán)限隔離工具。它的原理很簡單你給用戶開放的并不是底表而是表的一個“裁剪后投影”。比如有一張學(xué)生成績表 Score(Sno, Cno, Score, Comment)你不想讓普通老師看到Comment列可能含評語也不想讓他們按學(xué)號查到某個特定學(xué)生的成績于是就可以建一個視圖CREATE VIEW V_Score_Avg AS SELECT Cno, AVG(Score) AS AvgScore FROM Score GROUP BY Cno; -- 授權(quán)只讓老師查看視圖不給他底表的查詢權(quán)限 GRANT SELECT ON VIEW V_Score_Avg TO Teacher_Role;這樣用戶能查到的只有“課程號和平均分”既看不到個人成績細(xì)節(jié)也碰不到評語列。視圖真正的價值就在這里——它把“數(shù)據(jù)權(quán)限”從行、列的物理層面轉(zhuǎn)化成了“你只能看到我定義好的那一部分邏輯數(shù)據(jù)”不管底層表結(jié)構(gòu)怎么變用戶能訪問的邊界都牢牢鎖在視圖里面。需要注意的是視圖本身并不能完全自治地保證安全。如果創(chuàng)建視圖的人對底表執(zhí)行了允許更新的話某些數(shù)據(jù)庫里通過視圖還是可能反查到底層結(jié)構(gòu)的所以實(shí)際生產(chǎn)環(huán)境里視圖通常是和授權(quán)一起配合使用的而不是二選一。3.2 審計(jì)事后追責(zé)與異常排查審計(jì)是整個安全體系里經(jīng)常被輕視、但事故排查時最救命的一環(huán)。教材里的定義很明確審計(jì)功能把用戶對數(shù)據(jù)庫的所有操作自動記錄下來放入審計(jì)日志一旦發(fā)現(xiàn)問題DBA可以根據(jù)日志重現(xiàn)操作鏈條追查是誰在什么時間執(zhí)行了什么操作。教材給的語句語義很簡潔-- 對 Score 表的插入、修改、刪除操作進(jìn)行審計(jì) AUDIT INSERT, UPDATE, DELETE ON Score; -- 取消審計(jì) NOAUDIT INSERT, UPDATE, DELETE ON Score;落地到真實(shí)數(shù)據(jù)庫時會發(fā)現(xiàn)不同產(chǎn)品對審計(jì)的實(shí)現(xiàn)差異很大。MySQL 社區(qū)版默認(rèn)沒有完整的內(nèi)建審計(jì)功能通常需要借助插件或者開啟 general logPostgreSQL 可以通過配置參數(shù)和擴(kuò)展來實(shí)現(xiàn)比較細(xì)粒度的審計(jì)商業(yè)數(shù)據(jù)庫則往往自帶相對完整的審計(jì)體系。無論在哪個平臺開啟審計(jì)都是有代價的日志會持續(xù)增長磁盤I/O和寫放大也不小。所以在生產(chǎn)環(huán)境里審計(jì)開關(guān)往往只針對敏感表、合規(guī)表開啟而不是全庫全表一把抓。如果你在準(zhǔn)備考試審計(jì)相關(guān)的考點(diǎn)主要是“審計(jì)是什么”“有什么用”“用哪個語句”知道這些就夠了。但如果你在做真實(shí)的系統(tǒng)設(shè)計(jì)我的建議是涉及資金、權(quán)限、個人敏感信息的表一定要開審計(jì)而且要定期把審計(jì)日志異地轉(zhuǎn)儲。3.3 數(shù)據(jù)加密存儲與傳輸?shù)淖詈蠓谰€教材把數(shù)據(jù)加密作為數(shù)據(jù)庫安全性的最后一大模塊。數(shù)據(jù)加密解決的是“數(shù)據(jù)即使被拿到也讀不懂”的問題。加密分為存儲加密和傳輸加密兩個方向。存儲層面最常見的實(shí)踐是密碼字段不存明文。注冊用戶輸入密碼后數(shù)據(jù)庫里存的是經(jīng)過哈希處理后的散列值即使DBA直接翻數(shù)據(jù)表也看不到原始密碼。教材里提到的對稱加密和非對稱加密在這里都有應(yīng)用對稱加密適合大批量數(shù)據(jù)速度快非對稱加密適合少量敏感信息的交換安全性更高。很多云數(shù)據(jù)庫還提供透明數(shù)據(jù)加密TDE對上層應(yīng)用完全無感數(shù)據(jù)落盤時自動加密讀取時自動解密。傳輸層面教材強(qiáng)調(diào)的是防止數(shù)據(jù)在網(wǎng)絡(luò)上被截獲和篡改?，F(xiàn)在主流數(shù)據(jù)庫都已經(jīng)支持SSL/TLS加密傳輸。別忘了如果應(yīng)用和數(shù)據(jù)庫之間的鏈路是明文傳輸那么鏈路劫持者可以截獲賬號密碼、SQL語句、查詢結(jié)果——之前數(shù)據(jù)庫層面的所有權(quán)限控制就全部失去意義了。所以我在看項(xiàng)目時只要業(yè)務(wù)環(huán)境允許都會確認(rèn)數(shù)據(jù)庫連接串上有沒有啟用 SSL。4. 完整性約束實(shí)體、參照、用戶定義三類約束的一次講透4.1 實(shí)體完整性主碼不能為空且唯一完整性約束的第一個層次叫實(shí)體完整性。它針對的是關(guān)系模型里的“實(shí)體”概念每個元組都應(yīng)該有唯一的、可區(qū)分的身份。落到SQL實(shí)現(xiàn)上就是主碼PRIMARY KEY的兩個鐵律主碼屬性不能取空值主碼不能重復(fù)。這里有一個新手很容易踩的誤區(qū)復(fù)合主碼。如果主碼由兩個列共同組成比如選課表里的(Sno, Cno)那么Sno和Cno的每一個都不允許為空而不是“其中一個為空也可以只要兩個不同時為空”。DBMS在插入和修改主碼值時都會自動檢查這條規(guī)則一旦違反就直接拒絕操作。實(shí)體完整性的實(shí)現(xiàn)通常會在主碼上自動建立唯一索引這也是為什么按主碼查詢總是很快的原因之一。在教材里這種自動檢查是關(guān)系系統(tǒng)必須提供的基本能力不需要用戶額外寫代碼。4.2 參照完整性外鍵的“引用即存在”契約參照完整性解決的是“引用”的合法性問題。簡單說就是A表里的外鍵一旦寫了值這個值必須在B表的主碼里真實(shí)存在外鍵如果為空則表示不存在聯(lián)系是可以接受的但外鍵非空卻指向一個不存在的主碼就破壞了引用關(guān)系。外鍵違約的處理策略是這節(jié)最重要的考點(diǎn)。教材給出的策略可以從兩個方向理解方向一對參照表子表的插入、外碼修改如果新外碼不是 NULL但在被參照表中找不到對應(yīng)主碼拒絕執(zhí)行。方向二對被參照表父表的主碼刪除、主碼修改這時系統(tǒng)有幾種選擇拒絕NO ACTION / RESTRICT、級聯(lián)CASCADE、置空SET NULL。比如刪除一門課程如果該課程已有學(xué)生選課記錄默認(rèn)策略是拒絕刪除防止“選課記錄指向一門不存在的課”。但如果業(yè)務(wù)要求“刪課之后選課記錄也一并清除”就可以定義外鍵時帶上級聯(lián)刪除CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Score DECIMAL(4,1), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE, FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE CASCADE );在真實(shí)生產(chǎn)環(huán)境里我發(fā)現(xiàn)很多團(tuán)隊(duì)因?yàn)樾阅軉栴}干脆不建外鍵約束把“引用完整性”放到應(yīng)用層去維護(hù)。這種做法要承擔(dān)風(fēng)險應(yīng)用層的并行操作總有照顧不到的死角一旦產(chǎn)生孤兒數(shù)據(jù)排查成本非常高。穩(wěn)妥的做法是讀多寫少、對一致性要求高的核心關(guān)系老老實(shí)實(shí)建外鍵正確性讓數(shù)據(jù)庫來保證應(yīng)用層只做業(yè)務(wù)校驗(yàn)。4.3 用戶定義完整性與約束命名把規(guī)則寫到表結(jié)構(gòu)里實(shí)體完整性管主碼參照完整性管外碼但業(yè)務(wù)里還有大量約束不屬于這兩類。比如年齡不能為負(fù)、性別只能是男女、郵箱格式要合法、股票數(shù)量必須是整數(shù)且大于0。這些規(guī)則統(tǒng)稱用戶定義完整性落到SQL里就是三樣?xùn)|西NOT NULL、UNIQUE、CHECK。教材特別強(qiáng)調(diào)了完整性約束命名子句的用法目的是讓約束有名字便于后續(xù)通過名字刪除或修改CREATE TABLE Student ( Sno CHAR(9), Sname CHAR(20) NOT NULL, Sage SMALLINT, CONSTRAINT CS_Sno PRIMARY KEY(Sno), CONSTRAINT CS_Sname UNIQUE(Sname), CONSTRAINT CS_Sage CHECK (Sage BETWEEN 16 AND 40) );有了CONSTRAINT CS_Sage這樣的命名你可以用一句話把具體約束去掉或替換而不用刪表重建。這在項(xiàng)目迭代時尤其好用。還要注意UNIQUE的一個隱蔽行為多數(shù)數(shù)據(jù)庫允許UNIQUE列中存在多個NULL值因?yàn)椤懊看螢榭毡硎疚粗北舜瞬凰阒貜?fù)。如果你期望“這一列除空值外不能重復(fù)”那么UNIQUE是符合預(yù)期的如果你期望“空值也不允許出現(xiàn)”就得額外加NOT NULL。這個坑在真實(shí)業(yè)務(wù)里經(jīng)常引發(fā)線上bug值得記一下。4.4 約束的檢查時機(jī)與常見誤區(qū)完整性約束不是“寫進(jìn)表結(jié)構(gòu)就萬事大吉”它有一整套檢查時機(jī)。教材和考試?yán)镄枰莆盏氖遣迦?、修改、刪除操作時DBMS都會自動檢查相關(guān)約束不符合就語句整體失敗。這里面最容易忽略的是“修改主碼”這種操作——它既可能觸發(fā)實(shí)體完整性檢查也可能觸發(fā)參照完整性檢查。另外還有一個和索引相關(guān)的實(shí)操經(jīng)驗(yàn)外鍵列盡量建索引。原因很簡單刪除父表一行時DBMS需要快速找到所有引用它的子表記錄來執(zhí)行級聯(lián)或拒絕判斷沒有索引就只能全表掃。很多大表刪除慢不是刪除本身慢而是外鍵關(guān)聯(lián)的查找慢。這是我處理線上故障時踩過的很實(shí)際的坑。還有一個誤區(qū)不是所以約束都能像“寫死規(guī)則”一樣隨意疊加。約束越厚寫入性能越差尤其是帶有跨表檢查的外鍵和CHECK。在設(shè)計(jì)表結(jié)構(gòu)時約束要“該用的用足”但也不要為了“看起來嚴(yán)謹(jǐn)”在每列上堆七八個約束要平衡正確性、開發(fā)效率、運(yùn)行性能三者的關(guān)系。5. 觸發(fā)器約束不夠用時讓數(shù)據(jù)庫自己做個守衛(wèi)5.1 觸發(fā)器的ECA結(jié)構(gòu)與兩種粒度完整性約束解決的是“規(guī)則型”問題但業(yè)務(wù)里經(jīng)常有“規(guī)則不好用SQL聲明”的場景。比如某個操作同時要檢查多個表、要自動寫日志、要同步更新匯總字段。這時候教材就翻出了觸發(fā)器這張牌。觸發(fā)器的全稱叫“事件-條件-動作”Event-Condition-Action可以理解為一段被數(shù)據(jù)庫自動執(zhí)行的小程序。它包含四個關(guān)鍵要素觸發(fā)事件INSERT、UPDATE、DELETE 等操作觸發(fā)時機(jī)BEFORE操作前觸發(fā)或 AFTER操作后觸發(fā)觸發(fā)條件只有滿足條件才執(zhí)行動作觸發(fā)動作要執(zhí)行的一組SQL語句或過程而“行級觸發(fā)器”和“語句級觸發(fā)器”的區(qū)別是考試?yán)镒钊菀妆粏柕降狞c(diǎn)。FOR EACH ROW表示對被影響的每一行執(zhí)行一次動作FOR EACH STATEMENT則無論這次語句影響了多少行整體只觸發(fā)一次。舉個例子寫一個簡單的工資審計(jì)觸發(fā)器把“工資突然大幅下降”的更新行為記錄下來CREATE TRIGGER Tg_Salary_Log AFTER UPDATE OF Sal ON Employee FOR EACH ROW WHEN (NEW.Sal OLD.Sal * 0.8) BEGIN INSERT INTO Sal_Log(EID, Old_Sal, New_Sal, Change_Time) VALUES (NEW.EID, OLD.Sal, NEW.Sal, CURRENT_TIMESTAMP); END;這段觸發(fā)器的邏輯就是每當(dāng) Employee 表 Sal 列發(fā)生更新如果新工資比舊工資下降了超過20%就自動往日志表里插一條記錄。整個過程對業(yè)務(wù)層完全透明不需要應(yīng)用端寫任何額外代碼。5.2 典型觸發(fā)場景與“別濫用”的提醒觸發(fā)器真正好用的場景我經(jīng)歷過的有幾類一是敏感表變更的自動審計(jì)日志二是數(shù)據(jù)同步比如訂單表插入后自動往統(tǒng)計(jì)表里更新匯總值三是復(fù)雜完整性校驗(yàn)比如“庫存不能超賣”這種需要跨表判斷的規(guī)則。但是觸發(fā)器是把雙刃劍。我的建議是能用約束解決的不要用觸發(fā)器。約束是聲明式的數(shù)據(jù)庫可以優(yōu)化執(zhí)行路徑觸發(fā)器是過程式的一旦觸發(fā)會帶來額外的隱式操作復(fù)雜觸發(fā)器還可能造成連鎖觸發(fā)、死鎖而且排障時非常不好查。你執(zhí)行一條看似簡單的UPDATE實(shí)際背后跑了一長串觸發(fā)器這不是什么好體驗(yàn)。如果要上線含觸發(fā)器的功能我有三個實(shí)操習(xí)慣第一在測試環(huán)境里分別用單行更新和批量更新驗(yàn)證行級觸發(fā)器和語句級觸發(fā)器的行為差異第二在觸發(fā)器中統(tǒng)一加日志輸出方便事后定位第三評估觸發(fā)器的性能權(quán)重避免把高頻寫入路徑變成“觸發(fā)器瓶頸”。這些經(jīng)驗(yàn)在教材里沒有但真實(shí)的數(shù)據(jù)庫運(yùn)維里很重要。6. 備考時最容易翻車的三個細(xì)節(jié)與復(fù)習(xí)主線6.1 細(xì)節(jié)一WITH GRANT OPTION的回收鏈條考試?yán)锝?jīng)常出這樣的題A給B授權(quán)并帶WITH GRANT OPTIONB又把同一權(quán)限給了C現(xiàn)在A要回收B的權(quán)限問C的權(quán)限是否還能保留答案取決于實(shí)現(xiàn)但教材默認(rèn)是級聯(lián)回收即C的權(quán)限也會被回收。答題時如果不確定最好把條件寫清楚“默認(rèn)按級聯(lián)方式回收如果回收語句指定了 RESTRICT且有下游授權(quán)存在則回收失敗”。這才是完整拿分的思路。6.2 細(xì)節(jié)二完整性約束與觸發(fā)器的辨析簡答題里很容易出現(xiàn)“這個規(guī)則用什么實(shí)現(xiàn)更好”。判據(jù)很簡單如果規(guī)則能用一條CHECK、UNIQUE、NOT NULL或外鍵聲明清楚就用約束只有規(guī)則涉及多表聯(lián)動、操作時機(jī)、自定義過程邏輯時才上觸發(fā)器。答題時可以用“約束是聲明式觸發(fā)器是過程式”作為第一句定調(diào)然后結(jié)合實(shí)際場景說明。6.3 細(xì)節(jié)三安全方案的層次順序更考驗(yàn)綜合能力的題長這樣為一套成績系統(tǒng)設(shè)計(jì)安全方案要求只有教務(wù)處人員能查看統(tǒng)計(jì)結(jié)果并且每次查詢都要留痕。回答這類題要按層來先建“統(tǒng)計(jì)視圖”隔離敏感明細(xì)再通過角色授權(quán)僅給教務(wù)處角色最后開啟對該視圖的審計(jì)。這三步對應(yīng)視圖機(jī)制、存取控制、審計(jì)答出來就是一條完整的鏈路。掌握了這種“分層答題”思路安全性的論述題基本都能踩在得分點(diǎn)上。6.4 復(fù)習(xí)建議把概念題改造成場景題回過頭去看基礎(chǔ)篇四的內(nèi)容看起來是“背概念”但真正拉開差距的恰恰是“能不能把概念落成SQL、落成設(shè)計(jì)”。我的復(fù)習(xí)方法很簡單把每一道概念題都改造成“給一個場景自己寫一段建表、授權(quán)、觸發(fā)器的SQL”再對照教材核對。比如教材說“用戶定義完整性”你就親手在本地?cái)?shù)據(jù)庫里建一張帶CHECK的表試一下插入非法值會發(fā)生什么教材說“參照完整性的級聯(lián)刪除”你就建兩張帶外鍵的表實(shí)際刪一行看看教材說“審計(jì)”你就開一下general log看看自己剛才的操作被記成了什么樣。數(shù)據(jù)庫這個學(xué)科看十遍不如動手一遍。安全性與完整性這兩章尤其如此你把權(quán)限、約束、觸發(fā)器都真正跑過一遍之后那些零散的知識點(diǎn)會自動串成一張網(wǎng)。后面再去做系統(tǒng)設(shè)計(jì)或備考復(fù)試你會發(fā)現(xiàn)很多所謂難題其實(shí)都是你早就跑通過的操作場景。 數(shù)據(jù)庫安全性與完整性：從權(quán)限控制到觸發(fā)器，一次理清基礎(chǔ)篇（四）
很多自學(xué)《數(shù)據(jù)庫系統(tǒng)概論》第五版的同學(xué)基本都會卡在同一個地方基礎(chǔ)篇一學(xué)數(shù)據(jù)模型還行二學(xué)關(guān)系代數(shù)、關(guān)系演算也還行三學(xué)SQL更是信心滿滿結(jié)果到了基礎(chǔ)篇四突然感覺知識點(diǎn)變碎。自主存取控制、強(qiáng)制存取控制、審計(jì)、加密、實(shí)體完整性、參照完整性、觸發(fā)器……每個名詞單獨(dú)看都能理解合上書卻說不清它們各自到底在解決什么問題更別提考試時那些需要自己寫SQL授權(quán)的題目了。這種“斷層感”其實(shí)非常正常。因?yàn)榍鞍氩糠謱W(xué)的是“數(shù)據(jù)怎么組織、怎么查詢、怎么寫”后半部分學(xué)的是“數(shù)據(jù)怎么防、怎么守、怎么保證不出錯”。一個是功能邏輯一個是安全與質(zhì)量邏輯思維方式完全不同。這篇文章我就圍繞《數(shù)據(jù)庫系統(tǒng)概論》第五版基礎(chǔ)篇四的核心內(nèi)容把安全性與完整性這兩大塊拆開揉碎它們各自的邊界在哪、教材里的機(jī)制是怎么設(shè)計(jì)的、放在真實(shí)數(shù)據(jù)庫里又該怎么用、考試?yán)镉心男└哳l場景一次性捋清楚。如果你正在準(zhǔn)備專業(yè)課考試、考研復(fù)試或者只是想把數(shù)據(jù)庫的“防護(hù)體系”真正搞懂這篇文章就是按我的復(fù)習(xí)思路整理出來的“圖解式”筆記可以直接照著用。1. 基礎(chǔ)篇四到底學(xué)什么安全性與完整性的作用邊界1.1 為什么這兩章會讓人越學(xué)越散先說說這兩章在整本教材里的位置?；A(chǔ)篇前三章解決的是數(shù)據(jù)庫“怎么用”數(shù)據(jù)用什么結(jié)構(gòu)存、關(guān)系怎么表達(dá)、SQL怎么寫。到了第四章數(shù)據(jù)庫安全性和第五章數(shù)據(jù)庫完整性教材的視角從“你會不會操作”切換到了“數(shù)據(jù)能不能被信任、被合法訪問”。你會發(fā)現(xiàn)這兩章有一個共同點(diǎn)它們的知識點(diǎn)不是一條主線下來的而是圍繞“用戶訪問數(shù)據(jù)”這條路徑上的多個關(guān)卡展開的。安全性的主線可以理解為一條訪問鏈路用戶發(fā)起連接 → 身份鑒別 → 存取控制判斷權(quán)限 → 視圖進(jìn)一步隔離 → 審計(jì)記錄行為 → 加密保護(hù)存儲和傳輸完整性則是另一條獨(dú)立路線數(shù)據(jù)寫入數(shù)據(jù)庫之前數(shù)據(jù)庫系統(tǒng)自己先當(dāng)一次“質(zhì)檢員”檢查這條記錄合不合規(guī)則不合規(guī)則就不讓進(jìn)。理解了這個結(jié)構(gòu)你再去看那些名詞就不會覺得它們是一盤散沙。它們其實(shí)是守在不同關(guān)卡上的“保安”和“質(zhì)檢員”各自負(fù)責(zé)一段。1.2 安全性不等于完整性一道經(jīng)典辨析題很多同學(xué)一開始會混淆這兩個概念總覺得“安全了數(shù)據(jù)自然就完整了”。這屬于把兩個維度的問題疊在一起了。我在復(fù)習(xí)時反復(fù)給自己強(qiáng)調(diào)一句話安全性管的是“誰”能訪問、能做什么操作。重點(diǎn)在“人”和“權(quán)限”。防止的是非法用戶、越權(quán)訪問、惡意篡改。完整性管的是“數(shù)據(jù)本身”對不對、合不合語義。重點(diǎn)在“數(shù)據(jù)質(zhì)量”。防止的是不合法、不正確、不相容的數(shù)據(jù)混進(jìn)數(shù)據(jù)庫。舉個很直觀的例子某同學(xué)通過身份鑒別和授權(quán)合法登錄了教務(wù)系統(tǒng)他有權(quán)限查看成績——這是安全性在放行。但是成績列里被寫入了“-30”這種物理上不可能存在的負(fù)數(shù)數(shù)據(jù)庫系統(tǒng)必須拒絕這條記錄——這是完整性在攔截。所以二者是兩條平行且互補(bǔ)的防線。安全性再強(qiáng)也攔不住一個合法用戶誤錄入了錯誤數(shù)據(jù)完整性約束再多也管不了有人用合法賬號導(dǎo)出整張表。這也是教材為什么把這兩章放在一起的原因它們合在一起才構(gòu)成數(shù)據(jù)庫基礎(chǔ)層面的“可信數(shù)據(jù)環(huán)境”。2. 存取控制機(jī)制從GRANT/REVOKE到強(qiáng)制存取控制2.1 自主存取控制DAC權(quán)限像鑰匙而且可以轉(zhuǎn)贈自主存取控制Discretionary Access ControlDAC是教材重點(diǎn)介紹的第一種控制方式也是主流關(guān)系數(shù)據(jù)庫默認(rèn)采用的方式。它的核心思想非常樸素?cái)?shù)據(jù)對象的所有者自己決定把訪問權(quán)限給誰而且收了權(quán)限的人如果被授權(quán)了“轉(zhuǎn)贈資格”還可以繼續(xù)往下傳。這就是“自主”二字的含義——授權(quán)行為是用戶可以自己主導(dǎo)的。對應(yīng)到SQL上最核心的兩個語句就是-- 把 Student 表的查詢權(quán)限授予 U1 GRANT SELECT ON TABLE Student TO U1; -- 把 Student 表的查詢和 Sage 列的修改權(quán)限授予 U2并允許 U2 再轉(zhuǎn)授給別人 GRANT SELECT, UPDATE(Sage) ON TABLE Student TO U2 WITH GRANT OPTION; -- 回收 U1 的查詢權(quán)限 REVOKE SELECT ON TABLE Student FROM U1;這里有幾個細(xì)節(jié)值得展開說。第一權(quán)限粒度是可以到列的。UPDATE(Sage)這種方式表示只允許用戶修改Sage這一個列甚至還可以通過后面的視圖機(jī)制實(shí)現(xiàn)行級權(quán)限。很多初學(xué)者以為授權(quán)的最小單位是表其實(shí)教材里已經(jīng)給出了列級授權(quán)的寫法這在考試簡答題里是常考點(diǎn)。第二WITH GRANT OPTION是真正的“大坑”。它意味著 U2 不僅自己能查還能把SELECT權(quán)限繼續(xù)授給 U3、U4……一旦 U1 的權(quán)限被回收U2 先前授予出去的權(quán)限怎么辦教材明確給出了結(jié)論默認(rèn)情況下回收是級聯(lián)CASCADE的U3、U4 的權(quán)限也會一并被收回。雖然標(biāo)準(zhǔn)SQL里還區(qū)分了CASCADE和RESTRICT兩種回收方式但DAC體系下的級聯(lián)回收才是你答題時需要優(yōu)先掌握的默認(rèn)邏輯。第三從實(shí)踐角度看主流數(shù)據(jù)庫對GRANT的支持和教材有一點(diǎn)差異。比如 MySQL 8.0 之后建用戶和授權(quán)已經(jīng)分離了你得先CREATE USER再GRANT而且GRANT語句不能再順帶創(chuàng)建用戶。PostgreSQL 里則統(tǒng)一叫“角色ROLE”用戶和角色在概念上是打通的。這個差異不影響你理解教材的DAC模型但如果你在真實(shí)環(huán)境里敲命令發(fā)現(xiàn)報錯先檢查版本和語法差異。2.2 強(qiáng)制存取控制MAC用安全標(biāo)簽鎖死數(shù)據(jù)通路自主存取控制的缺點(diǎn)很明顯權(quán)限在用戶手里流通一個用戶可以把權(quán)限轉(zhuǎn)給別人系統(tǒng)對這種“流通”的控制不夠強(qiáng)。如果數(shù)據(jù)庫存儲的是絕密級或高度敏感的數(shù)據(jù)這種自由度就不是優(yōu)點(diǎn)而是隱患了。強(qiáng)制存取控制Mandatory Access ControlMAC的思路完全不同權(quán)限不再由用戶“自主”決定而是由系統(tǒng)強(qiáng)制規(guī)則決定。每一個數(shù)據(jù)對象表、行、文件都被標(biāo)上密級比如絕密、機(jī)密、可信、公開每一個主體用戶進(jìn)程也都有一個許可證級別。能不能操作不看你“想要什么權(quán)限”而看你的級別和數(shù)據(jù)密級之間的支配關(guān)系。教材給出的兩條核心規(guī)則可以記成一張表操作規(guī)則直觀理解讀主體的許可證級別 ≥ 客體的密級向下讀高級別能讀低級別低級別不能讀高級別寫主體的許可證級別 ≤ 客體的密級向上寫只能往同級或更高級別里寫禁止向低密級對象寫這兩條規(guī)則合起來就是經(jīng)典的“不向下寫”。為什么要禁止高級別主體向低密級客體寫因?yàn)橐坏└呒墑e數(shù)據(jù)被寫入了一個低密級的文件低密級用戶就可以從那個文件里讀到他本不該看到的高敏感內(nèi)容——這是典型的信息泄露路徑。MAC 的實(shí)現(xiàn)機(jī)制比DAC要復(fù)雜真實(shí)數(shù)據(jù)庫系統(tǒng)里通常不會對普通業(yè)務(wù)表全面啟用MAC但在軍方、政務(wù)、高保密要求的行業(yè)系統(tǒng)里它是一種基礎(chǔ)能力。舉個例子Oracle 的 Label Security、SELinux 的強(qiáng)制訪問控制策略走的都是類似思路。理解MAC的關(guān)鍵不在于你會不會用某個具體產(chǎn)品而在于掌握“主體級別、客體密級、讀規(guī)則、寫規(guī)則”這四個要素的排列組合考試?yán)锝o你一個新場景你能判斷這個訪問“放行還是不放行”。2.3 角色機(jī)制給批量授權(quán)做“中間層”DAC和MAC是兩種控制模型但在實(shí)際管理數(shù)據(jù)庫時還有一個非常實(shí)用的機(jī)制角色ROLE。教材里對角色機(jī)制的描述是“權(quán)限的集合”。我覺得更好的類比是快遞柜里的“團(tuán)長”你不需要給每個用戶逐一派發(fā)權(quán)限而是先定義一個“角色”把權(quán)限一次性授予角色再把角色授予用戶。SQL里的大致流程是-- 1. 創(chuàng)建一個角色 CREATE ROLE R1; -- 2. 把表 Student 的查詢權(quán)限授予角色 GRANT SELECT ON TABLE Student TO R1; -- 3. 把角色授予用戶用戶就自動擁有了角色的權(quán)限 GRANT R1 TO U1, U2, U3; -- 4. 還可以回收 REVOKE R1 FROM U1;這個機(jī)制最大的價值是“降維管理”。想想看如果公司有二十個實(shí)習(xí)生都需要只讀權(quán)限你挨個授權(quán)的話離職時需要挨個回收。有了角色你只需要維護(hù)“實(shí)習(xí)生”這一個角色入職就授予、離職就回收權(quán)限的粒度完全由角色統(tǒng)一控制。在 PostgreSQL 里這種用戶和角色打通的設(shè)計(jì)尤其明顯整個權(quán)限體系就是圍繞“角色”構(gòu)建的。3. 安全性的三道補(bǔ)充防線視圖、審計(jì)與加密3.1 視圖從邏輯層隔離敏感列很多同學(xué)在學(xué)SQL章節(jié)時把視圖當(dāng)成“查著方便的臨時表”學(xué)到安全性章節(jié)才發(fā)現(xiàn)視圖其實(shí)是天然的權(quán)限隔離工具。它的原理很簡單你給用戶開放的并不是底表而是表的一個“裁剪后投影”。比如有一張學(xué)生成績表 Score(Sno, Cno, Score, Comment)你不想讓普通老師看到Comment列可能含評語也不想讓他們按學(xué)號查到某個特定學(xué)生的成績于是就可以建一個視圖CREATE VIEW V_Score_Avg AS SELECT Cno, AVG(Score) AS AvgScore FROM Score GROUP BY Cno; -- 授權(quán)只讓老師查看視圖不給他底表的查詢權(quán)限 GRANT SELECT ON VIEW V_Score_Avg TO Teacher_Role;這樣用戶能查到的只有“課程號和平均分”既看不到個人成績細(xì)節(jié)也碰不到評語列。視圖真正的價值就在這里——它把“數(shù)據(jù)權(quán)限”從行、列的物理層面轉(zhuǎn)化成了“你只能看到我定義好的那一部分邏輯數(shù)據(jù)”不管底層表結(jié)構(gòu)怎么變用戶能訪問的邊界都牢牢鎖在視圖里面。需要注意的是視圖本身并不能完全自治地保證安全。如果創(chuàng)建視圖的人對底表執(zhí)行了允許更新的話某些數(shù)據(jù)庫里通過視圖還是可能反查到底層結(jié)構(gòu)的所以實(shí)際生產(chǎn)環(huán)境里視圖通常是和授權(quán)一起配合使用的而不是二選一。3.2 審計(jì)事后追責(zé)與異常排查審計(jì)是整個安全體系里經(jīng)常被輕視、但事故排查時最救命的一環(huán)。教材里的定義很明確審計(jì)功能把用戶對數(shù)據(jù)庫的所有操作自動記錄下來放入審計(jì)日志一旦發(fā)現(xiàn)問題DBA可以根據(jù)日志重現(xiàn)操作鏈條追查是誰在什么時間執(zhí)行了什么操作。教材給的語句語義很簡潔-- 對 Score 表的插入、修改、刪除操作進(jìn)行審計(jì) AUDIT INSERT, UPDATE, DELETE ON Score; -- 取消審計(jì) NOAUDIT INSERT, UPDATE, DELETE ON Score;落地到真實(shí)數(shù)據(jù)庫時會發(fā)現(xiàn)不同產(chǎn)品對審計(jì)的實(shí)現(xiàn)差異很大。MySQL 社區(qū)版默認(rèn)沒有完整的內(nèi)建審計(jì)功能通常需要借助插件或者開啟 general logPostgreSQL 可以通過配置參數(shù)和擴(kuò)展來實(shí)現(xiàn)比較細(xì)粒度的審計(jì)商業(yè)數(shù)據(jù)庫則往往自帶相對完整的審計(jì)體系。無論在哪個平臺開啟審計(jì)都是有代價的日志會持續(xù)增長磁盤I/O和寫放大也不小。所以在生產(chǎn)環(huán)境里審計(jì)開關(guān)往往只針對敏感表、合規(guī)表開啟而不是全庫全表一把抓。如果你在準(zhǔn)備考試審計(jì)相關(guān)的考點(diǎn)主要是“審計(jì)是什么”“有什么用”“用哪個語句”知道這些就夠了。但如果你在做真實(shí)的系統(tǒng)設(shè)計(jì)我的建議是涉及資金、權(quán)限、個人敏感信息的表一定要開審計(jì)而且要定期把審計(jì)日志異地轉(zhuǎn)儲。3.3 數(shù)據(jù)加密存儲與傳輸?shù)淖詈蠓谰€教材把數(shù)據(jù)加密作為數(shù)據(jù)庫安全性的最后一大模塊。數(shù)據(jù)加密解決的是“數(shù)據(jù)即使被拿到也讀不懂”的問題。加密分為存儲加密和傳輸加密兩個方向。存儲層面最常見的實(shí)踐是密碼字段不存明文。注冊用戶輸入密碼后數(shù)據(jù)庫里存的是經(jīng)過哈希處理后的散列值即使DBA直接翻數(shù)據(jù)表也看不到原始密碼。教材里提到的對稱加密和非對稱加密在這里都有應(yīng)用對稱加密適合大批量數(shù)據(jù)速度快非對稱加密適合少量敏感信息的交換安全性更高。很多云數(shù)據(jù)庫還提供透明數(shù)據(jù)加密TDE對上層應(yīng)用完全無感數(shù)據(jù)落盤時自動加密讀取時自動解密。傳輸層面教材強(qiáng)調(diào)的是防止數(shù)據(jù)在網(wǎng)絡(luò)上被截獲和篡改?，F(xiàn)在主流數(shù)據(jù)庫都已經(jīng)支持SSL/TLS加密傳輸。別忘了如果應(yīng)用和數(shù)據(jù)庫之間的鏈路是明文傳輸那么鏈路劫持者可以截獲賬號密碼、SQL語句、查詢結(jié)果——之前數(shù)據(jù)庫層面的所有權(quán)限控制就全部失去意義了。所以我在看項(xiàng)目時只要業(yè)務(wù)環(huán)境允許都會確認(rèn)數(shù)據(jù)庫連接串上有沒有啟用 SSL。4. 完整性約束實(shí)體、參照、用戶定義三類約束的一次講透4.1 實(shí)體完整性主碼不能為空且唯一完整性約束的第一個層次叫實(shí)體完整性。它針對的是關(guān)系模型里的“實(shí)體”概念每個元組都應(yīng)該有唯一的、可區(qū)分的身份。落到SQL實(shí)現(xiàn)上就是主碼PRIMARY KEY的兩個鐵律主碼屬性不能取空值主碼不能重復(fù)。這里有一個新手很容易踩的誤區(qū)復(fù)合主碼。如果主碼由兩個列共同組成比如選課表里的(Sno, Cno)那么Sno和Cno的每一個都不允許為空而不是“其中一個為空也可以只要兩個不同時為空”。DBMS在插入和修改主碼值時都會自動檢查這條規(guī)則一旦違反就直接拒絕操作。實(shí)體完整性的實(shí)現(xiàn)通常會在主碼上自動建立唯一索引這也是為什么按主碼查詢總是很快的原因之一。在教材里這種自動檢查是關(guān)系系統(tǒng)必須提供的基本能力不需要用戶額外寫代碼。4.2 參照完整性外鍵的“引用即存在”契約參照完整性解決的是“引用”的合法性問題。簡單說就是A表里的外鍵一旦寫了值這個值必須在B表的主碼里真實(shí)存在外鍵如果為空則表示不存在聯(lián)系是可以接受的但外鍵非空卻指向一個不存在的主碼就破壞了引用關(guān)系。外鍵違約的處理策略是這節(jié)最重要的考點(diǎn)。教材給出的策略可以從兩個方向理解方向一對參照表子表的插入、外碼修改如果新外碼不是 NULL但在被參照表中找不到對應(yīng)主碼拒絕執(zhí)行。方向二對被參照表父表的主碼刪除、主碼修改這時系統(tǒng)有幾種選擇拒絕NO ACTION / RESTRICT、級聯(lián)CASCADE、置空SET NULL。比如刪除一門課程如果該課程已有學(xué)生選課記錄默認(rèn)策略是拒絕刪除防止“選課記錄指向一門不存在的課”。但如果業(yè)務(wù)要求“刪課之后選課記錄也一并清除”就可以定義外鍵時帶上級聯(lián)刪除CREATE TABLE SC ( Sno CHAR(9), Cno CHAR(4), Score DECIMAL(4,1), PRIMARY KEY (Sno, Cno), FOREIGN KEY (Sno) REFERENCES Student(Sno) ON DELETE CASCADE, FOREIGN KEY (Cno) REFERENCES Course(Cno) ON DELETE CASCADE );在真實(shí)生產(chǎn)環(huán)境里我發(fā)現(xiàn)很多團(tuán)隊(duì)因?yàn)樾阅軉栴}干脆不建外鍵約束把“引用完整性”放到應(yīng)用層去維護(hù)。這種做法要承擔(dān)風(fēng)險應(yīng)用層的并行操作總有照顧不到的死角一旦產(chǎn)生孤兒數(shù)據(jù)排查成本非常高。穩(wěn)妥的做法是讀多寫少、對一致性要求高的核心關(guān)系老老實(shí)實(shí)建外鍵正確性讓數(shù)據(jù)庫來保證應(yīng)用層只做業(yè)務(wù)校驗(yàn)。4.3 用戶定義完整性與約束命名把規(guī)則寫到表結(jié)構(gòu)里實(shí)體完整性管主碼參照完整性管外碼但業(yè)務(wù)里還有大量約束不屬于這兩類。比如年齡不能為負(fù)、性別只能是男女、郵箱格式要合法、股票數(shù)量必須是整數(shù)且大于0。這些規(guī)則統(tǒng)稱用戶定義完整性落到SQL里就是三樣?xùn)|西NOT NULL、UNIQUE、CHECK。教材特別強(qiáng)調(diào)了完整性約束命名子句的用法目的是讓約束有名字便于后續(xù)通過名字刪除或修改CREATE TABLE Student ( Sno CHAR(9), Sname CHAR(20) NOT NULL, Sage SMALLINT, CONSTRAINT CS_Sno PRIMARY KEY(Sno), CONSTRAINT CS_Sname UNIQUE(Sname), CONSTRAINT CS_Sage CHECK (Sage BETWEEN 16 AND 40) );有了CONSTRAINT CS_Sage這樣的命名你可以用一句話把具體約束去掉或替換而不用刪表重建。這在項(xiàng)目迭代時尤其好用。還要注意UNIQUE的一個隱蔽行為多數(shù)數(shù)據(jù)庫允許UNIQUE列中存在多個NULL值因?yàn)椤懊看螢榭毡硎疚粗北舜瞬凰阒貜?fù)。如果你期望“這一列除空值外不能重復(fù)”那么UNIQUE是符合預(yù)期的如果你期望“空值也不允許出現(xiàn)”就得額外加NOT NULL。這個坑在真實(shí)業(yè)務(wù)里經(jīng)常引發(fā)線上bug值得記一下。4.4 約束的檢查時機(jī)與常見誤區(qū)完整性約束不是“寫進(jìn)表結(jié)構(gòu)就萬事大吉”它有一整套檢查時機(jī)。教材和考試?yán)镄枰莆盏氖遣迦?、修改、刪除操作時DBMS都會自動檢查相關(guān)約束不符合就語句整體失敗。這里面最容易忽略的是“修改主碼”這種操作——它既可能觸發(fā)實(shí)體完整性檢查也可能觸發(fā)參照完整性檢查。另外還有一個和索引相關(guān)的實(shí)操經(jīng)驗(yàn)外鍵列盡量建索引。原因很簡單刪除父表一行時DBMS需要快速找到所有引用它的子表記錄來執(zhí)行級聯(lián)或拒絕判斷沒有索引就只能全表掃。很多大表刪除慢不是刪除本身慢而是外鍵關(guān)聯(lián)的查找慢。這是我處理線上故障時踩過的很實(shí)際的坑。還有一個誤區(qū)不是所以約束都能像“寫死規(guī)則”一樣隨意疊加。約束越厚寫入性能越差尤其是帶有跨表檢查的外鍵和CHECK。在設(shè)計(jì)表結(jié)構(gòu)時約束要“該用的用足”但也不要為了“看起來嚴(yán)謹(jǐn)”在每列上堆七八個約束要平衡正確性、開發(fā)效率、運(yùn)行性能三者的關(guān)系。5. 觸發(fā)器約束不夠用時讓數(shù)據(jù)庫自己做個守衛(wèi)5.1 觸發(fā)器的ECA結(jié)構(gòu)與兩種粒度完整性約束解決的是“規(guī)則型”問題但業(yè)務(wù)里經(jīng)常有“規(guī)則不好用SQL聲明”的場景。比如某個操作同時要檢查多個表、要自動寫日志、要同步更新匯總字段。這時候教材就翻出了觸發(fā)器這張牌。觸發(fā)器的全稱叫“事件-條件-動作”Event-Condition-Action可以理解為一段被數(shù)據(jù)庫自動執(zhí)行的小程序。它包含四個關(guān)鍵要素觸發(fā)事件INSERT、UPDATE、DELETE 等操作觸發(fā)時機(jī)BEFORE操作前觸發(fā)或 AFTER操作后觸發(fā)觸發(fā)條件只有滿足條件才執(zhí)行動作觸發(fā)動作要執(zhí)行的一組SQL語句或過程而“行級觸發(fā)器”和“語句級觸發(fā)器”的區(qū)別是考試?yán)镒钊菀妆粏柕降狞c(diǎn)。FOR EACH ROW表示對被影響的每一行執(zhí)行一次動作FOR EACH STATEMENT則無論這次語句影響了多少行整體只觸發(fā)一次。舉個例子寫一個簡單的工資審計(jì)觸發(fā)器把“工資突然大幅下降”的更新行為記錄下來CREATE TRIGGER Tg_Salary_Log AFTER UPDATE OF Sal ON Employee FOR EACH ROW WHEN (NEW.Sal OLD.Sal * 0.8) BEGIN INSERT INTO Sal_Log(EID, Old_Sal, New_Sal, Change_Time) VALUES (NEW.EID, OLD.Sal, NEW.Sal, CURRENT_TIMESTAMP); END;這段觸發(fā)器的邏輯就是每當(dāng) Employee 表 Sal 列發(fā)生更新如果新工資比舊工資下降了超過20%就自動往日志表里插一條記錄。整個過程對業(yè)務(wù)層完全透明不需要應(yīng)用端寫任何額外代碼。5.2 典型觸發(fā)場景與“別濫用”的提醒觸發(fā)器真正好用的場景我經(jīng)歷過的有幾類一是敏感表變更的自動審計(jì)日志二是數(shù)據(jù)同步比如訂單表插入后自動往統(tǒng)計(jì)表里更新匯總值三是復(fù)雜完整性校驗(yàn)比如“庫存不能超賣”這種需要跨表判斷的規(guī)則。但是觸發(fā)器是把雙刃劍。我的建議是能用約束解決的不要用觸發(fā)器。約束是聲明式的數(shù)據(jù)庫可以優(yōu)化執(zhí)行路徑觸發(fā)器是過程式的一旦觸發(fā)會帶來額外的隱式操作復(fù)雜觸發(fā)器還可能造成連鎖觸發(fā)、死鎖而且排障時非常不好查。你執(zhí)行一條看似簡單的UPDATE實(shí)際背后跑了一長串觸發(fā)器這不是什么好體驗(yàn)。如果要上線含觸發(fā)器的功能我有三個實(shí)操習(xí)慣第一在測試環(huán)境里分別用單行更新和批量更新驗(yàn)證行級觸發(fā)器和語句級觸發(fā)器的行為差異第二在觸發(fā)器中統(tǒng)一加日志輸出方便事后定位第三評估觸發(fā)器的性能權(quán)重避免把高頻寫入路徑變成“觸發(fā)器瓶頸”。這些經(jīng)驗(yàn)在教材里沒有但真實(shí)的數(shù)據(jù)庫運(yùn)維里很重要。6. 備考時最容易翻車的三個細(xì)節(jié)與復(fù)習(xí)主線6.1 細(xì)節(jié)一WITH GRANT OPTION的回收鏈條考試?yán)锝?jīng)常出這樣的題A給B授權(quán)并帶WITH GRANT OPTIONB又把同一權(quán)限給了C現(xiàn)在A要回收B的權(quán)限問C的權(quán)限是否還能保留答案取決于實(shí)現(xiàn)但教材默認(rèn)是級聯(lián)回收即C的權(quán)限也會被回收。答題時如果不確定最好把條件寫清楚“默認(rèn)按級聯(lián)方式回收如果回收語句指定了 RESTRICT且有下游授權(quán)存在則回收失敗”。這才是完整拿分的思路。6.2 細(xì)節(jié)二完整性約束與觸發(fā)器的辨析簡答題里很容易出現(xiàn)“這個規(guī)則用什么實(shí)現(xiàn)更好”。判據(jù)很簡單如果規(guī)則能用一條CHECK、UNIQUE、NOT NULL或外鍵聲明清楚就用約束只有規(guī)則涉及多表聯(lián)動、操作時機(jī)、自定義過程邏輯時才上觸發(fā)器。答題時可以用“約束是聲明式觸發(fā)器是過程式”作為第一句定調(diào)然后結(jié)合實(shí)際場景說明。6.3 細(xì)節(jié)三安全方案的層次順序更考驗(yàn)綜合能力的題長這樣為一套成績系統(tǒng)設(shè)計(jì)安全方案要求只有教務(wù)處人員能查看統(tǒng)計(jì)結(jié)果并且每次查詢都要留痕。回答這類題要按層來先建“統(tǒng)計(jì)視圖”隔離敏感明細(xì)再通過角色授權(quán)僅給教務(wù)處角色最后開啟對該視圖的審計(jì)。這三步對應(yīng)視圖機(jī)制、存取控制、審計(jì)答出來就是一條完整的鏈路。掌握了這種“分層答題”思路安全性的論述題基本都能踩在得分點(diǎn)上。6.4 復(fù)習(xí)建議把概念題改造成場景題回過頭去看基礎(chǔ)篇四的內(nèi)容看起來是“背概念”但真正拉開差距的恰恰是“能不能把概念落成SQL、落成設(shè)計(jì)”。我的復(fù)習(xí)方法很簡單把每一道概念題都改造成“給一個場景自己寫一段建表、授權(quán)、觸發(fā)器的SQL”再對照教材核對。比如教材說“用戶定義完整性”你就親手在本地?cái)?shù)據(jù)庫里建一張帶CHECK的表試一下插入非法值會發(fā)生什么教材說“參照完整性的級聯(lián)刪除”你就建兩張帶外鍵的表實(shí)際刪一行看看教材說“審計(jì)”你就開一下general log看看自己剛才的操作被記成了什么樣。數(shù)據(jù)庫這個學(xué)科看十遍不如動手一遍。安全性與完整性這兩章尤其如此你把權(quán)限、約束、觸發(fā)器都真正跑過一遍之后那些零散的知識點(diǎn)會自動串成一張網(wǎng)。后面再去做系統(tǒng)設(shè)計(jì)或備考復(fù)試你會發(fā)現(xiàn)很多所謂難題其實(shí)都是你早就跑通過的操作場景。