0

I'm working with 2 Oracle 19C instances: Instance1 and Instance2

Users have certain roles and resulting privileges on tables in Instance1. I have a DB link from Instance1 to Instance2 and a schema in Instance 2 that has access to all tables for all users. In Instance2 I've created views in that 'all access' schema to all tables in Instance1.

I'm looking for the most straightforward way to ensure that users logging into Instance2 have the same privileges in Instance2 as they do in Instance1. In other words, they would only be able to access views in Instance2 to which they have access in Instance1 based on their roles in Instance1.

DBAs have provided me a view from Instance1 that provides the following fields: User, Role, Table_Name, Type (view/table), Privilege (Select/etc.)

Are there existing out-of-the-box or well-known/shared procedures/scripts that can take this view as input and create/update roles and run grant commands on views in Instance2 such that Instance1 and Instance2 are in sync and users only have access to Instance2 views that match their permissions in Instance1?

Thank you.

igb123
  • 1
  • While I'm certain people have worked out any number of custom approaches to this problem over the years, there is no out-of-the-box "official" or supported solution for replicating users/privileges between instances in the way you are describing that I'm aware of. – pmdba Aug 03 '21 at 14:43
  • If you have the same users in both DBs, are they [global users](https://docs.oracle.com/en/database/oracle/oracle-database/19/dbseg/configuring-authentication.html#GUID-E3F98B58-4087-4294-8368-D1F2B592DD69)? If so you could maybe have a public DB link defined with `connect to current_user`. The privs would then only need to be on instance1, not replkucated (I think - haven't actually tried this!). Not sure if it would quite do what you want anyway though. – Alex Poole Aug 03 '21 at 15:05

0 Answers0