Re: Grant all question

On 7/18/06, Sinardy Xing <oracle.rdbms_at_gmail.com> 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 on

prompt 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

--
http://www.freelists.org/webpage/oracle-l