How to control which Columns are updated in a View?

From: John <John_at_none_zzzz.com>
Date: Sun, 13 Apr 2003 21:18:44 GMT
Message-ID: <Uukma.100278$yh1.5661259@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. Received on Sun Apr 13 2003 - 16:18:44 CDT