Re: Access to LOB columns

Thanks for you note pointing out that you can still 'select for update' from a view that is declared as READ ONLY - hence update the LOB. Plan B:

Create a function for each lob column that returns the lob locator, having set the lob to read only in the function. Create a view on top of the table which replaces the lob column with the lob locator.

If the user tries to select for update, they will, but when they try to update the lob they will get error 22294 - cannot update a lob opened in read only mode.

There are side-effects - to do with real transactions getting blocked, LOBs being left open, being unable to commit, etc. but if you are really trying to ensure that the LOBs are read only in your application, this may be sufficient.

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

puffinry_at_my-deja.com wrote in message <8qvkre$37v$1_at_nnrp1.deja.com>...


>I have a database with two users, "admin" and

>"client".

>I have a table called DATA, owned by "admin".

>I want "client" to be able to read the data, so:

>

>CREATE SYNONYM client.DATA for admin.DATA;

>GRANT SELECT ON admin.DATA to client;

>

>For ordinary datatypes this approach works very

>well.

>However for any LOB (BLOB or CLOB), the client can

>SELECT the locator, and then use the locator to

>update the data (via OCI, JDBC, PL/SQL etc)

>

>That isn't what I wanted :-)

>Is there any way to allow some user to read from

>the locator,

>but not write to it?

>

> .robin.

>

>

>ps. As a workaround, I have a package owned by

>"admin",

>which can be executed by "client". I then only

>allow the client to SELECT from a restricted VIEW

>which excludes the LOB columns.  The client must

>use this package to read the LOB data. This is

>cumbersome, to say the least ;)

>

>

>Sent via Deja.com http://www.deja.com/

>Before you buy.