Use SQL to map users, roles, and grants from Oracle to PostgreSQL

July 2023: This post was reviewed for accuracy.

Many customers are undertaking the journey to open source relational database management system (RDBMS) databases. With the latest release of the AWS Schema Conversion Tool (AWS SCT), it’s easy to change your database engine. AWS SCT makes heterogeneous database migrations easier by automatically converting procedural code from the source SQL dialect into the target SQL dialect. Any code that the tool can’t convert automatically (due to incompatible constructs between SQL dialects) is clearly marked so that you can convert it yourself, often with suggestions.

A few weeks ago, we featured a blog post Introducing AWS Schema Conversion Tool Version 1.0.502 by Eran Schitzer that can help you get started. You can also get started with SCT by following the steps in the AWS documentation to create a project, connect to your source, and run an assessment report.

For a complete end-to-end migration, you need to create and prepare a few items in your target database. Items like users, roles, and grants that have to be re-created or reproduced on the target can be a challenge if you don’t use a repository to store that information. This post outlines a method for extracting users, grants, and roles from an Amazon EC2 Oracle instance and applying them to an Amazon RDS for PostgreSQL instance. You can download the code used in this blog post from this repository.

Create a target database

In preparing for migration, you first create a target RDS PostgreSQL database and establish security groups for connectivity.

Create an Amazon RDS target
Create a target RDS PostgreSQL database in your VPC. You can find a complete set of instructions in the Amazon RDS User Guide. Be sure to attach security groups with adequate database ingress and egress rules for your client machine to the new RDS instance. My EC2 Oracle instance is on the default port 1521, and my RDS PostgreSQL instance is on the default port 5432. I’m using the same security group for both instances. I restricted traffic inbound to those ports from my client but allowed all local traffic within the security group itself.

Required permissions
Now that your target database is up and running, turn to your source database to extract the list of users, roles, and grants. To execute the SQL scripts that extract the users, roles, and grants from your Oracle database, the connected user must be able to connect to the database and select from the data dictionary.

On the PostgreSQL RDS side, the user must be able to create within the database.

Extract users, roles, and grants from the source

Users and roles are not part of AWS SCT extraction and must be applied using separate scripts. To begin that process, extract the list of users, roles, and grants from the source database using the following script. Although role and grant syntax is slightly different between Oracle and PostgreSQL, you can use the power of the SQL engine to extract and remap the users, roles, and grants to PostgreSQL by extracting a list from the source Oracle database.

Users
To extract the list of existing users from the database, I can query the Oracle dictionary table DBA_USERS and include only the users that I want to re-create on the target.

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;

I spooled the output of the query from SQL*Plus in the previous example as a single list of user names in an output file named oracle-source-user-mapping.csv.

User mapping
User names in PostgreSQL are case insensitive if they are mapped to lowercase names and no quotes are provided during the create. Extracting a list of users from the source database system lets you map them to different users in the target database system if required. If users are to be named the same, simply copy over the source user to the target user and optionally change it to lowercase. In my case, I do want to transform the names slightly for my users.

I added a column for the destination user/role and prepended the names with svc_ to denote that they are service related.

A note about passwords
It’s not possible to transfer passwords from Oracle to PostgreSQL RDS. When you re-create the user on the PostgreSQL target, assign a new password to the user. Either use SQL to extract the create users/roles DDL from the mapping table in the target PostgreSQL database, or manually re-create the users.

Roles and grants
Roles and permissions that exist on the source system can be applied to the target database. Permissions granted to roles, permissions granted directly on objects, and system permissions have to be extracted from the source system before they can be syntactically transformed and applied to the target. The following script generates output that contains the relevant permissions and roles.

There are two main principals in Oracle that can hold permissions, users, and roles. By examining the dictionary and extracting all permissions related to those principals, you can extract a list of all the permissions granted at the source.

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)
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);

In this example, I spooled the output of the query from SQL*Plus as a set of grant statements in an output file named oracle-grants.txt.

Prepare the target database

Before transferring your users and roles to the target RDS PostgreSQL database, you will need to complete a few more tasks to ensure that the process goes smoothly.

Create tables and functions
To make translating the users and roles easier, create a few helper tables and functions on your target RDS PostgreSQL database. The scripts create one schema, three tables, and one function. The schema is created to contain all the objects and functions to ensure easy cleanup.

masterschema: A schema under which to create the tables and functions.

CREATE SCHEMA masterschema;

user_mapping: A table to hold the output of the user mapping from the previous section for use in assigning proper roles and permissions.

CREATE TABLE masterschema.user_mapping
(source_user varchar(100),
 target_user varchar(100));

roles_and_grants: A table to hold statement text to create roles and grant permissions from the source database. This DDL is transformed to apply to the target database user/role community.

CREATE TABLE masterschema.roles_and_grants
(statement_text varchar(512));

error_on_apply: A table to hold exception information and the DDL that was executed that generated the error. It allows the user to review any errors and manually adjust if required.

CREATE TABLE masterschema.error_on_apply
(procedure_datetime timestamp,
 erred_statement_text varchar(512),
 error_text text);

transform_grants_and_apply: A function that reads the data in the roles_and_grants and user_mapping tables, replaces source users with target users in the statements, and then applies the DDL to the target database. The function captures errors if they occur when applying the DDL to the table error_on_apply.

-- create this function in target postgreSQL database
CREATE OR REPLACE FUNCTION masterschema.transform_grants_and_apply()
  RETURNS text
  SECURITY DEFINER
  AS
  $$
