Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to control which Columns are updated in a View?
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)
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