Create Read only user in Oracle Database

You can simply do with this command by connection with schema to which you need read-only user:

Conn scott/tiger.
create user ro_user identified by ro_user;
--If using 12102 prior version then use SELECT any table
grant create session, select any table, select any dictionary to ro_user;

-- If using 12102 version or higher then use READ any table
grant create session, read any table, select any dictionary to ro_user;


Use READ PERMISSION from 12.1.0.2 version
For 12.1.0.2, if you want to give someone read-only permission then you never use SELECT ON for that you get new READ ON privileges.
It avoid the risk of using SELECT …. FOR UPDATE statement in Oracle database to avoid unnecessary locking at table for other session put in hang state util commit is used.
By READ ON it’s issue is fixed.


-- Create user
CREATE USER RO_USER IDENTIFIED BY ro_password;
GRANT CREATE SESSION to ro_user;

-- Assign permission to USER
BEGIN
FOR x IN (SELECT * FROM dba_tables WHERE owner='SCHEMA_NAME')
LOOP
EXECUTE IMMEDIATE 'GRANT READ ON schema_name.' || x.table_name || ' TO ro_user';
END LOOP;
END;
/


Use SELECT PERMISSION up-to 12.1.0.1 version
If you connect with sysdba then you can need the following steps to execute:

1. Create a user in the Oracle which have only access of READ ONLY

CREATE USER RO_USER
IDENTIFIED BY ro_password
DEFAULT TABLESPACE users
TEMPORARY TABLESPACE temp;


2. Grant CREATE SESSION permission to Readonly user

GRANT CREATE SESSION to ro_user;


3. You can assign permission with two ways:
1. Directly to USER.
2. Create a ROLE and then assigned to USER.

Directly assigned to USER

BEGIN
FOR x IN (SELECT * FROM dba_tables WHERE owner='SCHEMA_NAME')
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON schema_name.' || x.table_name || ' TO ro_user';
END LOOP;
END;
/


Create a Role and then assigned to USER
If you want give permission to all tables then its better to create a role first.

--Create role
Create role readonly_role;

--Grant role to user;
GRANT readonly_role TO ro_user;


4. You also need to assign dictionary views:

-- For grant dictionary views access
GRANT SELECT ANY DICTIONARY TO ro_user;