Sql-Server

使用跨數據庫證書時觸發器中的權限

  • March 6, 2017

我使用跨數據庫證書（如 Erland Sommarskog 所解釋的）來控制對我的環境（SQL Server 2008 R2）中某個數據庫的訪問。

我在數據庫 A 中儲存了更新數據庫 B 中表的儲存過程。到目前為止，這一直適用於 db A 中的各種儲存過程和 db B 中的表。我正在嘗試更新 db B 中的表，但該表上有一個觸發器。此觸發器正在 db B 的另一個表中插入其他數據。我收到錯誤消息：

消息 916，級別 14，狀態 1，過程 table_trigger，第 11 行 伺服器主體“sql\login”無法在目前安全上下文下訪問數據庫“B”。

我嘗試為與證書綁定的數據庫 B 使用者授予插入權限以插入到另一個表中，但它沒有解決錯誤。除了更改觸發器以使其使用之外，我還有其他選擇WITH EXECUTE AS OWNER嗎？

這是複制問題的 DDL：

CREATE LOGIN [GuggTest] WITH PASSWORD=N'abcd', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

CREATE DATABASE A;
CREATE DATABASE B;

USE A;

CREATE TABLE dbo.SPtoUpdate
   (
     ID INT
   , ILoveFishing VARCHAR(255)
   );
INSERT INTO dbo.SPtoUpdate
       ( ID , ILoveFishing )
VALUES  ( 1,'Musky'),( 2,'Pike'),( 3,'Yellow Perch');
CREATE TABLE dbo.TriggerToInsert
   (
     ID INT
   , ILoveFishing VARCHAR(255)
   , ChangeDate DATETIME2
   );
GO

CREATE TRIGGER dbo.SPtoUpdateTrigger ON dbo.SPtoUpdate
   FOR UPDATE
AS
   DECLARE @datetime DATETIME2;
   SELECT  @datetime = GETDATE()

   INSERT  INTO dbo.TriggerToInsert
           ( ID , ILoveFishing , ChangeDate )
   VALUES  ( 1 , 'Yes' , @datetime );
GO

CREATE CERTIFICATE BExecutor
  ENCRYPTION BY PASSWORD = 'Obfuscated'
  WITH SUBJECT = 'Execute sp from B to A',
  START_DATE = '20140101', EXPIRY_DATE = '20300101'
GO

BACKUP CERTIFICATE BExecutor TO FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                 ENCRYPTION BY PASSWORD = 'Obfuscated',
                 DECRYPTION BY PASSWORD = 'Obfuscated')
GO

CREATE USER BExecutor FROM CERTIFICATE BExecutor

GRANT UPDATE ON dbo.SPtoUpdate TO BExecutor
GRANT SELECT ON dbo.SPtoUpdate TO BExecutor
--Also give insert on dbo.TriggerToInsert
GRANT INSERT ON dbo.TriggerToInsert TO BExecutor

USE B
GO

CREATE USER [GuggTest] FOR LOGIN [GuggTest];
EXEC sp_addrolemember N'db_owner', N'GuggTest'
GO

CREATE PROCEDURE dbo.UpdateTableInA
AS
   BEGIN
       UPDATE  A.dbo.SPtoUpdate
       SET     ILoveFishing = 'Walleye'
       WHERE   ID = 2;
   END

GO


CREATE CERTIFICATE BExecutor FROM FILE = 'C:\temp\crossdbcert.cer'
WITH PRIVATE KEY (FILE = 'C:\temp\crossdbcert.pvk' ,
                 ENCRYPTION BY PASSWORD = 'Obfuscated',
                 DECRYPTION BY PASSWORD = 'Obfuscated')
GO

EXEC master..xp_cmdshell 'DEL C:\temp\crossdbcert.*', 'no_output'
GO

ADD SIGNATURE TO dbo.UpdateTableInA BY CERTIFICATE BExecutor
   WITH PASSWORD = 'Obfuscated'
GO

--Log In or Change execution context to GuggTest, then EXEC dbo.UpdateTableInA

這裡的問題是，雖然證書將 DatabaseA 中的儲存過程與 DatabaseB 中INSERT對這兩個表具有權限的使用者連結起來，但從儲存過程直接插入的表上的觸發器是鏈中的另一個模組，並且獲得了權限from 證書不會傳遞給鏈中的其他模組。意思是，證書允許儲存過程通過使用者插入到表中，甚至執行觸發器。但是，沒有授予觸發器執行與對象相關的任何操作的權限（執行類似的操作SELECT 1;）。

