Grants on Views

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.

Advertisements

One Comment on “Grants on Views”

  1. […] to all object privileges the original user had. I did not want to touch the original user, but as I explained before, I had no choice. I could have been more selective here, […]


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s