Sql-Server

數據庫使用者禁用登錄的跨數據庫引用選擇訪問

  • December 22, 2021

我們有兩個具有以下視圖結構的數據庫（DB1、DB2）。

DB1：

  • T1、T2、T3、…… Tn（表格）
  • V1（查看消費 {T1, T2, T3 ….. Tn }）

在 DB2 中，我們必須創建一個視圖 V2，它將從 DB1.dbo.V1 中提取數據

USE DB2;
CREATE VIEW dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

現在的問題是，為 DB2 創建的使用者不應該對 DB1 有任何登錄訪問權限，但應該能夠登錄 DB2 以使用 V2。

我不想禁用所有者鏈設置。到目前為止，我們已經嘗試了以下解決方案。但它使使用者能夠看到 DB1 中的選定視圖 (V1)。

USE DB1;

CREATE View dbo.V1 AS SELECT * FROM T1; -- And some other tables according to business logic

CREATE USER testUser FOR LOGIN testUser;

GRANT SELECT ON dbo.V1 To testUser;

USE DB2;

CREATE View dbo.V2 AS SELECT * FROM DB1.dbo.V1; -- with some business logic

請考慮 DB2 已經存在 testUser，我剛剛將它映射到 DB1 數據庫。

該解決方案為我提供了預期的結果。但它也允許 testUser 登錄 DB1 並直接從DB1.dbo.V1. 有什麼方法可以拒絕對 DB1 的登錄訪問，但同時允許對在 DB2 中創建的 V2 進行選擇訪問？

我們在這裡嘗試做的是創建一個可以完全訪問 DB2 的使用者。如上所述，使用者應該能夠使用內部視圖獲取數據。但應該無法登錄到 DB1。在基於 Linux/Unix 的系統中，管理員可以創建一個內部使用者，該使用者可用於訪問定義，但不能用於登錄系統。我們正在尋找在 SQL 中實現類似功能的解決方案。

如果我們不授予對 V1 的選擇訪問權限，伺服器會拋出權限錯誤：

對對象 V1、數據庫“DB1”、模式“dbo”的 SELECT 權限被拒絕。

我能得到的最接近的是按照這些步驟……

以系統管理員身份創建 DB1、t1、t2 和 v1

使用具有sysadminSQL Server 角色的 SQL Server 登錄創建數據庫DB1、表t1和t2視圖v1。並且不要忘記數據庫連結。

/*******************************************
* Create Database DB1
*******************************************/
USE [master];
GO
/****** Object:  Database [DB1]    Script Date: 06.07.2021 10:37:39 ******/
CREATE DATABASE [DB1];
GO
USE [DB1];
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO
/*******************************************
* Create Tables t1 and t2
*******************************************/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE TABLE [dbo].t1(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [sometext] [nchar](30) NULL
) ON [PRIMARY];
GO
CREATE TABLE [dbo].t2(
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [sometext] [nchar](30) NULL
) ON [PRIMARY];
GO
/*******************************************
* Create View v1 on t1 & t2
*******************************************/

USE [DB1];
GO
/****** Object:  View [dbo].[view1]    Script Date: 06.07.2021 10:37:11 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
CREATE VIEW [dbo].[v1]
AS
SELECT        dbo.t1.ID AS ID1, dbo.t1.sometext AS sometext1, dbo.t2.ID AS ID2, dbo.t2.sometext AS sometext2
FROM            dbo.t1 CROSS JOIN
                        dbo.t2; 
GO
/*******************************************
* Add a record to each table
*******************************************/
INSERT INTO t1 VALUES ('test');
INSERT INTO t2 VALUES ('test');

以系統管理員身份創建 DB2 和 v2

使用具有sysadminSQL Server 角色的 SQL Server 登錄創建數據庫DB2和視圖v2。並且不要忘記數據庫連結。

/*******************************************
* Create Database DB2
*******************************************/
USE [master];
GO
/****** Object:  Database [DB1]    Script Date: 06.07.2021 10:37:39 ******/
CREATE DATABASE [DB2];
GO
USE [DB2];
GO
ALTER DATABASE DB2 SET DB_CHAINING ON;
GO
USE [DB2]
GO
/*******************************************
* Create View v2
*******************************************/

