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,

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.

oracle vpd ile ilgili görsel sonucu
If more than one VPD is defined on a table; if we want a single VPD definition to be active based on an application context value, we make the Policy Groups definitions.
The general flow when defining Policy Groups is as follows,
  • 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.
Because this function returns value in the context of the application, it must be written to return one of the group policy names as a result.
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;
/

Let’s relate the context of the application and the object in which we define the VPD in policy groups.

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

We will test with TEST01 user. We must authorize this user to EXECUTE the POLICY_SELECT_PROCEDURE procedure to assign the application context value.

grant execute on POLICY_SELECT_PROCEDURE to TEST01;

Now let’s run our definitions. Let’s connect with TEST01 and assign no value to the POLICY_SELECT application context. Let’s see that we have access to the whole table since no policy group is active.

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;

 

We can delete the policy groups and VPD definitions used in the example with the following command,

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.

 

 

 

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

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

Deniz Parlak

I am a technical author on IT Tutorial. I am working on Oracle and Mysql databases, I also work on Datawarehouse and Big Data.If you need help, please contact deniz.parlak@yahoo.com.

Leave a Reply

Do NOT follow this link or you will be banned from the site!