SQL Server:如何授予架构权限? [英] SQL Server: How to permission schemas?

问题描述

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

Inspired by various schema related questions I've seen...

所有权链接 允许我在没有如果存储过程和表在同一架构中，则对我使用的表具有显式权限.

Ownership chaining allows me to GRANT EXECUTE on a stored procedure without explicit permissions on tables I use, if both stored procedure and tables are in the same schema.

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

If we use separate schemas then I'd have to explicitly GRANT XXX on the the different-schema tables. The ownership chaining example demonstrates that. This means the stored proc executing user can read/write your tables directly.

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

This would be like having direct access to your instance variables in a class, bypassing getter/setters, breaking encapsulation.

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

We also use row level security to restrict what someone sees and we apply this in the stored procedures.

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

So, how can we maintain schema separation and prevent direct table access?

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

Of course, the question won't apply if you use an ORM or don't use stored procs. But I'm not asking if I should use an ORM or stored proc in case anyone feels the need to enlighten me...

编辑，示例

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:

• 我们不使用跨数据库所有权链接"
• 行级安全性是不相关的:我们不会在任何地方使用它

推荐答案

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

I fear that either your description or your conception of Ownership Chaining is unclear, so let me start with that:

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

"Ownership Chaining" simply refers to that fact that when executing a Stored Procedure (or View) on SQL Server, the currently executing batch temporarily acquires the rights/permissions of the sProc's Owner (or the sProc's schema's Owner) while executing that SQL code. So in the case of a sProc, the User cannot use those privs to do anything that the sProc code does not implement for them. Note especially that it never acquires the Identity of the Owner, only it's rights, temporarily (however, EXECUTE AS... does do this).

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

So the typical approach to leverage this for security is to:

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

1. Put all of the Data Tables (and all non-security Views as well) into their own Schema, let's call it [data] (though typically [dbo] is used because it's already there and too privileged for the User's schema). Make sure that no existing Users, Schemas or Owners have access to this [data] schema.

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

Create a schema called [exec] for all of the sProcs (and/or possibly any security Views). Make sure that the owner of this schema has access to the [data] schema (this is easy if you make dbo the owner of this schema).

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

Create a new db-Role called "Users" and give it EXECUTE access to the [exec] schema. Now add all users to this role. Make sure that your users only have Connect rights and have no granted access to any other schema, including [dbo].

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

Now your users can access the data only by executing the sProcs in [exec]. They cannot access any other data or execute any other objects.

我不确定这是否能回答您的问题(因为我不确定问题的确切含义)，所以请随时重定向我.

I am not sure if this answers your question (because I was uncertain what the question was exactly), so feel free to redirect me.

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

As for row-level security, here is how I always do it with the security scheme above:

1. 我总是将行级安全性实现为一系列视图，这些视图对每个表进行镜像包装，并将用户的身份(通常使用 Suser_Sname() 或其他身份)与根据安全代码键入的安全列表进行比较在行本身.这些是安全视图.

1. I always implement row-level security as a series of Views that mirror-wrap every table and compare the User's identity (usually with Suser_Sname() or one of the others) to a security list keyed from a security code in the row itself. These are the Security-Views.

创建一个名为 [rows] 的新架构，为其所有者授予对 [data] 架构的访问权限，而不是其他任何内容.将所有安全视图放在此架构中.

Create a new schema called [rows], give it's owner access to the [data] schema and nothing else. Put all of the Security-Views in this schema.

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

Revoke the [exec] owner's access to the [data] schema and instead grant it data access to the [rows] schema.

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

Done. Now row-level security has been implemented by transparently slipping it between the sProcs and the tables.

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

Finally, here is a stored procure that I use to help me remember how much of this obscure security stuff works and interacts with itself (oops, corrected version of code):

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_

[更正的代码版本)

这篇关于SQL Server:如何授予架构权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！