Create Users with DBMS_METADATA

Not too long ago I wrote a blog on using DBMS_METADATA to extract DDL for tables, so when I got an assignment to migrate Users I immediately thought of using DBMS_METADATA to do this.

The User Migration would consist of all the users in the database being renamed following a new convention. Don't ask why they wanted this, they had some very good reasons to want so.
All the users had to be recreated along with all their privileges to their new name.
At first I thought of writing all sorts of queries against the datadictionary which sounded like a daunting task. On second thought it dawned to me that a CREATE USER statement is DDL and DDL can be extracted using DBMS_METADATA.
Google is your friend at times like that. Turned out you can use DBMS_METADATA to generate the CREATE USER statement, very easily.

The way we did it was create a table with the "old" and the "new" usernames, and join this to the appropriate DBA_* view. In the script below the name of this table is "USER_MIGRATIE" containing two columns: "old_name" and "new_name"
Instead of boring you with the details, here is the script we used to generate the scripts

--create table user_migratie
--(old_name varchar2(30)
--,new_name varchar2(30)
--);

begin
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'SQLTERMINATOR', true);
   dbms_metadata.set_transform_param (dbms_metadata.session_transform, 'PRETTY', true);
end;
/

set long 2000000000
set head off
set pages 0
set feedback off
set termout off


select replace (
          dbms_metadata.get_ddl ('USER', upper (mig.old_name))
               , upper (mig.old_name), upper (mig.new_name)
       )
  from user_migratie mig
  join dba_users     usr
    on usr.username = upper (old_name)
 union all
select replace (
          dbms_metadata.get_granted_ddl( 'TABLESPACE_QUOTA', upper (old_name))
               , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_ts_quotas tq
    on tq.username = upper (mig.old_name)
 union all 
 select replace (
          dbms_metadata.get_granted_ddl ('ROLE_GRANT', upper (old_name))
                       , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_role_privs rpv
    on rpv.grantee = upper (mig.old_name)
 union all
select replace (
          dbms_metadata.get_granted_ddl ('SYSTEM_GRANT', upper (old_name))
               , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_sys_privs spv
    on spv.grantee = upper (mig.old_name)
 union all
select replace (
          dbms_metadata.get_granted_ddl ('OBJECT_GRANT', upper (old_name))
               , upper (old_name), upper (new_name)
       )
  from user_migratie mig
  join dba_tab_privs tpv
    on tpv.grantee = upper (mig.old_name)
 union all
select case
        when ((select count(*)
                 from dba_role_privs
                where grantee = upper (old_name)
                  and default_role = 'YES'
                  and rownum = 1
               ) > 0
             )
       then replace (
            dbms_metadata.get_granted_ddl ('DEFAULT_ROLE', upper (old_name))
               , upper (old_name), upper (new_name))
       end
  from user_migratie
 union all
select replace (
         dbms_metadata.get_ddl('PROFILE', profile)
        , old_name, new_name)
  from user_migratie mig
  join dba_users     usr
    on usr.username = upper (mig.old_name)
 where usr.profile <> 'DEFAULT'
.


spool create_user_like.sql
/
spool off


ed create_user_like.sql

Links I used for creating this script:
Oracle Alchemist
askTom
Oracle Documentation