Create read only user like apps

Create read only user like apps

connect system/&systempwd
create user &&user identified by &&pass default tablespace iwatch temporary tablespace TEMP;
grant connect, resource, select any table to &&user;

connect apps/&appspwd
spool /tmp/syn.sql
set pages 500
set lines 200
select 'create synonym '||synonym_name||' for '||table_owner||'.'||table_name||';'
from user_synonyms;
spool off
spool /tmp/syn2.sql
set pages 500
set lines 200
select 'create synonym '||view_name||' for APPS.'||view_name||';'
from user_views;
spool off

connect &&user/&&pass
@/tmp/syn2.sql
@/tmp/syn.sql
select 'NEW USER '||USER||' CREATED'
from dual;
exit

One more method:
How to create a user similar to APPS but with READ-ONLY privileges.
-------------------------------------------------------------------

1. Create user:

create user apps_ro identified by usi_ro
default tablespace XXXX
temporary tablespace temp
quota 0M on XXXX;

2. Create Role:

Create role r_role;
Grant create session to r_role;
Grant connect to r_role;
Grant select any table to r_role;
Grant execute any procedure to r_role;
Grant create trigger to r_role;

3. Grant role to user:
Grant r_role to apps_ro;

4. Create trigger:

Login as apps_ro and create the following trigger

Create trigger rlogin_trigger
After logon on apps_ro.schema
Begin
Execute immediate 'Alter session set current_schema = apps';
End;
/

5. Revoke privilege
Revoke create trigger from r_role;