Browser & Privileges

[D.]

Hi,

Can you tell me what privileges are required to be able to see, for example, packages and their bodies in the browser window if you are not the owner of the objects ?

The scenario:
DBA user owns the objects, and I have use DBA Views switched on but I cannot see any packages and bodies in the browser (All objects filter). I have "SELECT ANY DICTIONARY" privilege (grnated through a ROLE) and can see the package and body text when running a query against DBA_SOURCE. Would be good to know what SQL is being run to populate the browser window, so I can get the correct privilege.

Thanks,
D.

 

[Marco Kalter]

Another user's package bodies will only be visible if you have select privileges on the sys.dba_objects and sys.dba_source views. The "Use DBA views if available" preference (Tools > Preferences > Options tab page) needs to be enabled though. To debug another user's package bodies you additionally need the "create any procedure" system privilege. This is an Oracle Debug requirement.

In the Object Browser you need to select a filter that shows the objects of the other user (e.g. "All objects").

 

[D.]

Hi Marco,

Thanks for a prompt reply, but I have everything enabled like you said....

"Use DBA Views" switched on.
"All Objects" filter selected.
SELECT ANY DICTIONARY prvilege granted directly to me - I can select data from sys.dab_objects and sys.dba_source from a SQL window.

I've traced my session and noticed that the SQL to be executed in this particular database is using sys.ALL_OBJECTS (which returns only 'SYS' owned objects in our environment) and not DBA_OBJECTS as would be expected. Again, "Use DBA views" is switched on and it works fine in other environments. Is there some sort of pre-check that is being done to change to the query to use ALL_OBJECTS instead ?

Puzzled ...

D.

 

[Marco Kalter]

This would indicate that the DBA_OBJECTS view is not returned in the ALL_OBJECTS view for this user. The "Use DBA views if available" preference relies on the ALL_OBJECTS contents.

Can you check this?

 

[D.]

This is indeed the case. Only the PUBLIC SYNONYM row is returned from the ALL_OBJECTS view. In other environments the same query will return the VIEW row as well.

I guess I'll have to do a comparison with another environment to get the GRANTs set up properly. Problem is that the other envronments are uncontrolled and we have most privs granted, so will take some time to work out the exact combination. Cant quite understand why ALL_OBJECTS is doing what it does though. You don't happen to know which privs need to be granted to get this to work do you ?

Many thanks,
D.

 

[Marco Kalter]

I have no immediate clues why this could happen. The all_objects view should return all objects for which you are privileged. Does the dba_objects view include it?

 

[MartinSchm]

Hi,

If got a similar problem. I can see the package of a other user in my object browser, but if I select "View Spec & Body" just the spec is opened.
My user has EXECUTE and DEBUG privileges on this package and furthermore SELECT privileges on ANY DICTIONARY and ANY TABLE and of course CREATE priviledges in my schema.

My DBA gave me DBA rights temporarely but even that didn't work out.

Do I need other privileges ?

Furthermore, did you notice that if you at first select "View" for a package you get the package spec. If you than want to see the spec and the body you will have to close the spec window first before you can successfully use "View Spec & Body".

Bye
Martin

 

[D.]

OK, got round this by GRANTing SELECT privs to DBA_OBJECTS and DBA_SOURCE to my user explicitly (also works through a role). It seems SELECT ANY DICTIONARY priv doesn't allow ALL_OBJECTS view to see the DBA_OBJECTS view. Strange, but true!

Works OK now...

Thanks for help.

 

[Marco Kalter]

Originally posted by MartinSchm:
Do I need other privileges ?

Have you enabled the "Use DBA views if available" preference? (Tools > Preferences > Options tab page)

 

[MartinSchm]

No. But after I enabled the option it worked.
Thanks.

Martin