Create Read-only user for a Schema

Sometimes the DBA requires  to create the read-only user for a particular schemas.While creating the user,one should always keep in mind that the user should have minimum privileges.  There is no any direct command to create the read-only user in oracle.Sometimes I find people grants  “read any table ”  privileges to create the read-only  user ,which is not correct . Here is demo for creating read-only users. 

In this demo, I have created a user name “neer” and given  access to select only  “hr”  schemas tables.

Step  1  :  Create User  “NEER”

SQL> create user neer identified by neer;

User created.

Step  2  :  Grant session and create synonym privileges

SQL> grant create session ,create synonym to  neer ;

Grant succeeded.

Step  3  :  Make script to grant  select privileges to neer 

Here I have created the script with the help of spool  by selecting the tables of the HR schemas .

SQL> SPOOL  C:\select_privs.sql

SQL> select 'grant select on hr.'||table_name|| '  to neer;' from dba_tables where owner='HR';

SQL> select 'grant select on hr.'||view_name||  '  to neer;' from dba_views where owner='HR';

SQL> spool off

Now , we will check the spool  “select _privs.sql”  and prepare this as script for grant permission.

Step  4  :  Run the script to grant the permission

SQL> @C:\select_privs.sql

The  script  “select_privs.sql”  script  after modification  is .

grant select on hr.REGIONS to neer;                                             

grant select on hr.LOCATIONS to neer;                                           

grant select on hr.DEPARTMENTS to neer;                                         

grant select on hr.JOBS to neer;                                                

grant select on hr.EMPLOYEES to neer;                                           

grant select on hr.JOB_HISTORY to neer;                                         

grant select on hr.COUNTRIES to neer;                                                                

grant select on hr.EMP_DETAILS_VIEW to neer;                                    

  

Step  5  :  Create the synonym    

Connect to “NEER” schemas and create the synonym so that the “NEER” user can access the “HR”  schemas without any  dot(.)  like  “select * from employees”   instead of   “select * from HR.employees” .Here again we use the above method.

SQL> conn neer/neer@noida

SQL> SPOOL  C:\synonym_privs.sql

SQL> select 'create synonym '||view_name|| '  for HR.'||view_name||';' from all_views where owner='HR';

SQL> select 'create synonym '||table_name|| '  for HR.'||table_name||';' from all_tables where owner='HR';

SQL> spool off

Now we have the script to create the synonym

SQL>@ C:\synonym_privs.sql

Now we have successfully created a read-only user for a particular schemas.

Step  6  :   Check the tables

SQL> select Count(*) from employees ;

  COUNT(*)

----------

       107

SQL> select Count(*) from hr.employees;

  COUNT(*)

----------

       107

SQL> sho user

USER is "NEER"

Enjoy   :-)