Selecting From a View: ORA-01031: insufficient privileges

If you’re selecting from a view in a different schema, and you’re encountering ORA-01031: insufficient privileges, this is probably why:

• You’re logged in under schema “A”
• You’re trying to select from a view owned by schema “B.”
• The view in schema “B” contains tables owned by schema “C.”
• Schema “B” has SELECT privileges on the tables owned by schema “C” but not SELECT…WITH GRANT OPTION.

You can fix the error by using the query below to figure out which grants you need.

CAUTION: The below query is a bit like killing a fly with a sledgehammer, because it lists ALL the grants needed. Think before you grant; you don’t want to risk opening up security holes for your organization.

MORE CAUTION: If you have a busy database with lots of transactions, doing object grants during periods of heavy activity can cause contention in your database.

Query to Fix “ORA-01031: insufficient privileges” when selecting from a view

To use the below query, fill in the view owner (i.e. schema “B”), the name of the view, and who you want to grant the select to (i.e. schema “A,” or whoever is attempting to select from the view).

with cte
as
(
  select 'VIEW_OWNER_GOES_HERE' view_owner
       , 'VIEW_NAME_GOES_HERE' view_name
       , 'WHO_YOU_WANT_TO_GRANT_SELECT_TO_GOES_HERE' grant_select_to
  from dual
)
select 'grant ' || decode(referenced_type, 'TABLE', 'select', 'VIEW', 'select', 'execute') || ' on ' || referenced_owner || '.' || referenced_name || ' to ' || (select view_owner from cte) || ' with grant option;' sql
     , lower(ltrim(SYS_CONNECT_BY_PATH(referenced_owner || '.' || referenced_name, ' -> '), ' -> ')) hierarchy
from dba_dependencies
where referenced_owner <> (select view_owner from cte)
  and (referenced_owner, referenced_name) not in (('SYS', 'STANDARD'), ('SYS', 'DUAL'))
start with owner = (select view_owner from cte)
       and name = (select view_name from cte)
connect by nocycle owner = prior referenced_owner
       and name = prior referenced_name
----------
union all
----------
select 'grant ' || decode(referenced_type, 'TABLE', 'select', 'VIEW', 'select', 'execute') || ' on ' || referenced_owner || '.' || referenced_name || ' to ' || (select grant_select_to from cte) || ';' sql
     , lower(ltrim(SYS_CONNECT_BY_PATH(referenced_owner || '.' || referenced_name, ' -> '), ' -> ')) hierarchy
from dba_dependencies
where (referenced_owner, referenced_name) not in (('SYS', 'STANDARD'), ('SYS', 'DUAL'))
start with owner = (select view_owner from cte)
       and name = (select view_name from cte)
connect by nocycle owner = prior referenced_owner
       and name = prior referenced_name
----------
union all
----------
select 'grant select on ' || view_owner || '.' || view_name || ' to ' || grant_select_to || ';' sql
     , lower(view_owner || '.' || view_name) hierarcy
from cte
;

Note that this query is intended to resolve permissions errors with views in the aforementioned scenario. Just because you’ve received an ORA-01031 error doesn’t mean that what you’re experiencing is the error I’ve described. You may just need a grant on a table, or an underlying function.

If you encounter ORA-01031: insufficient privileges after running the grants generated by this query, please leave a comment below. I’m always looking to refine my blog posts, and I’d love to know more about your specific situation. Your feedback can help me spot errors and improve queries. Thanks so much!

Free Oracle SQL Tuning Guide

Checkout my FREE guide, 7 SQL Tuning Secrets You Can Use Immediately, Even If You’ve Never Tuned a Query In Your Life!

Get it here: tuningsql.com/secrets