How we can Migrate Oracle Users, roles and grants to Azure PAAS postgresql database

Velmurugan 1

Hi Team,

Can you kindly let us know how we can migrate the on-premise Oracle database Users, roles and grants to Azure PAAS PostgreSQL database. Currently we are using Ora2pg tool to migrate the schemas/data. Kindly provide us any document to migrate the oracle users, roles and grants to Azure PAAS PostgreSQL, that would be greatly appreciated.

Thanks,

Velmurugan

11 answers

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-23T15:59:15.463+00:00

Hi, @Velmurugan Welcome to Microsoft Q&A, Thanks for posting your query.
As we understand the ask here is you want to know how to migrate Oracle users, roles, and grants to the Azure PAAS PostgreSQL database and also looking for documents.

Oracle users, roles, and grants to the Azure PAAS PostgreSQL database will be done automatically by ora2pg, if you use ./export_schema.sh it will export the whole schema together with roles and grants.

you can refer to these documents Ora2pg Moves database to PostgresSQL, Oracle to Azure Database for PostgreSQL: Migration guide | Microsoft Learn

Please let us know if this information helps.

Regards
Geetha
Please sign in to rate this answer.
Velmurugan 1 Reputation point

2022-03-24T06:28:01.53+00:00

Hi Geetha,

Thanks for your prompt response. Using ora2pg we can migrate only the grants but before that we need to migrate the users and roles. The documents which you shared doesn't have any steps to migrate the users and roles.

Regards,
Velmurugan

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-24T14:51:15.433+00:00

Hi @Velmurugan
We are sorry for not providing all the details we are checking with the internal team and provide you with more details.

Regards
Geetha

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-25T02:23:53.327+00:00

Hi @Velmurugan As per the internal team ./export_schema.sh will migrate also the roles and grants if the correct user with dba privileges is used. there is a GRANT type among many others so you can do it separately also by using this command:
ora2pg -p -t GRANT -c ./config/ora2pg.conf
and USER_GRANTS must be set to 0.
This is mentioned in the above document.

You can find the workshop link for more information.

Please let us know if you are looking for additional information.

Regards
Geetha

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-25T14:31:52.64+00:00

Hi @Velmurugan Here is the query to extract user roles from Oracle

-- extract oracle users

WITH user_role_list AS
(SELECT username as user_role FROM dba_users
WHERE username
IN ('BI','HR','OE','PM','IX','SH','SCOTT'))
SELECT user_role
FROM user_role_list;

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-25T14:33:30.957+00:00

Continuation ...
-- extract roles and grants
WITH user_role_list AS
(SELECT role as user_role FROM dba_roles
WHERE role
IN ('CONNECT','RESOURCE','SELECT_CATALOG_ROLE')
UNION
SELECT username as user_role FROM dba_users
WHERE username
IN ('BI','HR','OE','PM','IX','SH','SCOTT'))
SELECT 'CREATE ROLE '||role as role_text
FROM dba_roles r
WHERE r.role
IN (SELECT rl.user_role FROM user_role_list rl)
UNION ALL
SELECT 'GRANT '||rrp.granted_role||' TO '||rrp.role as role_text
FROM role_role_privs rrp
WHERE rrp.granted_role
IN (SELECT rl.user_role FROM user_role_list rl)
AND role
IN (SELECT rl.user_role FROM user_role_list rl)
UNION ALL
SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||role as granted_role
FROM role_tab_privs
WHERE role
IN (SELECT rl.user_role FROM user_role_list rl)
UNION ALL
SELECT 'GRANT '||privilege||' TO '||grantee as grant_privileges
FROM dba_sys_privs
WHERE grantee
IN (SELECT rl.user_role FROM user_role_list rl)
UNION ALL
SELECT 'GRANT '||privilege||' ON '||owner||'.'||table_name||' TO '||grantee as grant_privileges
FROM dba_tab_privs
WHERE grantee IN (SELECT rl.user_role FROM user_role_list rl)
AND grantor IN (SELECT rl.user_role FROM user_role_list rl)

GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-25T14:34:06.607+00:00

Continuation ..
UNION ALL
SELECT 'GRANT '||granted_role||' TO '||grantee as grant_privileges
FROM dba_role_privs
WHERE grantee IN (SELECT rl.user_role FROM user_role_list rl)
AND granted_role IN (SELECT rl.user_role FROM user_role_list rl)
UNION ALL
SELECT 'GRANT '||privilege||' TO '||role as grant_privileges
FROM role_sys_privs
WHERE role IN (SELECT rl.user_role FROM user_role_list rl);

Create a schema in Postgres
create schema XYZ; you can refer to this document for schema

createuser --nologin XYZA; you can refer to this document for users

Please let us know if this information helps

Regards
Geetha
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Velmurugan 1 Reputation point

2022-03-28T09:31:43.363+00:00

HI GeethaThatipatri-MSFT,

My configuration file looks different than what you have shared, In my config file grant means it is just showing as Export grants but in yours it is showing as Export roles converted to Pg groups, users and grants on all objects.

