Kaley's (mostly) Oracle Database Blog
Hi! I'm Kaley Crum, Oracle DBA.
Welcome to my (mostly) Oracle database blog.

Archive: Oct 2016

Query to Examine Blocking

 

See All Grants Necessary for Selecting From a View

Ran into a frustrating problem where schema “A” couldn’t select from a view that schema “B” owned.  This was because schema “B” referenced tables that were owned by schema “C,” and schema “B” was granted “SELECT” privileges only on those tables, where it should have been granted “SELECT…WITH GRANT OPTION.”

This is a bit like killing a fly with a sledgehammer, so it should be used with thoughtful consideration to the exact privileges that are being granted.  But this SQL should list out all the necessary grants for a user to be able to select from a view.  Just fill in the VIEW_OWNER, VIEW_NAME, and GRANT_SELECT_TO pieces.