Security5：授予許可權_ZenDei技術網路在線

SQL Server授予用戶訪問對象的許可權，通常的模式是：Grants permissions on a securable to a principal(user or login)，也就是說，授予許可權的命令分為三部分：Permission，Securable 和 Principal，用一句話來解 ...

SQL Server授予用戶訪問對象的許可權，通常的模式是：Grants permissions on a securable to a principal(user or login)，也就是說，授予許可權的命令分為三部分：Permission，Securable 和 Principal，用一句話來解釋這三個概念：授予 Principal 操作 Securable 的 Permission。Principal是被授予許可權的主體，是被授與者（Grantee），是Login，User或Role。Securable是table，view，SP等對象，是Principal操作的對象；有時Principal也會作為Securable，被Principal操縱。在授予許可權的子句中，沒有主語，這是因為，只能授予已有的許可權，而最原始的主體是在創建SQL Server實例時指定的，該賬號擁有最高的許可權，通過該賬號把許可權授予相應的安全主體。許可權可以授予（GRANT）或禁止（DENY），也可以被回收（REVOKE），SQL Server通過命令GRANT，允許主體對安全對象做某些操作，通過命令DENY禁止主體對安全對象做某些操作，通過REVOKE命令收回對主體已授予（GRANT）或已禁止（DENY）的許可權。

在SQL Server的安全模型中，安全主體主要分為兩個級別：實例級別和資料庫級別，每一個級別都需要一個與之對應的安全主體：Login是SQL Server實例級別的安全主體，用於登陸實例時的驗證，驗證方式分為Windows驗證（通過Windows域賬戶驗證登陸許可權）和SQL Server驗證（通過賬號和密碼驗證登陸許可權），而User是資料庫級別的安全主體，用於訪問資料庫。Login和User可以通過SID（安全標識）關聯起來，如果Login沒有關聯User，那麼Login只能通過Public角色去訪問資料庫；如果User沒有關聯Login，那麼該User是孤立用戶，只能通過模擬許可權被使用。一般情況下，一個User只能關聯一個Login。

在SQL Server的安全模型中，sa和dbo 這兩個安全主體擁有絕對控制許可權，sa是login，屬於實例級別的主體，dbo是user，屬於資料庫級別的主體。每一個屬於sysadmin伺服器角色的Login都會映射到資料庫級別的dbo用戶，也就是說，實例級別的login不同，資料庫級別的user是相同的，都是dbo，但是，這些Login的SID都是相同的。

SQL Server 資料庫引擎管理一個層次結構的實體集合，實體就是安全對象（Securable），最重要的安全對象是Server和Database，最重要的安全主體（Principal）是Login和User，如下圖所示：

左側為安全主體：安全主體（Principal）分為三種：在Windows 級別上是Login，在SQL Server實例級別上是Login和實例級別的Role，在資料庫級別是 User和資料庫級別的Role。
右側為安全對象（Securable）：對於安全對象（Securable）而言，SQL Server 實例是Database的集合，而Database是User，Role，Schema等的集合，Schema是資料庫對象的集合，資料庫對象是指Table，View，SP，Function等，操作不同的對象，需要的許可權不同。

SQL Server的安全模型是有層次結構的，對安全對象的許可權存在繼承關係，對父安全對象上設置的許可權，會自動繼承到子安全對象上，例如，擁有架構（Schema）的CONTROL許可權，這意味著，對該架構下的所有的資料庫對象都用於CONTROL許可權。

通常情況下，許可權的管理都是針對資料庫對象（Table，View，SP，Schema等）的，資料庫對象的主要許可權如下列表所示：

ALTER：用於修改資料庫對象的定義，是DDL級別的許可權；授予用戶對特定Schema的ALTER許可權，這意味著，用戶可以alter, create, 或 drop 該Schema下的任何資料庫對象。
VIEW DEFINITION：用於查看資料庫對象的定義；
EXECUTE：用於執行SP，函數的許可權；
DELETE，INSERT，UPDATE和SELECT：用於對數據表或視圖執行增刪改查操作，是DML級別的許可權；
REFERENCES：授予引用的許可權，用於在當前表中引用其他數據表主鍵的許可權；
CONTROL：控制權，被授予許可權的用戶，實際上擁有操作對象的所有許可權。The grantee effectively has all defined permissions on the securable.

