Let me start with the set up that we have in our Oracle Database. We have schema named ABC where all the raw tables are created. For each table we create Synonyms under different owner say XYZ.
And we do use synonym split/resplit logic to work around loading/Reporting using 2 identical tables name table_x, Table_y.
So to get access to these tables, Instead of giving direct access to these tables, DBA's created a Schema Role say ABC_S role, and they added list of tables to be part of this schema Role that user want to see.
nd a Select grant was run on this role to individual user. So user can have access to the tables/corresponding synonyms.
But now th problem is, the user can see the tables, but not synonyms through Oracle SQL developer or other tool like Alteryx. What are missing? Are we missing any grants? Does the view on the synonyms need any additional grants?