數(shù)據(jù)庫(kù)權(quán)限分配探討
上周， 有位朋友給我提出了這樣的需求：區(qū)分用戶(hù)訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)的權(quán)限。順便總結(jié)了下有如下要求：
- 某個(gè)用戶(hù)查詢(xún)所有數(shù)據(jù)庫(kù)的權(quán)限
- 某個(gè)用戶(hù)只有備份數(shù)據(jù)庫(kù)的權(quán)限
- 給一個(gè)用戶(hù)只能查看指定數(shù)據(jù)庫(kù)的權(quán)限
- 給一個(gè)用戶(hù)只有某個(gè)表的權(quán)限
要進(jìn)行以上任務(wù)，首先我們先了解下數(shù)據(jù)庫(kù)的權(quán)限相關(guān)的內(nèi)容
主體
“主體” 是可以請(qǐng)求 SQL Server資源的實(shí)體。 與 SQLServer授權(quán)模型的其他組件一樣，主體也可以按層次結(jié)構(gòu)排列。 主體的影響范圍取決于主體定義的范圍(Windows、服務(wù)器或數(shù)據(jù)庫(kù))以及主體是否不可分或是一個(gè)集合。 例如，Windows登錄名就是一個(gè)不可分主體，而Windows組則是一個(gè)集合主體。 每個(gè)主體都具有一個(gè)安全標(biāo)識(shí)符(SID)。
Windows級(jí)別的主體
Windows域登錄名
Windows 本地登錄名
SQL Server-級(jí)的主體
SQL Server登錄名
服務(wù)器角色
數(shù)據(jù)庫(kù)級(jí)的主體
數(shù)據(jù)庫(kù)用戶(hù)
數(shù)據(jù)庫(kù)角色
應(yīng)用程序角色
SQLServer sa登錄名
SQL Server sa登錄名是服務(wù)器級(jí)的主體。 默認(rèn)情況下，該登錄名是在安裝實(shí)例時(shí)創(chuàng)建的。
public數(shù)據(jù)庫(kù)角色
每個(gè)數(shù)據(jù)庫(kù)用戶(hù)都屬于 public數(shù)據(jù)庫(kù)角色。 當(dāng)尚未對(duì)某個(gè)用戶(hù)授予或拒絕對(duì)安全對(duì)象的特定權(quán)限時(shí)，則該用戶(hù)將繼承授予該安全對(duì)象的public角色的權(quán)限。
INFORMATION_SCHEMA和 sys
每個(gè)數(shù)據(jù)庫(kù)都包含兩個(gè)實(shí)體：
INFORMATION_SCHEMA和 sys，它們都作為用戶(hù)出現(xiàn)在目錄視圖中。這兩個(gè)實(shí)體是 SQL Server 所必需的。 它們不是主體，不能修改或刪除它們。
基于證書(shū)的 SQL Server登錄名
名稱(chēng)由雙井號(hào) (##)括起來(lái)的服務(wù)器主體僅供內(nèi)部系統(tǒng)使用。 下列主體是在安裝 SQL Server時(shí)從證書(shū)創(chuàng)建的，不應(yīng)刪除。
##MS_SQLResourceSigningCertificate##
##MS_SQLReplicationSigningCertificate##
##MS_SQLAuthenticatorCertificate##
##MS_AgentSigningCertificate##
##MS_PolicyEventProcessingLogin##
##MS_PolicySigningCertificate##
##MS_PolicyTsqlExecutionLogin##
guest用戶(hù)
每個(gè)數(shù)據(jù)庫(kù)包括一個(gè)guest。 授予guest用戶(hù)的權(quán)限由對(duì)數(shù)據(jù)庫(kù)具有訪(fǎng)問(wèn)權(quán)限，但在數(shù)據(jù)庫(kù)中沒(méi)有用戶(hù)帳戶(hù)的用戶(hù)繼承。不能刪除guest用戶(hù)，但可通過(guò)撤消該用戶(hù)的CONNECT權(quán)限將其禁用。 可以通過(guò)在master或 tempdb以外的任何數(shù)據(jù)庫(kù)中執(zhí)行 REVOKE CONNECTFROM GUEST來(lái)撤消 CONNECT權(quán)限。
客戶(hù)端和數(shù)據(jù)庫(kù)服務(wù)器
根據(jù)定義，客戶(hù)端和數(shù)據(jù)庫(kù)服務(wù)器是安全主體，可以得到保護(hù)。 在建立安全的網(wǎng)絡(luò)連接前，這些實(shí)體之間可以互相進(jìn)行身份驗(yàn)證。 SQLServer支持 Kerberos身份驗(yàn)證協(xié)議，該協(xié)議定義客戶(hù)端與網(wǎng)絡(luò)身份驗(yàn)證服務(wù)交互的方式。
創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù)
SQL2016中支持11種用戶(hù)類(lèi)型：
用戶(hù)基于登錄名在 master這是最常見(jiàn)的用戶(hù)類(lèi)型。
基于登錄名基于的Windows Active Directory帳戶(hù)的用戶(hù)
CREATE USER [Contoso\Fritz];
基于Windows組的登錄名的用戶(hù)。 CREATE USER [Contoso\Sales];
基于使用 SQLServer身份驗(yàn)證的登錄名的用戶(hù)。 CREATE USER Mary;
在數(shù)據(jù)庫(kù)進(jìn)行身份驗(yàn)證的用戶(hù)建議以幫助使你的數(shù)據(jù)庫(kù)可移植性。
始終允許在 SQL Database。 中包含的數(shù)據(jù)庫(kù)中只允許存在 SQL Server。
基于無(wú)登錄名的 Windows用戶(hù)的用戶(hù)
CREATEUSER [Contoso\Fritz];
基于無(wú)登錄名的Windows組的用戶(hù)。 CREATE USER [Contoso\Sales];
中的用戶(hù) SQLDatabase或 SQL數(shù)據(jù)倉(cāng)庫(kù) 基于 Azure Active Directory的用戶(hù)。 CREATE USER [Contoso\Fritz] FROMEXTERNAL PROVIDER;
擁有密碼的包含數(shù)據(jù)庫(kù)用戶(hù)。 (在中不可用 SQL數(shù)據(jù)倉(cāng)庫(kù)。)CREATE USER Mary WITHPASSWORD = '********';
基于 Windows主體通過(guò) Windows組登錄名進(jìn)行連接的用戶(hù)
基于無(wú)登錄名但可通過(guò)Windows組中的成員身份連接到數(shù)據(jù)庫(kù)引擎的Windows用戶(hù)的用戶(hù)
CREATE USER [Contoso\Fritz];
基于無(wú)登錄名但可通過(guò)其他Windows組中的成員身份連接到數(shù)據(jù)庫(kù)引擎的Windows組的用戶(hù)。 CREATE USER [Contoso\Fritz];
無(wú)法進(jìn)行身份驗(yàn)證的用戶(hù) 這些用戶(hù)無(wú)法登錄到 SQL Server或 SQL Database。
沒(méi)有登錄名的用戶(hù)。 不能登錄，但可以被授予權(quán)限
CREATE USER CustomAppWITHOUT LOGIN;
基于證書(shū)的用戶(hù)。 不能登錄，但可以被授予權(quán)限，也可以對(duì)模塊進(jìn)行簽名。 CREATE USERTestProcess FOR CERTIFICATE CarnationProduction50;
基于非對(duì)稱(chēng)密鑰的用戶(hù)。 不能登錄，但可以被授予權(quán)限，也可以對(duì)模塊進(jìn)行簽名。 CREATE User TestProcessFROM ASYMMETRIC KEY PacificSales09;
下面的圖片顯示了創(chuàng)建數(shù)據(jù)庫(kù)用戶(hù)需要的選項(xiàng)的含義：
創(chuàng)建用戶(hù)可以使用界面完成：
也可以使用T-SQL來(lái)進(jìn)行創(chuàng)建
-- 創(chuàng)建登錄名：Test 密碼是： '123456'.
CREATELOGIN Test
WITH PASSWORD = '123456';
GO
上面說(shuō)完了用戶(hù)，下面說(shuō)下數(shù)據(jù)庫(kù)的角色和權(quán)限
服務(wù)器級(jí)別的權(quán)限
SQL Server 提供服務(wù)器級(jí)角色以幫助你管理服務(wù)器上的權(quán)限。 這些角色是可組合其他主體的安全主體。服務(wù)器級(jí)角色的權(quán)限作用域?yàn)榉?wù)器范圍。 (“角色”類(lèi)似于 Windows 操作系統(tǒng)中的“組”。)
SQL Server 提供了九種固定服務(wù)器角色。 無(wú)法更改授予固定服務(wù)器角色的權(quán)限。 從 SQL Server 2012 開(kāi)始，您可以創(chuàng)建用戶(hù)定義的服務(wù)器角色，并將服務(wù)器級(jí)權(quán)限添加到用戶(hù)定義的服務(wù)器角色。
你可以將服務(wù)器級(jí)主體(SQL Server 登錄名、Windows帳戶(hù)和 Windows 組)添加到服務(wù)器級(jí)角色。 固定服務(wù)器角色的每個(gè)成員都可以將其他登錄名添加到該同一角色。用戶(hù)定義的服務(wù)器角色的成員則無(wú)法將其他服務(wù)器主體添加到角色。
下表顯示了服務(wù)器級(jí)的固定角色及其權(quán)限
下表顯示了固定數(shù)據(jù)庫(kù)角色及其能夠執(zhí)行的操作。 所有數(shù)據(jù)庫(kù)中都有這些角色。無(wú)法更改分配給固定數(shù)據(jù)庫(kù)角色的權(quán)限
無(wú)法更改分配給固定數(shù)據(jù)庫(kù)角色的權(quán)限。 下圖顯示了分配給固定數(shù)據(jù)庫(kù)角色的權(quán)限：
SQL 2016有一些數(shù)據(jù)庫(kù)的特殊權(quán)限
msdb角色
msdb數(shù)據(jù)庫(kù)中包含下表顯示的特殊用途的角色。
使用R Services
SQL Server(從 SQL Server vNext開(kāi)始)
安裝 R Services時(shí)，其他數(shù)據(jù)庫(kù)角色可用于管理包
下面講如何實(shí)現(xiàn)文章前面說(shuō)的需求：
給某個(gè)用戶(hù)查詢(xún)所有數(shù)據(jù)庫(kù)的權(quán)限
給某個(gè)用戶(hù)只有備份數(shù)據(jù)庫(kù)的權(quán)限
給一個(gè)用戶(hù)只有指定數(shù)據(jù)庫(kù)的權(quán)限
給一個(gè)用戶(hù)只有某個(gè)表的權(quán)限
給某個(gè)用戶(hù)查詢(xún)所有數(shù)據(jù)庫(kù)的權(quán)限
創(chuàng)建一個(gè)用戶(hù)
USE [master] GO CREATE LOGIN [Test1]WITH PASSWORD=N'password@123'
使用Test1連接數(shù)據(jù)庫(kù)實(shí)例
可以看到數(shù)據(jù)庫(kù)列表， 但是無(wú)法訪(fǎng)問(wèn)數(shù)據(jù)庫(kù)，
賦予test1對(duì)FinaceDemo的讀取權(quán)限
USE [FinaceDemo] GO CREATE USER [Test1] FOR LOGIN [Test1] ALTER ROLE [db_datareader] ADD MEMBER [Test1] GO
這樣就可以給test1用戶(hù)對(duì)finacedemo的讀取權(quán)限
但是test1 沒(méi)有寫(xiě)入權(quán)限
這樣就可以單獨(dú)對(duì)test1賦予數(shù)據(jù)庫(kù)的讀取權(quán)限進(jìn)行查看操作。
給某個(gè)用戶(hù)只有備份數(shù)據(jù)庫(kù)的權(quán)限
Test1 對(duì)于finacedemo無(wú)備份權(quán)限
賦予備份權(quán)限
ALTER ROLE [db_backupoperator] ADD MEMBER [Test1]
給一個(gè)用戶(hù)只有指定數(shù)據(jù)庫(kù)的權(quán)限
我們需要Test1只能看到 FinanceDemo，其他所有數(shù)據(jù)庫(kù)都不能看到
執(zhí)行下面腳本
USE [master] Deny VIEW any DATABASE TO Test1; go
運(yùn)行后的效果
Test1 連接后看不到任何數(shù)據(jù)庫(kù)
執(zhí)行：
ALTER AUTHORIZATIONON DATABASE::FinanceDemo TO test1
完成后結(jié)果：
Test1能查看到賦予權(quán)限的數(shù)據(jù)庫(kù)
給一個(gè)用戶(hù)只有某個(gè)表的權(quán)限
創(chuàng)建測(cè)試用戶(hù)test3
USE [master] GO CREATE LOGIN [Test3] WITH PASSWORD=N'password@123' -----賦予test2可以登錄testDB USE [testdb] GO CREATE USER [Test3] FOR LOGIN [Test3] GO
賦予test3對(duì)于t2表的update和select權(quán)限 grant update on dbo.t2to test3 grant select on dbo.t2to test3 use testDB 查看test3用戶(hù)獲得的權(quán)限 exec sp_helprotect @username='test3'
可以看到用戶(hù)test3擁有了t2的select和update權(quán)限
執(zhí)行select * from t2
執(zhí)行插入操作失敗。
以上介紹了對(duì)數(shù)據(jù)庫(kù)權(quán)限細(xì)致的管理，更加詳細(xì)的控制可以參考technet上面的信息。
權(quán)限管理非常復(fù)雜，以上只是做了簡(jiǎn)單的介紹。需要更加詳細(xì)的內(nèi)容，需要自己去研究。在technet上可以找到更加詳細(xì)的信息。
