Grants on ViewsPosted: August 7, 2008
I’ve been bitten by a well known, but not well documented issue with granting “select” privileges on views.
User A has three tables and two functions, user B has “select” privilege on the tables and “execute” privilege on the functions. User B created a view using these tables and functions, and he now wants to allow user C to select from this table. What privileges user C should have in order to select from the view?
- Select on user B’s view, select on user A’s table and execute on user A’s functions
- Select on user B’s view
- Select on user A’s table and execute on user A’s functions
- Select on user B’s view, provided that user B has “with grant option”
- Select on user B’s view given “with hierarchy option”
The correct answer is #4. You can grant a user “select” privilege on a view without giving him any privileges on the underlying tables and functions. This is actually a terrific feature, allowing for clever information hiding tricks. However this means that when user B grants “select” on his view to user C, he is actually giving him permission to view data from user A’s tables and also permission execute user A’s functions, so user B must have the ability to give user C these priviliges. Hence, “with grant option” is needed.
It makes a lot of sense that answer #1 would also work. After all, if user C already has permission on user A’s tables and functions, why not allow him to select from the view?
Unfortunately, this does not work. As I discovered while debugging “ORA-01031: insufficient privileges”. Incidentally, this is one of the very few ways to get “insufficient privileges” from a select statement. Usually Oracle prefers to pretend that the view does not exist, if a user with no privileges attempts to select from it.