Read-only database user

Sometimes the application developers need to debug data from the production database. It demands to give access to the live schema. How can I trust a developer who accidentally won't modify stuffs in database and blander everything!

I need a read-only user on which developers only would be able to issue "select" statements. For now, it will serve my purpose.

What I did, I created a user, created role with select any table privileges and assigned that user with the role. Now the developers are restricted to issue other than select commands - cool!

There are two ways of doing this. Here are the scripts for that -

** option-1 **

> sqlplus system/*******
> create user devels identified by xyz;

> create role SELECT_ANY_TABLE;
> grant select any table to SELECT_ANY_TABLE;

> grant connect to devels;
> grant SELECT_ANY_TABLE to devels;

** option-2 **

> sqlplus system/******
> create user devels identified by xyz;
> sqlplus prod/***********

Now have to run the following piece on PL/SQL

set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT x.table_name FROM user_tables x;
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON '||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/

The second option has a drawback - each time we add new tables in schema, we have to run it.