Creating a read only user in Oracle Database

There are many ways of achieving a read only user, here is a simple method using synonyms that creates a script to give RO_USER access to the RW_USER tables and views:

--As a dba user, create the read only user
CREATE USER RO_USER identified by password;
GRANT connect,resource to RO_USER ;
GRANT create synonym to RO_USER ;
-- This is script for table :
set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='RW_USER';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON RW_USER.'||tables.table_name||' TO RO_USER ';
dbms_output.put_line(sql_txt);
-- execute immediate sql_txt;
END LOOP;
END;
/
-- This is the script for grant select permission for views.
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='RW_USER';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON RW_USER.'||tables.view_name||' TO RO_USER ';
dbms_output.put_line(sql_txt);
-- execute immediate sql_txt;
END LOOP;
END;
/
-- This is the script for the synonyms.
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
(SELECT table_name name FROM all_tables where owner='RW_USER'
UNION SELECT VIEW_NAME name from all_views where owner='RW_USER')
MINUS SELECT SYNONYM_NAME from user_synonyms;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM RO_USER .'||syn.name|| ' FOR RW_USER.'||syn.name ;
dbms_output.put_line(sql_txt);
-- execute immediate sql_txt;
END LOOP;
END;
/