SQL Server 權限管理

概述

對資料庫系統而言，保證資料的安全性永遠都是最重要的問題之一。一個好的資料庫環境，必須明确每個使用者的職責，并配置設定其對應的權限。同時出現問題了也可以找到根源。

你是否會有這樣的需求：

給某個使用者查詢所有資料庫的權限
給某個使用者隻有備份資料庫的權限
給一個使用者隻有指定資料庫的權限
給一個使用者隻有某個表的權限
給一個使用者隻有檢視某些對象（例如：視圖）的權限
給一個使用者隻有執行一些存儲過程的權限

元素
- 登入名
- 角色
- 使用者
- 架構
權限配置設定
- 建立登入名
- 給使用者配置設定資料庫檢視權限
- 給使用者查詢某個對象的權限
- 授予使用者架構的權限
查詢權限
回收權限
總結

元素

文章可能會有些枯燥，還望耐心，相信應該有你想要的。

登入名

隻有擁有了登入名才能通路執行個體（sql server）.

角色

角色是一類權限的組合。

資料庫角色的擁有者可以是使用者也可以是資料庫角色本身，管理者可以建立資料庫角色，也可以勉強将資料庫角色了解為一組相同權限的使用者，為什麼這麼說呢，因為資料庫角色和資料庫使用者不允許存在同名。

SQL Server 權限管理

注意：不要将使用者建立的資料庫角色添加到固定的伺服器資料庫角色當中去，否則将導緻固定的資料庫角色的權限更新。

伺服器角色的擁有者隻有登入名，伺服器角色是固定的，使用者無法建立伺服器角色。

SQL Server 權限管理

注意：一般不建議給使用者直接配置設定伺服器角色，因為伺服器角色是全局的，也就是說你擁有了伺服器級别的權限，一般建議給使用者配置設定資料庫，然後給對應的資料庫配置設定資料庫角色權限。

使用者

使用者是資料庫級的概念，資料庫使用者必須綁定具體的登入名，你也可以在建立登入名的時候綁定此登入名擁有的資料庫，當你綁定登入名資料庫後，資料庫預設就建立了此登入名同名的資料庫使用者，登入名與資料庫使用者之間就存在關聯關系，資料庫使用者是架構和資料庫角色的擁有者，即你可以将某個架構配置設定給使用者那麼該使用者就擁有了該架構所包含的對象，你也可以将某個資料庫角色配置設定給使用者，此使用者就擁有該資料庫角色的權限。

架構

架構是對象的擁有者，架構本身無權限，架構包含資料庫對象：如表、視圖、存儲過程和函數等，平時最常見的預設架構dbo.,如果沒指定架構預設建立資料庫對象都是以dbo.開頭，架構的擁有者是資料庫使用者、資料庫角色、應用程式角色。使用者建立的架構和角色隻能作用于目前庫。

了解了這些概念之後接下來就可以實踐了,接下來我們測試的都是伺服器角色選擇public,隻測試對資料庫權限的控制。

權限配置設定

建立登入名

建立一個登入名person,隻給登入名伺服器角色配置設定public權限，不配置設定資料庫

接下來用person登入執行個體，person使用者無法通路任何資料庫，由于我們未給使用者配置設定任何資料庫。

給使用者配置設定資料庫檢視權限

隻允許使用者檢視AdventureWorks2008R2資料庫

此時使用者可以查詢所有對象，但無法修改對象。

給使用者查詢某個對象的權限

如果覺得給使用者檢視權限太大了，将da_datareader資料庫角色權限回收，你會發現使用者可以通路資料庫，但是看不到任何對象。

隻給使用者檢視Person.Address表

USE AdventureWorks2008R2;
GRANT SELECT ON OBJECT::Person.Address TO person;
--或者使用
USE AdventureWorks2008R2;
GRANT SELECT ON Person.Address TO RosaQdM;
GO

擴充功能

--以下都是賦予使用者對表的dml權限

SQL Server 權限管理

---授予使用者person對表Person.Address的修改權限
USE AdventureWorks2008R2;
GRANT UPDATE ON Person.Address TO person;
GO

---授予使用者person對表Person.Address的插入權限
USE AdventureWorks2008R2;
GRANT INSERT ON Person.Address TO person;
GO

---授予使用者person對表Person.Address的删除權限
USE AdventureWorks2008R2;
GRANT DELETE ON Person.Address TO person;

--授予使用者存儲過程dbo.prc_errorlog的執行權限

GRANT EXECUTE ON dbo.prc_errorlog TO person

SQL Server 權限管理

标量函數權限：EXECUTE、REFERENCES。

表值函數權限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

存儲過程權限：EXECUTE。

表權限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

視圖權限：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

SQL Server 權限管理

授予使用者架構的權限