用戶可以通過表值函數：sys.fn_builtin_permissions('object') 查看SQL Server支持的對object可授予許可權的列表。

一，授予用戶訪問資料庫對象的許可權

在創建Login和User之後（可以參考《Security1：登錄和用戶》），把訪問Object的許可權授予資料庫用戶（User）或角色（Role），語法如下：

GRANT <permission> [ ,...n ] 
ON OBJECT :: schema_name.object_name[ ( column [ ,...n ] ) ] 
TO [Database_user | Database_role] [ ,...n ]

對於Table，View等資料庫對象，可以把許可權控制在column的粒度上，只允許用戶訪問特定的數據列；如果在GRANT子句中忽略column，那麼用戶可以訪問整個table或view的所有列。

把訪問Schema的許可權授予資料庫用戶或角色，語法如下：

GRANT permission  [ ,...n ] 
ON SCHEMA :: schema_name  
TO database_principal [ ,...n ]

1，授予對數據表的select許可權

GRANT SELECT 
ON OBJECT::Person.Address 
TO RosaQdM;

2，授予執行SP的許可權

GRANT EXECUTE 
ON OBJECT::hr.usp_UpdateEmployeeHireInfo  
TO Recruiting11;

3，授予引用數據列的許可權
以下腳本授予用戶Wanida引用資料庫對象 HumanResources.vEmployee的數據列 BusinessEntityID作為外鍵。

GRANT REFERENCES (BusinessEntityID) 
ON OBJECT::hr.view_Employee   
TO Wanida WITH GRANT OPTION;

二，通過創建Role授予許可權

授予許可權的方法，共有兩種，第一種方法是把許可權直接授予用戶，

可以授予對單個資料庫對象（單個數據表或視圖等）的許可權，
也可以授予用戶操作Schema的許可權，由於Schema是objects的容器，授予用戶操作Schema的許可權，就等同於授予操作Schema下所有objects的許可權；

第二種方法是把許可權授予資料庫角色Role，然後把角色Role的許可權授予用戶，那樣，用戶就擁有Role的所有許可權，以下代碼，通過創建Role，把許可權授予特定的用戶：

--create login
create login [domain\user] 
from windows
go

--create user
create user [domain\user]  
for login [domain\user] 
go

--create role
create role role_name
go

--create schema
create schema schema_name
go

--grant permission on schema to role
grant select,execute
on schema::schema_name
to role_name;

--grant permission on object to role
grant select ,insert
on object::schema_name.object_name
to role_name;

--add member
alter role role_name
add member [domain\user] ;
go

View Code

三，通過創建許可權模擬來授予許可權

在SQL Server的安全模型中，模擬（IMPERSONATE ）許可權的安全對象是User或Login，被授予者（Grantee ）有許可權模擬指定用戶，在其安全上下文執行特定的操作。

例如，user1授予模擬user2的許可權，當user2的安全上下文有足夠的許可權，而user1沒有時，通過許可權模擬，user1能夠在user2的許可權上下文中執行查詢請求：

GRANT IMPERSONATE 
ON USER:: user2 
TO user1;

通過執行EXECUTE AS 命令模擬用戶的許可權，用戶user1就運行在user2的安全上下文中，例如，user1在登陸資料庫之後，模擬user2的許可權：

EXECUTE AS USER = 'user2';

通過執行 REVERT 命令退出許可權模擬，返回到用戶原始的安全上下文中：

REVERT;

一般情況下，對於特殊的管理任務，需要創建孤立用戶，給孤立用戶授予特定的許可權，並把模擬孤立用戶的許可權授予其他用戶，這些人就有許可權執行特定的管理任務。

參考文檔：

Permissions (Database Engine)

GRANT Object Permissions (Transact-SQL)

Basic SQL Server Security concepts - logins, users, and principals

Basic SQL Server Security concepts - permissions and special principals: sa, dbo, guest