sql - 动态使用 T-SQL 模式

Question

有没有一种方法可以省略存储过程中使用的对象模式，这些对象不绑定(bind)到存储过程的模式，而是以相同存储过程产生不同结果的方式使用记录的模式不同模式中的用户？

为了更好地理解我正在尝试做的事情，下面我将尝试更好地解释。

假设有以下数据库:

CREATE TABLE [dbo].[SampleTable01] (...)

CREATE TABLE [dbo].[SampleTable02] (...)

CREATE TABLE [dbo].[SampleTable03] (...)

CREATE TABLE [dbo].[SampleTable04] (...)

在这个数据库中，表名后面的数字代表表的所有者(这是一个遗留系统，我无法更改)。

为了将其集成到 .net Entity Framework 中，我提供了一个在不同模式中创建同义词的解决方案，因此更改连接字符串我可以更改使用的对象，而无需更改我的数据库上下文或我的编程逻辑。

像这样。

CREATE SCHEMA [s01]
CREATE SYNONYM [s01].[SampleTable] FOR [dbo].[SampleTable01]

...

CREATE SCHEMA [s04]
CREATE SYNONYM [s04].[SampleTable] FOR [dbo].[SampleTable04]

此解决方案运行良好，但我需要复制所有使用的存储过程，因为存储过程绑定(bind)到特定对象。

当我创建以下存储过程时:

CREATE PROCEDURE [dbo].[usp_SampleProc] AS
SELECT * FROM [SampleTable]

存储过程将产生错误，因为 [SampleTable] 不存在于架构 [dbo] 中。

我正在做的是复制存储过程以匹配登录用户的模式。

所以我这样做:

CREATE PROCEDURE [s01].[usp_SampleProc] AS
SELECT * FROM [s01].[SampleTable]

...

CREATE PROCEDURE [s04].[usp_SampleProc] AS
SELECT * FROM [s04].[SampleTable]

[s01] 模式中的用户将从 [s01].[SampleTable] 和 [s04] 模式中的用户获取值将从 [s04].[SampleTable] 获取值，在执行 [usp_SampleProc] 时未指定架构，这是我的预期结果。

到目前为止一切顺利，这在我的真实场景中并没有产生效果。我有上千个表、数百个过程和几十个模式(我知道这很丑陋，但我将遗留系统与 .net 集成，到目前为止，这是我得出的最佳解决方案)。

那么，问题又来了:

有没有一种方法可以省略存储过程中使用的对象模式，并且这些对象不绑定(bind)到存储过程的模式，而是以相同存储过程生成的方式绑定(bind)到登录用户的模式不同模式下用户的不同结果？

Answer 1

这是我知道的两种方法来做我想做的事情。

这两种方式对开发人员都是透明的，因此他们不需要了解解决方案的复杂性。

下面我创建了一个每个人都可以使用的示例:

原始遗留数据库创建:保持不变，因为遗留应用程序仍在使用数据库。

CREATE TABLE [dbo].[SampleTable01] (
    value varchar(100)
)
INSERT INTO [dbo].[SampleTable01] VALUES ('[dbo].[SampleTable01]')

CREATE TABLE [dbo].[SampleTable02] (
    value varchar(100)
)
INSERT INTO [dbo].[SampleTable02] VALUES ('[dbo].[SampleTable02]')

CREATE TABLE [dbo].[SampleTable03] (
    value varchar(100)
)
INSERT INTO [dbo].[SampleTable03] VALUES ('[dbo].[SampleTable03]')

CREATE TABLE [dbo].[SampleTable04] (
    value varchar(100)
)
INSERT INTO [dbo].[SampleTable04] VALUES ('[dbo].[SampleTable04]')
GO

我的应用程序使用的用户和架构分离:这些是很多重复的代码，但将由应用程序设置生成。

CREATE SCHEMA [S01]
GO

CREATE SCHEMA [S02]
GO

CREATE SCHEMA [S03]
GO

CREATE SCHEMA [S04]
GO

CREATE USER USER_S01 WITHOUT LOGIN WITH DEFAULT_SCHEMA = S01
GO

CREATE USER USER_S02 WITHOUT LOGIN WITH DEFAULT_SCHEMA = S02
GO

CREATE USER USER_S03 WITHOUT LOGIN WITH DEFAULT_SCHEMA = S03
GO

CREATE USER USER_S04 WITHOUT LOGIN WITH DEFAULT_SCHEMA = S04
GO

