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