VPD and Columnar FGAC

A requirement came in recently that some users should be able to see all the columns of a given table, whilst other users could only see a restricted subset of the available columns on the table – the first thought being that we should create a view over the top of the table with the restricted column list and if the user had the appropriate privileges then they get to see that view only otherwise they get to see the target table and all its columns.

Easy enough but a little bit much like hard work so we implemented a different solution with Virtual Private Database columnar Fine Grained Access Control. Column level FGAC allows the values within a column or columns to be returned as NULLs when the value of a given security function returns FALSE – in this way hiding sensitive columns of data from users without removing the visibility of the column being present (they just get NULLS if they are not authorised for access) and in a fairly simple manner.

From some simple performance testing, I noticed no noticeable degradation on typical data warehouse queries (i.e. queries where the elapsed time is not insignificant) indicating that the column level checking is done once at the outset of the query and not fired for each row visited.

(NOTE – I’ve changed the specifics to generics to protect confidentiality)

Firstly we create a role R_ROLE which we granted only to users who we wanted to be able to see all the columns of table MY_TABLE.

Next we created a Package with a function which returned Boolean TRUE / FALSE depending whether the session of the caller has the R_ROLE enabled or not. However, if you get all worked up thinking you have been viagra soft tablets http://downtownsault.org/wp-content/uploads/2018/02/06-14-17-DDA-MINUTES.pdf inflicted by some serious condition, you would find this ‘new stress’ taking over as the villain this time. General dose that are referred to the biological inability to conceive, after one year of regular sexual intercourse without using any birth control methods. viagra price Sadly, when a man suffers from sexual problem like erectile dysfunction, his brain levitra prices canada find out these guys does not become able carrying nitric oxide to work. Vaginal trainers are cylindrical shapes that are inserted into cialis españa the tissue with the help of needles. The code is like this:

PACKAGE pkg_fgac AS
FUNCTION func_fgac(object_schema IN VARCHAR2
,object_name VARCHAR2 ) RETURN VARCHAR2;
END pkg_fgac;
/

PACKAGE BODY pkg_fgac AS
FUNCTION func_fgac(object_schema IN VARCHAR2
,object_name VARCHAR2 ) RETURN VARCHAR2
AS
BEGIN
RETURN (CASE WHEN dbms_session.is_role_enabled(‘R_ROLE’)
THEN ‘1=1’
ELSE ‘1=0’
END);
END func_fgac;

END pkg_fgac;
/

Next we were able to add a policy to any table which required column level security as follows:

BEGIN
dbms_rls.add_policy (object_schema => ‘MY_SCHEMA’
,object_name => ‘MY_TABLE’
,policy_name => ‘MY_POLICY’
,function_schema => ‘SECURITY_FUNCTION_SCHEMA’
,policy_function => ‘PKG_FGAC.FUNC_FGAC’
,statement_types => ‘SELECT’
,sec_relevant_cols => ‘COLUMN1,COLUMN2’
,sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS
);
END;
/

Policy MY_POLICY would therefore ensure that on table MY_TABLE in schema MY_SCHEMA the security function PKG_FGAC.FUNC_FGAC would be called for any user issuing a SELECT against table MY_TABLE and it would return NULLs instead of the column values for columns COLUMN1 and COLUMN2 if the user issuing the request does not have the R_ROLE role granted to them and active in their current session.

It seems to work nicely in early testing…your mileage may vary of course!

Addendum:

23-JAN-2006 – added the sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS line after a discussion with Jonathan Lewis who proved that the code I originally posted didn’t exhibit the stated behaviour. Thanks Jonathan.