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