11 November 2009

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

5 comments:

  1. Really interesting, Alex!
    The DBMS_METADATA package is one of the gems of the RDBMS releases since 9i.
    I never cease to be amazed at how much can be done with it!

    ReplyDelete
  2. Can anyone please suggest whether dbms.metadata can be used to generate DDL for creating database similar to existing database.

    Thanks,
    Rahul

    ReplyDelete
  3. Rahul,
    I wouldn't use DBMS_METADATA to create a copy of a database. Cloning a database would be more suitable, for instance with RMAN.

    ReplyDelete
  4. But if you have thousands of object grants, this statement will be running for hours.

    Anyway, thanks for this post.

    ReplyDelete