动手操作oracle细粒度访问控制（FGAC)一则
环境：
sys@ORCL> select * from v$version where rownum=1;BANNER----------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prodsys@ORCL> !uname -aLinux 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/zhangsanConnected.zhangsan@ORCL> select * from scott.tvpd;NAME SALARY DEPARTMENT_ID-------------------- ---------- -------------张三 5000 10李四 250 20zhangsan@ORCL> conn scott/tigerConnected.scott@ORCL> create or replace function func_vpd(owner varchar2,objname varchar2)return varchar2is 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 sysdbaConnected.sys@ORCL> select * from dba_policies where object_owner='SCOTT';no rows selectedsys@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_DEFAULTSCOTT_POLICY123 SCOTTFUNC_VPD YES NO NO NO NO NO YES NO DYNAMIC NOscott@ORCL> conn zhangsan/zhangsanConnected.zhangsan@ORCL> select * from scott.tvpd;no rows selectedzhangsan@ORCL> select name from scott.tvpd;NAME--------------------张三李四
在本测试中，我们只是对列salary作精细化控制，如果不查工资还是可以全部看到的，正如上面所示。
但是请注意，sys仍然不受影响，因为他有个权限叫“exempt access policy”，这个的性质和sysdba一样。
zhangsan@ORCL> conn / as sysdbaConnected.sys@ORCL> grant exempt access policy to zhangsan;Grant succeeded.sys@ORCL> conn zhangsan/zhangsanConnected.zhangsan@ORCL> select * from scott.tvpd;NAME SALARY DEPARTMENT_ID-------------------- ---------- -------------张三 5000 10李四 250 20
同时，受策略保护的表若被drop是不进recyclebin，也就无法用flashback ... to before drop。
zhangsan@ORCL> conn scott/tigerConnected.scott@ORCL> show recyclebinscott@ORCL> drop table tvpd;Table dropped.scott@ORCL> show recyclebinscott@ORCL> flashback table tvpd to before drop;flashback table tvpd to before drop*ERROR at line 1:ORA-38305: object not in RECYCLE BIN