CODEWITHSUNDEEP
sqloracle-databaseplsqlcopyclone
Sangathamilan Ravichandran
Sangathamilan Ravichandran

Reputation: 381

how to copy or clone one user to other in Oracle

i wanted to copy all the rights,roles and mandant from one user to other. How to do this in plsql procedure or function. I am using oracle 12c database.

Upvotes: 0

Views: 2043

Answers (1)

sandman
sandman

Reputation: 2118

I use this script to generate user creation:

set long 1000000 pagesize 0 feedback off trimspool on verify off

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

variable v_username VARCHAR2(30);

exec:v_username := upper('&1');

select dbms_metadata.get_ddl('USER', u.username)
from   dba_users u
where  u.username = :v_username
union all
select dbms_metadata.get_granted_ddl('TABLESPACE_QUOTA', tq.username)
from   dba_ts_quotas tq
where  tq.username = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('ROLE_GRANT', rp.grantee)
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('SYSTEM_GRANT', sp.grantee)
from   dba_sys_privs sp
where  sp.grantee = :v_username
union all
select dbms_metadata.get_granted_ddl('OBJECT_GRANT', tp.grantee)
from   dba_tab_privs tp
where  tp.grantee = :v_username
and    rownum = 1
union all
select dbms_metadata.get_granted_ddl('DEFAULT_ROLE', rp.grantee)
from   dba_role_privs rp
where  rp.grantee = :v_username
and    rp.default_role = 'YES'
and    rownum = 1
union all
select dbms_metadata.get_ddl('PROFILE', u.profile)
from   dba_users u
where  u.username = :v_username
and    u.profile <> 'DEFAULT'
/

Upvotes: 1

Related Questions