November 18, 2010 at 12:49 am
I have a table say salary to which i have denied select to a particular group say Support.
I create a view called getsalary which says select * from salary .
If any member say select * from salary he/she gets access denied message.
But if he/she says select * from getsalary view they are able to get the results.
how is so ?
the sql version is 2005
thanks
November 18, 2010 at 2:58 am
it's by design.
access to base objects is not ownership chained to views/proc/functions that access that object. so when accessing the view, permissions to the objects that view access is not validated...only the permissions on the view itself.
you can grant select to a view, and the user has no access to the base table underneath whatsoever.
similarly, you could grant EXECUTE to a proc, and the user has no access to the multiple tables the proc manipulates.
another example is that a user who has read only access, but can execute a procedure, and the procedure inserts/updates data.
it is a very common security scenario where a role is created that has access to just a handfull of objects that exist in the database, and never any of the base tables themselves.
if you do not want them to access the view, you'll need to remove their access to it, or deny access to specific columns in the view to hide sensitive data.
Lowell
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply