当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

oracle列级权限控制

客户有个需求,一张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> 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://www.dbdream.com.cn/2012/07/oracle%e5%88%97%e7%ba%a7%e6%9d%83%e9%99%90%e6%8e%a7%e5%88%b6/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2012年07月01日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: oracle列级权限控制 | 信春哥,系统稳,闭眼上线不回滚!
关键字: ,

oracle列级权限控制:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter