動手操作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