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