新手學SQL Server 2012「使用者定義伺服器角色(User-defined server roles)」

by Derrick 5. 九月 2012 10:25

前言

在先前的SQL Server版本上,雖然在執行個體層級上有提供了九個「固定伺服器角色(fixed server-level roles)」。這些角色都是由系統事先所制定,身為資料庫管理師,你無法變更「固定伺服器角色」所具備的權限,你只能學習了解這些「固定伺服器角色」所具備的權力範圍,將登入帳戶加入到適當的「固定伺服器角色」內。

這樣的作法,不但是缺乏彈性,也容易賦予過大的權限,違反了「最小權限原則(Least Privilege Principle)」的規範。

如今,在SQL Server 2012上新增加了「使用者定義伺服器角色」,將可以解決前述遇到的問題。

認識「使用者定義伺服器角色」

在SQL Server 2012版本,新增加了「使用者定義伺服器角色(User-defined server roles)」,讓你可以自行建立所需的伺服器角色,將伺服器層級的權限組態加入到此「使用者定義伺服器角色」內。

之後,再將伺服器層級的「主體」(例如:Windows登入帳戶、Windows群組或是SQL Server驗證登入帳戶等),加入到「使用者定義伺服器角色」。

使用「使用者定義伺服器角色」,除了可以更彈性地組態所需伺服器層級的權限,還具備了以下的特性:

  • 遵守拒絕(DENY)優先的規則。
  • 其成員無法將其他伺服器主體加入至此角色內。

在設計上,「使用者定義伺服器角色」也可以是其他伺服器角色的成員,這包含了「固定伺服器角色」以及「使用者定義伺服器角色」,也就是說,可以做到巢狀式的伺服器角色。

遵守拒絕(DENY)優先的規則

若將登入帳戶加入到具備最高權限的sysadmin固定伺服器角色內,隸屬於此角色的成員,在伺服器內不但是可以執行任何活動。而且,也無法拒絕(DENY)此成員不得具備某些特定的權限。

但若是使用「使用者定義伺服器角色」,則是遵守了「拒絕(DENY)」優先的規則。例如:雖然賦予此角色具備了CONTROL SERVER的權限,但需要遵守拒絕優先的規則,仍是可以明確拒絕不得行使某些權限。

其成員無法將其他伺服器主體加入至此角色內

隸屬於各個「固定伺服器角色」的成員,是可以將其他的登入帳戶也加入到相對的「固定伺服器角色」內。

舉例來說:建立兩個登入帳戶set01與set02,先將set01登入帳戶加入到setupadmin固定伺服器層級角色內,而使用set01登入帳戶也可以將另外一個set02登入帳戶加入到setupadmin固定伺服器層級角色內;若使用SSMS工具可能無法執行,但卻可以使用Transct-SQL陳述式來達成,請參考以下的範例程式碼:

USE master
GO
ALTER SERVER ROLE [setupadmin]
    ADD MEMBER [set02]
GO

範例程式碼1:將登入帳戶加入到setupadmin「固定伺服器角色」內


但是在「使用者定義伺服器角色」上,其內所屬的成員,卻是無法將其他登入帳戶再加入到此角色內。

變更伺服器角色所需具備的權限

整理前述,請參考以下表格的整理:

伺服器角色

說明

「固定伺服器角色」

若要將成員加入至「固定伺服器角色」,你必須是該「固定伺服器角色」的成員,或是sysadmin「固定伺服器角色」的成員。

具備CONTROL SERVER和ALTER ANY SERVER ROLE權限,但這並不足夠執行固定伺服器角色的 ALTER SERVER ROLE陳述式,而且也無法授與固定伺服器角色的ALTER 權限。

「使用者定義伺服器角色」

若要將成員加入至使用者定義伺服器角色,你必須是sysadmin固定伺服器角色的成員,或擁有CONTROL SERVER或ALTER ANY SERVER ROLE權限。或者,你必須有該角色的ALTER權限。

