AWS enables use of SQL to map users, roles, and grants from Oracle to PostgreSQL

Amazon Web Services (AWS) released Monday latest version of the AWS Schema Conversion Tool (AWS SCT), to make the transition to open source relational database management system (RDBMS) databases. 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 users can convert it on their own, often with suggestions.

For complete end-to-end migration, users need to create and prepare a few items in the target database. Items like users, roles, and grants that have to be re-created or reproduced on the target can be a challenge if the user 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.

AWS SCT provides a project-based user interface to automatically convert the database schema of source database into a format compatible with the target Amazon RDS instance. If schema from the user’s source database can’t be converted automatically, AWS SCT provides guidance on how to create equivalent schema in the target Amazon RDS database.

In addition to converting existing database schema from one database engine to another, AWS SCT provides certain additional features that help move data and applications to the cloud. Users can use data extraction agents to extract data from their data warehouse to prepare to migrate it to Amazon Redshift. To manage the data extraction agents, users can use AWS SCT by adopting data extraction agents. AWS SCT can also be used to create AWS DMS endpoints and tasks; run and monitor these tasks from AWS SCT.

In some cases, database features can’t be converted to equivalent Amazon RDS or Amazon Redshift features. The AWS SCT extension pack wizard can install AWS Lambda functions and Python libraries to emulate the features that can’t be converted. AWS SCT can also be implemented to optimize existing Amazon Redshift database. AWS SCT recommends sort keys and distribution keys to optimize the database.

Users can also adopt AWS SCT to copy existing on-premises database schema to an Amazon RDS DB instance running the same engine, and analyze potential cost savings of moving to the cloud and of changing the license type. It can also be implemented to convert SQL in C++, C#, Java, or other application code. You can view, analyze, edit, and save the converted SQL code.

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, users 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.

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 allows to map them to different users in the target database system if required. If users are to be named the same, copy over the source user to the target user and optionally change it to lowercase.

It’s not possible to transfer passwords from Oracle to PostgreSQL RDS. When re-creating 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 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. 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, the user can extract a list of all the permissions granted at the source. Roles and permissions must be applied after the schema and objects are created on the target database.

Leave a Reply

WWPI – Covering the best in IT since 1980