oracle grant all on to,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>

打赏

微信扫一扫，打赏作者吧～