How to Migrate Oracle users,roles and grants to Azure PAAS postgresql

Question

Hi Team,

Kindly let me know how can I migrate the Oracle users, roles and grants from on-premise to Azure PAAS PostgreSQL database.

Regards,
Velmurugan

Answer 1

Hi, @Velmurugan Thanks for the ask in the Microsoft Q&A forum,
I see you have posted the same query in another post here I have responded to the post with a query to extract user roles and provided documents in Postgres to create the same.
Please let us know if you need any further information, we are happy to provide you with more information in solving your problem.

step 1: Create an ora2pg migration project

step 2: Set up the correct ora2pg config file parameters

step 3: Run ora2pg to export users and grants to a file

step 4: execute output file ("grants.sql") against Azure Database for PostgreSQL

Here is an example:

Go to oracle and run the following:

create role rl_read_hr;

grant select on hr.departments to rl_read_hr;
grant select on hr.countries to rl_read_hr;
grant select on hr.jobs to rl_read_hr;
grant select on hr.job_history to rl_read_hr;

grant rl_read_hr to hr_read;

re-run your ora2pg command, such as:
ora2pg -p -t GRANT -o grant.sql -b c:\demo\demo_mig -c c:\demo\demo_mig\config\demo_conf.conf

Please let me know if you find the above reply useful. If yes, do click on 'Mark as answer' link in the above reply. This will help other community members facing similar queries to refer to this solution.

Regards
Geetha

Answer 2

@ GeethaThatipatri-MSFT I need a quick resolution in this matter.

What are all the privileges we need to provide for the newly created oracle_user that we are using the configuration file?

For POC purpose, we used system user. But in reality a new user will be created, but not sure what are the privileges need to granted for that user in order to access the db objects.

Regards,
Velmurugan

Answer 3

@Velmurugan Welcome back to the Q&A forum,
Normally DBA role will do, Grant DBA to oracle_user;

Please let me know if this information is helpful.
Regards
Geetha

Answer 4

@GeethaThatipatri-MSFT , Thanks for you response.

Client is not ready to provide the DBA role or the system user.

They are requesting us to provide the privileges, so that they can grant it to the newly created user. Your support will be appreciatable.

Regards,
Velmurugan

Answer 5

@GeethaThatipatri-MSFT any update on the below ask?

Regards,
Velmurugan