Virtual Private Database (VPD) Column level data security

What Is Oracle Virtual Private Database?

Oracle Virtual Private Database (VPD) lets you to create security policies that's control database access at the row and column level. 

Let's see how we can implement Column level security in 19c database .

Source Environment setup :

RAC Database         : RENODBPR
GRID Home      : /u01/app/19.3.0.0/grid
RDBMS Home         : /u01/app/oracle/product/19.3.0.0/dbhome_1
Version              : Oracle Database 19c EE - Production Version 19.17.0.0.0
hosts      : primhost01 &  primhost02

Our LAB environment is a 2 node RAC cluster with 19c version and with patch set 19.17 applied. 

CDB name : RENODBPR
PDB name : ONEPDB

Our Test Case Scenario

Lets say we have a requirement where we want few users not see the SSN column of the above table. 

In this case, lets say USER3 can view all the rows/columns of USER1.TABLE1 except the SSN column and let's say USER2 can view all the rows/columns of USER1.TABLE1 including the SSN column

To achieve this, we need to create a VPD function and VPD policy as shown below.

Step 1: Grant 'EXECUTE' privilege on DBMS_RLS package to USER1.

Login to the Database as SYSDBA and grant above privilege.

[oracle@labhost01 admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Jan 19 09:59:48 2023
Version 19.17.0.0.0
Copyright (c) 1982, 2022, Oracle.  All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.17.0.0.0
SQL> alter session set container=onepdb;
Session altered.
SQL> GRANT EXECUTE ON dbms_rls TO user1;

Step 2: Create the VPD Policy function.

Login as USER1 to the PDB and create the function as shown below.

SQL> alter session set container=onepdb;
Session altered.

SQL> create or replace function ssn_secure_fnc (p_schema varchar2, p_obj varchar2)
return varchar2 as
l_user  varchar2(20);
l_predicate varchar2(100);
begin
  select user into l_user from dual;
   if l_user!='USER2' then 
    l_predicate:='1=2';
  end if;
 
  return l_predicate;
end ssn_secure_fnc;
/

In Above function, we are telling that if user connect is USER2, it can return the data or else return nothing.

Step 3: Create the VPD Policy.

create below policy as USER1.

SQL> alter session set container=onepdb;
Session altered

begin
  dbms_rls.add_policy
    (object_schema=>'USER1'      -- specify the schema containing the object 
    ,object_name=>'TABLE1'  -- specify the object name within the schema.
    ,policy_name=>'ssn_secure'  -- specify the policy name. Policy name is unique for an object. 
    ,function_schema=>'USER1'  -- specify the schema in which the policy function is created
    ,policy_function=>'ssn_secure_fnc'  -- specify the name of the policy function
    ,statement_Types=>'SELECT'  -- Operations when this policy applies. SELECT    ,sec_relevant_cols=>'SSN'  -- ALL relevant columns to be hidden from users
    ,sec_relevant_cols_opt=> dbms_rls.ALL_ROWS
    );
end;
/

Step 4.  Grant select on user1.table1 to the users  user2 and user3

SQL> grant select on table1 to user2;
Grant succeeded.
SQL> grant select on table1 to user3;
Grant succeeded.
SQL>

Step 5 . Run the below select statement by connecting to the user USER2 and USER3 and see the results.

select * from user1.table1;

As we can see from above, that when we run the SELECT statement with USER2, it can see the SSN column data because we created VPD policy and added this user in it ensuring that USER2 can view all data including SSN column data.

But when we run the same select statement with USER3 or in fact any other user, we can see that SSN data is not displayed.

Thanks
Sambaiah Sammeta