On 7/18/06, Sinardy Xing <oracle.rdbms@xxxxxxxxx> wrote:
Hi guys,
I have schema A and A own 20 many objects (tables, functions, triggers and others stuff).
How to create user B having same privileges with what A can do with his own objects
The following script will do what you want.
-- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist
-----------------------------------------------------------------------------
-- dup_user.sql -- can call from command line -- e.g. @dup_user OLD_USERNAME NEW_USERNAME -- duplicate a user with a new name, same privs. -- does NOT move objects -- you must run the generated file manually -- you will also have to manually drop the old user
set pause off set echo off set timing off set trimspool on set feed on term on echo off verify off set line 80 set pages 24 head on
clear col clear break clear computes
btitle off ttitle off
col colduser new_value uolduser noprint col cnewuser new_value unewuser noprint col cspoolfile new_value uspoolfile noprint
prompt Old Username:
set term off feed off
select upper('&1') colduser from dual;
set term on feed onprompt New Username:
set term off feed off
select upper('&2') cnewuser from dual;select '_' || lower(replace('&unewuser','$','\$')) || '.sql' cspoolfile
from dual;set term on feed on
set pages 0 lines 200 term on feed off
spool &uspoolfile
prompt set echo on
select 'create user &unewuser identified by values ' || '''' || password ||
'''' ||
' default tablespace ' || default_tablespace ||
' temporary tablespace ' || temporary_tablespace || ';'
from dba_users
where username = upper('&uolduser')
/select 'alter user &unewuser quota ' ||
decode(max_bytes, -1, ' UNLIMITED ', max_bytes ) || ' on ' ||
tablespace_name || ';'
from dba_ts_quotas
where username = upper('&uolduser')
/
select 'grant ' || granted_role || ' to &unewuser;' from dba_role_privs where grantee = upper('&uolduser') union select 'grant ' || privilege || ' to &unewuser;' from dba_sys_privs where grantee = upper('&uolduser') union select 'grant ' || decode(privilege, 'READ', 'READ on directory ', 'WRITE', 'WRITE on directory ', privilege || ' on ' ) || owner || '.' || table_name || ' to &unewuser;' from dba_tab_privs where grantee = upper('&uolduser') --order by 1, 2, 3, 4, 5 /
prompt set echo off
spool off
set pages 60 lines 80 feed on
prompt prompt The file '&uspoolfile' will create the new user '&unewuser' prompt
