Issue/Symptom/Question

Create read-only database user to run queries

Applies To

Oracle Database System Privileges

Golden

Resolution/Fix/Answer

This should be done by an Oracle Database Administrator (DBA). If you do not have one, please open up a Service Desk ticket and reference this article.

To setup a read-only database user use the following steps.

NOTE:  This example is using 'pentaro' as the username. You can make it what you want.  It is also assuming your database schema name is 'penta'.

1. Create the user

   Done by the sysdba

   create user pentaro identified by <somepassword> default tablespace users temporary tablespace temp;
   grant create session to pentaro;
   grant create synonym to pentaro;

2. Grant select on table or view in the PENTA schema to the user you created.

   Done by the penta schema user

   grant select on <sometable> to pentaro;

3. The read-only user then creates synonyms for each table or view they want to query.

   Done by the read only user you created.

   create synonym <sometable> for penta.<sometable>;