How to give read only access to the tables in a schema

Create a user

SQL> create user b identified by b;

User created.

SQL> grant select  table,create session to b;

From target schema idl

SQL>conn idl/idl;

connected

sql>GRANT SELECT ON AUDIRAIL TO b ;

From source schema

sql>conn b/b;

connected

SQL>  select * from idl.audirail;

select * from tab where tname = 'AUDIRAIL';

You can write a simple procedure to achieve this :

set serveroutput on

DECLARE

sql_txt VARCHAR2(300);

CURSOR tables_cur IS

SELECT table_name 

FROM dba_tables 

 where owner='idl'

  and table_name in ('audirail', 'audirail2') ;

BEGIN 

dbms_output.enable(10000000);

FOR tables IN tables_cur LOOP

sql_txt:='GRANT SELECT ON '||audirail|| TO GPS;

execute immediate sql_txt;

END LOOP;

END;

/