Restrict users with read only privilege for all objects in database level

Method which i adhered

You can simply achieve this goal by giving him three permission.

1)Create Session: With this privilege he will be able to log on into the database.

2)Select any Table: With this privilege he will be able to select any table schema rather than SYS objects. Though if O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then SELECT ANY TABLE privilege provides access to all SYS and non-SYS objects.

3)Select any dictionary: If O7_DICTIONARY_ACCESSIBILITY parameter is set to TRUE then this privilege is not required to select data dictionary objects or in other word SYS objects. If O7_DICTIONARY_ACCESSIBILITY parameter is set to false then this privilege is required to give select permission on the data dictionary objects.

Following is an example. User arju will be globally read only user.

SQL> create user arju identified by a;

User created.

SQL> grant create session,select any dictionary,select any table to arju;

Grant succeeded.

References :
https://community.oracle.com/thread/1093477?start=0&tstart=0
https://community.oracle.com/thread/1042230
http://arjudba.blogspot.in/2008/09/create-read-only-user-for-schema.html

Create Read only user for a Schema

One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.

I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.

Let's start by creating PROD user.

SQL> CREATE USER PROD IDENTIFIED BY P;

User created.

SQL> GRANT DBA TO PROD;

Grant succeeded.

SQL> CONN PROD/P;

Connected.

SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);

Table created.

SQL> INSERT INTO PROD_TAB1 VALUES(1,2);

1 row created.

SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);

Table created.

SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1 

BEGIN

INSERT INTO PROD_TAB2 VALUES(SYSDATE);

END;

/

Trigger created.

SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;

View created.

Method 1: Granting Privilege Manually

Step 1: Create devels user

SQL> CREATE USER DEVELS IDENTIFIED BY D;

User created.

Step 2: Grant only select session and create synonym privilege to devels user.

SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;

Grant succeeded.

Step 3:Make script to grant select privilege.

$vi /oradata2/script.sql

SET PAGESIZE 0

SET LINESIZE 200

SET HEADING OFF

SET FEEDBACK OFF

SET ECHO OFF

SPOOL /oradata2/select_only_to_prod.sql

@@/oradata2/select_only_script.sql

SPOOL OFF

This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.

Step 4:

Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.

$vi /oradata2/select_only_script.sql

SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';

SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';

Step 5:

Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.

SQL> @/oradata2/script.sql

GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;

GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;

Step 6:

Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.

SQL> @/oradata2/select_only_to_prod.sql

Step 7:

Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.

To create synonym do the following,

SQL>CONN DEVELS/D;

SQL>host vi /oradata2/script_synonym.sql

SET PAGESIZE 0

SET LINESIZE 200

SET HEADING OFF

SET FEEDBACK OFF

SET ECHO OFF

SPOOL /oradata2/synonym_to_prod.sql

@@/oradata2/synonym_script.sql

SPOOL OFF

SQL>host vi /oradata2/synonym_script.sql

SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';

SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';

SQL>@/oradata2/script_synonym.sql

SQL>@/oradata2/synonym_to_prod.sql

Step 8: At this stage you have completed your job. Log on as devels schema and see,

SQL> select * from prod_tab1;

1 2

SQL> show user

USER is "DEVELS"

Only select privilege is there. So DML will throw error. Like,

SQL> insert into prod_tab1 values(4,3);

insert into prod_tab1 values(4,3)

*

ERROR at line 1:

ORA-01031: insufficient privileges

Method 2: Writing PL/SQL Code

This is script for table :

set serveroutput on

DECLARE

sql_txt VARCHAR2(300);

CURSOR tables_cur IS

SELECT table_name FROM dba_tables where owner='PROD';

BEGIN 

dbms_output.enable(10000000);

FOR tables IN tables_cur LOOP

sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';

execute immediate sql_txt;

END LOOP;

END;

/

This is the script for grant select permission for views :

DECLARE

sql_txt VARCHAR2(300);

CURSOR tables_cur IS

SELECT view_name FROM dba_views where owner='PROD';

BEGIN dbms_output.enable(10000000);

FOR tables IN tables_cur LOOP

sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';

--dbms_output.put_line(sql_txt);

execute immediate sql_txt;

END LOOP;

END;

/

To create synonym on prod schema,

Log on as devels and execute the following procedure.

SQL>CONN DEVELS/D

SQL>

DECLARE

sql_txt VARCHAR2(300);

CURSOR syn_cur IS

SELECT table_name name FROM all_tables where owner='PROD'

UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;

BEGIN dbms_output.enable(10000000);

FOR syn IN syn_cur LOOP

sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;

dbms_output.put_line(sql_txt);

execute immediate sql_txt;

END LOOP;

END;

/

Method 3: Writing a Trigger

After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.

Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.

create or replace trigger log_on_after_devels

after logon ON DEVELS.SCHEMA

BEGIN

EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';

END;

/