Oracle schema as intermediate filter

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

Hello,

   I would like to connect Delphix connectors not directly to Oracle schema owner of the objects

to profile. But I would like to connect to another Oracle schema without tables defined on it and

acting as intermediate schema granted with select privileges on the other Oracle schema owning the

objects to profile. I would like to grant "select_catalog_role" to that intermediate schema  to

enable selecting Oracle Catalg for meta-data definitition of objects to profile.

When i connect new rule-set with that  connector defined on oracle intermediate schema I get

"No tables found". Is there a solution regards this issue?



Photo of luigidep

luigidep

  • 622 Points 500 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,782 Points 4k badge 2x thumb
Hi,

It seems to be a correct behavior as the intermediate schema isn't owning nor have any tables object defined on it.

You just give the account the role to select on object that are outside of his scope (meaning the principal schema)

Can you be please more verbose on the need and why you are trying to do it this why and not only using the schema it self.

Regards,

Mouhssine
Photo of luigidep

luigidep

  • 622 Points 500 badge 2x thumb

Thank you for your availability.


There are  some tables on the principal schema that i haven't to profile. But uncheck them from the

list in the rule-set creation wizard seems to take too time and therefore be prone to errors. Since I

haven't to act directly on the schema owning those tables I would like to create an intermediate

schema with select grants only to tables that i have to profile.

Luigi



Photo of luigidep

luigidep

  • 622 Points 500 badge 2x thumb

And  what if i will define on the intermediate schema synonyms only on those tables on the principal schema that i have to profile?

Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,782 Points 4k badge 2x thumb
Hi


To be honest no idea on how the engine is fetching the table list.


May be it profiles only tables that you are owning (user_tables) and not those you have access to (all_tables).


But it's an idea to create synonyms and give it a try.


Regards,


Mouhssine
(Edited)
Photo of Mouhssine SAIDI

Mouhssine SAIDI

  • 4,782 Points 4k badge 2x thumb
Hi,

After checking out what the masking engine is sending to get out the list of tables to profile on schema, i can confirme you that it will look on all_tables so if the user is granted the select role on those tables you will see there up.

eg (tested on my lab env. ):

Select distinct table_name from all_tables where owner ='DELPHIXDB' order by table_name asc;


Regards,

Mouhssine
Photo of luigidep

luigidep

  • 622 Points 500 badge 2x thumb

Thank you very much.