Oracle read-only JDBC connection

Question

Is there a way to acquire read-only JDBC connection from an oracle database. Typically I am looking for a jdbc url parameter that will enable this, something like:

jdbc:oracle:thin:@hostname:1521:sid?readonly=true

I am using the thin driver

Solution 1

As suggested in comments. Best is to grant the read only permissions to user accessing the database.

There is an alternative which is not suggested.

You can set the readOnly parameter in the Connection class using connection.setReadOnly.

Refer API docs for more details.

http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#setReadOnly(boolean)

Solution 2

As far as I'm aware, the thin drive will have the same permissions as the user you're connected with will have, therefore, the easiest way to acquire this is by having a user in the database which is read-only. Check the last bit of this link: http://docs.oracle.com/cd/B19306_01/java.102/b14355/apxtips.htm

Comments

[isnot2bad]

Just specify a user that has right permissions only!

[erencan]

You can do it with database grants not with connection url.

[Stephen]

Why is the read only "alternative" not suggested? Because of connection pooling or something?

[Abhijith Nagarajan]

Sorry for the late reply. Setting readOnly attribute is not suggested as you have to do it while retrieving connection everytime. And, this is not a restriction and this setting is to give an hint to the driver to enable database optimizations.

[Stoopkid]

To clarify, setReadOnly(true) does not actually set the connection to read only as it might sound. It's not really an alternative at all if your goal is a connection that cannot modify the datasource.