Create a read only user – Oracle

If you wish to create a user in Oracle that just have read permissions in the tables, then you must follow these steps:

First Create a user, let’s say “prod”:

SQL> create user prod identified by anypassword default tablespace
     tableSpaceName quota unlimited on tableSpaceName;

User created

//You must specify a default tablespace in order to let them login.

Then Grant Session privilege so that they can login and perform any task:

SQL>  GRANT CREATE SESSION TO prod;

Grant succeeded.

There are various priveleges and roles given to a user, but here we will only
discuss about giving select privilege in order to make this user a read only user:

Let's say there is a Schema schema1 with one table in it table1, then do:

SQL> grant select on schema1.table1 to prod;

Grant succeeded.

Now if there is a Schema schema2 with more than one table in it, above
query wouldn't work:

SQL> grant select on schema2.* to prod;
grant select on schema.* to prod
                     *
ERROR at line 1:
ORA-00903: invalid table name

Note: I tried * because i wanted user prod select privelege to all the tables
inside schema2

Here is the script to give select privilege to all the tables:

BEGIN
   FOR R IN (SELECT owner, table_name FROM all_tables WHERE owner='schema2 owner') LOOP
      EXECUTE IMMEDIATE 'grant select on '||R.owner||'.'||R.table_name||' to prod';
   END LOOP;
END;
/