建立資料庫角色db_persons

新增架構

資料庫-安全性-架構

架構包含資料庫對象

建立架構persons表

---建立架構persons的表
CREATE TABLE Persons.sutdent
(id int not null)

你會發現使用者同時有了Persons.sutdent表的檢視權限，因為使用者是資料庫角色db_person的所有者，而db_person又是架構persons的所有者。

建立一些persons架構的視圖，存儲過程

SQL Server 權限管理

---建立視圖
USE AdventureWorks2008R2
GO
CREATE VIEW Persons.vwsutdent
AS
SELECT * FROM Persons.sutdent

GO
USE AdventureWorks2008R2
GO
---建立存儲過程
CREATE PROCEDURE Persons.SP_sutdent
(@OPTION NVARCHAR(50))
AS
BEGIN
    SET NOCOUNT ON
    IF @OPTION='Select'
    BEGIN
    SELECT * FROM Persons.sutdent
    END
END

SQL Server 權限管理

詳細的GRANT功能可以查詢2008r2連接配接叢書:

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/a760c16a-4d2d-43f2-be81-ae9315f38185.htm

查詢權限

SQL Server 權限管理

---登入名表
  select * from master.sys.syslogins 
  ---登入名與伺服器角色關聯表
  select * from sys.server_role_members
  ---伺服器角色表
  select * from sys.server_principals
  ----查詢登入名擁有的伺服器角色
  select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid
  from sys.server_role_members m  inner join sys.server_principals g on  g.principal_id = m.role_principal_id 
  inner join sys.server_principals u on u.principal_id = m.member_principal_id

  ---資料庫使用者表
  select * from sysusers
  ---資料庫使用者表角色關聯表
  select * from sysmembers 
  ---資料庫角色表
  select * from sys.database_principals
  ----查詢資料庫使用者擁有的角色
  select ta.name as username,tc.name as databaserole  from sysusers ta inner join sysmembers tb on ta.uid=tb.memberuid
  inner join  sys.database_principals tc on tb.groupuid=tc.principal_id

SQL Server 權限管理

查詢登入名與資料庫使用者之間的關系

SQL Server 權限管理

--查詢目前資料庫使用者關聯的登入名
  use AdventureWorks2008R2
  select ta.name as loginname,tb.name as databaseusername from master.sys.syslogins ta inner join sysusers tb on ta.sid=tb.sid 
  
  /*如果将目前資料庫還原到另一台伺服器執行個體上,剛好那台伺服器上也存在person登入使用者，你會發現二者的sid不一樣，
  由于sid不一樣,是以登入使用者不具有目前資料庫的通路權限,我們要想辦法将二者關聯起來。
  */
  ---關聯登入名與資料庫使用者（将資料庫使用者的sid刷成登入名的sid）
    use AdventureWorks2008R2
    EXEC sp_change_users_login 'Update_One', 'person', 'person'
    Go

SQL Server 權限管理

查詢資料庫使用者被授予的權限

exec sp_helprotect @username = 'person'

查詢person資料庫使用者權限會發現，資料庫使用者擁有的權限都是前面使用GRANT賦予的權限，而後面給使用者配置設定的架構對象不在這個裡面顯示，上面顯示的隻是被授予的權限，而架構是資料庫使用者所擁有的權限。

回收權限

如果安全對象是資料庫，對應 BACKUP DATABASE、BACKUP LOG、CREATE DATABASE、CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE TABLE 和 CREATE VIEW。

如果安全對象是标量函數，對應 EXECUTE 和 REFERENCES。

如果安全對象是表值函數，對應 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。

如果安全對象是存儲過程，表示 EXECUTE。

如果安全對象是表，對應 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。

如果安全對象是視圖，對應 DELETE、INSERT、REFERENCES、SELECT 和 UPDATE。

回收dbo.prc_errorlog存儲過程的執行權限

USE AdventureWorks2008R2;
REVOKE EXECUTE ON dbo.prc_errorlog    FROM person;

回收Person.Address表的查詢，修改，删除權限

SQL Server 權限管理

--回收修改
USE AdventureWorks2008R2;
REVOKE update ON   Person.Address FROM person;

USE AdventureWorks2008R2;
REVOKE alter ON   Person.Address FROM person;

--回收删除
USE AdventureWorks2008R2;
REVOKE delete ON   Person.Address FROM person;

--回收查詢
USE AdventureWorks2008R2;
REVOKE select ON   Person.Address FROM person;

SQL Server 權限管理

最後剩下owner為‘.’的是資料庫級的權限

最後回收資料庫的權限

SQL Server 權限管理

USE AdventureWorks2008R2;
REVOKE CREATE TABLE FROM person;
GO

