-
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 := ld.YMPHRT;
:new.YMRTAM := ld.YMRTAM;
:new.YMGPA := ld.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.
-
Yes, you need to drop/re-create policy.
"The person who says it cannot be done should not interrupt the person doing it." --Chinese Proverb
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|