SQL Server Execute As (Switch Context)

2022-02-22

筆記 SQL Server 如何切換使用者身分，分別說明 Execute As 以及 Execute As Clause 的使用方式，藉由切換身分不僅可以解決授權後的成功與否的測試，更可以達到用 Stored Procedure 來限縮授權的安全性，是非常重要的功能。

Execute AS

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

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

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

可以使用下列方式切換 TSQL 執行的身分：

EXECUTE AS User = 'dbusername'

EXECUTE AS Login = 'loginname'

需要注意的是，AS User 是切換使用者、AS Login 則是切換登入。如果要跨資料庫(同資料庫伺服器)，必須要使用 AS Login 否則會發生安全性錯誤 🙂

Revert

使用 REVERT 可以撤銷目前的身分切換。

EXECUTE AS Login = 'loginname'
REVERT

user_name()

SELECT user_name();

可以使用 user_name 確認當下 context 的使用者。

Execute AS STACK

EXECUTE AS User = 'User1'
EXECUTE AS User = 'User2'
EXECUTE AS User = 'User3'
REVERT
REVERT
REVERT

Execute AS 是可以 STACK (FILO) 疊加的，如果 IMPERSONATE 權限具備可以不斷疊加，要撤銷也需要連續撤銷。

Etc

其他的 Execute 語法寫法如下：

EXEC('SELECT User,* FROM T1') AS USER = 'U1'

另 SETUSER 也可以用於切換執行身分，但屬於 Deprecated 的指令，不建議在使用。

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

Execute Arguments

WITH EXECUTE AS SELF
AS
  ...

WITH EXECUTE AS CALLER
AS
  ...

WITH EXECUTE AS userName
AS
  ...