• create a role for read only purpose
create role <SCHEMA>_RO;
  • grant to the role the select priv on main schema objects, and create public synonyms.

set serveroutput on
spool grant_priv.sql

declare
 v_owner   varchar2(30) := '<SCHEMA>';
 v_grantee varchar2(30) := '<SCHEMA>_RO';
 v_sql     varchar2(255):= '';
begin
 for cr in ((select t.owner,t.table_name from dba_tables t where owner=v_owner) union
            (select v.owner,v.view_name from dba_views v,dba_objects o where v.owner=o.owner 
               and v.view_name=o.object_name and o.status='VALID' and v.owner=v_owner)) 
 loop 
   v_sql:='grant select on ' || cr.owner || '.' || cr.table_name || ' to ' || v_grantee;
   dbms_output.put_line(v_sql || ';');
   v_sql:='create public synonym ' || cr.table_name || ' for ' || cr.owner || '.' || cr.table_name;
   dbms_output.put_line(v_sql || ';');
 end loop;
end;
/

spool off

@grant_priv.sql
  • create multi users, and assign the role.
create user SCH4GRP1 identified by xxxxxx;
create user SCH4GRP2 identified by yyyyyy;
create user SCH4GRP3 identified by zzzzzz;
grant connect, <SCHEMA>_RO to SCH4GRP1;
grant connect, <SCHEMA>_RO to SCH4GRP2;
grant connect, <SCHEMA>_RO to SCH4GRP3;