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, ' -> '), ' -> ')) heirarchy
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, ' -> '), ' -> ')) heirarchy
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) heirarcy
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!

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

My Team’s Top Tips for Going Remote

Upgrading to Liferay DXP part #2 — working with modules UpgradeSteps

Finally available and accessible, but wrong way?

CS373 Fall 2020: Safin Kasturi

The Goods #52 / January 24, 2018

RoadMap: The Journey So Far

The Focus 4 Cloud Native in 2019

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
LearnWithSathya

LearnWithSathya

More from Medium

Creating the clone of “us.shein.com”

CS371p Spring 2022: Eric Dai

More on Conditional Statements

You probably know me, Mon Cheri.