sql - Dynamically hide column in Oracle VPD -

sql - Dynamically hide column in Oracle VPD -

i have policy working under vpd, , i'm trying hide columns. know can utilize 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 hides pre-determined columns, has 'sal' , 'comm'.

what have reference table contains reference columns i'd 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, code generated adding policy.

the goal create policy "agile" possible, if non-experimented user wants hide new column, thing have alter reference table, , not modify oracle code.

thanks help

first off, i'm not big fan of level of agility. in general, if you've gone point you're using vpd, implies you've done fair bit of analysis columns contain sensitive data. reclassifying columns sensitive or adding new sensitive columns should involve reasonable level of analysis. involves updates documentation auditors , others of nature review. amount of effort required developer add together or remove column list should pretty trivial in scheme of things. additionally, if create easy people add together new columns, create easy remove sensitive columns list, run queries extract data, , re-hide columns. seems whole lot of work minimal payoff.

that said, if want sort of thing, could

create trigger on table of columns hide. in trigger, utilize dbms_job bundle submit job runs after transaction commits. job phone call generatevpdpolicy procedure. the generatevpdpolicy procedure query table of columns hide , generate appropriate vpd policies.

this means there delay of sec or 2 (or more depending on other background jobs have , job_queue_processes setting) between when alter committed , when vpd policy updated. means there more moving pieces debug if , when goes wrong. if makes typo when edit list of columns, example, procedure presumably throw error written alert log (or custom error table needs monitored). if causes jobs not run (most commonly setting job_queue_processes 0 part of patch/ upgrade script , forgetting set again), need know debug that. of time, should work quite smoothly. when fail, however, you've got much more complicated scheme simple vpd policy function alter part of scheduled build.

sql oracle hide multiple-columns policy