這是不同於「固定伺服器角色」的,隸屬「使用者定義伺服器角色」的成員本來就沒有將成員加入至該相同角色的權限。

表1:組態登入帳戶加入到「固定伺服器角色」或「使用者定義伺服器角色」所需具備的權限

刪除「使用者定義伺服器角色」

若需要刪除「使用者定義伺服器角色」,需要留意以下的事情:

  • 擁有「安全性實體」的「使用者定義伺服器角色」,無法從伺服器卸除。
    • 若要卸除一個擁有「安全性實體」的「使用者定義伺服器角色」,必須先轉移那些「安全性實體」的擁有權,或者刪除「安全性實體」。
  • 含有成員的「使用者定義伺服器角色」,無法卸除。
    • 若要卸除具有成員的「使用者定義伺服器角色」,你必須先使用ALTER SERVER ROLE移除此角色內的成員。
  • 「固定伺服器角色」是無法移除。

 

實作練習一:認識使用者定義伺服器角色

任務一:建立與組態使用者定義伺服器角色


步驟01. 使用SSMS管理工具,建立SQL驗證登入帳戶:dev01與s1,密碼設定為:P@ssw0rd,或是執行以下的範例程式碼:

-- 建立SQL驗證登入帳戶:dev01
USE [master]
GO
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'dev01')
    DROP LOGIN [dev01]
GO
CREATE LOGIN [dev01]
    WITH PASSWORD=N'P@ssw0rd',
    DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- 建立SQL驗證登入帳戶:s1
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N's1')
    DROP LOGIN [s1]
GO
CREATE LOGIN [s1] WITH PASSWORD=N'P@ssw0rd',
    DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

範例程式碼2:建立SQL驗證登入帳戶:dev01與s1

步驟02. 使用「物件總管」,連線到目標執行個體,展開「安全性」\「伺服器角色」。滑鼠右鍵,選擇「新增伺服器角色」。


SNAGHTML30e2ff

圖1:新增伺服器角色

步驟03. 在「New Server Role」視窗,輸入以下的參數:

  • 在「伺服器角色名稱」方塊,輸入:DevGroup。
  • 在「擁有者」方塊,點選...瀏覽圖示,在「選取伺服器登入或角色」視窗,點選「瀏覽」,在「瀏覽物件」視窗,勾選sysadmin,點選「確定」\「確定」。請參考下圖所示:

image

圖2:建立使用者定義伺服器角色

  • 在中間的「安全性實體」區域,展開「伺服器」,展開此執行個體名稱,在下方的「<執行個體名稱>的權限」區域,在「明確」頁籤,勾選所需要具備的權限。
  • 在本次實作練習中,明確的「授與」:「控制伺服器」權限,並且,明確的「拒絕」:「改變任何伺服器角色」、「改變任何伺服器稽核」、「改變任何登入」、「關機」等權限。請參考下圖所示:

SNAGHTML4ab6c2

圖3:在組態的權限上,明確的授與或拒絕


在圖3中,可能尚未完全中文化,視窗標題顯示的是:New Server Role,一般推論應該是翻譯為:新增伺服器角色。

步驟04. 延續步驟03,在左邊窗格,點選「成員」頁籤,在右邊窗格,設定以下參數:

  • 在「此角色的成員」區域,點選「加入」。
  • 在「選取伺服器登入或角色」視窗,點選「瀏覽」。
  • 在「瀏覽物件」視窗,勾選dev01登入帳戶。
  • 點選「確定」\「確定」。請參考下圖所示:

image

圖4:將登入帳戶加入到「使用者定義伺服器角色」

步驟05. 點選「確定」,完成「使用者定義伺服器角色」的建立與組態作業,請參考下圖所示:

image

圖5:檢視先前建立的「使用者定義伺服器角色」

或是使用以下的範例程式碼來建立與組態「使用者定義伺服器角色」:

