adding a column to a policy in VPD
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: adding a column to a policy in VPD

  1. #1
    Join Date
    Apr 2009
    Posts
    48

    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.

  2. #2
    Join Date
    Jul 2002
    Location
    Lake Worth, FL
    Posts
    1,476

    Cool

    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
  •  



Click Here to Expand Forum to Full Width