SQL Server 委託預存程序執行 TSQL (Stored Procedures With Impersonation)

2022-01-14

說明 SQL Server 如何設計 Stored Procedures，讓僅具有 Execute Stored Procedures 權限的 User，可以藉由 Stored Procedures 來使用 User 本身未具權限的物件。

說明

如何透過 Stored Procedures 執行功能？

結論：一定要具有執行 SP 的權限，並且有對物件的使用權限。如果沒有使用權限可以透過 Execute As 或 Execute As Clause 的方式取得權限；如果要使用的物件是 Table 另可以透過 Ownership Chaining 的方式取得權限。

圖形的流程非完整邏輯，只是下列輔助文字敘述的視覺化呈現 :)

首先判斷是否為 EXECUTE AS，如果具有 IMPERSONATE ，則以該使用者身分進行後續的所有動作。

接著檢查 User 具備 Execute 該 Stored Procedures 的權限。

GRANT EXECUTE TO [userName]

再來就是進入到 Stored Procedures，如果有使用 Execute As Clause 接著在 Stored Procedures 就以該 AS 身分進行後續所有動作。

若在 Stored Procedures 中有使用到 EXECUTE AS 則檢查是否具備 IMPERSONATE 權限，並以 IMPERSONATE 身分繼續後續動作，直到 Revert 權限。

在 Stored Procedures 中使用到的 Object 以及 Command，必須要檢查目前的身分 (AS 或 IMPERSONATE 身分，非實際身分)是否具有權限，

例如 Truncate Table 需要 Alter 權限、Table 的 CRUD 則有對應的 SELECT, DELETE, UPDATE 以及 INSERT 等權限。

但例外的情況就是利用 Ownership Chaining，如果 Stored Procedures 的 Owner 與物件的 Owner 相同，就不受此侷限。可以藉由 Stored Procedures 與 Table 的 Getter、Setter 來確定是否有相同的 Owner。

執行身分

Stored Proecures 透過 Impersonate 的方式切換執行者身分，以其他具有權限的角色執行。

詳細可以參考 ⭐SQL Server Execute As (Switch Context)

Execute AS

Execute AS 使用者本身必需具備 Impersonate 特定使用者的權限，並使用該使用者的權限去進行。

CREATE OR ALTER PROC usp_B
	AS
	EXECUTE AS USER = 'U1'
	TRUNCATE TABLE dbo.T1
	REVERT
GO

舉例來說，使用 Execute AS 就像是同一個普通人，可以向外宣稱他就是特權者的代理人，所有特權者任何可以做的事情，代理人都可以去做，包括去渡假村的所有活動。

Execute AS Clause

相較於 Execute 最大的差別就是是在 Modlues (如 StoredProcedures 中設定)，使用者僅需要 Execute Stored Procedures 的權限，而不需要 Impersonate 的權限。

使用 Execute As Clause，在 Stored Procedures Modules 中改變執行身分，而能夠創造 Stored Procedures 並設定 Execute As Clause 的只有 Database Owner，因此可以保證授權的安全性。

CREATE OR ALTER PROC usp_A
	WITH EXECUTE AS 'U1' 
	AS
	TRUNCATE TABLE dbo.T1
GO

舉例來說，使用 ExecuteA AS CLAUSE 則是特權者安排好一個特定的權限範圍，例如允許普通人可以去渡假村消費雞尾酒與聖代，但也僅限於此，其他服務一律不准使用。與EXECUTE AS 相比，使用 EXECUTE AS CLAUSE 搭配代理的 User Without Login 以及 Stored Procedures 可以達到最小權限的設計 😉

Execute As Clause

權限

Getter / Setter

Stored Procedures

Getter
- sys.objects.principal_id
- (sp_stored_procedures 的 owner 固定為 schema，不具參考價值)
Setter
- alter authorization

Table

Getter
- sys.objects.principal_id
- sp_table_privileges (Grantee)
Setter
- alter authorization

sp_table_privileges

EXEC sp_table_privileges 'TableName'

上述 TSQL 可以用於 Table 的 SELECT, UPDATE, INSERT, DELETE, REFERENCE 的 GRANTEE 是否給予非 dbo，如果是的話，可能會造成 Ownership Chaining Broken。

但也僅限於此，無法顯示使用者在該資料表上的所有權限，例如 View Table Definition 就不會在此顯示。

sys.database_principals

SELECT 
  p.name PrincipalName,
  p.principal_id,
  OBJECT_NAME(d.major_id) ObjectName,
  d.class_desc,
  d.grantor_principal_id,
  d.grantee_principal_id,
  d.permission_name,
  d.state_desc
FROM sys.database_principals AS p
JOIN sys.database_permissions AS d 
  ON d.grantee_principal_id = p.principal_id
WHERE d.major_id >= 0
ORDER BY p.name