动手操作Oracle细粒度访问控制（FGAC)一则

动手操作Oracle细粒度访问控制（FGAC)一则

环境：

sys@ORCL> select * from v$version where rownum=1; 
 
BANNER 
---------------------------------------------------------------- 
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
 
sys@ORCL> !uname -a 
Linux localhost.localdomain 2.6.18-308.el5xen #1 SMP Fri Jan 27 17:59:00 EST 2012 i686 i686 i386 GNU/Linux

实验过程如下：

scott@ORCL> create table tvpd (name varchar2(20),salary number(8,2),department_id number(5)); 
 
Table created. 
 
scott@ORCL> insert into tvpd values('张三',5000,10); 
 
1 row created. 
 
scott@ORCL> insert into tvpd values('李四',250,20); 
 
1 row created. 
 
scott@ORCL> commit; 
 
Commit complete. 
 
 
sys@ORCL> grant connect to zhangsan identified by zhangsan; 
 
Grant succeeded. 
 
sys@ORCL> grant select on scott.tvpd to zhangsan; 
 
Grant succeeded. 
 
sys@ORCL> grant connect to lisi identified by lisi; 
 
Grant succeeded. 
 
sys@ORCL> grant select on scott.tvpd to lisi; 
 
Grant succeeded. 
 
sys@ORCL> conn zhangsan/zhangsan 
Connected. 
zhangsan@ORCL> select * from scott.tvpd; 
 
NAME                    SALARY DEPARTMENT_ID 
-------------------- ---------- ------------- 
张三                      5000            10 
李四                        250            20 
 
zhangsan@ORCL> conn scott/tiger 
Connected. 
scott@ORCL> create or replace function func_vpd 
(owner varchar2,objname varchar2) 
return varchar2 
is 
  v_where_clause varchar2(2000); 
begin 
  v_where_clause :='name=initcap(sys_context(''userenv'',''session_user''))'; 
  return v_where_clause; 
end;  2    3    4    5    6    7    8    9   
 10  / 
 
Function created. 
 
scott@ORCL> conn / as sysdba 
Connected. 
sys@ORCL> select * from dba_policies where object_owner='SCOTT'; 
 
no rows selected 
 
sys@ORCL> BEGIN 
  dbms_rls.add_policy(object_schema => 'SCOTT', 
  object_name => 'TVPD', 
  policy_name => 'scott_policy123', 
  function_schema =>'SCOTT', 
  policy_function => 'func_vpd', 
  statement_types  =>'select', 
  sec_relevant_cols=>'salary'); 
END;  2    3    4    5    6    7    8    9   
 10  / 
 
PL/SQL procedure successfully completed. 
 
sys@ORCL> select * from dba_policies where object_owner='SCOTT'; 
 
OBJECT_OWNER                  OBJECT_NAME                    POLICY_GROUP 
------------------------------ ------------------------------ ------------------------------ 
POLICY_NAME                    PF_OWNER                      PACKAGE 
------------------------------ ------------------------------ ------------------------------ 
FUNCTION                      SEL INS UPD DEL IDX CHK ENA STA POLICY_TYPE              LON 
------------------------------ --- --- --- --- --- --- --- --- ------------------------ --- 
SCOTT                          TVPD                          SYS_DEFAULT 
SCOTT_POLICY123                SCOTT 
FUNC_VPD                      YES NO  NO  NO  NO  NO  YES NO  DYNAMIC                  NO 
scott@ORCL> conn zhangsan/zhangsan 
Connected. 
zhangsan@ORCL> select * from scott.tvpd; 
 
no rows selected 
 
zhangsan@ORCL> select name from scott.tvpd; 
 
NAME 
-------------------- 
张三 
李四 

在本测试中，我们只是对列salary作精细化控制，如果不查工资还是可以全部看到的，正如上面所示。

但是请注意，sys仍然不受影响，因为他有个权限叫“exempt access policy”，这个的性质和sysdba一样。

zhangsan@ORCL> conn / as sysdba 
Connected. 
sys@ORCL> grant exempt access policy to zhangsan; 
 
Grant succeeded. 
 
sys@ORCL> conn zhangsan/zhangsan 
Connected. 
zhangsan@ORCL> select * from scott.tvpd; 
 
NAME                    SALARY DEPARTMENT_ID 
-------------------- ---------- ------------- 
张三                      5000            10 
李四                        250            20 

同时，受策略保护的表若被drop是不进recyclebin，也就无法用flashback ... to before drop。

zhangsan@ORCL> conn scott/tiger 
Connected. 
scott@ORCL> show recyclebin 
scott@ORCL> drop table tvpd; 
 
Table dropped. 
 
scott@ORCL> show recyclebin 
scott@ORCL> flashback table tvpd to before drop; 
flashback table tvpd to before drop 
* 
ERROR at line 1: 
ORA-38305: object not in RECYCLE BIN