Sql-Server

創建幫助程序 UDF 或儲存過程並限制外部訪問

  • July 23, 2016

我正在尋找將函式和儲存過程劃分為“公共 API”和“私有實現細節”的推薦最佳實踐。

我正在開發一個安全系統，我希望客戶端應用程序只能訪問少數頂級功能。例如：

  • CanUserEditObject(@personId, @objectId)– 呼叫以查看是否可以編輯
  • EditableBy(@personId)– 返回可編輯對象 ID 的列表

這些構成了我希望客戶端應用程序使用的 API。

為了實現這一點，我需要一些我不希望客戶端應用程序呼叫的其他功能；

  • GetLocationsAdministeredBy(@personId)
  • IsUserSysadmin(@personId)

客戶端應用程序不需要呼叫它們，我也不保證會支持它們——它們只是一個實現細節。所以我想隱藏它們。理想情況下，客戶端應用程序呼叫它的任何嘗試都將失敗。理想情況下，我可以在 SQL Server Management Studio 中呼叫它們來開發它們。

基本上，這是受到資訊隱藏的 OO 原則和C++/Java/C#/etc 中的訪問修飾符public等語言結構的啟發。private

我不需要它是一種有保證的、硬安全的解決方案。我在這里基本上信任我的客戶，但我只是不希望一些勇敢的開發人員發現我編寫的函式並使用它，然後在我以後重寫它時抱怨。

這主要適用於 SQL Server 2014，但如果有任何“標準”SQL 技術，我很感興趣。

此外，我們有一個團隊，我們中的一些人比其他人更擅長 SQL。SQL 人員會添加他們自己的對象，但他們不會使用我的“私有”函式。其他開發人員可能會發現現有功能而不是添加它們。我正在尋找一種區分方法，這樣就不會有人意外依賴不受支持的功能。像命名約定這樣簡單的事情就可以了，但我很感興趣其他人如何應付大多數非 SQL 語言都很好支持的問題。

在非常基本的級別（這僅適用於 SQL Server，因為每個供應商處理安全性略有不同），如果其他人沒有創建自己的數據庫對象（儲存過程、函式等）並且僅依賴於您的 API，則處理此問題本質上是通過“所有權連結”。所有權連結允許對模組內引用的對象的隱含權限，只要引用的對象與正在執行的模組的“所有者”具有相同的“所有者”，並且這些引用的對像沒有在動態 SQL 中被引用。

所有權鏈

以下範例說明了此行為。Test登錄名無法直接訪問該功能，因為尚未EXECUTE授予其權限。但是，當它在Test登錄/使用者已被授予EXECUTE權限的儲存過程中使用時，它會起作用，因為兩者都在同一個模式中（即dbo）。但是，動態 SQL 破壞了所有權鏈，這就是為什麼第二個儲存過程PublicDoSomethingDS會出錯。

USE [master];
CREATE LOGIN [Test] WITH PASSWORD = 'test';
GO

USE [tempdb];
CREATE USER [Test] FROM LOGIN [Test];
CREATE TABLE dbo.PrivateTest (Col1 INT);
INSERT INTO dbo.PrivateTest (Col1) VALUES (1), (4), (999);
GO

CREATE FUNCTION dbo.PrivateGetRowCount (@Multiplier INT)
RETURNS INT
AS
BEGIN
 DECLARE @Return INT = 0;

 SELECT @Return = COUNT(*)
 FROM   dbo.PrivateTest;

 RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomething
