Search code examples
sqloracle-databasehidemultiple-columnspolicy

Dynamically hide column in Oracle VPD

I have a policy that is working well under VPD, and I'm trying to hide columns. I know I can use this: 

BEGIN
 DBMS_RLS.ADD_POLICY(
   object_schema         => 'scott', 
   object_name           => 'emp',
   policy_name           => 'hide_sal_policy', 
   policy_function       => 'hide_sal_comm',
   sec_relevant_cols     =>' sal,comm',
   sec_relevant_cols_opt => dbms_rls.ALL_ROWS);
END;

But this only hides pre-determined columns, which has 'sal' and 'comm'.

What I would like to do is have a reference table that contains a reference to the columns I'd like to hide:

SCHEMA TABLE COLUMNS_TOHIDE
my_schema my_table my_column1;my_column2
my_schema2 my_table2 my_column3;my_column4;my_column5

Ideally, automatically, the code would be generated for adding the policy.

The goal is to make the policy as "agile" as possible, so that if a non-experimented user wants to hide a new column, the only thing they have to do is change the reference table, and not modify some Oracle Code.

Thanks for your help

Solution

  • First off, I'm not a big fan of this level of agility. In general, if you've gone to the point where you're using VPD, that implies that you've done a fair bit of analysis about which columns contain sensitive data. Reclassifying columns as sensitive or adding new sensitive columns should involve a reasonable level of analysis. It almost always involves updates to documentation that auditors and others of that nature will review. The amount of effort required to get a developer to add or remove a column from a list should be pretty trivial in the scheme of things. Additionally, if you make it easy for people to add new columns, you make it easy for someone to remove sensitive columns from the list, run some queries to extract the data, and then quickly re-hide the columns. This seems like a whole lot of work for really minimal payoff.

    That said, if you want to do this sort of thing, you could

    • Create a trigger on the table of columns to hide.
    • In this trigger, use the dbms_job package to submit a job that runs after the transaction commits. This job would then call a generateVPDPolicy procedure.
    • The generateVPDPolicy procedure would then query the table of columns to hide and would generate the appropriate VPD policies.

    This means that there will likely be a delay of a second or two (or more depending on what other background jobs you have and your job_queue_processes setting) between when a change is committed and when the VPD policy is updated. It means that there are now more moving pieces to debug if and when something goes wrong. If someone makes a typo when they edit the list of columns, for example, the procedure will presumably throw an error that would get written to the alert log (or to some custom error table that needs to be monitored). If something causes jobs to not run (most commonly setting job_queue_processes to 0 as part of some patch/ upgrade script and forgetting to put it back again), someone will need to know to debug that. Most of the time, this should work quite smoothly. When something does fail, however, you've now got a much more complicated system than a simple VPD policy function change that is part of a scheduled build.