65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 Facade 数据库限制对 SQL Server 数据的访问

Shameel
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.81/5 (11投票s)

2019 年 9 月 7 日

CPOL

3分钟阅读

viewsIcon

10534

本文介绍了一种创建 Facade 数据库的方法，以便在不授予对任何底层数据库/表的直接访问权限的情况下，为特定用户提供对数据库中特定表的受限访问权限。

引言

您有多少次感到需要向外部用户授予对 SQL Server 数据库的受限访问权限，却又对此感到不安全？外部用户可能是您数据的下游消费者，或者是在同一组织下需要访问您的数据库以使其应用程序/数据库正常工作的团队。如果外部用户试图侵入您的数据库并读取他们不应该读取的数据，或者更糟的是，获得对它的写访问权限呢？如果他们破坏/损坏您的数据呢？

本文介绍了一种创建 Facade 数据库的方法，以便在不授予对任何底层数据库/表的直接访问权限的情况下，为特定用户提供对数据库中特定表的受限访问权限。 SQL Server 提供了一个名为 Cross-database Ownership Chaining 的功能，可以帮助我们实现这一点。本文中提供的示例已在 SQL Server 2008 R2 服务器上开发和测试。此功能也受较旧版本的 SQL Server 的支持，但我们将讨论限制在以下版本

  • SQL Server 2005
  • SQL Server 2008
  • SQL Server 2008 R2
  • SQL Server 2012
  • SQL Server 2014

所有权链接

当一个脚本按顺序访问多个数据库对象时，该序列被称为链。尽管这种链本身并不存在，但当 SQL Server 遍历链中的链接时，它对构成对象的权限评估与单独访问这些对象时的评估方式不同。这些差异对管理访问和安全性有重要影响。

当通过链访问一个对象时，SQL Server 首先将该对象的拥有者与调用对象的拥有者进行比较。如果两个对象拥有者相同，则不会评估对引用对象的权限。

跨数据库所有权链接

SQL Server 可以配置为允许在特定数据库之间或在单个 SQL Server 服务器内的所有数据库之间进行所有权链接。默认情况下，跨数据库所有权链接处于禁用状态，除非明确需要，否则不应启用。要使跨数据库所有权链接起作用，涉及的数据库必须具有共同的拥有者。

服务器级别与数据库级别

跨数据库链接可以在服务器级别或单个数据库级别启用。在服务器级别启用它会使跨数据库所有权链接在服务器上的所有数据库中都起作用，而不管数据库的单独设置如何。如果要求仅对少数几个数据库启用它，则应在数据库级别启用它。

服务器级跨数据库所有权链接

要启用服务器级跨数据库所有权链接，请使用以下 T-SQL 语句。

EXECUTE sp_configure 'show advanced', 1
GO
RECONFIGURE
GO
EXECUTE sp_configure 'cross db ownership chaining', 1
GO
RECONFIGURE
GO

要检查它是否已启用，请使用此查询

SELECT [name], value  
FROM [sys].configurations 
WHERE [name] = 'cross db ownership chaining';

值为 1 表示它已启用。

数据库级跨数据库所有权链接

要启用数据库级跨数据库所有权链接，请使用以下 T-SQL 语句。

ALTER DATABASE myDatabase SET DB_CHAINING ON
GO

要检查它是否已在单个数据库级别启用，请运行

SELECT name, is_db_chaining_on FROM sys.databases
GO

准备主数据库

让我用一个例子来说明这一点。创建一个名为 CustomerDB 的数据库。然后，创建一个名为 Customers 的表并插入一些测试数据。

CREATE TABLE [dbo].[Customers](
     [CustomerId] [int] IDENTITY(1,1) NOT NULL,
     [CustomerName] [varchar](50) NOT NULL,
     [Address] [varchar](500) NOT NULL,
     [City] [varchar](50) NOT NULL,
     [Country] [varchar](50) NOT NULL,
     CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
     [CustomerId] ASC
) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, _
  IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

INSERT INTO Customers ([CustomerId], [CustomerName], [Address], _
       [City], [Country]) VALUES (1, 'Michael Douglas', 'LA Home', 'Los Angeles', 'US')
INSERT INTO Customers ([CustomerId], [CustomerName], [Address], _
       [City], [Country]) VALUES (2, 'Al Pacino', 'NY Home', 'New York', 'US')
INSERT INTO Customers ([CustomerId], [CustomerName], [Address], _
       [City], [Country]) VALUES (3, 'James Cameroon', 'NJ Home', 'New Jersey', 'US')

创建 Facade 数据库

创建一个名为 FacadeDB 的数据库（或者任何其他名称）。

创建视图

为希望授予受限用户访问权限的主数据库中的每个表创建 View。

CREATE VIEW [dbo].[CustomerView] AS 
SELECT * FROM CustomerDB.dbo.Customers

您的对象资源管理器现在应该看起来像这样

Object Explorer

创建登录名和用户以简化访问

创建受限用户登录名及其在数据库中的关联用户。必须将用户添加到主数据库作为“public”。否则，所有权链接将不起作用。该用户必须在 Facade 数据库上至少拥有“db_datareader”角色。

CREATE LOGIN [FacadeUser] WITH PASSWORD=N'facadeuser', DEFAULT_DATABASE=[FacadeDB], _
       DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [CustomerDB]
GO
CREATE USER [FacadeUser] FOR LOGIN [FacadeUser] WITH DEFAULT_SCHEMA=[dbo]
GO
 
USE [FacadeDB]
GO
CREATE USER [FacadeUser] FOR LOGIN [FacadeUser] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_datareader', N'FacadeUser'
GO

在两个数据库上启用数据库所有权链接

ALTER DATABASE CustomerDB SET DB_CHAINING ON
GO
ALTER DATABASE FacadeDB SET DB_CHAINING ON
GO

测试

以受限用户 (FacadeUser) 身份登录到服务器并执行以下命令。

SELECT * FROM CustomerView

您应该能够看到底层表的行。

Restricted output

现在，尝试直接查询底层表。

SELECT * FROM CustomerDB.dbo.Customers

您应该会看到此错误

Permission denied

拒绝了对对象 'Customers'、数据库 'CustomerDB'、架构 'dbo' 的 SELECT 权限。

结论

如果您按照上述步骤操作，您应该会有一个有效的设置，受限用户可以查询 FacadeDB 并查看结果，但他们无法查询 CustomerDB 中的底层表。

历史

  • 2019 年 9 月 7 日：初始版本
© . All rights reserved.