Grants on Views 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?

1. Select on user B’s view, select on user A’s table and execute on user A’s functions
2. Select on user B’s view
3. Select on user A’s table and execute on user A’s functions
4. Select on user B’s view, provided that user B has “with grant option”
5. 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.