SQLServer權限鏈

我有以下問題：

我有兩個不同的資料庫，db1 和 db2。我有一個將資料加載到 db2 或 db3 的應用程式。db1 有一些表，應用程式使用這些表來確定行為，包括應用程式應該將資料加載到哪個資料庫中。

用戶需要對 db1 有寫權限才能操作應用程式（有一個控制臺應用程式寫入 db1 中的表，使用 windows 身份驗證操作）。

用戶不應該對 db2 和 db3 擁有 DML 權限，除了一些預先確定的操作。我們授予 AD 組資料庫角色以從組織角度控制訪問。具體來說，我正在嘗試在 db1 中構建一個存盤程序，操作員可以使用該存盤程序通過適當的日志記錄將加載到 db2 或 db3 的資料反向。

我正在嘗試使用 create proc ... execute as owner 來完成此操作，但是當我嘗試訪問 db2/db3 中的表時它似乎不起作用（我認為“execute as owner”在 db 上運行級別用戶不是服務器級別的登錄？）。以下導致權限錯誤，指出所有者（我自己）沒有對 db2/db3 的權限。

use db1
go

create proc dbo.wrapper @recordid int 
as begin 
/*
  capturing user
*/
declare @usr varchar(255) = SUSER_SNAME()
exec dbo.inner @usr , @recordid 
end 

use db1
go

create proc dbo.inner @usr varchar(255), @recordid int
with execute as owner 
as begin 
    /*
       logic to determine whether to update db2 or db3 goes here
    */
    insert db2.rolled_back 
    select * , @usr from db2.transactions where id = @recordid 

    delete from db2.transactions where id = @recordid 

    insert db3.rolled_back 
    select * , @usr from db3.transactions where id = @recordid 

    delete from db3.transactions where id = @recordid 

end

有沒有辦法讓它作業？我聽說證書簽名可以做到這一點，有沒有人有使用證書用戶的經驗。我們的 DBA 寧愿不必維護證書，所以如果有一種方法可以在沒有證書的情況下使其作業，那將是最好的。

任何意見將是有益的。

謝謝你！

uj5u.com熱心網友回復：

我將在這里介紹跨資料庫鏈接方面的內容。請注意，使用此方法時肯定有安全考慮。例如，有權在一個資料庫中創建物件的人可以讓自己訪問具有所有者的另一個資料庫中的資料，而他們自己無權訪問另一個資料庫。但是，安全問題超出了此答案的范圍。

首先，讓我們創建幾個測驗資料庫。

USE master;
GO

CREATE DATABASE Chain1;
CREATE DATABASE Chain2;

現在我要去CREATE一個LOGIN，它是禁用的，并使它成為這兩個資料庫的所有者。擁有相同所有者的資料庫很重要，否則鏈接將不起作用。

CREATE LOGIN ChainerOwner WITH PASSWORD = N'SomeSecurePassword123';

ALTER LOGIN ChainerOwner DISABLE;
GO
ALTER AUTHORIZATION ON DATABASE::Chain1 TO ChainerOwner;
ALTER AUTHORIZATION ON DATABASE::Chain2 TO ChainerOwner;

我還將創建一個LOGIN我們將用于測驗的：

CREATE LOGIN SomeUser WITH PASSWORD = N'SomeSecurePassword123';

太好了，現在我可以創建一些物件了；中的一個表Chain1，一個訪問 的中PROCEDURE，以及兩個資料庫中的一個。在中將不會被授予權限，并且在用戶中將被授予權限：Chain2TABLEUSERSomeUserChain1USERChain2EXECUTEPROCEDURE

USE Chain1;
GO
CREATE TABLE dbo.SomeTable (I int IDENTITY,
                            S varchar(10));
INSERT INTO dbo.SomeTable (S)
VALUES ('abc'),
       ('xyz');
GO
CREATE USER SomeUser FOR LOGIN SomeUser;
GO
USE Chain2;
GO
CREATE PROC dbo.CrossDBProc @I int AS
BEGIN
    SELECT I,
           S
    FROM Chain1.dbo.SomeTable
    WHERE I = @I;
END;
GO
CREATE USER SomeUser FOR LOGIN SomeUser;
GO
GRANT EXECUTE ON dbo.CrossDBProc TO SomeUser;
GO

太好了，所有物件都已創建，現在讓我們嘗試EXECUTE一下PROCEDURE：

EXECUTE AS LOGIN = 'SomeUser';
GO
EXEC dbo.CrossDBProc 1; --This fails
GO
REVERT;
GO

這失敗了，出現權限錯誤：

物件“SomeTable”、資料庫“Chain1”、模式“dbo”的 SELECT 權限被拒絕。

這是意料之中的，因為沒有所有權鏈接。因此，讓我們現在啟用它。

USE master;
GO

ALTER DATABASE Chain1 SET DB_CHAINING ON;
ALTER DATABASE Chain2 SET DB_CHAINING ON;

現在，如果我再次嘗試相同的操作，相同的 SQL 將起作用：

USE Chain2;
GO
EXECUTE AS LOGIN = 'SomeUser';
GO
EXEC dbo.CrossDBProc 1; --This now works
GO
REVERT;
GO

這樣就成功回傳了結果集

所以，是的，您可以鏈接跨資料庫，但它需要進行一些設定，并且（再次）您需要考慮一些安全問題。

清理：

USE master;
GO
DROP DATABASE Chain1;
DROP DATABASE Chain2;
GO
DROP LOGIN ChainerOwner;
DROP LOGIN SomeUser;

標籤：sql服务器

上一篇：根據最小訂單值按鍵分組

下一篇：將驅動程式更新到10.2后無法從JetbrainsRider連接到SQLServer