adding a column to a policy in VPD
Hi guys,
we have already configure VPD(virtaul private database) on set of tables, our requirement is to add one more column for secured named YMPDBA on CRPDTA.F06106 table which has already 3 column secured (YMPHRT,YMRTAM,YMGPA) ,
please assists me how do i do this?
do i need to drop policy and recreate it by adding new column with existing ?
for example:
CREATE TABLE CRPDTA.F06106
(
YMAN8 NUMBER,
YMEXR NCHAR(30),
YMPDBA NUMBER,
YMDISO NCHAR(1),
YMRTAM NUMBER,
YMGPA NUMBER,
YMPHRT NUMBER,
YMPPRT NUMBER)
CREATE OR REPLACE TRIGGER CRPDTA.F06106_before_upd
BEFORE UPDATE ON CRPDTA.F06106
FOR EACH ROW
WHEN (
new.YMGPA = 0
)
DECLARE
BEGIN
if dbms_session.is_role_enabled('SECURED_PAYROLL_ROLE') then
:new.YMPHRT := :old.YMPHRT;
:new.YMRTAM := :old.YMRTAM;
:new.YMGPA := :old.YMGPA;
end if;
END;
/
BEGIN
SYS.DBMS_RLS.ADD_POLICY (
object_schema => 'CRPDTA'
,object_name => 'F06106'
,policy_name => 'F06106_POLICY'
,function_schema => 'CRPDTA'
,policy_function => 'PAYROLL_FGAC.FUNC_FGAC'
,statement_types => 'SELECT'
,policy_type => dbms_rls.dynamic
,long_predicate => FALSE
,sec_relevant_cols => 'YMPHRT,YMRTAM,YMGPA'
,sec_relevant_cols_opt => dbms_rls.all_rows
,update_check => FALSE
,static_policy => FALSE
,enable => TRUE );
END;
/
Best Regards.