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.

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

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还是存储的proc，以防万一有人觉得需要启发我...

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

• 我们不使用跨数据库所有权链接"
• 行级安全性是一招，无关紧要:我们不会在所有地方都使用它

推荐答案

我担心您对所有权链接的描述或概念不清楚，所以让我从以下内容开始:

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，用户无法使用这些priv来执行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.

为所有sProcs(和/或可能的任何安全视图)创建一个名为[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]模式的执行权限.现在，将所有用户添加到该角色.确保您的用户仅具有连接"权限，并且没有授予对任何其他架构(包括[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屋！