November 23, 2010 at 2:03 am
Hi. I read always that a way to simplify permission setting is to set the select permission on a way without an explicit select permission on the underlaying tables. In effect everything works fine, but only if the view and tables are in the same schema.
So, if we have:
Create View view1
as
select schema1.table1.a, schema1.table2.a
from schema1.table1 inner join schema1.table2
on schema1.table1.id = schema1.table2.id
SELECT ON OBJECT::view1 TO User1
you have no problem to see table1 and table2 data. But if the view definition is
Create View view1
as
select schema1.table1.a, schema2.table2.a
from schema1.table1 inner join schema2.table2
on schema1.table1.id = schema2.table2.id
you receive a non select permission error on schema2.table2. It makes me wonder that view granting chain breaks on multiple schemas: security semplification is a need above all inter different schema, I think. Obviously I can set the select permission on all schemas involved on view, but it's not the same thing: by setting this permission you'll be able to select all objects inside, not only the fields shown by the view.
Can someone add some details about this question, or something else that I didn't consider about this restrinction?
Thank you.
November 23, 2010 at 8:32 am
To maintain the ownership chain - you need to have those schemas owned by the same user. If both schemas are owned by dbo, you shouldn't have any problems with granting access through a view.
If the schemas are not owned by the same user - then the ownership chain has been broken and you would be required to grant access to each schema.
So, when you create the schemas you would use:
CREATE SCHEMA schema1 AUTHORIZATION dbo;
CREATE SCHEMA schema2 AUTHORIZATION dbo;
Now, when you create your view - create it in one of the schemas, or even a different schema that is also owned by dbo and the user wouldn't need explicit rights to the other schemas.
Lookup the topic in books online: ownership chains
Note: you can also setup cross-database ownership chaining, but you need to be aware of the consequences and potential threats of doing so.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
November 26, 2010 at 9:43 am
Thank you for answer. I already implemented the explicit granting and revoking 'cause the relationship between schema and user ownership was known, but this solution has some minus. Unfortunately schemas can't be owned by the same user, isolation is strictly required indeed by design: our application creates objects (tables, schemas, views, users) at run time, and destroies the same objects at dispose time. It seems to me that it would be good if views could avoid the complexity of an explicit granting, regardless of table schemas.
antonio
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply