Hi folks,
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;
/