防止儲存過程寫入數據庫

我是一名數據庫開發人員，主要編寫 SSRS 報告。我想創建儲存過程並向 DBA 保證這些儲存過程無法寫入生產數據庫，而 DBA 不必仔細檢查儲存過程。

我在 DatabaseA 中創建儲存過程。我需要儲存過程來選擇 DatabaseB 中的數據。

我希望任何使用者都能夠安全地執行 DatabaseA 中的儲存過程，但不能使用該儲存過程在 DatabaseB 中插入/更新/刪除數據，而不管使用者可能擁有的任何其他權限。

DBA 可以在數據庫或過程級別設置權限來強制執行此操作嗎？

這適用於 SQL Sever 2016。

DBA 可以在數據庫或過程級別設置權限來強制執行此操作嗎？

是的，您的 DBA 可以使用證書籤署報告儲存過程，而不是拒絕對 DatabaseB 表的寫入權限。只要所有權連結不適用（@AMtwo 詳細說明的跨數據庫查詢的預設行為）並且呼叫者不是特權使用者（sysadmin 角色成員或 DatabaseB 的所有者），DENY則優先於呼叫者權限。GRANT

如果允許跨數據庫連結（DB_CHAINING數據庫選項或cross db ownership chaining伺服器選項），則需要關閉這些連結（或更改 proc 所有者）以便評估權限。

下面是DENY權限的證書方法的完整註釋範例腳本。創建證書後，您的 DBA 可以將簽名添加到新的或更改的報告過程中。

CREATE DATABASE DatabaseA;
CREATE DATABASE DatabaseB;
GO

--create demo user to test permissions
CREATE LOGIN DemoUser WITH PASSWORD = 'S*39NOO7756sfv*&6';
GO

USE DatabaseB;
GO
CREATE USER DemoUser;
CREATE TABLE dbo.Table1(Col1 int);
--grant permissions to demo user
GRANT SELECT, INSERT, UPDATE, DELETE ON dbo.Table1 TO DemoUser;
GO
--create cert and user from cert
CREATE CERTIFICATE DenyWriteCertificate
  ENCRYPTION BY PASSWORD = 'S%^aap34577Hbw'
  WITH SUBJECT = 'Deny INSERT UPDATE DELETE';
CREATE USER DenyWriteCertificateUser FROM CERTIFICATE DenyWriteCertificate;
--deny writes to cert user
ALTER ROLE db_denydatawriter ADD MEMBER DenyWriteCertificateUser;
--copy cert to DatabaseA
DECLARE @cert_id int = cert_id('DenyWriteCertificate')
DECLARE @public_key  varbinary(MAX) = certencoded(@cert_id),
       @private_key varbinary(MAX) =
          certprivatekey(@cert_id,
             'S%^aap34577Hbw',
             'S%^aap34577Hbw')
DECLARE @sql nvarchar(MAX) =
     'CREATE CERTIFICATE DenyWriteCertificate
      FROM  BINARY = ' + convert(varchar(MAX), @public_key, 1) + '
      WITH PRIVATE KEY (BINARY = ' +
         convert(varchar(MAX), @private_key, 1) + ',
         DECRYPTION BY PASSWORD = ''S%^aap34577Hbw'',
         ENCRYPTION BY PASSWORD = ''S%^aap34577Hbw'')'
EXEC DatabaseA.sys.sp_executesql @sql;
GO

USE DatabaseA;
CREATE USER DemoUser;
GO

CREATE PROC dbo.ReadProcedure
AS
SELECT Col1 FROM DatabaseB.dbo.Table1;
GO
GRANT EXECUTE ON dbo.ReadProcedure TO DemoUser;
GO

CREATE PROC dbo.WriteProcedure
AS
INSERT INTO DatabaseB.dbo.Table1(Col1) VALUES(1);
GO
GRANT EXECUTE ON dbo.WriteProcedure TO DemoUser;
GO

--these both succeed because caller has read and write permissions
EXECUTE AS LOGIN = 'DemoUser';
GO
EXECUTE dbo.ReadProcedure;
GO
EXECUTE dbo.WriteProcedure;
GO
REVERT;
GO

--add deny cert to report procs
ADD SIGNATURE TO dbo.ReadProcedure BY CERTIFICATE DenyWriteCertificate WITH PASSWORD = 'S%^aap34577Hbw';
ADD SIGNATURE TO dbo.WriteProcedure BY CERTIFICATE DenyWriteCertificate WITH PASSWORD = 'S%^aap34577Hbw';
GO

--read proc succeeds, write proc fails
EXECUTE AS LOGIN = 'DemoUser';
GO
EXECUTE dbo.ReadProcedure;
GO
EXECUTE dbo.WriteProcedure;
GO
REVERT;
GO

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