Toad World® Forums

ORA-01039: insufficient privileges on underlying objects of the view


#1

from sys.all_objects

Using all_objects in pl/sql works fine. Please help.

Hello,

When sys.all_objects is used, I get an error “ORA-01039: insufficient privileges on underlying objects of the view”.

For example, scanning following simple SQL code gives the same error.

select *


#2

Hi,

Check out the “Database Privileges” topic in the online help. It explains all of the Oracle database privileges that are needed to use each module of Quest SQL Optimizer.

Rene


#3

Hello Rene,

I’m having a difficulty to find “online help” link. All I can see is forum help. Can you give me the link to online help?

Thank you.


#4

Never mind. I found it. Thanks,


#5

Hi,

The “ORA-01039: insufficient privileges on underlying objects of the view” message actually means that you don’t have privileges to access the BASE OBJECTS that used to build the view, not that you don’t have privileges to use the VIEW itself.

For example, if you have this view:

Create view User1.V1 as select * from User1.Table1

/
Grant all on User1.V1 to User2
/

and if you are logging in as User2, you have the privileges to access User1.View1 but not to access User1.Table1 directly.

Normally if you (as User2) run a SQL like this
Select * from User1.V1
you don’t need privileges to access the underlying object (i.e. User1.Table1). But in Oracle, if you tried to Explain Plan the same SQL, you would need privileges to access the underlying objects. If not, this is where this error came in.

This is the security control in Oracle to requrie privileges to access the underlying objects of a view when running the Explain Plan. To workaround, either grant the privlieges on all underlying objects of the view to the user doing the Explain Plan, or use a user with privileges to access all underlying tables (normally it will be the user who owns the view, in your case it will be SYS).

Hope I explained the issue OK. If you need more help, please feel free to let me know.

Thanks.