January 26, 2006 at 9:34 am
I have permissions issue.
I have USER1.TABLE1 for which USER1 has granted Select,Insert,Update,Delete to USER2 with grant option.
I have USER2.VIEW1 derived from USER1.TABLE1. USER2 has granted Select,Insert,Update & delete to USER3.
Now, I login as USER3 and query as Select * from USER2.VIEW1.
I get permission denied ,owner 'USER1'
How can I make this work?
Haritha
January 26, 2006 at 9:55 am
You have an ownership chaining problem. If the objects are owned by the same user, say both the view and the table, you only need to give permissions on the view. However, when they are owned by different people (as is the case here), permissions must be granted on both objects. Therefore, USER3 needs SELECT permissions on USER1.TABLE1.
K. Brian Kelley
@kbriankelley
January 26, 2006 at 9:57 am
Thanks for the reply. I found out that just now. Do you think this has been changed in SQL 2005.
January 26, 2006 at 10:03 am
No. Ownership chaining still exists in SQL Server 2005. There are features for impersonation on stored procedures and the like, but SQL Server 2005 still does a security check if the ownership from one object to another changes.
K. Brian Kelley
@kbriankelley
January 26, 2006 at 2:03 pm
If anything this problem is worse in 2005. With the user/schema separation, you will have to plan and think about security much more than previously.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
January 26, 2006 at 2:04 pm
Actually, I shouldn't have used the term "problem" as it is a "feature" that really does increase flexibility, but that power does come at a cost.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply