Defining VPD (Virtual Private Database) Policy Groups -3

When a VPD is defined on a table, all VPD definitions are passed to AND.  This means that all VPD definitions apply.

Before this article you should look the previously article,

Oracle Virtual Private Database (VPD) -2

For example, let’s hide column A of a table in a VPD definition that we define based on a column.  In another VPD definition, let’s hide column B.

In this case, when a command to this table is executed, the security functions associated with the two VPD definitions are executed one by one.

In this case, when a command to this table is executed, the safety functions associated with the two VPD definitions are executed one by one.

• Policy groups are created with DBMS_RLS.CREATE_POLICY_GROUP.
• DBMS_RLS.ADD_GROUPED_POLICY defines VPD definitions for policy groups.
• The application context that manages policy groups is created.
• The application context and the object to be identified as VPD are mapped to DBMS_RLS.ADD_POLICY_CONTEXT.

To assign policy groups within a connection, a value is assigned to the application context.  This value should be one of the policy group names that relate to how the link will access the table.

With the value assigned to the application context, the VPD definition to be used in the connection is selected.

Let’s make an example. Hide one of the “salary” and “phone number” columns in the EMPLOYEES table with policy groups

First, let’s create policy groups. We will hide the SALARY column with the POLICY_HIDE_SAL policy group, and the PHONE NUMBER column with POLICY_HIDE_PHONE.

BEGIN
dbms_rls.create_policy_group(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_group => 'POLICY_HIDE_SAL');
END;
/


BEGIN
dbms_rls.create_policy_group(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_group => 'POLICY_HIDE_PHONE');
END;
/

You can view these policy group definitions with DBA_POLICY_GROUPS.

select * from DBA_POLICY_GROUPS;

Let’s create the function to be used in the VPD definition.

create or replace function hide_column (v_SchemaName IN VARCHAR2, v_ObjectName IN VARCHAR2)
RETURN VARCHAR2
AS
condition VARCHAR2(200);
BEGIN
condition := '1=2';
RETURN (condition);
END hide_column;
/

We can now create policy groups with the DBMS_RLS.ADD_GROUPED_POLICY procedure.  We use the policy group names we created just below the policy_group parameter here. The policy_name parameter does not matter here.

BEGIN
DBMS_RLS.ADD_GROUPED_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_group => 'POLICY_HIDE_SAL',
policy_name => 'FILTER_SAL',
function_schema => 'SYSTEM',
policy_function => 'HIDE_COLUMN',
statement_types => 'select',
policy_type => DBMS_RLS.STATIC,
sec_relevant_cols => 'SALARY',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

BEGIN
DBMS_RLS.ADD_GROUPED_POLICY(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_group => 'POLICY_HIDE_PHONE',
policy_name => 'FILTER_PHONE',
function_schema => 'SYSTEM',
policy_function => 'HIDE_COLUMN',
statement_types => 'select',
policy_type => DBMS_RLS.STATIC,
sec_relevant_cols => 'PHONE_NUMBER',
sec_relevant_cols_opt => DBMS_RLS.ALL_ROWS);
END;
/

Created VPD definitions can be selected with the view dba_policies.

select * from dba_policies where object_owner= 'HR';

It is time to create the context of implementation, which will lead the policy groups.  First we create the application context and then the function of the application context.

create or replace context policy_select USING POLICY_SELECT_PROCEDURE;

create or replace procedure POLICY_SELECT_PROCEDURE (POLICY_GROUP varchar2 default null) IS
BEGIN 
CASE SYS_CONTEXT('USEREBV', 'CLIENT_IDENTIFIER')
WHEN 'POLICY_HIDE_SAL' THEN
DBMS_SESSION.SET_CONTEXT('POLICY_SELECT','POLICY_GROUP','POLICY_HIDE_SAL');
WHEN 'POLICY_HIDE_PHONE' THEN
DBMS_SESSION.SET_CONTEXT('POLICY_SELECT','POLICY_GROUP','POLICY_HIDE_PHONE');
END CASE;
END POLICY_SELECT_PROCEDURE;
/

BEGIN 
DBMS_RLS.ADD_POLICY_CONTEXT(
object_schema => 'HR',
object_name => 'EMPLOYEES',
namespace => 'POLICY_SELECT',
attribute => 'POLICY_GROUP');
END;
/

grant execute on POLICY_SELECT_PROCEDURE to TEST01;

exec dbms_session.set_identifier('POLICY_HIDE_SAL');

exec system.POLICY_SELECT_PROCEDURE;

exec dbms_session.set_identifier('POLICY_HIDE_PHONE');

exec system.POLICY_SELECT_PROCEDURE;

BEGIN 
dbms_rls.drop_grouped_policy(
object_schema => 'HR',
object_name => 'EMPLOYEES',
policy_group => 'POLICY_HIDE_SAL',
policy_name => 'FILTER_SAL');
END;
/

We’re at the end of this article. So we finished VPD. See you in next article.

Oracle Secure External Password Store

Do you want to learn Oracle Database for Beginners, then read the following articles.

https://ittutorial.org/oracle-database-19c-tutorials-for-beginners/