********************************************************************************************
* Description: Retrieve Oracle db user's password and role, and replicate between databases
* Date: 05:40 PM EST, 11/29/2017
********************************************************************************************

		 
<1> Introduction: 
     |
     |__ o. Sometimes, application team may request creating the same user in production database with same password, privilege and roles as in test database.
            Basically, you need to retrieve the user creation DDL first, and apply the SQL statement accordingly.

			

			
<2> Retrieving user creation DDL. The password will be encrypted:
     |
     |__ SQL> select dbms_metadata.get_ddl('USER', 'DB_USERNAME') || '/' usercreate from dba_users;


                 CREATE USER "DB_USERNAME" IDENTIFIED BY VALUES 'S:DCFA5B4BB559561681E491489143868D1C39AE429736996EEF1DCF425BF4;523BB2628D7C049A'
                 DEFAULT TABLESPACE "USERS"
                 TEMPORARY TABLESPACE "TEMP"
                 PROFILE "EMERALIT_APPS"
                 /	 
	

	

<3> Retrieving the roles granted to database user:
     |
     |__ SQL> select grantee, granted_role, default_role from dba_role_privs where grantee='DB_USERNAME';
	
	
                 GRANTEE                        GRANTED_ROLE                   DEF
                 ------------------------------ ------------------------------ ---
                 DB_USERNAME                    CONNECT                        YES
                 DB_USERNAME                    TPEC_READONLY_ROLE             YES
                 DB_USERNAME                    W3C_READONLY_ROLE              YES