This discussion is archived
1 Reply Latest reply: Oct 4, 2012 11:24 PM by dataseven RSS

VPD Update policy at column level

966275 Newbie
Currently Being Moderated
Hi,

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?

Thanks.
  • 1. Re: VPD Update policy at column level
    dataseven Explorer
    Currently Being Moderated
    hi,

    You can use sec_relevant_cols parameter of the DBMS_RLS.ADD_POLICY procedure

    begin

    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');

    end;

    you can see this doc

    http://www.in-oracle.com/Oracle-DBA/DBA-I/vpd-virtual-private-database.php

    or

    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.

    regards,

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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points