Using VPD with roles to control column-level access
We're using Oracle's column-level VPD functionality in conjunction with roles to control access to sensitive fields (e.g. SSN).
Currently, each sensitive field has a corresponding policy function. For example:
f_vpd_can_see_ssn controls access to SSN
f_vpd_can_see_birth_date controls access to Birth Date
Each policy function checks dba_role_privs to see the user has been granted a specific role corresponding to the sensitive field.
For example, f_vpd_can_see_ssn checks to see if the user has been granted the CAN_SEE_SSN role.
If the user has been granted the role, then show the data in the SSN column, else show NULL.
F_vpd_can_see_birth_date and all the other policy functions work the same way, checking for the presence of a granted role to determine whether the user has access to the column data.
Since the policy functions are identical except that each one checks for a different role, one might think that a single function could be created to handle every case, eliminating the need to create a new policy function each time we want to control access to another sensitive field.
However, since we don't necessarily know which column is being queried when a given policy function is executed, I'm not sure that such a function can be created.
Can anyone think of how to create such a function?
Oracle version is 10.2.0.4, OS is Solaris
Edited by: user1983440 on Aug 3, 2009 2:47 PM
Edited by: user1983440 on Sep 23, 2009 12:32 PM