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

Column based grant should do it.

• Jusung Yang

SQL> desc test2

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 VARCHAR2(2)
 C2                                                 NUMBER(1)

SQL> create view test2_vw as select * from test2;

View created.

SQL> grant update(c2) on test2_vw to scott;

Grant succeeded.

SQL> conn scott/tiger_at_athena
Connected.
SQL> update jyang.test2_vw set c1='a';
update jyang.test2_vw set c1='a'

             *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> update jyang.test2_vw set c2=9;

9 rows updated.

SQL> rollback;

Rollback complete.

SQL> "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.
Received on Mon Apr 14 2003 - 19:30:17 CEST