-- 02_建立「使用者定義的伺服器角色(User-defined server roles)」:DevGroup
-- 設定sysadmin「固定伺服器角色」來擁有此DevGroup「使用者定義伺服器角色」。
USE [master]
GO
CREATE SERVER ROLE [DevGroup]
    AUTHORIZATION [sysadmin]
GO

-- 03_明確的「授與」或「拒絕」
use [master]
GO
-- 授與:「控制伺服器」
GRANT CONTROL SERVER TO [DevGroup]
GO

-- 拒絕:改變任何伺服器角色
DENY ALTER ANY SERVER ROLE TO [DevGroup]
GO
-- 拒絕:改變任何伺服器稽核
DENY ALTER ANY SERVER AUDIT TO [DevGroup]
GO
-- 拒絕:改變任何登入
DENY ALTER ANY LOGIN TO [DevGroup]
GO
-- 拒絕:「關機」
DENY SHUTDOWN TO [DevGroup]
GO

-- 04_將登入帳戶:dev01,加入到此「使用者定義伺服器角色」內。
ALTER SERVER ROLE [DevGroup]
    ADD MEMBER [dev01]
GO

範例程式碼3:建立與組態「使用者定義伺服器角色」

若要檢視伺服器層級角色的成員等相關資料,請參考以下的範例程式碼,以及下圖所示:

-- 檢視伺服器層級角色的成員等相關資料
USE master
GO
SELECT r.name N'固定伺服器角色', p.name N'主體名稱',
    p.type_desc N'主體類型', p.create_date N'建立時間', p.modify_date N'修改時間', p.default_database_name N'預設資料庫',
    p.default_language_name N'預設語言',p.is_disabled N'是否已停用'
FROM sys.server_role_members AS srm INNER JOIN sys.server_principals AS r
    ON srm.role_principal_id = r.principal_id
    INNER JOIN sys.server_principals AS p
    ON srm.member_principal_id = p.principal_id
ORDER BY 1;
GO

範例程式碼4:檢視伺服器層級角色的成員等相關資料

image

圖6:檢視伺服器層級角色的成員等相關資料

任務二:測試使用者定義伺服器角色所具備的權限


步驟01. 利用dev01登入帳戶來使用「物件總管」。
步驟02. 測試以下的工作,例如:建立資料庫、刪除資料庫,這應該會成功,但建立與刪除「使用者定義伺服器角色」等,這應該會失敗。或是,請參考以下的範例程式碼:

USE master
GO
-- 建立DB02資料庫
CREATE DATABASE DB02
GO
-- 刪除DB02資料庫
DROP DATABASE DB02
GO
-- 建立Dev2使用者定義伺服器角色
CREATE SERVER ROLE [Dev2]
    AUTHORIZATION [sysadmin]
GO
/*
訊息 15247,層級 16,狀態 1,行 2
使用者沒有執行此動作的權限。
*/

範例程式碼5:測試建立與刪除物件1

步驟03. 測試以下的工作,例如:建立登入帳戶,修改已經存在的登入帳戶,刪除已經存在的登入帳戶、關閉伺服器等,這些都會失敗。或是,請參考以下的範例程式碼:

USE [master]
GO
-- 失敗:建立SQL驗證登入帳戶:s2
CREATE LOGIN [s2]
    WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
/*
訊息 15247,層級 16,狀態 1,行 2
使用者沒有執行此動作的權限。
*/
-- 失敗:刪除已經存在的SQL驗證登入帳戶:s1
DROP LOGIN [s1]
GO
/*
訊息 15151,層級 16,狀態 1,行 1
無法 卸除 登入 's1',因為它不存在或您沒有權限。
*/
-- 失敗:停用已經存在的SQL驗證登入帳戶:s1
ALTER LOGIN [s1] DISABLE
GO
/*
訊息 15151,層級 16,狀態 1,行 1
無法 改變 登入 's1',因為它不存在或您沒有權限。
*/
-- 成功:拒絕CONNECT權限
DENY CONNECT SQL TO [s1]
GO

