How do you set permissions on a schema that has objects accessing other schemas?

Question

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

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:

The SELECT permission was denied on the object 'ITest', database 'MyDB', schema '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?

Thanks

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

Answer 1

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:

• Securing SQL Server
• SQL Server Permission Hierarchy
• White Paper: Security Overview for Administrators

Answer 2

Hey my first post! Surely you give permissions to users not objects and that's it. If you or the owner of the other schema wants to allow other users to access objects (whether they are tables, views or whatever) within it then it is up to the owner of the other schema. Just because you as the developer can write a procedure that accesses objects in other schemas it does not follow that anyone running your procedure should be allowed to do so too. Roles are the way to go.