首页 » ORACLE » oracle grant update a column on table(给一列授权update)

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>

打赏

目前这篇文章有1条评论(Rss)评论关闭。

  1. Michal Hussman | #1
    2011-12-21 at 09:04

    I would like to take the ability of thanking you for that professional assistance I have usually enjoyed viewing your site. I’m looking forward to the particular commencement of my university research and the overall groundwork would never have been complete without coming over to your website. If I might be of any assistance to others, I will be ready to help via what I have learned from here.