您如何在具有访问其他架构的对象的架构上设置权限? [英] How do you set permissions on a schema that has objects accessing other schemas?
问题描述
我有 2 个模式，第一个模式中的一个对象需要访问另一个模式中的对象.例如:
I have 2 schemas and one of the objects in the first schema needs to access an object in the other schema. For example:
CREATE VIEW I.ITest
AS
SELECT 1 as TestColumn
GO
CREATE VIEW O.OTest
AS
SELECT * FROM I.ITest
GO
EXEC ('SELECT * FROM O.OTest') AS USER = 'TestUser'
DROP VIEW O.OTest
DROP VIEW I.ITest
在上面的示例中，TestUser 只能访问O"架构.所以选择本身工作正常，但因为视图正在从另一个模式I"中进行选择，所以它失败并出现错误:
In the above example, TestUser only has access to the 'O' Schema. So the select itself works fine, but because the view is doing a select from another schema 'I' then it fails with the error:
对象ITest"、数据库MyDB"、架构I"的 SELECT 权限被拒绝.
The SELECT permission was denied on the object 'ITest', database 'MyDB', schema 'I'.
为了解决这个问题，我可以授予O"架构访问I"架构的权限，但这听起来不对，而且似乎绕过了架构权限.
To get around this I can give the 'O' schema permission to access the 'I' schema, but this doesn't sound right and looks to be bypassing the schema permissions.
能做什么?我做这一切都错了吗?这种情况下的最佳实践是什么?
What can be done? Am I doing this all wrong? Whats the best practice in this scenario?
谢谢
更新:我的架构由不同的 db 角色拥有，所以我通过简单地将两者的所有者更改为 dbo 然后授予 db 角色访问架构的权限来解决这个问题.这意味着所有者 dbo 可以看到所有内容，然后我只能向 db 角色授予特定权限，除非通过此模式，否则其余 db 对象不可用.感谢您的帮助
UPDATE: My schemas were owned by different db roles so I got around this problem by simply changing the owner of both to dbo and then giving the db role permission to access the schema. This meant that the owner dbo could see everything and I could then give specific permission to the db role only and the rest of the db objects were not available unless via this schema. Thanks for your help
推荐答案
您应该将来自其他"架构的视图中的数据选择包装在存储过程中.然后授予对存储过程的执行权限.尽管用户不能直接访问视图，但他们可以通过存储过程进行访问.
You should wrap the selection of data from the view on the "other" Schema, within a stored procedure. Then grant execute rights on the stored procedure. Although the user will not have direct access to the view they are permitted access via the stored procedure.
以下是一个示例演练，用于演示安全原则的工作:
Here is an example walkthrough for you demonstrating the security principles as work:
USE DATABASE SANDBOX;
--Create Logins
CREATE LOGIN UserOne WITH Password='Hello123';
CREATE LOGIN UserTwo WITH Password='Hello123';
--Create Database Users
CREATE USER UserOne;
CREATE USER UserTwo;
--Create the Test Schemas
CREATE SCHEMA SchemaOne AUTHORIZATION UserOne;
CREATE SCHEMA SchemaTwo AUTHORIZATION UserTwo;
--Create a View on SchemaOne
CREATE VIEW SchemaOne.ViewOne
AS SELECT 1 as TestColumn;
--Create a View on SchemaTwo
CREATE VIEW SchemaTwo.ViewTwo
AS SELECT * FROM SchemaOne.ViewOne;
--Test that the SchemaOne
EXEC('select * from SchemaOne.ViewOne') AS USER = 'UserOne'
--1
EXEC('select * from SchemaTwo.ViewTwo') AS USER = 'UserOne'
--The SELECT permission was denied on the object 'ViewTwo', database 'SANDBOX', schema 'SchemaTwo'.
--Create a stored procedure to safely expose the view within SchemaTwo to UserOne who's default Schema is
--SchemaOne.
CREATE PROCEDURE SchemaTwo.proc_SelectViewTwo
AS
select * from SchemaTwo.ViewTwo;
--Grant execute rights on the procedure
GRANT EXECUTE ON SchemaTwo.proc_SelectViewTwo TO UserOne;
--Test the
EXECUTE AS LOGIN='UserOne';
Exec SchemaTwo.proc_SelectViewTwo;
revert;
我的评论中建议的另一种方法是使用数据库角色来控制对多个模式的访问.使用上述解决方案中定义的主体，您可以像这样使用数据库角色:
An alternative approach as suggeted in my comments would be to use a Database Role to control access to multiple schemas. Using the principals as defined in the solution above, you could use Database Roles like so:
EXEC sp_addrole 'CrossSchemaRole';
EXEC sp_addrolemember 'CrossSchemaRole','UserOne';
GRANT SELECT ON SCHEMA::SchemaOne TO CrossSchemaRole;
GRANT SELECT ON SCHEMA::SchemaTwo TO CrossSchemaRole;
EXECUTE AS LOGIN='UserOne';
select * from SchemaTwo.ViewTwo;
revert;
有些人建议进一步阅读:
Some suggested further reading:
这篇关于您如何在具有访问其他架构的对象的架构上设置权限?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
