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; /