Re: Make columns read only
Date: Mon, 8 Nov 2010 10:56:29 -0800 (PST)
Message-ID: <8e683f12-8f1e-49ee-9ac8-88fdb54abe14_at_r31g2000prg.googlegroups.com>
On Nov 6, 12: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
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 -- Received on Mon Nov 08 2010 - 12:56:29 CST