CODEWITHSUNDEEP
sqlsql-servert-sql
gfreeman
gfreeman

Reputation: 143

How to allow a view to read from multiple schemas

I have two tables in different schemas, one owned by dbo, the other not. I have a third schema where I'm trying to create a view that reads from those two tables. My understanding is that if dbo is the owner of the view, because it has grant options to all objects in the database, users with only access to the view should be able to query it. But that isn't working, even when I explicitly grant dbo SELECT WITH GRANT to the underlying table in the schema it doesn't own.

I've read a few other posts, but don't see an answer that works: https://stackoverflow.com/a/4134892/1499015, Ownership Chaining and Tutorial: Ownership Chains and Context Switching

I assume I'm doing something wrong with permission chaining, but I can't figure out what the problem is.

--use master
--GO
--create login testuser with password='******************'
-- principal3 will own schema3
--create login principal3 with password='*****************'

--use testingDB
--GO
create user testuser for login testuser
create user principal3 for login principal3
GO
-- testrole that will only have select permissions on schema2
CREATE ROLE testrole
exec sp_addrolemember 'testrole', 'testuser'
GO

-- the first schema is owned by dbo
CREATE SCHEMA schema1 authorization dbo
GO
-- the schema where the view will live, also owned by dbo
create schema schema2 authorization dbo
GO
-- schema3 owned by principal3
create schema schema3 authorization principal3
go
-- tables in schema1 and schema3
CREATE TABLE schema1.testtable1 (
    id int
    , testvalue sysname
)
GO
CREATE TABLE schema3.testtable3 (
    id int
    , testvalue sysname
)
GO
-- some base data
INSERT schema1.testtable1
SELECT 1, 'a'
UNION SELECT 2, 'b'
UNION SELECT 3, 'c'

INSERT schema3.testtable3
SELECT 1, 'hot dogs rule'
UNION SELECT 2, 'pizza sucks'
UNION SELECT 3, 'haw just kidding that''s obviously backwards'
GO

-- view in schema2 that queries tables in schema1 and schema3, owned by dbo
CREATE VIEW schema2.testview
AS 
SELECT t1.testvalue as v1
    , t3.testvalue as v3
FROM schema1.testtable1 t1
    INNER JOIN schema3.testtable3 t3 ON t1.id = t3.id
GO

GRANT SELECT ON schema::schema1 TO dbo WITH GRANT OPTION
GRANT SELECT ON schema::schema3 TO dbo WITH GRANT OPTION

ALTER AUTHORIZATION ON schema2.testview TO dbo

-- give testrole permission to query the view
GRANT SELECT ON schema2.testview to testrole
GO

EXECUTE AS USER = 'testuser';
SELECT USER_NAME();  
BEGIN TRY  
    select top 100 * from schema2.testview
END TRY  
BEGIN CATCH  
    SELECT   
        ERROR_NUMBER() AS ErrorNumber  
       ,ERROR_MESSAGE() AS ErrorMessage;  
END CATCH  
REVERT;  
SELECT  USER_NAME();

When I run this code, I get

The SELECT permission was denied on the object 'testtable3', database 'testingDb', schema 'schema3'.

I've also tried to create a schema owner user for the schema that the view lives in, and granting that user select with grant to the underlying tables, but it's still not working.

Any ideas what I'm doing wrong? Thanks!

Upvotes: 1

Views: 2713

Answers (1)

Razvan Socol
Razvan Socol

Reputation: 5694

The documentation says:

When an object is accessed through a chain, SQL Server first compares the object's owner to the owner of the calling object (the previous link in the chain). If both objects have the same owner, permissions on the referenced object are not checked. Whenever an object accesses another object that has a different owner, the ownership chain is broken and SQL Server must check the caller's security context.

In this case, when the object testtable3 is accessed, SQL Server first compares testtable3 owner to the owner of the testview. If both objects have the same owner, permissions on the referenced object are not checked. It does not matter if you GRANT dbo rights on testtable3, even WITH GRANT (he this right anyway). It matters that the owner of testtable3 is the same as the owner of the testview.

For example, try:

ALTER AUTHORIZATION ON SCHEMA::schema3 TO dbo

And it will work.

If the owners are different, the ownership chain is broken and SQL Server must check the caller's security context. In this case: does testuser have rights to select from testtable3?.

Therefore, if the owners are different you need to do this to make it work:

GRANT SELECT ON schema3.testtable3 TO testuser

Upvotes: 1

Related Questions