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

From: Dave Hau <davehau-no-spam-123_at_no-spam.netscape.net>
Date: Sun, 13 Apr 2003 14:50:46 -0700
Message-ID: <b7ckv8$ou7$1@slb6.atl.mindspring.net>

"John" <John_at_none_zzzz.com> wrote in message news:Uukma.100278$yh1.5661259_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.

GRANT SELECT, UPDATE (funding), DELETE
ON funding_update
TO johndoe;

Cheers,
Dave Received on Sun Apr 13 2003 - 16:50:46 CDT