為 SQL Server 中的儲存過程授予特殊權限

我有一個名為數據庫B的受限數據庫（包含機密數據），只有幾個使用者可以訪問它。但是，我需要從B中的幾個表中選擇一些數據，以供請求它的使用者使用。請求數據的使用者不會作為數據庫使用者添加到數據庫B，因為他們不允許訪問。但是，這些使用者可以訪問數據庫A（他們被添加為 DB A的 DB 使用者）。有沒有辦法讓儲存過程訪問機密數據庫（B），以便任何執行 SP 的使用者都可以獲得他們請求的數據？如果是這樣，怎麼做？我研究過這是可能的，但沒有提到如何。

注意：通過 IS 包將所需數據從數據庫B移動到A不是一種選擇（需要實時訪問）。我也考慮過使用視圖（但同樣，如何使用？如果您無權訪問基表，您也無法通過視圖訪問數據）。

這很容易實現（包括處理多個數據庫和動態 SQL），無需任何模擬（IMPERSONATE權限）、跨數據庫所有權連結（伺服器/實例或數據庫設置）或TRUSTWORTHY（數據庫設置）。一般來說，您需要做的是：

1. 在數據庫 A 中創建證書
2. 使用ADD SIGNATURE對數據庫 A 中的儲存過程進行簽名
3. 在數據庫 B 中創建相同的證書
4. 根據該證書在數據庫 B 中創建使用者
5. 為新使用者分配訪問所需表所需的權限

例子：

清理

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

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

CREATE PROCEDURE dbo.RunReport
AS
SET NOCOUNT ON;

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
GO

GRANT EXECUTE ON dbo.RunReport 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].[RunReport]
   BY CERTIFICATE [PermissionsCert]
   WITH PASSWORD = 'WeakPassword';

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];

CREATE TABLE dbo.[RestrictedTable]
(
 [ID] INT NOT NULL IDENTITY(1, 1)
    CONSTRAINT [PK_RestrictedTable] PRIMARY KEY,
 [Other] VARCHAR(50)
);

GRANT SELECT ON [dbo].[RestrictedTable] TO [PermissionsUser];

INSERT INTO dbo.[RestrictedTable] ([Other]) VALUES ('Ta da!');
GO

測試

-- Quick test to show that [PermissionsUser] cannot be Impersonated:
USE [DatabaseB];
EXECUTE AS USER = 'PermissionsUser';
/*
Msg 15517, Level 16, State 1, Line 123
Cannot execute as the database principal because the principal "PermissionsUser" does not
exist, this type of principal cannot be impersonated, or you do not have permission.
*/


-- Main test:
USE [DatabaseA];

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

SELECT * FROM [DatabaseB].[dbo].[RestrictedTable];
/*
Msg 916, Level 14, State 1, Line 144
The server principal "JohnnyLunchbucket" is not able to access the database "DatabaseB"
under the current security context.
*/

EXEC [dbo].[RunReport]; -- SUCCESS!!!
-- 1    Ta da!


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

有關模擬與模組簽名的更多資訊，您可以查看我的其他答案（以及其中連結的答案）：

SQL Server 模擬只是不工作

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