sql-server-2005 - SQL Server : How to permission schemas?

Question

受到我见过的各种与模式相关的问题的启发......

Ownership chaining如果存储过程和表都在同一架构中，则允许我对存储过程进行 GRANT EXECUTE 对我使用的表没有显式权限。

如果我们使用单独的模式，那么我必须在不同的模式表上显式地 GRANT XXX。所有权链接示例演示了这一点。这意味着存储过程执行用户可以直接读/写您的表。

这就像直接访问类中的实例变量，绕过 getter/setter，破坏封装。

我们还使用行级安全来限制某人看到的内容，并将其应用到存储过程中。

那么，我们如何才能保持模式分离并防止直接访问表呢？

当然，如果您使用 ORM 或不使用存储过程，则该问题将不适用。但我不是问我是否应该使用 ORM 或存储过程以防万一有人觉得需要启发我......

编辑，示例

CREATE USER OwnsMultiSchema WITHOUT LOGIN
GO
CREATE SCHEMA MultiSchema1 AUTHORIZATION OwnsMultiSchema
GO
CREATE SCHEMA MultiSchema2 AUTHORIZATION OwnsMultiSchema
GO

CREATE USER OwnsOtherSchema WITHOUT LOGIN
GO
CREATE SCHEMA OtherSchema AUTHORIZATION OwnsOtherSchema
GO

CREATE TABLE MultiSchema1.T1 (foo int)
GO
CREATE TABLE MultiSchema2.T2 (foo int)
GO
CREATE TABLE OtherSchema.TA (foo int)
GO

CREATE PROC MultiSchema1.P1
AS
SELECT * FROM MultiSchema1.T1
SELECT * FROM MultiSchema2.T2
SELECT * FROM OtherSchema.TA
Go
EXEC AS USER = 'OwnsMultiSchema'
GO
--gives error on OtherSchema
EXEC MultiSchema1.P1
GO
REVERT
GO

CREATE PROC OtherSchema.PA
AS
SELECT * FROM MultiSchema1.T1
SELECT * FROM MultiSchema2.T2
SELECT * FROM OtherSchema.TA
Go
GRANT EXEC ON OtherSchema.PA TO OwnsMultiSchema
GO
EXEC AS USER = 'OwnsMultiSchema'
GO
--works
EXEC OtherSchema.PA
GO
REVERT
GO

编辑2:

我们不使用“跨数据库所有权链接”

行级安全性是不相关的:我们不会在任何地方使用它

Answer 1

我担心你的描述或你对所有权链接的概念不清楚，所以让我从这个开始:

“所有权链接”只是指当在 SQL Server 上执行存储过程(或 View )时，当前执行的批处理在执行该 SQL 代码时临时获取 sProc 的所有者(或 sProc 的架构的所有者)的权利/许可。所以在 sProc 的情况下，用户不能使用这些 privs 做任何 sProc 代码没有为他们实现的事情。尤其要注意，它从不获取 身份所有者的，只有它的权利，暂时(但是，EXECUTE AS... 确实这样做)。

因此，利用这一点来确保安全性的典型方法是:

将所有数据表(以及所有非安全 View )放入它们自己的架构中，让我们称其为 [data](尽管通常使用 [dbo]，因为它已经存在并且对于用户的架构而言过于特权)。确保没有现有用户、架构或所有者有权访问此 [数据] 架构。

为所有 sProc(和/或可能的任何安全 View )创建一个名为 [exec] 的模式。确保此架构的所有者有权访问 [data] 架构(如果您让 dbo 成为此架构的所有者，这很容易)。

创建一个名为“Users”的新 db-Role 并授予它对 [exec] 架构的 EXECUTE 访问权限。现在将所有用户添加到此角色。确保您的用户只有连接权限，并且没有被授予对任何其他架构的访问权限，包括 [dbo]。

现在您的用户只能通过执行 [exec] 中的 sProcs 来访问数据。他们不能访问任何其他数据或执行任何其他对象。

我不确定这是否能回答您的问题(因为我不确定问题究竟是什么)，所以请随时重定向我。

至于行级安全性，以下是我始终使用上述安全性方案的方式:

我总是将行级安全性实现为一系列 View ，这些 View 镜像包装每个表，并将用户的身份(通常与 Suser_Sname() 或其他一个)与从行本身的安全代码中键入的安全列表进行比较。这些是安全 View 。

创建一个名为 [rows] 的新架构，为其所有者授予对 [data] 架构的访问权限，而不是其他任何内容。把所有的安全 View 放在这个模式中。

撤消 [exec] 所有者对 [data] 架构的访问权限，而是授予其对 [rows] 架构的数据访问权限。

完毕。现在行级安全已经通过在 sProcs 和表之间透明地滑动来实现。

最后，这是一个存储采购，我用它来帮助我记住这些晦涩的安全性东西有多少工作并与自身交互(哎呀，代码的更正版本):

CREATE proc [TestCnxOnly].[spShowProc_Security_NoEX]  as
--no "With Execute as Owner" for this version
--create User [UserNoLogin] without login
--Grant connect on database :: TestSecurity to Guest
--alter database TestSecurity set trustworthy on

--Show current user context:
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (sproc)]
, suser_sname() as sname
, system_user as system_


--Execute As Login = 'UserNoLogin'
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (after exec as)]
, suser_sname() as sname
, system_user as system_

EXEC('select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in Exec(sql))]
, suser_sname() as sname
, system_user as system_')

EXEC sp_ExecuteSQL N'select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (in sp_Executesql)]
, suser_sname() as sname
, system_user as system_'

--Revert
select current_user as current_
, session_user as session
, user_name() as _name
, suser_name() as [suser (aftr revert)]
, suser_sname() as sname
, system_user as system_

[编辑:更正的代码版本)