1 Reply Latest reply on Oct 5, 2012 6:24 AM by dataseven

    VPD Update policy at column level


      I have a table abc and I have applied a policy for update. Now the user who has no access is unable to update the table abc. Can I just restrict the user from updating a specific column ( user_id column in the table abc), rather than restricting the user from updating all columns?

        • 1. Re: VPD Update policy at column level

          You can use sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure


          dbms_rls.add_policy (

          object_schema => 'SCOTT',

          object_name => 'EMP',

          policy_name => 'VPD_TEST_POLICY',

          function_schema => 'SCOTT',

          policy_function => 'TEST_VPD',

          statement_types => 'select, insert, update, delete',

          sec_relevant_cols => 'sal,comm');


          you can see this doc



          You can also use views to enforce column-level security, showing only which columns in a table may be updated. For example, assume that you must design roles based a table where only managers may view or update the salary column of the employee table (column restriction).

          1 - You can grant the end-user access to only those columns you wish to update:

          grant update (col1, col2) on mytab to fred;

          2 - You might create a view with only those columns that you want to allow updates, the table appears to the end-user as-if it contains only those columns. By granting access only to that view (and not the base table), you can effectively implement column-level security and restrict which columns in a table may be changed.


          Edited by: dataseven on 04.Eki.2012 23:22