-- 失敗:立即停止 SQL Server
SHUTDOWN
GO
/*
使用者沒有執行此動作的權限。
訊息 0,層級 11,狀態 0,行 0
在目前的命令上發生嚴重錯誤。如果有任何結果,都必須捨棄。
*/

範例程式碼6:測試建立與刪除物件2


在範例程式碼6中,dev01登入帳戶是被明確拒絕:改變任何登入DENY ALTER ANY LOGIN,所以像是CREATE LOGIN、ALTER LOGIN或DROP LOGIN等Transact-SQL陳述式是無法執行。但拒絕CONNECT權限的Transact-SQL陳述式卻是可以正常執行。

任務三:使用sysadmin固定伺服器角色當做對照組


步驟01. 使用「物件總管」,以sysadmin固定伺服器角色的成員登入到系統。
步驟02. 建立op01登入帳戶,加入到sysadminsysadmin固定伺服器角色內。組態此op01登入帳戶,明確拒絕以下的權限:改變任何伺服器稽核、改變任何登入、關機等。請參考以下的範例程式碼

-- 01_建立登入帳戶:op01
USE [master]
GO
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'op01')
DROP LOGIN [op01]
GO
CREATE LOGIN [op01]
    WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- 02_加入到「固定伺服器層級角色」:sysadmin內
ALTER SERVER ROLE [sysadmin]
    ADD MEMBER [op01]
GO
-- 03_明確「拒絕」以下的權限
-- 拒絕:改變任何伺服器稽核
DENY ALTER ANY SERVER AUDIT TO op01
GO
-- 拒絕:改變任何登入
DENY ALTER ANY LOGIN TO op01
GO
-- 拒絕:「關機」
DENY SHUTDOWN TO op01
GO

範例程式碼7:使用sysadmin固定伺服器角色的成員當做對照組

步驟03. 利用op01登入帳戶來使用「物件總管」。參考任務二:測試使用者定義伺服器角色所具備的權限,或是執行範例程式碼5與範例程式碼6來執行測試作業。應該可以成功執行,最後此執行個體也被關機,請參考以下的訊息以及下圖所示:

伺服器已由 request 從登入 op01 關機。
SQL Server 正在結束這個處理序。

image

圖7:op01登入帳戶,成功地對執行個體執行關機動作

步驟04. 請重新啟動執行個體。

任務四:變更與刪除使用者定義伺服器角色

請參考表1:組態登入帳戶加入到「固定伺服器角色」或「使用者定義伺服器角色」所需具備的權限之說明。此外,在先前的任務一,或參考範例程式碼3,已經明確地賦予拒絕權限:改變任何伺服器角色(ALTER ANY SERVER ROLE)。

步驟01. 利用dev01登入帳戶來使用「物件總管」。
步驟02. 測試以下的工作:將s1登入帳戶加入到DevGroup「使用者定義伺服器角色」內,或是將s1登入帳戶加入到sysadmin「固定伺服器角色」內,這些都應該會失敗。或是,請參考以下的範例程式碼以及下圖所示:

USE master
GO
-- 失敗:將s1登入帳戶,加入到DevGroup「使用者定義伺服器角色」內
ALTER SERVER ROLE [DevGroup]
    ADD MEMBER [s1]
GO
-- 失敗:將DevGroup「使用者定義伺服器角色」,加入到sysadmin固定伺服器角色內
ALTER SERVER ROLE [sysadmin]
    ADD MEMBER [DevGroup]
GO
/*
訊息 15151,層級 16,狀態 1,行 1
無法 改變 伺服器角色 'sysadmin',因為它不存在或您沒有權限。
*/
-- 失敗:將dev01登入帳戶,加入到sysadmin固定伺服器角色內
ALTER SERVER ROLE [sysadmin]
    ADD MEMBER [dev01]
GO
/*
訊息 15151,層級 16,狀態 1,行 1
無法 改變 伺服器角色 'sysadmin',因為它不存在或您沒有權限。
*/
-- 失敗:將s1登入帳戶,加入到sysadmin固定伺服器角色內
ALTER SERVER ROLE [sysadmin]
    ADD MEMBER [s1]
