沈云，資深工程師惭载，微軟解決方案專家
SQL Server如何指定用戶單獨(dú)權(quán)限
上周， 有位朋友給我提出了需求：
為公司應(yīng)用提供用戶分配數(shù)據(jù)訪問(wèn)權(quán)限。順便總結(jié)了下有如下要求：
給某個(gè)用戶查詢所有數(shù)據(jù)庫(kù)的權(quán)限
給某個(gè)用戶只有備份數(shù)據(jù)庫(kù)的權(quán)限
給一個(gè)用戶只有指定數(shù)據(jù)庫(kù)的權(quán)限
給一個(gè)用戶只有某個(gè)表的權(quán)限
要進(jìn)行以上任務(wù)心例，首先我們先了解下數(shù)據(jù)庫(kù)的權(quán)限相關(guān)的內(nèi)容
主體
“主體” 是可以請(qǐng)求 SQL Server 資源的實(shí)體。 與 SQL Server 授權(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ù)用戶
數(shù)據(jù)庫(kù)角色
應(yīng)用程序角色
SQL Server sa登錄名
SQL Server sa登錄名是服務(wù)器級(jí)的主體乘寒。 默認(rèn)情況下，該登錄名是在安裝實(shí)例時(shí)創(chuàng)建的匪补。
public數(shù)據(jù)庫(kù)角色
每個(gè)數(shù)據(jù)庫(kù)用戶都屬于 public數(shù)據(jù)庫(kù)角色伞辛。 當(dāng)尚未對(duì)某個(gè)用戶授予或拒絕對(duì)安全對(duì)象的特定權(quán)限時(shí)，則該用戶將繼承授予該安全對(duì)象的public角色的權(quán)限夯缺。
INFORMATION_SCHEMA和sys
每個(gè)數(shù)據(jù)庫(kù)都包含兩個(gè)實(shí)體：INFORMATION_SCHEMA和sys蚤氏，它們都作為用戶出現(xiàn)在目錄視圖中。 這兩個(gè)實(shí)體是SQL Server所必需的踊兜。 它們不是主體竿滨，不能修改或刪除它們。
基于證書(shū)的SQL Server登錄名
名稱由雙井號(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用戶
每個(gè)數(shù)據(jù)庫(kù)包括一個(gè)guest典蝌。 授予guest用戶的權(quán)限由對(duì)數(shù)據(jù)庫(kù)具有訪問(wèn)權(quán)限曙砂，但在數(shù)據(jù)庫(kù)中沒(méi)有用戶帳戶的用戶繼承。 不能刪除guest用戶骏掀，但可通過(guò)撤消該用戶的CONNECT權(quán)限將其禁用鸠澈。 可以通過(guò)在master或tempdb以外的任何數(shù)據(jù)庫(kù)中執(zhí)行REVOKE CONNECT FROM GUEST來(lái)撤消CONNECT權(quán)限。
客戶端和數(shù)據(jù)庫(kù)服務(wù)器
根據(jù)定義截驮，客戶端和數(shù)據(jù)庫(kù)服務(wù)器是安全主體笑陈，可以得到保護(hù)。 在建立安全的網(wǎng)絡(luò)連接前葵袭，這些實(shí)體之間可以互相進(jìn)行身份驗(yàn)證涵妥。 SQL Server支持Kerberos身份驗(yàn)證協(xié)議，該協(xié)議定義客戶端與網(wǎng)絡(luò)身份驗(yàn)證服務(wù)交互的方式坡锡。
創(chuàng)建數(shù)據(jù)庫(kù)用戶
SQL 2016 中支持11種用戶類型：
用戶基于登錄名在master這是最常見(jiàn)的用戶類型蓬网。
基于登錄名基于的 Windows Active Directory 帳戶的用戶。 CREATE USER [Contoso\Fritz];
基于 Windows 組的登錄名的用戶鹉勒。 CREATE USER [Contoso\Sales];
基于使用 SQL Server 身份驗(yàn)證的登錄名的用戶帆锋。 CREATE USER Mary;
在數(shù)據(jù)庫(kù)進(jìn)行身份驗(yàn)證的用戶建議以幫助使你的數(shù)據(jù)庫(kù)可移植性。
始終允許在 SQL Database禽额。 中包含的數(shù)據(jù)庫(kù)中只允許存在 SQL Server锯厢。
基于無(wú)登錄名的 Windows 用戶的用戶皮官。 CREATE USER [Contoso\Fritz];
基于無(wú)登錄名的 Windows 組的用戶。 CREATE USER [Contoso\Sales];
中的用戶 SQL Database 或 SQL 數(shù)據(jù)倉(cāng)庫(kù) 基于 Azure Active Directory 的用戶实辑。 CREATE USER [Contoso\Fritz] FROM EXTERNAL PROVIDER;
擁有密碼的包含數(shù)據(jù)庫(kù)用戶捺氢。 (在中不可用 SQL 數(shù)據(jù)倉(cāng)庫(kù)。) CREATE USER Mary WITH PASSWORD = '********';
基于Windows主體通過(guò)Windows組登錄名進(jìn)行連接的用戶
基于無(wú)登錄名但可通過(guò) Windows 組中的成員身份連接到數(shù)據(jù)庫(kù)引擎的 Windows 用戶的用戶剪撬。 CREATE USER [Contoso\Fritz];
基于無(wú)登錄名但可通過(guò)其他 Windows 組中的成員身份連接到數(shù)據(jù)庫(kù)引擎的 Windows 組的用戶摄乒。 CREATE USER [Contoso\Fritz];
無(wú)法進(jìn)行身份驗(yàn)證的用戶這些用戶無(wú)法登錄到 SQL Server 或 SQL Database。
沒(méi)有登錄名的用戶婿奔。 不能登錄，但可以被授予權(quán)限问慎。 CREATE USER CustomApp WITHOUT LOGIN;
基于證書(shū)的用戶萍摊。 不能登錄，但可以被授予權(quán)限如叼，也可以對(duì)模塊進(jìn)行簽名冰木。 CREATE USER TestProcess FOR CERTIFICATE CarnationProduction50;
基于非對(duì)稱密鑰的用戶。 不能登錄笼恰，但可以被授予權(quán)限踊沸，也可以對(duì)模塊進(jìn)行簽名。 CREATE User TestProcess FROM ASYMMETRIC KEY PacificSales09;
下面的圖片顯示了創(chuàng)建數(shù)據(jù)庫(kù)用戶需要的選項(xiàng)的含義：
創(chuàng)建用戶可以使用界面完成：
也可以使用T-SQL 來(lái)進(jìn)行創(chuàng)建
-- 創(chuàng)建登錄名：Test? 密碼是： '123456'.
CREATE LOGIN Test
WITH PASSWORD = '123456';
GO
上面說(shuō)完了用戶社证，下面說(shuō)下數(shù)據(jù)庫(kù)的角色和權(quán)限
服務(wù)器級(jí)別的權(quán)限
SQL Server提供服務(wù)器級(jí)角色以幫助你管理服務(wù)器上的權(quán)限逼龟。 這些角色是可組合其他主體的安全主體。 服務(wù)器級(jí)角色的權(quán)限作用域?yàn)榉?wù)器范圍追葡。 （“角色”類似于Windows操作系統(tǒng)中的“組”腺律。）
SQL Server提供了九種固定服務(wù)器角色。 無(wú)法更改授予固定服務(wù)器角色的權(quán)限宜肉。 從SQL Server 2012開(kāi)始匀钧，您可以創(chuàng)建用戶定義的服務(wù)器角色，并將服務(wù)器級(jí)權(quán)限添加到用戶定義的服務(wù)器角色谬返。
你可以將服務(wù)器級(jí)主體（SQL Server登錄名之斯、Windows帳戶和Windows組）添加到服務(wù)器級(jí)角色。 固定服務(wù)器角色的每個(gè)成員都可以將其他登錄名添加到該同一角色遣铝。 用戶定義的服務(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è)用戶查詢所有數(shù)據(jù)庫(kù)的權(quán)限
給某個(gè)用戶只有備份數(shù)據(jù)庫(kù)的權(quán)限
給一個(gè)用戶只有指定數(shù)據(jù)庫(kù)的權(quán)限
給一個(gè)用戶只有某個(gè)表的權(quán)限
給某個(gè)用戶查詢所有數(shù)據(jù)庫(kù)的權(quán)限
創(chuàng)建一個(gè)用戶
USE [master]
GO
CREATE LOGIN [Test1] WITH PASSWORD=N'password@123'
使用Test1連接數(shù)據(jù)庫(kù)實(shí)例
可以看到數(shù)據(jù)庫(kù)列表檀何， 但是無(wú)法訪問(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用戶對(duì)finacedemo的讀取權(quán)限
給某個(gè)用戶只有備份數(shù)據(jù)庫(kù)的權(quán)限
Test1對(duì)于finacedemo無(wú)備份權(quán)限
賦予備份權(quán)限
ALTER ROLE [db_backupoperator] ADD MEMBER [Test1]
給一個(gè)用戶只有指定數(shù)據(jù)庫(kù)的權(quán)限
我們需要Test1只能看到FinanceDemo，其他所有數(shù)據(jù)庫(kù)都不能看到
執(zhí)行下面腳本
USE [master]
Deny VIEW any DATABASE TO Test1;
go
運(yùn)行后的效果
執(zhí)行：
ALTER AUTHORIZATION ON DATABASE::FinanceDemo TO test1
完成后結(jié)果：
給一個(gè)用戶只有某個(gè)表的權(quán)限
創(chuàng)建測(cè)試用戶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.t2 to test3
grant select on dbo.t2 to test3
use testDB
查看test3用戶獲得的權(quán)限
exec sp_helprotect @username = 'test3'
執(zhí)行select * from t2
執(zhí)行插入操作失敗频鉴。
以上實(shí)現(xiàn)了對(duì)數(shù)據(jù)庫(kù)權(quán)限很細(xì)致的管理栓辜，更加詳細(xì)的控制可以參考technet上面的信息。
權(quán)限管理非常復(fù)雜垛孔，以上只是做了簡(jiǎn)單的介紹藕甩。需要更加詳細(xì)的內(nèi)容，需要自己去研究周荐。在technet上可以找到更加詳細(xì)的信息狭莱。
立即訪問(wèn)http://market.azure.cn