CREATE SYNONYM [S01].[SampleTable] FOR [dbo].[SampleTable01]
CREATE SYNONYM [S02].[SampleTable] FOR [dbo].[SampleTable02]
CREATE SYNONYM [S03].[SampleTable] FOR [dbo].[SampleTable03]
CREATE SYNONYM [S04].[SampleTable] FOR [dbo].[SampleTable04]
GO

GRANT DELETE     ON SCHEMA::[S01] TO [USER_S01]
GRANT EXECUTE    ON SCHEMA::[S01] TO [USER_S01]
GRANT INSERT     ON SCHEMA::[S01] TO [USER_S01]
GRANT REFERENCES ON SCHEMA::[S01] TO [USER_S01]
GRANT SELECT     ON SCHEMA::[S01] TO [USER_S01]
GRANT UPDATE     ON SCHEMA::[S01] TO [USER_S01]
GRANT EXECUTE    ON SCHEMA::[S01] TO [USER_S01]
GO

GRANT DELETE     ON SCHEMA::[S02] TO [USER_S02]
GRANT EXECUTE    ON SCHEMA::[S02] TO [USER_S02]
GRANT INSERT     ON SCHEMA::[S02] TO [USER_S02]
GRANT REFERENCES ON SCHEMA::[S02] TO [USER_S02]
GRANT SELECT     ON SCHEMA::[S02] TO [USER_S02]
GRANT UPDATE     ON SCHEMA::[S02] TO [USER_S02]
GRANT EXECUTE    ON SCHEMA::[S02] TO [USER_S02]
GO

GRANT DELETE     ON SCHEMA::[S03] TO [USER_S03]
GRANT EXECUTE    ON SCHEMA::[S03] TO [USER_S03]
GRANT INSERT     ON SCHEMA::[S03] TO [USER_S03]
GRANT REFERENCES ON SCHEMA::[S03] TO [USER_S03]
GRANT SELECT     ON SCHEMA::[S03] TO [USER_S03]
GRANT UPDATE     ON SCHEMA::[S03] TO [USER_S03]
GRANT EXECUTE    ON SCHEMA::[S03] TO [USER_S03]
GO

GRANT DELETE     ON SCHEMA::[S04] TO [USER_S04]
GRANT EXECUTE    ON SCHEMA::[S04] TO [USER_S04]
GRANT INSERT     ON SCHEMA::[S04] TO [USER_S04]
GRANT REFERENCES ON SCHEMA::[S04] TO [USER_S04]
GRANT SELECT     ON SCHEMA::[S04] TO [USER_S04]
GRANT UPDATE     ON SCHEMA::[S04] TO [USER_S04]
GRANT EXECUTE    ON SCHEMA::[S04] TO [USER_S04]
GO

解决方案 1(我的选择):在不同的模式中使用相同的过程名称。每个用户一个过程(有自己的模式)。

CREATE PROCEDURE [S01].[usp_SampleProc]
AS
SELECT * FROM [SampleTable]
GO

CREATE PROCEDURE [S02].[usp_SampleProc]
AS
SELECT * FROM [SampleTable]
GO

CREATE PROCEDURE [S03].[usp_SampleProc]
AS
SELECT * FROM [SampleTable]
GO

CREATE PROCEDURE [S04].[usp_SampleProc]
AS
SELECT * FROM [SampleTable]
GO

解决方案 2: 使用动态创建，因为在执行时表引用将被解析为用户架构中的同义词。

GRANT EXECUTE    ON SCHEMA::[dbo] TO [USER_S01]
GRANT EXECUTE    ON SCHEMA::[dbo] TO [USER_S02]
GRANT EXECUTE    ON SCHEMA::[dbo] TO [USER_S03]
GRANT EXECUTE    ON SCHEMA::[dbo] TO [USER_S04]
GO

CREATE PROCEDURE [dbo].[usp_SampleProc]
AS
    exec(N'SELECT * FROM [SampleTable]')
GO

执行:两种解决方案完全相同。

EXECUTE AS USER = 'USER_S01'
EXEC [usp_SampleProc]
REVERT;

EXECUTE AS USER = 'USER_S02'
EXEC [usp_SampleProc]
REVERT;

EXECUTE AS USER = 'USER_S03'
EXEC [usp_SampleProc]
REVERT;

EXECUTE AS USER = 'USER_S04'
EXEC [usp_SampleProc]
REVERT;

选择理由:我不希望开发人员简化程序的创建和测试。并解决生产中发生的错误。按照我决定使用的方式，所有模式的过程都将完全相同。因此，只需记录该问题并为所有模式解决该问题，就可以轻松测试该模式中发生的问题。

该解决方案的缺点是我无法将模式放入过程内的表中。所以这将是一个轻微的性能损失。