How do you set permissions for a schema that has objects that access other schemas?

Question

I have 2 circuits, and one of the objects in the first circuit must access the object in the other circuit. For instance:

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

In the above example, TestUser only has access to the "O" schema. Thus, the choice itself works fine, but since the view makes a choice from another “I” circuit, then it fails with an error:

SELECT permission was denied for the ITest object, the MyDB database, schema I.

To get around this, I can provide the “O” schema permission to access the “I” schema, but it doesn’t sound that way and looks like a circuit permission bypass.

What can be done? Am I doing all this wrong? What is the best practice in this scenario?

thank

UPDATE. db, , dbo, db . , dbo , db, db , . .

Answer 1

"" . . , .

, :

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 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;

, , . , , :

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;

:

• SQL Server
• SQL
• :

Answer 2

, , , . ( , - ) , . , , , , , , -, , . - .