SQL Server 許可權管理

pursuer.chen發表於2014-11-10
原文網址 : https://www.cnblogs.com/chenmh/p/4080420.html
SQLServer

標籤：SQL SERVER/MSSQL SERVER/資料庫/DBA/許可權控制/管理/分配/登入名/資料庫使用者/角色

概述

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

你是否會有這樣的需求：

  1. 給某個使用者查詢所有資料庫的許可權
  2. 給某個使用者只有備份資料庫的許可權
  3. 給一個使用者只有指定資料庫的許可權
  4. 給一個使用者只有某個表的許可權
  5. 給一個使用者只有檢視某些物件（例如：檢視）的許可權
  6. 給一個使用者只有執行一些儲存過程的許可權

 

目錄

元素

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

登入名

只有擁有了登入名才能訪問例項（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許可權
---授予使用者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

標量函式許可權：EXECUTE、REFERENCES。

表值函式許可權：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

儲存過程許可權：EXECUTE。

表許可權：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

檢視許可權：DELETE、INSERT、REFERENCES、SELECT、UPDATE。

授予使用者架構的許可權

新建資料庫角色db_persons

新增架構

資料庫-安全性-架構

架構包含資料庫物件

建立架構persons表

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

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

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

---建立檢視
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

 

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

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

查詢許可權

 

  ---登入名錶
  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

 

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

--查詢當前資料庫使用者關聯的登入名
  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

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

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表的查詢，修改，刪除許可權

--回收修改
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;

 

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

最後回收資料庫的許可權

USE AdventureWorks2008R2;
REVOKE CREATE TABLE FROM person;
GO

CONNECT許可權是使用者訪問資料庫的許可權，將此許可權回收後使用者將無法訪問資料庫
--USE AdventureWorks2008R2;
--REVOKE CONNECT FROM person;
--GO

再執行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）： 
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;

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

其它相關許可權授予

---授予Profile許可權
USE master
GO
GRANT ALTER TRACE TO person
GO
---授予活動監視器許可權
USE master
GO
GRANT VIEW SERVER STATE TO person
GO

 

總結

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

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

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

 

 

備註：

    作者：pursuer.chen

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

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

《歡迎交流討論》

相關文章