September 13, 2007 at 5:59 am
If you create a view (as an administrator), and only the SELECT permission on that view to some user say BOB, then login as B, how can BOB modify the base tables that the view is based on, I know this can be done I just don't know the syntax. Keep in mind the view is just very simple and does not have anything in it that normally restricts a user from updating/inserting/deleting from the base tables (I'm talking about things like CHECK CONSTRAINT, aggregate functions, computations, etc).
The link I got my intel from is http://msdn2.microsoft.com/en-us/library/ms180800.aspx
but yeah that does not have any examples/syntax of how a restricted user (not an admin) can modify the base tables, having access only to the view, if anyone knows it would be very helpful.
September 13, 2007 at 10:57 am
GRANT UPDATE,INSERT,DELETE on ViewName to BOB
That will let the user "update,delete and insert" on the base table as long as the ownership chain is maintained.
* Noel
September 13, 2007 at 1:12 pm
Is that the only way though? what about if BOB only had a SELECT permission on the view?
And again whats the syntax to update the base table, does it just happen if you update the view or what?
September 13, 2007 at 1:22 pm
Only if you view meets the grounds for an updateable view and Bob has permission to update the view.
September 14, 2007 at 6:20 am
Can you re-state your question. I don't think I understand what it is you want Bob to be table to do. Select (only) from the view but modify on the base tables?
Or do you want Bob to have access to the view, not the base tables, but be able to modify data through the view (Which is what has been answered)
When you say modify are we talking DDL or DML.
September 14, 2007 at 6:25 am
I want him to have SELECT (only) from the view and modify base tables, but I too am now pretty sure he needs permissions such as UPDATE/INSERT/DELETE to be able to modify the view and hence the base tables.
We are talking about DML here.
September 14, 2007 at 6:34 am
Yes, you are correct, if you only grant select on the view the user cannot modify data in the view.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 14, 2007 at 11:12 am
I didn't test it, but I believe it will work. This should prevent those operations from the view, but will still allow them if they access the tables directly. Not sure why you would want to do this, but oh well.
DENY
INSERT,UPDATE,DELETE ON vwbob to BOB
September 14, 2007 at 4:06 pm
Totally agree, not sure why would you want that but only the poster "knows" ...
* Noel
September 17, 2007 at 6:59 am
Isn't the traditional solution to create Stored Procedures to update the base tables and give the user permissions to the SPs.
JimFive
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply