STEP BY STEP CREATE READ ONLY USER IN ORACLE

Here I am giving select privileges to KUMAR schema  from SUNIL SCHEMA so KUMAR schemas can access objects from SUNIL SCHEMA.


Step 1)
Create read only Role for Read only user .


ROLE: READONLY

CREATE ROLE READONLY NOT IDENTIFIED

Step 2) Give previliges to read only role which was we created


-- For tables

SQL> spool table.sql
SQL> SELECT 'GRANT SELECT ON KUMAR.' ||TABLE_NAME || ' TO READONLY;' FROM DBA_TABLES WHERE OWNER='SUNIL';


-- For Views
SQL> SELECT 'GRANT SELECT ON KUMAR.' ||VIEW_NAME || ' TO READONLY;' FROM DBA_VIEWS WHERE OWNER='SUNIL';



Step 3) Now If User Already exits give this role to KUMAR user. IF not Here is DDL for KUMAR Shema DDL



CREATE USER "KUMAR" PROFILE "DEFAULT" IDENTIFIED BY XXXXXX DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP" ACCOUNT UNLOCK;
GRANT ALTER SESSION TO "KUMAR";
GRANT CREATE SESSION TO "KUMAR";
GRANT READONLY_CDSBI TO "KUMAR";
GRANT UNLIMITED TABLESPACE TO "KUMAR";


Then Execute Above table.sql which was we spool in above in Step 2 as dba privileges

table.sql

Thats It you have given read privileges to USER.


There is another way to create read only privileges to user

you can grant select any table privilege to user so he can select any table from database. But in some situations we have to give select privileges on some stuff instead of select any table so in that situation  above method will work fine.


Disclaimer: If you feel this content related to your content  please feel to contact me to remove this information.  I am sharing the knowledge with people whoever in the need which is learning daily basis