Thursday, March 01, 2012

GRANT UPDATE by Column

(Tested on Oracle 9.) When you grant update privilege on a table, you can restrict it to specific columns. First create a table and add a row to it:

SQL> conn andrew/reid
Connected.
SQL> create table emp
  2  (first_name varchar2(10),
  3  salary    number)
  4  /

Table created.

SQL> insert into emp values ('Boris', 10000)
  2  /

1 row created.

SQL> select * from emp
  2  /

FIRST_NAME    SALARY
---------- ----------
Boris          10000

SQL>

Then allow Fred to update the salary column:

SQL> grant select, update (salary) on emp to fred
  2  /

Grant succeeded.

SQL> 

When Fred tries to update the first_name column, he is unable to do so:

SQL> conn fred/bloggs
Connected.
SQL> --
SQL> update andrew.emp set first_name = 'David'
  2  /
update andrew.emp set first_name = 'David'
              *
ERROR at line 1:
ORA-01031: insufficient privileges

SQL>

But he is able to update the salary as specified in the grant statement above:

SQL> update andrew.emp set salary = 12000
  2  /

1 row updated.

SQL> select * from andrew.emp
  2  /

FIRST_NAME    SALARY
---------- ----------
Boris          12000

SQL>

No comments: