Here’s an unexpected piece of Oracle behaviour that had me tearing my hair out last night until I figured out what was going on. It’s to do with object permissions as applied to views.
Suppose you have an oracle user called TABLE_OWNER who grants select access on his table to a role which all users have. One of those user, let’s call him VIEW_OWNER, creates a view based on that table and also grants select access on it to everybody.
Now, what happens when a third user attempts to select from the view? They get an “ORA-1031: Insufficient Privileges” error. Remember, they have select permission on both the view and the underlying table, so what’s happening?
The answer is that it’s Oracle security going a bit overboard. VIEW_OWNER has permission to see TABLE_OWNER’s table, but by creating a view of it and granting other users permission to view it, he’s effectively granting them select permission on the table. You need permission to do that, and VIEW_OWNER doesn’t have it. Curiously, this check is made when others try to select the view, rather than when the grant is made.
The solution is for TABLE_OWNER to do this:
GRANT SELECT ON my_table TO view_owner WITH GRANT OPTION;
That gives VIEW_OWNER permission to pass on the privilege, and thus other users permission to select from the view.