Can't View All Stored Procedures in List

[scottycope]

I'm using DBeaver version 21.2.3.202110151816.

I have a connection to our Oracle database and full access to schema A. This same account has read access to schema B for most objects. When I expand schema B, navigate down to the Procedures folder and expand that, I only see 3 procedures, not the entire list.

Is there an issue with how I set up my connection in that I can only see objects I can modify? If so, how can I configure it to list all objects that I have any sort of access to?

If it's not that, any other ideas on what I can try to correct this?

Thanks in advance!
Scott

[LonwoLonwo]

Are these procedures in the Procedures tab or in the Packages tab?

[p91paul]

Do you have execute grants on this procedures for you DWLOG user? Oracle usually allows you to see only the objects you've been granted for.

[scottycope]

Please check Query Manager. First, you need to enable all query properties and then for the first time after connecting open the "Procedures" tab. You will see a query for procedures reading. But this query is very simple, I don't know yet what could go wrong.

I enabled all properties like you said but still didn't see all of the procedures. Thanks for your insight.

[scottycope]

Do you have execute grants on this procedures for you DWLOG user? Oracle usually allows you to see only the objects you've been granted for.

p91paul, you may be on to something. I opened the procedures I could see in DBeaver using Oracle SQL Developer and confirmed that I did have execute privileges on those objects. Additionally, I opened some procedures I could see in Oracle SQL Developer and confirmed that I do NOT have execute privileges on those objects. Perhaps that is why I don't see them in DBeaver.

Then, I guess my next question is - if the ability to see objects that I don't have execute grants on in SQL Developer exists, why wouldn't DBeaver have similar functionality. Probably not a question for you, I know. Just thinking aloud.

Thank you for taking the time to respond.

[p91paul]

try going in connection settings, oracle properties, and check "always use DBA views".

long version: DBeaver defaults to using ALL_* views (e.g. ALL_TABLES, ALL_PROCEDURES) to get the object list. Those views can be read by all users without specific grants, but they do not show all the objects (despite the name): they are filtered to show only the objects the user is granted for.
Oracle provides other views, called DBA views (DBA_TABLES, DBA_PROCEDURES) that show all the objects and are much faster to query. However, by default only system users can access those views: usually normal users can't (and I guess that's why Dbeaver defaults to the other views). Your user might however be allowed to access them by your dba, and sql developer is probably smart enough to detect you are granted and use them. DBeaver can too, but requires you to check that option in preferences.

[scottycope]

@p91paul that did it! Thank you so much! This has been an issue for so long! Selecting "always use DBA views" did it.

I know this is slightly offtopic but is the Snowflake connection set up in DBeaver similar? I'm having a similar issue with not seeing Snowflake views. If it's too far off topic, I'll create another post.

Again, thank you so much!

[Headley76]

Hi all,

same problem here. Login in DBeaver with User that have the following grants:

SELECT ANY DICTIONARY
SELECT ANY SEQUENCE
SELECT ANY TABLE

Cant see PLSQL-Objects in the different schema.

Using same login with sql developer or TOAD for oracle I can see objects. Its not allowed to grant execute rights on these objects. But for support I need to see them.

Any ideas for fix this?

Greetings

[Headley76]

ok, I found the solution:

Connectionsettings -> Oracle -> "Always use DBA views"