CREATEORREPLACEfunction func_vpd(p_owner varchar2,p_objectname varchar2)
RETURNVARCHAR2
AS
v_predicate varchar2(4000);
BEGIN
v_predicate :='last_name=initcap(sys_context(''userenv'',''session_user''))';
RETURN v_predicate;
end;
select a.object_name,a.status
from dba_objects a where a.object_name='FUNC_VPD'
SELECT * FROM USER_ERRORS
BEGIN
DBMS_RLS.ADD_POLICY (object_schema => 'hr',
object_name => 'employees',
policy_name => 'sp_job',
function_schema => 'sys',
policy_function => 'FUNC_VPD',
statement_types=>'select,insert',
sec_relevant_cols =>'salary,commission_pct'
);
END;
SQL> update employees setlast_name='King2' where employee_id=100;
1 row updated.
SQL> commit;
SQL> create user king identified byoracle;
User created.
SQL> grant connect to king;
Grant succeeded.
SQL> grant select on hr.employees toking;
SQL> select count(last_name) fromhr.employees;
COUNT(LAST_NAME)
----------------
107
Grant succeeded.
SQL> select last_name,salary from hr.employees;
LAST_NAME SALARY
-------------------------------------------------------------------------------------
King 10000
SQL> selectlast_name,commission_pct fromhr.employees;
LAST_NAME COMMISSION_PCT
-----------------------------------------------------------------------------------------
King .35
由于受到policy的影响,一旦查询到salary 或者是commissi_pct 时,查询
条件(谓语)会自动带有where last_name=
下面演示如何从策略中豁免
SQL> conn / as sysdba
Connected.
SQL> grant exempt access policy to king;
Grant succeeded.
SQL> conn king/oracle
Connected.
SQL> select last_name,commission_pct from hr.employees;
.
.
.
Everett
McCain
Jones
Walsh
Feeney
107 rows selected.