在這種情況下，需要通過相同的證書向觸發器授予權限，以便它可以採取任何必要的操作。這至少可以通過對觸發器進行會籤來完成。你可以通過執行ADD COUNTER SIGNATURE TO [TriggerSchema].[TriggerName] BY CERTIFICATE ...;. 之後，它應該可以正常工作，即使INSERT觸發器插入的表上的基於證書的使用者沒有直接許可。

下面的範常式式碼重現了該問題，通過添加計數器簽名解決了該問題，但不授予INSERT觸發器填充表的權限。

清理

USE [master];
GO
IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseA')
BEGIN
   PRINT 'Dropping [DatabaseA] DB...';
   ALTER DATABASE [DatabaseA] SET OFFLINE WITH ROLLBACK IMMEDIATE;
   ALTER DATABASE [DatabaseA] SET ONLINE;
   DROP DATABASE [DatabaseA];
END;

IF EXISTS (SELECT 1 FROM [sys].[databases] WHERE [name] = N'DatabaseB')
BEGIN
   PRINT 'Dropping [DatabaseB] DB...';
   ALTER DATABASE [DatabaseB] SET OFFLINE WITH ROLLBACK IMMEDIATE;
   ALTER DATABASE [DatabaseB] SET ONLINE;
   DROP DATABASE [DatabaseB];
END;

IF (SUSER_ID(N'JohnnyLunchbucket') IS NOT NULL)
BEGIN
 PRINT 'Dropping [JohnnyLunchbucket] Login...';
 DROP LOGIN [JohnnyLunchbucket];
END;

IF (OBJECT_ID(N'tempdb..#CertInfo') IS NOT NULL)
BEGIN
 PRINT 'Dropping [#CertInfo] Temp Table...';
 DROP TABLE #CertInfo;
END;

設置

USE [master];

EXECUTE AS LOGIN = N'sa';
PRINT 'Creating databases...';
CREATE DATABASE [DatabaseA] COLLATE Latin1_General_100_CI_AS_SC;
CREATE DATABASE [DatabaseB] COLLATE Latin1_General_100_CI_AS_SC;
REVERT;
GO

-- Default for both options should be OFF, but just to be sure:
ALTER DATABASE [DatabaseA] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseA] SET TRUSTWORTHY OFF;

ALTER DATABASE [DatabaseB] SET DB_CHAINING OFF;
ALTER DATABASE [DatabaseB] SET TRUSTWORTHY OFF;
GO

CREATE LOGIN [JohnnyLunchbucket] WITH PASSWORD = 'OhSoSecure;)';


USE [DatabaseA];

CREATE USER [JohnnyLunchbucket] FOR LOGIN [JohnnyLunchbucket];
GO

--DROP PROCEDURE dbo.InsertIntoTableWithoutTrigger;
CREATE PROCEDURE dbo.InsertIntoTableWithoutTrigger
(
   @SomeValue NVARCHAR(50)
)
AS
SET NOCOUNT ON;

INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] (SomeValue)
VALUES (@SomeValue);
GO

GRANT EXECUTE ON dbo.InsertIntoTableWithoutTrigger TO [JohnnyLunchbucket];
GO

CREATE PROCEDURE dbo.InsertIntoTableWithTrigger
AS
SET NOCOUNT ON;

INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] (SomeOtherValue)
VALUES (NEWID());
GO

GRANT EXECUTE ON dbo.InsertIntoTableWithTrigger TO [JohnnyLunchbucket];



CREATE CERTIFICATE [PermissionsCert]
 AUTHORIZATION [dbo]
 ENCRYPTION BY PASSWORD = 'WeakPassword'
 WITH SUBJECT = 'Used to test granting permissions to code',
 EXPIRY_DATE = '2099-12-31';

ADD SIGNATURE TO [dbo].[InsertIntoTableWithoutTrigger]
   BY CERTIFICATE [PermissionsCert]
   WITH PASSWORD = 'WeakPassword';

ADD SIGNATURE TO [dbo].[InsertIntoTableWithTrigger]
   BY CERTIFICATE [PermissionsCert]
   WITH PASSWORD = 'WeakPassword';

-- Save Certificate info in temporary table so we can recreate in DatabaseB
SELECT CERTENCODED(CERT_ID(N'PermissionsCert')) AS [PublicKey],
      CERTPRIVATEKEY(CERT_ID(N'PermissionsCert'), 'OtherPassword', 'WeakPassword')
             AS [PrivateKey]
INTO   #CertInfo;
GO

USE [DatabaseB];

DECLARE @SQL NVARCHAR(MAX);

SELECT @SQL = N'CREATE CERTIFICATE [PermissionsCert] AUTHORIZATION [dbo] FROM BINARY = '
              + CONVERT(NVARCHAR(MAX), [PublicKey], 1)
              + N' WITH PRIVATE KEY (BINARY = '
              + CONVERT(NVARCHAR(MAX), [PrivateKey], 1)
              + N', DECRYPTION BY PASSWORD = N''OtherPassword'''
              + N', ENCRYPTION BY PASSWORD = ''WeakPassword'');'
