Hello,
I am trying to implement access control on dynamically changing database tables using VPD. To reduce DDL and administration effort, I want to write general policy functions that can be reused by many policies. This works out perfectly for row-level security, because it depends only on schema and table name to derive predicates. For that purpose, I store predicates for each table in an administration table and query this table in the policy function by using the schema and table name parameters of the policy function.
An example implementation could look like this:
function filter_row( schema_name in varchar2, table_name in varchar2 )
is
dyn_predicate varchar2(4000);
begin
select p.dyn_predicate
into dyn_predicate
from predicates p
where p.schema_name = schema_name
and p.table_name = table_name;
return dyn_predicate;
end;
However, when it comes to column-level security, the approach described above is not applicable, because I have no access to the column name from within the policy function. That means, I would have to create a policy function for each column that needs masking. Does anyone have an idea how I could implement column masking in a similar dynamic way?
What I am looking for is something like that (although this won't work):
function filter_col( schema_name in varchar2, table_name in varchar2, column_name in varchar2 )
is
dyn_predicate varchar2(4000);
begin
select p.dyn_predicate
into dyn_predicate
from predicates p
where p.schema_name = schema_name
and p.table_name = table_name
and p.column_name = column_name;
return dyn_predicate;
end;
Kind regards,
Gerhard