How to create Read only Connection in Java for Oracle Database

Question

I have to design a tool which having text area where user can enter the their query and by giving submitting results will be shown. My requirement is such a way that it should accept only select queries but not update, insert, delete. If it is select results should be shown, otherwise error should be thrown. I have tried as below to make DB connection read only.

Connection.setReadOnly(true) which is not recommendable and its throwing an error.

'setReadOnly' is not supported on WebSphere java.sql.Connection implementation.

Can some one please help me in getting Read only connection for Database or if any design changes are needed

Answer 1

A common concept is to give the DB-User only the grants that are needed. In your case the application could use an application-specific user with select-grants on the allowed tables / views.

To clarify some misunderstandings: I am talking about the user your JDBC-Connection works with.

Answer 2

You can create a read only user in the following way :

SQL> create user read_only identified by read_only;

User created.

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

Grant succeeded.

SQL> conn read_only@pdborcl/read_only;
Connected.
SQL> create table t(col number);
create table t(col number)
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> desc scott.emp;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

SQL> update scott.emp set ename='LALIT';
update scott.emp set ename='LALIT'
             *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL> insert into scott.emp(ename) values('LALIT');
insert into scott.emp(ename) values('LALIT')
                  *
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>