Can you please let us know where we are missing here. PFB

Type of export. Values can be the following keyword:

TABLE Export tables, constraints, indexes, ...

PACKAGE Export packages

INSERT Export data from table as INSERT statement

COPY Export data from table as COPY statement

VIEW Export views

GRANT Export grants

SEQUENCE Export sequences

TRIGGER Export triggers

FUNCTION Export functions

PROCEDURE Export procedures

TABLESPACE Export tablespace (PostgreSQL >= 8 only)

TYPE Export user defined Oracle types

PARTITION Export range or list partition (PostgreSQL >= v8.4)

FDW Export table as foreign data wrapper tables

MVIEW Export materialized view as snapshot refresh view

QUERY Convert Oracle SQL queries from a file.

KETTLE Generate XML ktr template files to be used by Kettle.

DBLINK Generate oracle foreign data wrapper server to use as dblink.

SYNONYM Export Oracle's synonyms as views on other schema's objects.

DIRECTORY Export Oracle's directories as external_file extension objects.

LOAD Dispatch a list of queries over multiple PostgreSQl connections.

TEST perform a diff between Oracle and PostgreSQL database.

TEST_COUNT perform only a row count between Oracle and PostgreSQL tables.

TEST_VIEW perform a count on both side of number of rows returned by views

TEST_DATA perform data validation check on rows at both sides.

TYPE TABLE,COPY,VIEW,GRANT,SEQUENCE,TRIGGER,PROCEDURE
Please sign in to rate this answer.
GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-29T00:54:26.967+00:00

Hi @Velmurugan This is how you do it: ora2pg -p -t GRANT -o grant.sql -b c:\confirmo\confirmo_mig\schema\grants -c c:\confirmo\ora2pg.conf

Can you please share your config file so I can assist you, also did you get a chance to check the query which I shared.

Regards
Geetha

Velmurugan 1 Reputation point

2022-03-29T06:27:18.257+00:00

187765-ora2pg-conf.txt

Attached the conf file for your reference. Kindly check. Here my grants is showing as only export grants

TABLE Export tables, constraints, indexes, ...

PACKAGE Export packages

INSERT Export data from table as INSERT statement

COPY Export data from table as COPY statement

VIEW Export views

GRANT Export grants
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
GeethaThatipatri-MSFT 29,552 Reputation points Microsoft Employee Moderator

2022-03-29T11:48:12.29+00:00

Hi @Velmurugan , I have updated the 187840-oracle-to-postgres.txt step-by-step instructions in the attached document please follow the instructions and let us know if you have any further queries.
Happy to assist you to resolve your issue.

Regards
Geetha`
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Velmurugan 1 Reputation point

2022-03-30T07:59:43.247+00:00

Hi @GeethaThatipatri-MSFT

I have tried the document which you have provided but in vain. I'm attaching the config file and grants file which generated for your reference.

188278-ora2pg-conf.txt

Grants output: (not able to attach more than one attachement, so pasting it)

[root@databasepocserverrep grants]# cat grant.sql
-- Generated by Ora2Pg, the Oracle database Schema converter, version 23.0
-- Copyright 2000-2021 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=databasepocserver;sid=orapgdb;port=1521

SET client_encoding TO 'UTF8';

\set ON_ERROR_STOP ON

-- Nothing found of type GRANT

[root@databasepocserverrep grants]#
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Velmurugan 1 Reputation point

2022-03-30T08:01:49.16+00:00

@GeethaThatipatri-MSFT

Still I didn't receive the answer why my config file type is looking different than yours? if you look at the attachment, grans are showing just export grants.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How we can Migrate Oracle Users, roles and grants to Azure PAAS postgresql database

11 answers

Type of export. Values can be the following keyword:

TABLE Export tables, constraints, indexes, ...

PACKAGE Export packages

INSERT Export data from table as INSERT statement

COPY Export data from table as COPY statement

VIEW Export views

GRANT Export grants

SEQUENCE Export sequences

TRIGGER Export triggers

FUNCTION Export functions

PROCEDURE Export procedures

TABLESPACE Export tablespace (PostgreSQL >= 8 only)

TYPE Export user defined Oracle types

PARTITION Export range or list partition (PostgreSQL >= v8.4)

FDW Export table as foreign data wrapper tables

MVIEW Export materialized view as snapshot refresh view

QUERY Convert Oracle SQL queries from a file.

KETTLE Generate XML ktr template files to be used by Kettle.

DBLINK Generate oracle foreign data wrapper server to use as dblink.

SYNONYM Export Oracle's synonyms as views on other schema's objects.

DIRECTORY Export Oracle's directories as external_file extension objects.

LOAD Dispatch a list of queries over multiple PostgreSQl connections.

TEST perform a diff between Oracle and PostgreSQL database.

TEST_COUNT perform only a row count between Oracle and PostgreSQL tables.

TEST_VIEW perform a count on both side of number of rows returned by views

TEST_DATA perform data validation check on rows at both sides.