Grants read-only access on all schemas tables in oracle

 SQL> BEGIN
  FOR x IN (select table_name from dba_tables where owner='SCHEMA_OWNER') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO <role_name>';
  END LOOP;
END;
/ 

SQL> CREATE USER <username> IDENTIFIED BY "<password>"
DEFAULT TABLESPACE "USERS"
TEMPORARY TABLESPACE "TEMP"
PROFILE "DEFAULT";  2    3    4

User created.

SQL> create role pune_ro;
role created.

SQL> show user;
USER is "SYS"
SQL> BEGIN
  FOR x IN (select table_name from dba_tables where owner='PUNE') LOOP
    EXECUTE IMMEDIATE 'GRANT SELECT ON PUNE.'|| x.table_name ||' TO pune_ro';
  END LOOP;
END;
/  2    3    4    5    6

PL/SQL procedure successfully completed.

SQL> alter user <username> default role all;

User altered.

SQL> grant app_user_dbpull to <username> ;

Grant succeeded.


SQL> revoke select on pune.alert from <username>;

Revoke succeeded.

SQL> grant pune_ro to <username>;

Grant succeeded.

---

1. create a user 

1.1 alter user <username> default role all;

2. create a role

3. grant all tables select on access on schemas tables

4. grant the role to <username>