How to create a read only user in database with specific schema access privileges .

Here are the steps :-


Step 1 :- connect system@xyz.testworld.com

Step 2 :- CREATE USER vinay_R IDENTIFIED by vinayr123 default tablespace users quota 0 on system quota unlimited on users;

Step 3 :- grant connect, create session to vinay_r;

Step 4 :- create role RL_vinay_R;

Step 5 :- grant RL_vinay_R to vinay_R;

Step 6 :-

select 'GRANT '||
DECODE(OBJECT_TYPE,'PROCEDURE','EXECUTE',
'PACKAGE','EXECUTE',
'SEQUENCE','SELECT',
'SELECT, INSERT, UPDATE, DELETE')||
' ON '||OWNER||'.'||OBJECT_NAME||' TO '||
DECODE(object_type,'VIEW',' vinay_R WITH GRANT OPTION ',' RL_vinay_R ')||';'
from dba_objects
where owner IN ('Schema1','Schema2')
and object_name not like '%$%'
--and object_type IN ('TABLE','VIEW','SEQUENCE','PROCEDURE','PACKAGE','FUNCTION')
and object_type = 'VIEW'
order by owner, OBJECT_TYPE, object_name

Step 7:-

select 'CREATE SYNONYM vinay_R.'||object_name||' for '||OWNER||'.'||object_name||'; '
from dba_objects
where owner IN ('Schema1','Schema2')
and Object_Type IN ('FUNCTION', 'PROCEDURE', 'VIEW', 'TABLE', 'SEQUENCE', 'PACKAGE')