Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 3 down vote favorite

I have a table Table_A and a view View_A that is a select of the contents of Table_A.

If I deny SELECT on Table_A to a user, the user can still use the view View_A.

Is there a way that I can force the view to return an error when the user tries to use it?

The real world case here is a database with about 20 tables and hundreds of views. User access is logically tied to the tables so we are hoping to stop folks from using the views based on those tables.

share|improve this question

2 Answers

up vote 4 down vote accepted

You need to give the views a different owner than the tables so that the ownership chain is broken and the permissions are checked on the base objects.

share|improve this answer
Perfect, Martin - I knew there'd be some way to get around it! Thanks!! – JHFB Jul 10 '12 at 19:12
up vote 3 down vote

One of the advantages of views is that you can restrict access to base tables, but grant access via views. Sound like your system has it backwards.

You are going to have to deny user's access to the views.

share|improve this answer
+1 this is one of the selling points of views; I need to know if there's a work-around to force it in the other way... – JHFB Jul 10 '12 at 18:50

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.