Re: Make columns read only

From: ddf <oratune_at_msn.com>
Date: Sat, 6 Nov 2010 18:05:00 -0700 (PDT)
Message-ID: <37f1f5f1-1e61-4287-9e77-b41a194aad82_at_z20g2000pra.googlegroups.com>

On Nov 6, 6:22 pm, "Gerard H. Pille" <g..._at_skynet.be> wrote:
> ddf schreef:
>
>
>
>
>
> > On Nov 6, 1:14 pm, Ninja Li<nickli2..._at_gmail.com>  wrote:
> >> 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
>
> > 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
>
> 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.- Hide quoted text -
>
> - Show quoted text -

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 Received on Sat Nov 06 2010 - 20:05:00 CDT