客户有个需求,一张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.
|