(
 @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT dbo.PrivateGetRowCount(@SomeInput);
GO

CREATE PROCEDURE dbo.PublicDoSomethingDS
(
 @SomeInput INT
)
AS
SET NOCOUNT ON;

EXEC sys.sp_executesql
N'SELECT dbo.PrivateGetRowCount(@tmpSomeInput);',
N'@tmpSomeInput INT',
@tmpSomeInput = @SomeInput;
GO

GRANT EXECUTE ON dbo.PublicDoSomething TO [Test];
GRANT EXECUTE ON dbo.PublicDoSomethingDS TO [Test];
GO

SELECT SESSION_USER; -- dbo
SELECT dbo.PrivateGetRowCount(1); -- 1

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- 12
EXEC('EXEC dbo.PublicDoSomethingDS 5;'); -- 15

EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT dbo.PrivateGetRowCount(1); -- error:
-- Msg 229, Level 14, State 5, Line 60
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

EXEC dbo.PublicDoSomething 2; -- 6
EXEC('EXEC dbo.PublicDoSomething 3;'); -- 9

EXEC dbo.PublicDoSomethingDS 4; -- error:
-- Msg 229, Level 14, State 5, Line 71
-- The EXECUTE permission was denied on the object 'PrivateGetRowCount',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

但是，如果其他人可以創建他們自己的對象——儲存過程、函式、視圖等——那麼所有權連結不會讓你走到任何地方，因為在架構中創建自己的模組的dbo人就可以訪問那些“私有”模組。

在這種情況下，您應該能夠通過使用簽名和模組簽名來完成這種分離。

模組簽名

模組簽名使用非對稱密鑰或證書來創建登錄名和/或使用者，將所需的權限分配給該基於非對稱密鑰或基於證書的登錄名和/或使用者，然後“簽署”模組應使用ADD SIGNATURE命令（在 SQL Server 2008 中引入）授予這些權限。

這種方法稍微複雜一些，並且需要更多時間來設置，但允許對權限進行非常精細的控制。

假設您已經完全執行了上面顯示的範常式式碼，現在執行以下命令：

USE [tempdb];
GO

CREATE SCHEMA [Private] AUTHORIZATION [dbo];
GO

CREATE FUNCTION [Private].[GetRowCount](@Multiplier INT)
RETURNS INT
AS
BEGIN
 DECLARE @Return INT = 0;

 SELECT @Return = COUNT(*)
 FROM   dbo.PrivateTest;

 RETURN (@Return * @Multiplier);
END;
GO

CREATE PROCEDURE dbo.PublicDoSomethingPrvt
(
 @SomeInput INT
)
AS
SET NOCOUNT ON;

SELECT [Private].[GetRowCount](@SomeInput);
GO

GRANT EXECUTE ON dbo.PublicDoSomethingPrvt TO [Test];

SELECT SESSION_USER; -- dbo
SELECT [Private].[GetRowCount](1); -- 3

EXEC dbo.PublicDoSomethingPrvt 2; -- 6



EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
SELECT [Private].[GetRowCount](1); -- error:
-- Msg 229, Level 14, State 5, Line 41
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

EXEC dbo.PublicDoSomethingPrvt 2; -- 6

REVERT;
SELECT SESSION_USER; -- dbo

我們創建了一個新的 Schema 並將新的 Function 放入其中，但TestUser 仍然能夠執行PublicDoSomethingPrvt. 為什麼？因為 Schema 的“所有者”Private是 ，與 Schemadbo的“所有者”相同dbo。因此，所有權鏈仍然允許它工作。

但是我們最終可以通過執行以下命令來打破它：

CREATE USER [MrX] WITHOUT LOGIN;

ALTER AUTHORIZATION ON SCHEMA::[Private] TO [MrX];

EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- still works for dbo


EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 71
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.


REVERT;
SELECT SESSION_USER; -- dbo

現在沒有更多的所有權鏈。事實上，即使您EXECUTE將[Private].[GetRowCount]函式授予Test使用者，他們仍然會收到錯誤，因為函式和它從中選擇的表之間沒有更多的自動連結。

那麼現在怎麼辦？好吧，現在我們創建非對稱密鑰，然後從該密鑰創建使用者，然後授予該使用者所需的權限，然後使用相同的非對稱密鑰簽署儲存過程：

CREATE ASYMMETRIC KEY [PrvtKey] WITH ALGORITHM = RSA_4096 ENCRYPTION BY PASSWORD = 'silly';
-- RSA_2048 for SQL Server 2012 and older

CREATE USER [PrvtUser] FROM ASYMMETRIC KEY [PrvtKey];

GRANT EXECUTE ON [Private].[GetRowCount] TO [PrvtUser];

ADD SIGNATURE TO [dbo].[PublicDoSomethingPrvt]
 BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';


EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 2; -- 6
-- Msg 229, Level 14, State 5, Procedure PublicDoSomethingPrvt, Line 94
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

REVERT;
SELECT SESSION_USER; -- dbo

差不多了。PublicDoSomethingPrvt儲存過程（不是使用者！）現在可以執行該Test函式，但該函式仍然沒有與表的內在連結，該表位於另一個具有不同所有者的模式中。現在我們只需將最終權限添加到基於非對稱密鑰的使用者，然後簽署函式：

GRANT SELECT ON [dbo].[PrivateTest] TO [PrvtUser];

ADD SIGNATURE TO [Private].[GetRowCount]
 BY ASYMMETRIC KEY [PrvtKey] WITH PASSWORD = 'silly';



EXECUTE AS LOGIN = 'Test';
SELECT SESSION_USER; -- Test
EXEC dbo.PublicDoSomethingPrvt 12; -- 36
-- works now!!!!!!!!!!!!!!!!!!  :-)


