What security needed to see objects in Oracle for a read-only user?

On Oracle 19c in dev I can see all of the objects which makes sense because I have full privileges there.

In test and prod on the other hand, where I am a read-only user, I can only see tables and views.

Using Toad I can see everything but not in DataGrip or DBeaver. I do have access to DBA_SOURCE so I can query for the procedure name and the source code but would rather have it display in the tree if possible.

What do I need in order to have the missing objects display?  Bonus points are given if this results in able to also see procedure text.

3 comments
Comment actions Permalink

Jimgust Please also check that you have granted with 'select any dictionary' & 'select_catalog_role'

0
Comment actions Permalink

I was denied access to the two privileges but I was tentatively granted "DEBUG ANY PROCEDURE", awaiting approval from dba manager.

That privilege added PACKAGE, PACKAGE BODY, PROCEDURE, and TYPE to the results of sys.ALL_OBJECTS. 

+------+---------------+-------+
|OWNER |OBJECT_TYPE |MYCOUNT|
+------+---------------+-------+
|SCH_CC|FUNCTION |3 |
|SCH_CC|INDEX |59 |
|SCH_CC|PACKAGE |12 |
|SCH_CC|PACKAGE BODY |10 |
|SCH_CC|PROCEDURE |296 |
|SCH_CC|TABLE |50 |
|SCH_CC|TABLE PARTITION|114 |
|SCH_CC|TYPE |8 |
|SCH_CC|VIEW |82 |
+------+---------------+-------+

The only thing it lacks is the synonyms.  I do have access to ALL_SYNONYMS but those details are not in ALL_OBJECTS.

select 'SYNONYMS' as OBJECT_TYPE
, count(1) MyCount
from sys.ALL_SYNONYMS s
where s.OWNER = 'SCH_CC'

/*
+-----------+-------+
|OBJECT_TYPE|MYCOUNT|
+-----------+-------+
|SYNONYMS |60 |
+-----------+-------+
*/

Can I force the schema refresh to use ALL_SYNONYMS? 

0
Comment actions Permalink

Edit: Created Issue https://youtrack.jetbrains.com/issue/DBE-13027

Turns out this is an issue with Oracle where ALL_OBJECTS doesn't contain private synonyms that were granted from another user.

https://support.oracle.com/knowledge/Oracle%20Database%20Products/2249527_1.html

I will create an issue to change the logic to a LEFT OUTER JOIN when capturing catalog details for synonyms.

select S.owner         as synonym_schema_name,
       O.object_id     as synonym_id,
       S.synonym_name  as synonym_name,
       O.created       as created_timestamp,
       O.last_ddl_time as modified_timestamp,
       S.db_link       as origin_db_link,
       S.table_owner   as origin_schema_name,
       S.table_name    as origin_object_name
from ALL_SYNONYMS S
    LEFT OUTER JOIN ALL_OBJECTS O
    ON    S.owner = O.owner
      AND S.synonym_name = O.object_name
WHERE S.owner in ( 'ADBASE' )
  AND coalesce(O.object_name, S.synonym_name) not like '%/%'
--   #inc> and (O.last_ddl_time is null or O.last_ddl_time >= :since)
order by O.object_id, S.synonym_name;

 

0

Please sign in to leave a comment.