Security : SQL Server Object Access From One Schema to another schema

Question

Scenario: I have denied permissions from table1 for USER1. DENY SELECT,INSERT,UPDATE,DELETE ON OBJECT :: dbo.table1 TO USER1

Instead, I have allowed access to a view(vw_table1) which will selects from table1 and filter some data using where clause. The idea is, users won't be able to see entire data of the table. To support insert, update and delete, I have created instead of triggers.

The above scenario works fine when Table1 and VW_table1 are on same schema.

Now, I have a requirement to move the view only in a different schema (sec.vw_table1). After moving the view when I do "select * From sec.vw_table1" It gives access denied error when the view tries to access dbo.table1.

How can we achieve this in SQL Server? I don't want to give access to dbo.Table1 to User1. But should work when it is called from sec.vw_table1.

Any help would be highly appreciated!!

Answer 1

The concept you want to understand is called "ownership chaining"

As long as the owner of the object and database remain the same, SQL Server will not re-evaluate the permissions of a user after the 1st object in the chain.

In your first example you denied USER1 access to TABLE1 but allowed access to VW_TABLE1. Since USER1 has access to VW_TABLE1 and the ownership chain has not been broken, they can see TABLE1 because permissions are not re-evaluated inside the VIEW.

In your second example I'm assuming the SEC schema must have a different owner which causes SQL Server to re-evaluate permissions when inside the VW_TABLE1 view.

In short, to fix this problem make sure all objects have the same owner

Answer 2

Since the objects are in separate schemas, it's likely you're missing a REFERENCES clause. Try the following:

GRANT SELECT, REFERENCES ON OBJECT::sec.vw_table1 TO [USER1];

If you'd like to grant the user access to the entire sec schema and, conversely remove his access to dbo, it would be:

DENY SELECT, INSERT, UPDATE, DELETE ON SCHEMA::dbo TO [USER1];

GRANT SELECT, REFERENCES ON SCHEMA::sec TO [USER1];

If the view SELECT still fails, you could try stripping the users' permissions on all applied objects and schemas using REVOKE. Since DENY takes precedence over GRANT, it can make things confusing. REVOKE would trump both, and give you a fresh slate onto which you can begin reapplying the permissions above.