New User With Same Privileges As Existing User

Question:
I have 2 Users, User 2 is created with "like User 1".
But I need to grant select on all tables, views and synonyms, which owned by user 1 to user 2.

Solution:

A simple script like this should help.
Spool the output and run the script.
You may need to add specific entries for other missing objects.

Code: 

scott@9i > @cr_user_like
Enter user to model new user to: SCOTT
Enter new user name: ANOTHERSCOTT
Enter new user's password: ANOTHERTIGER
create user ANOTHERSCOTT identified by ANOTHERTIGER default tablespace USERS temporary tablespace TEMP profile DEFAULT;
grant DBA to ANOTHERSCOTT;
grant CONNECT to ANOTHERSCOTT;
grant RESOURCE to ANOTHERSCOTT;
grant UNLIMITED TABLESPACE to ANOTHERSCOTT;
grant SELECT ANY DICTIONARY to ANOTHERSCOTT;
alter user ANOTHERSCOTT default role DBA;
alter user ANOTHERSCOTT default role CONNECT;
alter user ANOTHERSCOTT default role RESOURCE;

scott@9i > get cr_user_like
  1  set pages 0 feed off veri off lines 500
  2  accept oldname prompt "Enter user to model new user to: "
  3  accept newname prompt "Enter new user name: "
  4  accept psw     prompt "Enter new user's password: "
  5  -- Create user...
  6  select 'create user &&newname identified by &&psw'||
  7         ' default tablespace '||default_tablespace||
  8         ' temporary tablespace '||temporary_tablespace||' profile '||
  9         profile||';'
 10  from   sys.dba_users
 11  where  username = upper('&&oldname');
 12  -- Grant Roles...
 13  select 'grant '||granted_role||' to &&newname'||
 14         decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
 15  from   sys.dba_role_privs
 16  where  grantee = upper('&&oldname');
 17  -- Grant System Privs...
 18  select 'grant '||privilege||' to &&newname'||
 19         decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
 20  from   sys.dba_sys_privs
 21  where  grantee = upper('&&oldname');
 22  -- Grant Table Privs...
 23  select 'grant '||privilege||' on '||owner||'.'||table_name||' to &&newname;'
 24  from   sys.dba_tab_privs
 25  where  grantee = upper('&&oldname');
 26  -- Grant Column Privs...
 27  select 'grant '||privilege||' on '||owner||'.'||table_name||
 28         '('||column_name||') to &&newname;'
 29  from   sys.dba_col_privs
 30  where  grantee = upper('&&oldname');
 31  -- Set Default Role...
 32  select 'alter user &&newname default role '|| granted_role ||';'
 33    from sys.dba_role_privs
 34   where grantee = upper('&&oldname')
 35*    and default_role = 'YES';

PS: 
It works with some small modifications.
One thing what was missing were the tables which owned by the original user. The script only looks for granted things.
And the other was a connection problem. This Program has silly restictions. I couldn't grant on tables or views which are owned by the user even if I tried as sys. I have to implement a connect in this script, which makes it not portable.
But it works.

Here's my script:

set pages 0 feed off veri off lines 500
accept oldname prompt "Enter user to model new user "
accept newname prompt "Enter new user Name "
accept pw prompt "Enter Users Password "
spool users.sql
select 'create user &&newname identified by &&pw'||
' default tablespace '||default_tablespace||
' temporary tablespace '||temporary_tablespace||' profile ' ||
profile||';'
from sys.dba_users
where username = upper('&&oldname');
select 'grant '||granted_role|| ' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_role_privs
where grantee = upper('&&oldname');
select 'alter user &&newname default role '|| granted_role ||';'
from sys.dba_role_privs
where grantee = upper('&&oldname')
and default_role = 'YES';
prompt connect deskrepo/deskrepo
select 'grant '||privilege||' to &&newname'||
decode(ADMIN_OPTION, 'YES', ' WITH ADMIN OPTION')||';'
from sys.dba_sys_privs
where grantee = upper('&&oldname');
select 'grant '||privilege|| ' on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tab_privs
where grantee = upper('&&oldname')
and privilege in ('SELECT', 'REFERENCES');
select 'grant '||privilege||'on '||owner||'.'||table_name||
'('||column_name||') to &&newname;'
from sys.dba_col_privs
where grantee = upper('&&oldname');
prompt connect desk/desk
select 'grant select on '||owner||'.'||table_name||' to &&newname;'
from sys.dba_tables
where owner = upper('&&oldname');
spool off

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.