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