SELECT * FROM [dbo].[PrivateTest];
-- Msg 229, Level 14, State 5, Line 110
-- The SELECT permission was denied on the object 'PrivateTest',
--     database 'tempdb', schema 'dbo'.

SELECT [Private].[GetRowCount](12);
-- Msg 229, Level 14, State 5, Line 115
-- The EXECUTE permission was denied on the object 'GetRowCount',
--     database 'tempdb', schema 'Private'.

REVERT;
SELECT SESSION_USER; -- dbo

如您所見，Test使用者只有明確授予它的權限，即EXECUTE在PublicDoSomethingPrvt儲存過程上。

筆記：

  1. 您可以通過成為唯一擁有該“密碼”的人來控制這一點（在創建非對稱密鑰時確定，並在執行時使用ADD SIGNATURE）。
  2. EXECUTE AS出於安全原因，如果定義發生任何變化，即使是語句的子句，也會從模組中刪除簽名CREATE。這樣可以確保某人不會將您認為可以接受的情況更改為您不可以接受的情況。因此，當定義更改時，您需要重新執行該ADD SIGNATURE命令。
  3. 您不需要多個使用者（即MrX和PrvtUser），這只是為了展示程式碼。您可以使用基於非對稱密鑰的使用者來擁有“私有”模式。
  4. 您無需為每個模組的基於非對稱密鑰的使用者分配權限。相反，您可以授予 Schema 範圍的權限來簡化事情（例如EXECUTE，在PrivateSchema 上、SELECT在dboSchema 上等）。
  5. 您可以通過創建一個非對稱密鑰和關聯的使用者來進一步劃分權限，以允許“公共”模組執行Private架構中的內容。然後，創建另一個非對稱密鑰和關聯使用者，以允許“私有”模組訪問“公共”模式中的對象。

引用自：https://dba.stackexchange.com/questions/144645

相關問答

Sql-Server

SQL Server 允許儲存過程連結

  • March 1, 2022
檢視問答
Sql-Server

SQL Server - 通過證書向儲存過程添加簽名不起作用

  • January 28, 2022
檢視問答
Sql-Server

在函式中使用連結伺服器時選擇權限被拒絕，除非內聯關閉

  • February 28, 2021
檢視問答
Sql-Server-2008

授予對儲存過程、函式和視圖的執行權限

  • May 8, 2020
檢視問答
Sql-Server

為過程引用的所有表生成 GRANT

  • April 23, 2020
檢視問答
Sql-Server

SQL 語句層過濾查詢限制訪問

  • February 10, 2020
檢視問答