How to migrate users, roles and grants from Oracle to Azure Database for PostgreSQL using ora2pg
Introduction

This document highlights how to migrate oracle users and roles using ora2pg

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

Estimated Time 5 minutes

Pre-Requisites

ora2pg is already installed in either Linux or Windows computer/machine

Azure Database for PostgreSQL is provisioned

An Oracle user with DBA role is necessary to perform this task

Step 1: Create an ora2pg migration project
This is not a mandatory step. This is a best practice for organising the output script when running ora2pg projects

Let's create our project base for the migration:

On “cmd”, navigate to your ora2pg folder
cd c:\ora2pg
Run the command to create a migration template
ora2pg --project_base c:\demo -c c:\demo\ora2pg.conf --init_project demo_mig
Summary: In this step, you learnt how to create a project base for the migration project. Note that the path c:\demo has to be created manually.

Step 2: Set up the correct ora2pg config file parameters
Let's setup the ora2pg config file in order to enable users, roles and grants export from the Oracle database

first, set the connection parameters in the ora2pg file to connect to the desired Oracle database.

   ORACLE_HOME	C:\Users\corp\oracle\db_home
   ORACLE_DSN	dbi:Oracle:host=localhost;sid=myoradb;port=1521
   ORACLE_USER	system
   ORACLE_PWD	manager
After that, set the necessary grant extraction parameters. Make sure on the TYPE parameter, you include at least GRANT but it works with a list of objects as well.

   USER_GRANTS     0


   TYPE           GRANT
OR

   TYPE	TABLE PACKAGE VIEW GRANT SEQUENCE TRIGGER FUNCTION PROCEDURE TYPE PARTITION FDW MVIEW
Summary: The config file is ready.

Step 3: Run ora2pg to export users and grants to a file
- Run the command to create a script with the users, roles and grants

 ora2pg -p -t GRANT -o grant.sql -b c:\demo\demo_mig\schema\grants -c C:\demo\demo_mig\config\demo_conf.conf	
Summary: Navigate to c:\demo\demo_mig\schema\grants and look for grant.sql file.

Step 4: Run grants.sql against Azure Database for PostgreSQL
psql -h lab8381412.postgres.database.azure.com -p 5432 -U dummydb@lab8381412 -d lab303
Password is YYYYYYY

\o 'C:/demo/demo_mig/schema/grants/grants.log'
\i 'C:/demo/demo_mig/schema/grants/grants.sql'
\q
Summary: The users, roles and permissions are now successfully created in Azure Database for PostgreSQL