Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to control which Columns are updated in a View?

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

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Sun, 13 Apr 2003 22:37:12 +0100
Message-ID: <i1mj9v0datog3g35ggafgkaa377u9mp90m@4ax.com>


On Sun, 13 Apr 2003 21:18:44 GMT, "John" <John_at_none_zzzz.com> wrote:

>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';

 You can grant privileges at column level, see the docs.

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_912a.htm#SQLRF01603

 For example:

Connected to Oracle9i Enterprise Edition Release 9.2.0.3.0 Connected as test

SQL> create table accounts (

  2      account_id      varchar2(10)   not null,
  3      account_name    varchar2(50)   not null,
  4      funding         number(14,2)   not null,
  5      constraint accounts_pk
  6          primary key (account_id)

  7 );

Table created

SQL> insert into accounts values ('ACC1', 'Account one', 100);

1 row inserted

SQL> create or replace view funding_update as (select account_id, account_name, funding from accounts);

View created

SQL> grant select on funding_update to test2;

Grant succeeded

SQL> grant update (funding) on funding_update to test2;

Grant succeeded

SQL> connect test2/test2_at_dev92
Connected to Oracle9i Enterprise Edition Release 9.2.0.3.0 Connected as test2

SQL> select * from test.funding_update;

ACCOUNT_ID ACCOUNT_NAME                                                FUNDING
---------- -------------------------------------------------- ----------------
ACC1       Account one                                                  100.00

SQL> update test.funding_update set funding = 200;

1 row updated

SQL> update test.funding_update set account_id = 'ACC2';

update test.funding_update set account_id = 'ACC2'

ORA-01031: insufficient privileges

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)
Received on Sun Apr 13 2003 - 16:37:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US