oracle grant update a column on table(给一列授权update)

oracle 10 允许 基于表中某一列单独授权 update 权限

anbob@ORCL> conn system
Enter password:
Connected.
system@ORCL> create user test identified by test;

User created.

system@ORCL> grant create session to test;

Grant succeeded.

system@ORCL> conn anbob
Enter password:
Connected.

anbob@ORCL> create table testcol(id number,
2  name varchar2(10),
3  updatetime date default sysdate);

Table created.

anbob@ORCL> insert into testcol(id,name) values(1,’anbob’);

1 row created.

anbob@ORCL> insert into testcol(id,name) values(2,’weizhao’);

1 row created.

anbob@ORCL> commit;

Commit complete.

anbob@ORCL> select * from testcol;

ID NAME       UPDATETIME
———- ———- ——————-
1 anbob      2011-05-31 09:53:22
2 weizhao    2011-05-31 09:53:31

anbob@ORCL> grant select ,update (name) on testcol to test;

Grant succeeded.

anbob@ORCL> conn test/test;
Connected.

test@ORCL> alter session set current_schema=anbob;

Session altered.

test@ORCL> select * from testcol;

ID NAME       UPDATETIME
———- ———- ——————-
1 anbob      2011-05-31 09:53:22
2 weizhao    2011-05-31 09:53:31

test@ORCL> update anbob.testcol set id=id+10;
update anbob.testcol set id=id+10
*
ERROR at line 1:
ORA-01031: insufficient privileges

test@ORCL> update anbob.testcol set name=name||’.com’
2  ;
update anbob.testcol set name=name||’.com’
*
ERROR at line 1:
ORA-12899: value too large for column “ANBOB”.”TESTCOL”.”NAME” (actual: 11, maximum: 10)

test@ORCL> update anbob.testcol set name=name||’.c’
2  ;

2 rows updated.

test@ORCL> commit;

Commit complete.

test@ORCL> select * from anbob.testcol;

ID NAME       UPDATETIME
———- ———- ——————-
1 anbob.c    2011-05-31 09:53:22
2 weizhao.c  2011-05-31 09:53:31

test@ORCL> conn anbob
Enter password:
Connected.
anbob@ORCL> revoke all on testcol from test;

Revoke succeeded.

anbob@ORCL> conn test/test
Connected.
test@ORCL> select * from anbob.testcol;
select * from anbob.testcol
*
ERROR at line 1:
ORA-00942: table or view does not exist

test@ORCL> conn anbob/anbob
Connected.

anbob@ORCL> grant select (name) on testcol to test;
grant select (name) on testcol to test
*
ERROR at line 1:
ORA-00969: missing ON keyword

anbob@ORCL> grant update(name) on testcol to test;

Grant succeeded.

anbob@ORCL> grant delete(name) on testcol to test;
grant delete(name) on testcol to test
*
ERROR at line 1:
ORA-00969: missing ON keyword

anbob@ORCL>