This was tested on Oracle 11.2. First I created a couple of users:
SQL> create user andrew identified by reid
2 default tablespace users
3 quota unlimited on users
4 /
User created.
SQL> grant create session, create table to andrew
2 /
Grant succeeded.
SQL> grant create session to fred
2 identified by bloggs
3 /
Grant succeeded.
SQL>
User Andrew created a table with two columns. Note that col1 could be null but col2 could not. You will see why later:
SQL> conn andrew/reid
Connected.
SQL> create table tab1
2 (col1 number,
3 col2 number not null)
4 /
Table created.
SQL> insert into tab1 values (1,2)
2 /
1 row created.
SQL>
Andrew then allowed Fred to update col1:
SQL> grant select, update (col1) on tab1 to fred
2 /
Grant succeeded.
SQL>
Fred then logged in and updated col1 but, when he tried to update col2, Oracle returned an
ORA-01031:
SQL> conn fred/bloggs
Connected.
SQL> update andrew.tab1 set col1 = 3
2 /
1 row updated.
SQL> update andrew.tab1 set col2 = 4
2 /
update andrew.tab1 set col2 = 4
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> select * from andrew.tab1
2 /
COL1 COL2
---------- ----------
3 2
SQL>
Andrew then allowed Fred to insert values into col1:
SQL> conn andrew/reid
Connected.
SQL> grant insert (col1) on tab1 to fred
2 /
Grant succeeded.
SQL>
Fred tried to insert a value into col1 but Oracle returned an ORA-01400 because col2 could not be null. If you grant insert access to selected columns in this way, you must grant it to all the not null columns in the table:
SQL> conn fred/bloggs
Connected.
SQL> insert into andrew.tab1 (col1) values (5)
2 /
insert into andrew.tab1 (col1) values (5)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into
("ANDREW"."TAB1"."COL2")
SQL> select * from andrew.tab1
2 /
COL1 COL2
---------- ----------
3 2
SQL>
Andrew then granted insert access to col2 instead. Note how the first revoke failed as you cannot revoke a privilege from a single column:
SQL> conn andrew/reid
Connected.
SQL> revoke insert (col1) on tab1 from fred
2 /
revoke insert (col1) on tab1 from fred
*
ERROR at line 1:
ORA-01750: UPDATE/REFERENCES may only be REVOKEd from
the whole table, not by column
SQL> revoke insert on tab1 from fred
2 /
Revoke succeeded.
SQL> grant insert (col2) on tab1 to fred
2 /
Grant succeeded.
SQL>
As col2 was the only not null column in the table, Fred was then able to insert a row successfully:
SQL> conn fred/bloggs
Connected.
SQL> insert into andrew.tab1 (col2) values (6)
2 /
1 row created.
SQL> select * from andrew.tab1
2 /
COL1 COL2
---------- ----------
3 2
6
SQL>
No comments:
Post a Comment