CONNECT權限是使用者通路資料庫的權限，将此權限回收後使用者将無法通路資料庫
--USE AdventureWorks2008R2;
--REVOKE CONNECT FROM person;
--GO

SQL Server 權限管理

再執行exec sp_helprotect @username = 'person'，就剩下action=connect的資料庫通路權限

将權限回收後，資料庫使用者還剩下架構Persons的權限，如果還需要将該權限回收，隻需要使用者取消關聯對應的db_person資料庫角色權限。

詳細的revoke權限回收請參考2008r2聯機叢書：

ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.zh-CHS/s10de_6tsql/html/9d31d3e7-0883-45cd-bf0e-f0361bbb0956.htm

補充

針對生産資料庫伺服器建立一個應用程式通路的使用者最常見的是授予使用者某個資料庫：“查詢”、“删除”、“修改”、“插入”、“執行”的權限，用SQL語句實作如下（使用者：person,資料庫：news）：

SQL Server 權限管理

USE [master]
GO
---建立登入名
CREATE LOGIN [person] WITH PASSWORD=N'person', DEFAULT_DATABASE=[news], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [news]
GO
---在指定的資料庫下建立和登入名相關聯的資料庫使用者
CREATE USER [person] FOR LOGIN [person]
GO
USE [news]
GO
---在指定的資料庫下授予使用者SELECT,DELETE,UPDATE,INSERT,EXECUTE權限。
GRANT SELECT,DELETE,UPDATE,INSERT,EXECUTE TO person;

SQL Server 權限管理

注意：建立登入名在master資料庫下，建立資料庫使用者和授予資料庫權限都是在具體的資料庫下操作。

總結

是以如果你想對某個使用者某個資料庫的權限進行細分，你可以通過GRANT來授予具體的對象給使用者（當然你也可以revoke回收權限），也可以通過添加某個架構的權限給使用者那麼使用者就擁有該類架構的權限。

使用者擁有什麼權限取決于角色，而擁有哪些對象取決于擁有包含這些對象的架構，架構的擁有者可以是資料庫使用者也可以是資料庫角色也可以是應用程式角色，明白了這個道理你對權限的管理也就很清晰了。

雖然心有餘但是還是無法将整個知識點給講透，寫文章之前雖然把整個架構給整理了，但是在寫的過程中發現要寫的内容太多了，比如GRANT權限裡面就涉及了表、資料庫、應用程式角色、函數、證書、角色、架構、存儲過程、同義詞還有很多；同時表有可以精确到給具體的某個字段的權限，是以太多了，接下來的REVOKE也同樣是這麼多。本文可以起到一個引領的作用，讓你了解有這些功能，了解權限的功能細分；如果有興趣的朋友可以更深入的去鑽研，這篇文章寫下來還是挺累的,寫完這篇文章看一下時間已經是淩晨二點鐘，主要是思維不想被中斷是以一口氣給寫完了，希望能給大家有所幫助。

如果文章對大家有幫助，希望大家能給個贊，謝謝！！！

備注：

作者：pursuer.chen

部落格：http://www.cnblogs.com/chenmh

本站點所有随筆都是原創，歡迎大家轉載；但轉載時必須注明文章來源，且在文章開頭明顯處給明連結，否則保留追究責任的權利。

《歡迎交流讨論》

SQL Server 權限管理

概述

元素

登入名

角色

使用者

架構

權限配置設定

建立登入名

給使用者配置設定資料庫檢視權限

給使用者查詢某個對象的權限

授予使用者架構的權限

查詢權限

回收權限

總結

繼續閱讀

美軍改口了，因為紮哈羅娃提到了多個國家首都，字少事大

邊框變窄了蘋果準許iPhone 16 Pro顯示屏量産

英偉達市值一夜漲出一個“麥當勞”，距蘋果僅差1000億美元

中國消費者訴蘋果反壟斷案一審宣判：蘋果有市場支配地位，但未濫用

蘋果高調官宣WWDC 2024，手裡的AI大招終于藏不住了？

蘋果新低價無線耳機即将開售丨iPhone Ultra 超旗艦創曆史新高

蘋果CEO，一代不如一代？

蘋果 iPhone 16/Pro 系列手機殼首曝！果然多個洞

蘋果終于不再擠牙膏了？iPhone 16螢幕更大更亮更出衆

蘋果大降價反擊華為，奏效了？

蘋果iPhone 15 Pro之前機型将無緣iOS 18上諸多AI功能

最新中國手機銷量排名：蘋果重回第一小米升至第三

蘋果套袋小技術

髒亂差！美媒評價：國際空間站像垃圾堆，中國空間站像蘋果專賣店

“AI總龍頭”步步緊逼蘋果，英偉達能否奪走“市值TOP 2”王座？

蘋果新一代系統官宣：6月11日，正式釋出