Oracle fine-grained access control (fgac)

Environment:

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

The experiment process is as follows:

Scott @ orcl> Create Table tvpd (name varchar2 (20), salary number (8, 2), department_id number (5); table created. scott @ orcl> insert into tvpd values ('zhang san', 10); 1 row created. scott @ orcl> insert into tvpd values ('lily', 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 -------------------- ------------------- Zhang San 5000 10 Li Si 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 extends y_name pf_owner package extends function sel ins UPD del idx chk ENA sta policy_type Lon functions --- Scott tvpd implements scottfunc_vpd Yes 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 -------------------- Zhang sanli Si

In this test, we only refined the control of column salary. If we do not check the salary, we can still see all of it, as shown above.

However, note that sys is still not affected because it has the permission "exempt access policy", which is of the same nature as 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 -------------------- ---------- --------------- Zhang San 5000 10 Li Si 250 20

At the same time, if a table protected by the policy is dropped and does not enter recyclebin, flashback... To before drop cannot be used.

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