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.
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.
|CREATE ON 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.
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.
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
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.
|Oracle Dictionary Views|
In this example, I spooled the output of the query from SQL*Plus as a set of grant statements in an output file named
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.
user_mapping: A table to hold the output of the user mapping from the previous section for use in assigning proper roles and permissions.
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.
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.
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.
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.
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.
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
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.
The following shows the output of this query.
Load the data from the saved file
oracle-grants.txt into the table roles_and_grants using the psql \copy command.
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.
The following shows the output of the function.
Review and resolve errors
Review any errors and take any needed action.
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.
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.