GO
/*
訊息 15151,層級 16,狀態 1,行 1
無法
改變 伺服器角色 'sysadmin',因為它不存在或您沒有權限。
*/

範例程式碼8:變更使用者定義伺服器角色、固定伺服器角色

  image

圖8:無法變更使用者定義伺服器角色、固定伺服器角色

步驟03. 若是使用Transact-SQL陳述式來刪除「使用者定義伺服器角色」,請參考以下的範例程式碼:

USE master
GO
-- 刪除DevGroup使用者定義伺服器角色
DROP SERVER ROLE [DevGroup]
GO

範例程式碼9:刪除使用者定義伺服器角色

若是此「使用者定義伺服器角色」包含有成員,將導致刪除失敗,請先移除此角色的成員。請參考以下的錯誤訊息與下圖所示:

訊息 15144,層級 16,狀態 1,行 1
角色具有成員。在卸除之前必須是空的。

image

圖9:刪除使用者定義伺服器角色,卻失敗

步驟04. 若是使用SSMS管理工具的「物件總管」來刪除此「使用者定義伺服器角色」,系統會自動先將此「使用者定義伺服器角色」內的成員移除後,再刪除此「使用者定義伺服器角色」,你可以檢視其所產生的陳述式,請參考以下的範例程式碼:

USE [master]
GO
DECLARE @RoleName sysname
set @RoleName = N'DevGroup'
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = @RoleName AND type = 'R')
IF @RoleName <> N'public' and (select is_fixed_role from sys.server_principals where name = @RoleName) = 0
BEGIN
    DECLARE @RoleMemberName sysname
    DECLARE Member_Cursor CURSOR FOR
    select [name]
    from sys.server_principals
    where principal_id in (
        select member_principal_id
        from sys.server_role_members
        where role_principal_id in (
            select principal_id
            FROM sys.server_principals where [name] = @RoleName  AND type = 'R' ))

    OPEN Member_Cursor;

    FETCH NEXT FROM Member_Cursor
    into @RoleMemberName

    DECLARE @SQL NVARCHAR(4000)
       
    WHILE @@FETCH_STATUS = 0
    BEGIN
       
        SET @SQL = 'ALTER SERVER ROLE '+ QUOTENAME(@RoleName,'[') +' DROP MEMBER '+ QUOTENAME(@RoleMemberName,'[')
        EXEC(@SQL)
       
        FETCH NEXT FROM Member_Cursor
        into @RoleMemberName
    END;

    CLOSE Member_Cursor;
    DEALLOCATE Member_Cursor;
END
IF  EXISTS (SELECT * FROM sys.server_principals WHERE name = N'DevGroup' AND type = 'R')
DROP SERVER ROLE [DevGroup]
GO

範例程式碼10:先移除使用者定義伺服器角色內的成員,再刪除此使用者定義伺服器角色

結語


在本期文章中,介紹了認識「使用者定義伺服器角色」、遵守拒絕(DENY)優先的規則、其成員無法將其他伺服器主體加入至此角色內、變更伺服器角色所需具備的權限、刪除「使用者定義伺服器角色」等主題。

Tags:

SQL Server資料庫 | 陳俊宇Derrick Chen

新增評論




  Country flag
biuquote
  • 評論
  • 線上預覽
Loading






NET Magazine國際中文電子雜誌

NET Magazine國際中文電子版雜誌,由恆逸資訊創立於2000,自發刊日起迄今已發行超過500篇.NET相關技術文章,擁有超過40000名註冊讀者群。NET Magazine國際中文電子版雜誌希望藉於電子雜誌與NET Developer達到共同學習與技術新知分享,歡迎每一位對.NET 技術有興趣的朋友們多多支持本雜誌,讓作者群們可以有持續性的動力繼續爬文。<請加入免費訂閱>

月分類Month List