DECLARE
-- create a cursor to iterate over the stataments
tab_cur CURSOR FOR
  SELECT statement_text
    FROM masterschema.roles_and_grants;
-- variable to hold the output statement_text
out_statement varchar(512);
-- variable to datetime of the proc running
v_procedure_datetime timestamp;
-- kept the original variables from the procedure
old_name1 varchar(100);
new_user varchar(100);
BEGIN
  v_procedure_datetime := now();
  -- loop through the table to replace the syntax
  FOR tab_rec IN tab_cur LOOP
    -- look for the name of the user or role in the table text
    IF position(' to ' IN LOWER(tab_rec.statement_text))>1 THEN
        old_name1 :=substr(tab_rec.statement_text,position(' to ' IN LOWER(tab_rec.statement_text))+4);
    ELSIF position(' from ' IN LOWER(tab_rec.statement_text))>1 THEN
        old_name1 :=substr(tab_rec.statement_text,position(' from ' IN LOWER(tab_rec.statement_text))+6);
    ELSE
        old_name1 := 'noanswer';
    END IF;
    -- reset new_user and out_statement to NULL
    new_user := NULL;
    out_statement := NULL;
    -- search for the target user by using the source user from the statment
    new_user := (SELECT LOWER(target_user) FROM masterschema.user_mapping
                 WHERE LOWER(source_user)=LOWER(old_name1));
    -- if you have a mapping then do a replace and execute the statment
    IF new_user IS NOT NULL THEN
        out_statement := replace(LOWER(tab_rec.statement_text),LOWER(old_name),LOWER(new_user));
        -- if no mapping exists, try to just execute the statement
    ELSE
        out_statement := LOWER(tab_rec.statement_text);
    END IF;
    BEGIN
        EXECUTE out_statement;
        EXCEPTION
        WHEN OTHERS THEN
            INSERT INTO masterschema.error_on_apply
                (procedure_datetime, erred_statement_text, error_text)
            VALUES
                (v_procedure_datetime, 'old: '||COALESCE(old_name, 'none-provided')||' new: '||COALESCE(new_user, 'none-provided')||' out_statement: '||COALESCE(out_statement, 'none-provided'), SQLERRM);
    END;
  END LOOP;
  RETURN 'completed: unapplied statements noted in masterschema.error_on_apply';
END;
$$
LANGUAGE 'plpgsql';

Load users and grants from source

If you are using the spool file, load the data from the oracle-source-user-mapping.csv file into the user_mapping table using the psql \copy command. For more information about the PostgreSQL copy command, consult the PostgreSQL documentation page.

\copy masterschema.user_mapping FROM ' oracle-source-user-mapping.csv' DELIMITER ',' CSV

Next you can extract the user creation DDL using the data from the masterschema.user_mapping table to create your users. The following SQL creates an identical password for all users. Although not ideal, it can make the process go smoother for systems with large numbers of users. Users must change their password the first time they connect.

SELECT 'CREATE USER '||target_user||' WITH PASSWORD ''newpassword'';'
  FROM masterschema.user_mapping;

The following shows the output of this query.

Next, grant your users enough privileges to be able to connect to the PostgreSQL database. GRANT ALL PRIVILEGES ON DATABASE grants the CREATE, CONNECT, and TEMPORARY privileges on a database to a role (users are referred to as roles in PostgreSQL). None of those privileges actually permits a role to read data from a table. The SELECT privilege on the table is required for that. The following command grants these privileges on the target database to your list of users. For more information about PostgreSQL privileges, see the PostgreSQL documentation.

SELECT 'GRANT ALL PRIVILEGES ON DATABASE <dbname> to '||target_user||';'
  FROM masterschema.user_mapping;

The following shows the output of this query.

Load data
Load the data from the saved file oracle-grants.txt into the table roles_and_grants using the psql \copy command.

\copy masterschema.roles_and_grants FROM ' oracle-grants.txt' DELIMITER ',' CSV

Apply roles

Roles and permissions must be applied AFTER the schema and objects are created on the target database. If you run the function to apply the permissions before the objects are created, the following errors result.

If you instead wait until after your objects are created using the AWS SCT or your scripts, the roles and grants apply easily.

Execute the function
Execute the function that transforms the grants and applies them to the target database.

SELECT masterschema.transform_grants_and_apply();

The following shows the output of the function.

Review and resolve errors
Review any errors and take any needed action.

SELECT * FROM masterschema.error_on_apply;

Examine the output of the errors carefully to determine whether you need to take further action on them. I have three similar errors that require action on my database.

Not all GRANTS transfer between the two engines, and some, like CREATE VIEW, are simplified to just CREATE ON SCHEMA. You can resolve errors like GRANT CREATE VIEW TO svc_hr by transforming the grant statement to GRANT CREATE ON SCHEMA hr TO svc_hr. Currently in PostgreSQL the CREATE right in a schema lets users create any object type. There’s no way to limit what objects they can create—functions, operators, operator classes, tables, views, etc.

Next steps

Now that you have finished transferring your users and roles, you’re ready to transfer your procedural code and schema objects using the SCT and AWS Database Migration Service.

About the Author

Wendy Neu has worked as a Data Architect with Amazon since January 2015. Prior to joining Amazon, she worked as a consultant in Cincinnati, OH helping customers integrate and manage their data from different unrelated data sources.