FROM   #CertInfo;

PRINT @SQL;
EXEC (@SQL);

CREATE USER [PermissionsUser] FROM CERTIFICATE [PermissionsCert];

--DROP TABLE dbo.[TableWithoutTrigger];
CREATE TABLE dbo.[TableWithoutTrigger]
(
 [TableWithoutTriggerID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_TableWithoutTrigger] PRIMARY KEY,
 [SomeValue] NVARCHAR(50)
);

GRANT INSERT ON [dbo].[TableWithoutTrigger] TO [PermissionsUser];


CREATE TABLE dbo.[TableWithTrigger]
(
 [TableWithTriggerID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_TableWithTrigger] PRIMARY KEY,
 [SomeOtherValue] NVARCHAR(50)
);

GRANT INSERT ON [dbo].[TableWithTrigger] TO [PermissionsUser];


CREATE TABLE dbo.[TablePopulatedByTrigger]
(
 [TablePopulatedByTriggerID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_TablePopulatedByTrigger] PRIMARY KEY,
 [DuplicatedValue] NVARCHAR(50)
);
GO

CREATE TRIGGER dbo.CopySomeOtherValue
ON dbo.[TableWithTrigger]
AFTER INSERT
AS
BEGIN
   SET NOCOUNT ON;

   INSERT INTO dbo.[TablePopulatedByTrigger] ([DuplicatedValue])
       SELECT ins.[SomeOtherValue]
       FROM   inserted ins;
END;
GO

測試 1：觸發器失敗

USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
INSERT INTO [DatabaseB].[dbo].[TableWithoutTrigger] ([SomeValue]) VALUES (N'test 0');
USE [DatabaseB];
/* -- All 5 statements above get the following error:
Msg 916, Level 14, State 1, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
   "DatabaseB" under the current security context.
*/


EXEC [dbo].[InsertIntoTableWithoutTrigger] @SomeValue = N'test A'; -- SUCCESS!!!

EXEC [dbo].[InsertIntoTableWithTrigger]; -- ERROR:
/*
Msg 916, Level 14, State 1, Procedure CopySomeOtherValue, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
   "DatabaseB" under the current security context.
*/

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

-- Check to make sure that dbo.InsertIntoTableWithoutTrigger really did work:
SELECT * FROM [DatabaseB].[dbo].[TableWithoutTrigger];
-- 1    test A

測試 2：觸發成功

請注意，所做的唯一更改是ADD COUNTER SIGNATURE；沒有GRANT INSERT ON dbo.TablePopulatedByTrigger TO [PermissionsUser];。

USE [DatabaseB];

ADD COUNTER SIGNATURE
   TO dbo.[CopySomeOtherValue]
   BY CERTIFICATE [PermissionsCert]
   WITH PASSWORD = 'WeakPassword';
GO


USE [DatabaseA];

EXECUTE AS LOGIN = 'JohnnyLunchbucket';
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];
GO

INSERT INTO [DatabaseB].[dbo].[TableWithTrigger] ([SomeOtherValue]) VALUES (N'Test B');
INSERT INTO [DatabaseB].[dbo].[TablePopulatedByTrigger]([DuplicatedValue]) VALUES ('Test B')
/*
Msg 916, Level 14, State 1, Line xxxxxx
The server principal "JohnnyLunchbucket" is not able to access the database
   "DatabaseB" under the current security context.
*/


EXEC [dbo].[InsertIntoTableWithTrigger]; -- SUCCESS!!!

REVERT;
SELECT SESSION_USER AS [User], ORIGINAL_LOGIN() AS [OriginalLogin];

SELECT * FROM [DatabaseB].[dbo].[TableWithTrigger];
SELECT * FROM [DatabaseB].[dbo].[TablePopulatedByTrigger];
-- 2    968DB092-C3DE-4E4B-92B9-E21CA551A5FA
-- 1    968DB092-C3DE-4E4B-92B9-E21CA551A5FA

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

相關問答

Sql-Server

DDL觸發權限錯誤

  • November 10, 2016
檢視問答
Sql-Server

查找簽名的過程、函式、觸發器、程序集以及哪些證書/非對稱密鑰

  • January 26, 2016
檢視問答
Sql-Server

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

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

觸發問題的權限

  • December 15, 2021
檢視問答
Sql-Server

外部開發人員的 SSMS 登錄

  • February 24, 2020
檢視問答
Sql-Server

模擬使用者或登錄名映射到證書

  • February 12, 2020
檢視問答