oracle列级权限控制

发表于:2013-12-31来源:IT博客大学习作者:db_dream点击数: 标签:oracle
客户有个需求,一张150多个字段的表,客户要求只将部分字段给扫描公司的人看,这个需求用视图就可以很容易实现,客户又要求,这些字段,扫描公司只可以修改其中的个别字段,我之前还真没遇到这样在列级别做权限控制的需求,做了个实验,感觉很有意思,记录下

  客户有个需求,一张150多个字段的表,客户要求只将部分字段给扫描公司的人看,这个需求用视图就可以很容易实现,客户又要求,这些字段,扫描公司只可以修改其中的个别字段,我之前还真没遇到这样在列级别做权限控制的需求,做了个实验,感觉很有意思,记录下测试过程。

  1.创建测试表并插入点测试数据:

  SQL> create table test( id number,table_name varchar2(50),

  owner varchar2(50),TABLESPACE_NAME varchar2(50));

  Table created.

  SQL> insert into test select rownum,table_name,owner,

  TABLESPACE_NAME from dba_tables;

  5490 rows created.

  SQL> commit;

  Commit complete.

  2.创建测试用户并赋予基本权限:

  SQL> CONN / AS SYSDBA

  Connected.

  SQL> create user stream identified by stream default tablespace users;

  User created.

  SQL> grant connect,resource to stream;

  Grant succeeded.

  3.赋予测试用户列级权限:

  SQL> conn auth/auth

  Connected.

  SQL> grant update (id) on test to stream;

  Grant succeeded.

  SQL> grant insert (table_name) on test to stream;

  Grant succeeded.

  SQL>

  SQL> grant select on test to stream;

  Grant succeeded.

  4.查询列级权限设置信息:

  SQL> select GRANTEE,OWNER,TABLE_NAME,COLUMN_NAME,GRANTOR,PRIVILEGE,

  GRANTABLE from user_col_privs;

  GRANTEE OWNER TABLE_NAME COLUMN_NAME GRANTOR PRIVILEGE GRA

  ------- ----- ---------- ----------- ------- --------- ---

  STREAM AUTH TEST TABLE_NAME AUTH INSERT NO

  AUT AUTH TEST ID AUTH INSERT NO

  AUT AUTH TEST ID AUTH UPDATE NO

  STREAM AUTH TEST ID AUTH UPDATE NO

  5.登陆测试用户验证SELECT权限:

  SQL> conn stream/stream

  Connected.

  SQL> select * from(select * from auth.test order by 1) where rownum< =10;

  ID TABLE_NAME OWNER TABLESPACE

  ---------- -------------------- ---------- ----------

  1 ICOL$ SYS SYSTEM

  2 IND$ SYS SYSTEM

  3 COL$ SYS SYSTEM

  4 CLU$ SYS SYSTEM

  5 TAB$ SYS SYSTEM

  6 LOB$ SYS SYSTEM

  7 COLTYPE$ SYS SYSTEM

  8 SUBCOLTYPE$ SYS SYSTEM

  9 NTAB$ SYS SYSTEM

  10 REFCON$ SYS SYSTEM

  10 rows selected.

  6.验证列级UPDATE权限控制:

  SQL> update auth.test set owner='STREAM' where id =1;

  update auth.test set owner='STREAM' where id =1

  *

  ERROR at line 1:

  ORA-01031: insufficient privileges

  可见,不允许修改测试表的OWNER字段的值,报ORA-01031:权限不足,由于上文赋予了测试用户对修改测试表ID字段的修改权限,修改ID字段是可以的。

  SQL> update auth.test set id=10 where id=1;

  1 row updated.

  SQL> rollback;

  Rollback complete.

  7.验证列级INSERT权限控制:

  SQL> insert into auth.test values(1,'stream','stream','users');

  insert into auth.test values(1,'stream','stream','users')

  *

  ERROR at line 1:

  ORA-01031: insufficient privileges

  可见,整行插入是不被允许的,也是权限不够,由于上文赋予了测试用户对修改测试表TABLE_NAME字段的插入权限,所以插入TABLE_NAME字段是可以的,但是前提是其他字段没有NOT NULL约束。

  SQL> insert into auth.test(table_name) values ('stream');

  1 row created.

  SQL> rollback;

  Rollback complete.

原文转自:http://blogread.cn/it/article/5558