Hi,

I would like to make certain columns in an Oracle table read only to
prevent anyone from updating the values in these columns. I can think
of creating trigger to issue error message whenever update occurs.

Are there better ways to do that? Your suggestions are appreciated.

Nick Li

On Nov 6, 1:14*pm, Ninja Li <nickli2... (AT) gmail (DOT) com> wrote:
I don't know of any; if you're using 10.2.0.x you could create a read-
only materialized view and if you're using 11.2 you can make the table
read only. Of course making the entire table or materialized view
read only doesn't solve your problem and Oracle hasn't yet provided a
way (through, say, the ALTER TABLE statement) to make selected columns
read-only. It would appear that a before insert trigger is the only
option you have.


David Fitzjarrell

ddf schreef:
Or a "before update" ?

But why not "grant update (column1, column3) on my_table to ..." ? It won't
stop the table owner from updating those columns, but I suppose he could disable
the triggers too. I've never used it but it's in the manuals.

ddf wrote:
Is it possible to create two views, a read-only one containing the
read-only columns and a writeable one containing the writeable columms,
and "concatenating" these into a third one?

--

"I'm a doctor, not a mechanic." Dr Leonard McCoy <mccoy (AT) ncc1701 (DOT) starfleet.fed>
"I'm a mechanic, not a doctor." Volker Borchert <v_borchert (AT) despammed (DOT) com>

On Nov 6, 6:22*pm, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
It does work, and I've learned something new:

SQL> grant select, update(empno, ename, job) on mytab to gleebo;

Grant succeeded.

SQL> connect gleebo/uarm
Connected.
SQL> update bing.mytab set sal=sal*1.2;
update bing.mytab set sal=sal*1.2
*
ERROR at line 1:
ORA-01031: insufficient privileges


SQL>


David Fitzjarrell

ddf wrote:

One is never too old to learn, David, certainly not a greenhorn like you.

On Nov 6, 12:14*pm, Ninja Li <nickli2... (AT) gmail (DOT) com> wrote:
To add to what has already been suggested.

If you do not want certain columns in a table updated by most users
then those users should not have update privilege on the table to
begin with. You can use views and/or stored code to provide read
access and update access.

Also on 10g+ you can restrict column access via virtual private
database (VPD).

Combing VPD with views and stored procedures can do a lot to protect
data.

If certain columns should never change after initial insert then an
after update trigger could be used to stop the transaction if the
update touched certain columns. A trigger could be username aware so
as to allow the owner or production batch username to update the
target columns.

HTH -- Mark D Powell --

Mark D Powell wrote:
Why would you waste performance that way, iso only granting update on selective columns?

Thanks for all your help.

Nick