CREATE VIEW [dbo].[v2]
AS
SELECT        *
FROM            DB1.dbo.v1
GO

創建 SQL Server 登錄 dbuser1

創建一個 SQL Server 登錄名dbuser1並以數據庫使用者身份連結到DB1和DB2。db_owner在兩個數據庫中分配 db_role 。此外，將dbo模式的所有者更改為dbuser1兩個數據庫中的所有者。

USE [master]
GO
CREATE LOGIN [dbuser1] WITH PASSWORD=N'dbuser1', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
/* 
 Create Database User dbuser1 in DB1
*/
USE [DB1]
GO
CREATE USER [dbuser1] FOR LOGIN [dbuser1]
GO
ALTER USER [dbuser1] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [dbuser1]
GO
/*
 Change the ownership of the database role dbo to dbuser1
*/
use [DB1]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbuser1]
GO
GRANT TAKE OWNERSHIP ON SCHEMA::[dbo] TO [dbuser1] WITH GRANT OPTION 
GO
/*
 Create Database User dbuser1 in database DB2
*/
USE [DB2]
GO
CREATE USER [dbuser1] FOR LOGIN [dbuser1]
GO
ALTER USER [dbuser1] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_owner] ADD MEMBER [dbuser1]
GO
ALTER ROLE [db_datareader] ADD MEMBER [dbuser1]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [dbuser1]
GO
/*
 Change the ownership of the database role dbo to dbuser1
*/
USE [DB2]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_owner] TO [dbuser1]
GO
GRANT TAKE OWNERSHIP ON SCHEMA::[dbo] TO [dbuser1] WITH GRANT OPTION 
GO

現在我們有一個使用者dbuser1同時擁有數據庫和dbo每個數據庫中的預設模式。

創建 SQL Server 登錄 dbuser2

創建一個 SQL Server 登錄名dbuser2並以數據庫使用者身份連結到DB1和DB2，但沒有任何權限。僅（public預設）訪問。

USE [master]
GO
CREATE LOGIN [dbuser2] WITH PASSWORD=N'dbuser2', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
USE [DB2]
GO
CREATE USER [dbuser2] FOR LOGIN [dbuser2] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [DB1]
GO
CREATE USER [dbuser2] FOR LOGIN [dbuser2] WITH DEFAULT_SCHEMA=[dbo]
GO

允許使用者dbuser2查看數據庫，但除此之外沒有太多權限/特權。使用者不能SELECT來自任何表或視圖。

授予數據庫使用者 dbuser2 SELECT 權限

現在我們要授予數據庫中視圖dbuser2的SELECT權限。我們將在以 . 身份登錄時執行此操作。（或使用解決方法）v2``DB2``dbuser1``EXEUCTE AS...

EXECUTE AS USER = 'dbuser1'
go
use [DB2]
GO
GRANT SELECT ON dbo.v2 TO [dbuser2]
GO

檢查權限

打開一個新的 SQL 查詢並dbuser2使用密碼進行連接dbuser2。輸入以下命令以驗證安全性是否正常工作：

USE DB2
go
SELECT * FROM dbo.v2

這將返回一個結果集：

ID1 | 一些文本1 | ID2 | 一些文本2 
--: | :----------------------------- | --: | :-----------------------------
 1 | 測試 | 1 | 測試

下一個語句將失敗。

SELECT * FROM DB1.dbo.v1;

Msg 229, Level 14, State 5, Line 7
The SELECT permission was denied on the object 'v1', database 'DB1', schema 'dbo'.

概括

我們已經實現了一種情況，在基於視圖的視圖上dbuser2被授予了SELECT權限，但即使這樣也不能直接訪問。dbuser1``DB2.dbo.v2``DB1.dbo.v1``dbuser2``DB1.dbo.v1

參考： 所有權鏈(Microsoft | Docs)

