1 person found this helpful
we don't know enough to know, for example, how is your readonly_role defined, exactly?
you can see the query we run to populate the tables node yourself, View > Log > Statements. review that for your user having the 'issue.'
This is how I am creating the role and user:
CREATE ROLE readonly_role;
CREATE USER ro_user
IDENTIFIED BY ro_user
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;
GRANT CREATE SESSION to ro_user;
GRANT readonly_role TO ro_user;
Thanks Jeff Smith for giving direction to look at the SQL logs.
Here is the SQL running in the background when refreshing "Tables" option. Looking at the SQL, it doesn't look like it is pulling "public" synonyms. If this is true, the only option to look at public synonyms (even for underlying object type of table) is to expand the "Public Synonyms" option in the left panel. Can anyone confirm if my understanding is correct? See comments for the 2 sqls below.
select * from (
SELECT o.OBJECT_NAME, o.OBJECT_ID ,'' short_name, NULL partitioned,
o.OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
FROM SYS.ALL_OBJECTS O
WHERE O.OWNER = :SCHEMA
AND O.OBJECT_TYPE = 'TABLE' -- no records returned as ro_user doesn't own any tables
SELECT OBJECT_NAME, OBJECT_ID , syn.SYNONYM_NAME short_NAME, NULL partitioned,
SYN.TABLE_OWNER OBJECT_OWNER, o.CREATED, o.LAST_DDL_TIME, O.GENERATED, O.TEMPORARY, NULL EXTERNAL
FROM SYS.ALL_OBJECTS O, sys.user_synonyms syn
WHERE syn.table_owner = o.owner
and syn.TABLE_NAME = o.object_NAME
and o.object_type = 'TABLE'
and :INCLUDE_SYNS = 1 -- no records returned as ro_user doesn't own any synonyms
Parameters: "SCHEMA"="ro_user", "INCLUDE_SYNS"=1
right, you need to create synonyms for those objects in the user account, then they'll be able to 'see' them as tables
That worked. Thanks!