Create Read only user for a Schema in Oracle

I will demonstrate the procedure with examples to setup a read only user for a schema in oracle database. In the example I will make chris user with read only permission on sam schema.
Let's start by creating SAM user.
SQL> CREATE USER SAM IDENTIFIED BY S;
User created.

SQL> GRANT DBA TO SAM;
Grant succeeded.

SQL> CONN SAM/S;
Connected.

SQL> CREATE TABLE SAM_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.

SQL> INSERT INTO SAM_TAB1 VALUES(1,2);
1 row created.

SQL> CREATE TABLE SAM_TAB2(DATE_COL DATE);
Table created.

SQL> CREATE OR REPLACE TRIGGER SAM_TAB2_T AFTER INSERT ON SAM_TAB1
BEGIN
INSERT INTO SAM_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM SAM_TAB2;
View created.


Method 1: Granting Privilege Manually

Step 1: Create Chris User
SQL> CREATE USER CHRIS IDENTIFIED BY C;
User created.

Step 2: Grant only select session and create synonym privilege to Chris user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO CHRIS;
Grant succeeded.

Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_sam.sql
@@/oradata2/select_only_script.sql
SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate an output script /oradata2/select_only_to_sam.sql which will be rerun.

Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql

SELECT 'GRANT SELECT ON SAM.' ||TABLE_NAME || ' TO CHRIS;' FROM DBA_TABLES WHERE OWNER='SAM';
SELECT 'GRANT SELECT ON SAM.' ||VIEW_NAME || ' TO CHRIS;' FROM DBA_VIEWS WHERE OWNER='SAM';

Step 5:
Now execute the /oradata2/script.sql which will generate scipt /oradata2/select_only_to_sam.sql.
SQL> @/oradata2/script.sql

GRANT SELECT ON SAM.SAM_TAB1 TO CHRIS;
GRANT SELECT ON SAM.SAM_TAB2 TO CHRIS;

Step 6:
Execute the output script select_only_to_sam.sql which will grant read only permission to chris user no sam's schema.
SQL> @/oradata2/select_only_to_sam.sql

Step 7:
Log on as chris user and create synonym that will access sam's table without any dot(.). Like to access sam_tab2 of sam schema he need to write sam.sam_tab2. But after creating synonym he simply can use sam_tab2 to access chris table and views.
To create synonym do the following,

SQL>CONN CHRIS/C;

SQL>host vi /oradata2/script_synonym.sql

SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_sam.sql
@@/oradata2/synonym_script.sql
SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR SAM.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='SAM';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR SAM.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='SAM';
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_sam.sql

Step 8: At this stage you have completed your job. Log on as chris schema and see,
SQL> select * from sam_tab1;
1 2

SQL> show user
USER is "CHRIS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into sam_tab1 values(4,3);
insert into sam_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code
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='SAM';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON SAM.'||tables.table_name||' TO chris';
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='SAM';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON SAM.'||tables.view_name||' TO chris';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

To create synonym on sam schema,
Log on as chris and execute the following procedure.

SQL>CONN CHRIS/C
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='SAM'
UNION SELECT VIEW_NAME name from all_views where owner='SAM' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR SAM.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/

Method 3: Writing a Trigger

After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to sam just after log in CHRIS user.

create or replace trigger log_on_after_chris
after logon ON CHRIS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = sam';
END;
/