參考： 教程：所有權鍊和上下文切換(Microsoft | SQL Docs)較新的文章

  • 確保DB_CHAINING是ON.
  • 將 TestUser Login 映射到 DB1 和 DB2。
  • 授予SELECTV2。
  • 不要授予SELECTV1。

請參閱文件中的所有權鏈。

基本上，它的作用是，因為 V1 和 V2 具有相同的所有者（這是強制性的）並且因為DB_CHAINING是ON，當 TestUser 將SELECTV2 時，不會在 V1 上檢查權限，因此您不需要授予它任何權限。

TestUser 將能夠從 V2 中檢索數據，但SELECT在 V1 上將被拒絕。

DB_CHAINING通過ON執行以下命令進行仔細檢查：

SELECT name, is_db_chaining_on 
FROM sys.databases;

如果不是這種情況，請將其設置為：

ALTER DATABASE DBX SET DB_CHAINING ON

…並確保兩個視圖具有相同的所有者。

您可以嘗試以下程式碼來驗證它是否正常工作：

以系統管理員身份連接並創建數據庫 DB1 和 DB2

USE master
GO
CREATE DATABASE DB1;
GO
CREATE DATABASE DB2;
GO

在 DB1 中：

創建樣本表 TB1

在 TB1 表中插入一個值（這只是因為我不喜歡空結果集）

創建一個範例視圖，選擇 TB1 表中的所有行

USE DB1
GO
CREATE TABLE TB1 (id int);
GO
INSERT INTO TB1 VALUES(42);
GO
CREATE VIEW V1 AS SELECT * FROM TB1;
GO

在 DB2 中：

創建一個範例視圖 V2，選擇 DB1.dbo.V1 視圖中的所有行

USE DB2
GO
CREATE VIEW V2 AS SELECT * FROM DB1.dbo.V1;
GO

創建測試使用者登錄

USE [master]
GO
CREATE LOGIN [TestUser] WITH PASSWORD=N'password', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

在 DB1 中：

創建映射到 TestUser 登錄的 TestUser 使用者

USE [DB1]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO

在 DB2 中：

創建映射到 TestUser 登錄的 TestUser 使用者

將 V2 視圖上的 SELECT 授予 TestUser 使用者

USE [DB2]
GO
CREATE USER [TestUser] FOR LOGIN [TestUser]
GO
GRANT SELECT ON V2 TO TestUser;

使用 TestUser 登錄連接

在 DB2 中：

嘗試從 V2 中選擇

USE DB2
GO
SELECT * FROM V2;

這應該會失敗並顯示錯誤消息：對象“V1”、數據庫“DB1”、模式“dbo”的 SELECT 權限被拒絕。

以系統管理員身份連接

在 DB1 上啟用 DB_CHAINING

USE master
GO
ALTER DATABASE DB1 SET DB_CHAINING ON;
GO

在 DB2 上啟用 DB_CHAINING

ALTER DATABASE DB2 SET DB_CHAINING ON;
GO

使用 TestUser 登錄連接

在 DB2 中：

再次嘗試Select From V2，到時候應該會成功

USE DB2
GO
SELECT * FROM V2;

在 DB1 中：

嘗試從 V1 中選擇

USE DB1
GO
SELECT * FROM V1;
GO

由於我們沒有授予 TestUser 任何權限，這將失敗並顯示錯誤消息：對象“V1”、數據庫“DB1”、模式“dbo”的 SELECT 權限被拒絕。

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

相關問答

Sql-Server

對象“TheTable”、數據庫“TheDb”、模式“dbo”的 INSERT/UPDATE/DELETE 權限被拒絕

  • March 12, 2022
檢視問答
Sql-Server

對象“SPROC”、數據庫“DATABASE”、模式“dbo”的 EXECUTE 權限被拒絕

  • March 10, 2022
檢視問答
Sql-Server

觸發問題的權限

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

如何刪除 dbcreator 伺服器角色？

  • July 23, 2020
檢視問答
Sql-Server

使用者登錄失敗錯誤：18456 狀態：10

  • May 13, 2020
檢視問答
Sql-Server

如何在 Sql Server 中以待機/只讀模式將使用者添加到數據庫

  • February 15, 2020
檢視問答