Get user creation metadata and all roles/privileges granted to the user

Requirement:

Create a database user in another DB based on the user available in the source DB with the same object/DB level grants.

DB Version: 11.2.0.2.0

Steps

1. Find the User creation script from source database

Extract the DDL used for the user creation using dbms_metadata

SQL> select dbms_metadata.get_ddl('USER','MYUSER') from dual;

DBMS_METADATA.GET_DDL('USER','MYUSER')

---------------------------------------------------------

   CREATE USER "MYUSER" IDENTIFIED BY VALUES 'S:2526C5A32BD6A196B2A

2F2CC6F5E88D4A5932593709E4CC838BFDC036C27;C84965A9EC7311F4'

      DEFAULT TABLESPACE "USERS"  TEMPORARY TABLESPACE "TEMP"

2. Get system privileges granted to the user using dba_sys_privs

SQL> select PRIVILEGE from dba_sys_privs where GRANTEE='MYUSER';

PRIVILEGE

----------------------------------------

UNLIMITED TABLESPACE

3. Get the roles granted to the user using dba_role_privs

SQL> select GRANTED_ROLE from dba_role_privs where GRANTEE='MYUSER';

GRANTED_ROLE

------------------------------

CONNECT

DBA

4. Get all object grants of the user using dba_tab_privs

SQL> select OWNER,TABLE_NAME,PRIVILEGE from dba_tab_privs where GRANTEE='MYUSER';

no rows selected