MS SQL 跨資料庫擁有權鏈 cross db ownership chaining

MS SQL 跨資料庫擁有權鏈 cross db ownership chaining

 

日前打算整合多台SQL Server到同一部SQL Server來節省資源。我們有一些核心資料表

是很多系統都需要用到,然而在清查欲搬移的這些DB中赫然發現每一個資料庫都存在一

份這一些核心資料表。這表示前人在開發時就是習慣搬一份核心資料表放在自己DB自己

用,然後再用排程固定去同步這一些核心資料表。

 

但如果整合這些DB後,勢必不可能再這樣做。每個DB都同步自己的那一份實在有一點蠢

。因此我想將這一些核心資料表獨立出到一個DB中。每個DB針對自己需求再到該核心資

料表的DB去撈資料。日後只要核心資料表DB同步就可以了。

 

然而原來的程式都已經在RUN了,一旦資料表需要改到別的DB去存取,那勢必要改寫所有

程式。因此我想到的方法就是建View或同義字來解。將核心資料表在每個DB都建成View或

同義字,這樣一來程式就都不用改了。

 

但用這樣的方式屆時在各帳號權限設定上也是很費工。想了一下偷吃步,也就是今天我們要

介紹的跨資料庫擁有權鏈(預設此功能是關閉)。這項功能簡單的說就是我們在DB1建立一個

跨DB存取資料的物件,例如建立一個View去存取DB2的資料表資料,並將該物件(View)Grant

給帳號User可以Select。然而帳號User並無存取DB2的權限,因此當他Select DB1建立的View

時會產生無權限存取DB2的警告訊息。此時我們只要將DB1及DB2的跨資料庫擁有權鏈功能打

開,User就可以順利的透過DB1上的View取得DB2上的資料表資料。

 

注意 : 此項功能牽扯到安全性問題,這裡只是介紹。屆時我自己還是會乖乖設權限吧。

 

 


/**建立兩個測試資料庫**/

Create Database DB_Chaining1;

Create Database DB_Chaining2;

GO



/**建立測試USER**/

CREATE LOGIN [TestUser] WITH PASSWORD=N'123', CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;

GO



/**將測試USER帳號加入至DB_Chaining1中並給予db_datareader 權限**/

Use DB_Chaining1

GO

Create User [TestUser] For Login [TestUser];

Alter Role db_datareader Add Member [TestUser];

GO



/**將測試USER帳號加入至DB_Chaining2甚麼權限都不給**/

Use DB_Chaining2

GO

Create User [TestUser] For Login [TestUser];

GO



/**在DB_Chaining2建立一資料表tb1並寫入一筆資料**/

Use DB_Chaining2

GO

Create Table tb1(id int,name nvarchar(10));

Insert Into tb1 Values(1,N'ROCK');

GO



/**在DB_Chaining1建立一View vw_tb1去Select DB_Chaining2.dbo.tb1資料表的資料**/

Use DB_Chaining1

GO

Create View vw_tb1 As Select * From DB_Chaining2.dbo.tb1;

GO

 

以上就是我們的前置作業,接下來我們利用帳號TestUser測試看看,如下圖所示該帳號

Select DB_Chaining1.dob.vw_tb1時出現無權限Select DB_Chaining2.dbo.tb1的訊息。

clip_image002[4]

 

 

這時我們開啟DB_Chaining1跟DB_Chaining2的跨資料庫擁有權鏈選項,如下圖所示。

clip_image004[4]

 

 

我們重新執行一下剛剛會發生錯誤的查詢,這時我們可以看見在開啟該功能選項後就可以順利取到資料了。

clip_image006[4]

 

 

注意 : 開啟此功能有兩個需要注意的地方

1 . 使用者帳號需在兩個DB都有連線的權限。

2 . 兩個DB的dbo都需要是同一個Login帳號。兩個DB的dbo是不同Login的話,即使開啟此功能也是會有權限問題。

 

 

參考資料來源

跨資料庫擁有權鏈結伺服器組態選項

擁有權鏈結

 

我是ROCK

rockchang@mails.fju.edu.tw