Re: How to control which Columns are updated in a View?

"John" <John_at_none_zzzz.com> wrote in message news:<Utjma.99814$yh1.5637163_at_news1.east.cox.net>...
> Hi.
>
> I have a table:
>
> ACCOUNTS
> ACCOUNT_ID VARCHAR2(10) PRIMARY KEY
> ACCOUNT_NAME VARCHAR2(50)
> FUNDING NUMBER(14,2)
> .....
>
> I have made a view for a user who needs to only update the FUNDING column.
>
> FUNDING_UPDATE
> CREATE OR REPLACE FUNDING_UPDATE AS (SELECT ACCOUNT_ID, ACCOUNT_NAME,
> FUNDING FROM ACCOUNTS);
>
> OK, this gives a good slice by hiding the other columns. I can also prevent
> the user from adding new data, by only granting him select, update, and
> delete to the view.
>
> What I can not figure out is the Oracle way of preventing the user from
> attempting to change the ACCOUNT_ID and ACCOUNT_NAME, in addition to
> FUNDING. Obviously, he would need ACCOUNT_ID in the view so he can form an
> update like this:
>
> UPDATE FUNDING_UPDATE
> SET FUNDING=200.00
> WHERE ACCOUNT_ID='xxxxx';
>
> Thanks.

You must implement an INSTEAD-OF-UPDATE Trigger to enforce your rules for protecting the columns that the user must not update.

Regards from Spain. Received on Mon Apr 14 2003 - 11:27:01 CEST