Sql-Server-2012
在儲存過程中選擇 sys 表的權限
我已經實現了儲存過程來檢查是否存在特定的
[schema].[procedure]
。CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName varchar(128), @procedureName varchar(128) AS SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id] WHERE s.[name] = @schemaName AND p.[name] = @procedureName), 1, 0) GO
當我使用
sa
它進行測試時,我按預期選擇了 1,但是當我嘗試使用僅授予EXECUTE
該程序的最小權限帳戶時,它選擇了 0。我對儲存過程機制的假設是角色/使用者只需要
EXECUTE
執行該過程。我們不需要關心每個數據庫對象的權限設置,比如 CRUD 到 table 等等。但似乎 sys 表是例外?還是我錯過了什麼?
此外,如果我不想將 select
sys.procedures
/授予sys.schemas
該角色。我只希望我的程序按預期工作,我該怎麼辦?
我對儲存過程機制的假設是角色/使用者只需要 EXECUTE 來執行該過程。我們不需要關心每個數據庫對象的權限設置,比如 CRUD 到 table 等等。
當所有權連結適用時,此陳述是正確的。使用完整的鏈,使用者不需要間接引用對象的權限。
但是,在從目錄視圖中進行選擇時,所有權連結不會應用元數據可見性。必須授予使用者對對象的某些權限,以便在從目錄視圖中選擇對象時可見。
下面是一個為您的案例顯示此行為的腳本。如果您需要返回使用者無權使用的對象,則需要使用
EXECUTE AS
證書籤名或證書來提升BlobCheckExisting.usp_DoesStoredProcedureExist
過程使用者的權限。CREATE SCHEMA BlobCheckExisting; GO CREATE PROCEDURE [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName varchar(128), @procedureName varchar(128) AS SELECT IIF (EXISTS (SELECT 1 FROM sys.procedures p INNER JOIN sys.schemas s ON p.[schema_id] = s.[schema_id] WHERE s.[name] = @schemaName AND p.[name] = @procedureName), 1, 0) GO CREATE PROCEDURE dbo.usp_TestProc AS RETURN; GO CREATE LOGIN MinimallyPrivilegedUser WITH PASSWORD = 'sdf4$$$gFx&'; GO CREATE USER MinimallyPrivilegedUser; GO GRANT EXECUTE ON BlobCheckExisting.usp_DoesStoredProcedureExist TO MinimallyPrivilegedUser; GO --test permissions without permissions on object EXECUTE AS USER = 'MinimallyPrivilegedUser'; GO --no rows returned because user has no permissions on dbo.usp_TestProc EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc'; GO REVERT; GO --grant permissions GRANT EXECUTE ON dbo.usp_TestProc TO MinimallyPrivilegedUser; GO --test permissions with permissions on object EXECUTE AS USER = 'MinimallyPrivilegedUser'; GO --row returned because user has execute permissions on dbo.usp_TestProc EXEC [BlobCheckExisting].[usp_DoesStoredProcedureExist] @schemaName = N'dbo', @procedureName = N'usp_TestProc'; GO REVERT; GO