How to Rename an Oracle User in AWS RDS
Background & Overview
This document provides a step by step guide to renaming a user on AWS RDS. It is important to note, that this method is not sanctioned by Oracle and should NEVER be attempted on a production environment. Furthermore, this method does not always work as it does depend on the type of objects the user/schema owns.
Known Issues
This process may fail with the following issues
- ORA-42287: cannot rename user on whose type a table depends
Step-By-Step Guide
1. Set the following hidden parameter at the session level
- sqlplus / as sysdba
- SQL> alter session set "_enable_rename_user"=true;
2. Put the database into Restricted session mode by executing the RDS restricted session procedure
- SQL> exec rdsadmin.rdsadmin_util.restricted_session(p_enable => true)
3. Make sure all users are disconnected.
4. Rename the user
- SQL> alter user MyUser rename to MyNewUser identified by 'MyPassword';
5. Take the database out of Restricted session mode by executing the RDS restricted session procedure
- SQL> exec rdsadmin.rdsadmin_util.restricted_session(p_enable => false)
- quit
