Security predicates exposed #JoelKallmanDay

Since way back in version 8.1 of the database, there has been a cool feature in Oracle called Virtual Private Database (VPD), which essentially lets you force predicates into SQL statements run by users to ensure that they can only see the data they are entitled to see.

Here’s a trivial example, where I’ve decided that the users should only ever see rows for DEPTNO=10 from the SCOTT.EMP table.

First I need a function that returns the predicate that will be attached to my SQL statements.


SQL> create or replace
  2  function scott.emp_policy(user_name in varchar2,tab_name in varchar2)
  3  return varchar2 is
  4  begin
  5   return 'deptno=10';
  6  end;
  7  /

Function created.

SQL>
SQL> grant execute on scott.emp_policy to demo;

Grant succeeded.

And then I can add a policy to my EMP table to enforce that rule.


SQL> begin
  2      sys.dbms_rls.add_policy(
  3        object_schema   => 'scott',
  4        object_name     => 'emp',
  5        policy_name     => 'emp_vpd_policy',
  6        function_schema => 'scott',
  7        policy_function => 'emp_policy',
  8        statement_types => 'select'
  9     );
 10  end;
 11  /

PL/SQL procedure successfully completed.

Now when I connect as the DEMO schema and I run my query against the SCOTT.EMP table, we can only see DEPTNO=10 rows.


SQL> conn demo/demo@pdb21a
Connected.
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
      7839 KING       PRESIDENT            17-NOV-81       5000                    10
      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

In that sense, the DEMO user has no idea that there are any rows in the table that do not have a DEPTNO equal to 10. But an administrator can indeed see when policies are in action, because they have access to the V$VPD_POLICY view. It stores the policy and predicate used for the relevant SQL_IDs.


SQL> conn system/admin@pdb21a
Connected.
SQL> select * from v$vpd_policy
  2  @pr
==============================
ADDRESS                       : 00007FF8E61B8768
PARADDR                       : 00007FF8E61B8EC8
SQL_HASH                      : 52404428
SQL_ID                        : ggqns3c1jz86c
CHILD_NUMBER                  : 0
OBJECT_OWNER                  : SCOTT
OBJECT_NAME                   : EMP
POLICY_GROUP                  : SYS_DEFAULT
POLICY                        : EMP_VPD_POLICY
POLICY_FUNCTION_OWNER         : SCOTT
PREDICATE                     : deptno=10
CON_ID                        : 3

PL/SQL procedure successfully completed.

Now I just wrote that “In that sense, the DEMO user has no idea that there are any rows in the table that do not have a DEPTNO equal to 10“, suggesting that non-admin users will not be able to see the predicate that was silently applied.

However…you do need to be a little careful here.

In more recent versions of Oracle Database, we have the DBMS_UTILITY.EXPAND_SQL_TEXT routine. What happens when I run that on a SQL statement that will enact a VPD predicate.


SQL> variable c clob
SQL> begin
  2    dbms_utility.expand_sql_text('select * from scott.emp',:c);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL>
SQL> set long 5000
SQL> print c

C
--------------------------------------------------------------------------------
SELECT
  "A1"."EMPNO" "EMPNO",
  "A1"."ENAME" "ENAME",
  "A1"."JOB" "JOB",
  "A1"."MGR" "MGR",
  "A1"."HIREDATE" "HIREDATE",
  "A1"."SAL" "SAL",
  "A1"."COMM" "COMM",
  "A1"."DEPTNO" "DEPTNO"
FROM (
    SELECT
      "A2"."EMPNO" "EMPNO",
      "A2"."ENAME" "ENAME",
      "A2"."JOB" "JOB",
      "A2"."MGR" "MGR",
      "A2"."HIREDATE" "HIREDATE",
      "A2"."SAL" "SAL",
      "A2"."COMM" "COMM",
      "A2"."DEPTNO" "DEPTNO"
    FROM
      "SCOTT"."EMP" "A2"
    WHERE
      "A2"."DEPTNO" = 10
  )  "A1"

And voila! There is our predicate, visible for the DEMO user to see. Seeing “DEPTNO=10” is hardly an issue, and the DEMO user cannot bypass this predicate in any way. However, consider a scenario where the predicate reveals information that we would rather people not see. You might have a policy function like the following:


SQL> create or replace
  2  function scott.emp_policy(user_name in varchar2,tab_name in varchar2)
  3  return varchar2 is
  4  begin
  5   return 'MY_SECRET_IMPORTANT_PRIVATE_COLUMN=....';
  6  end;
  7  /

Function created.

And you are using a view on top of your table to keep that column hidden from view (no pun intended).


SQL> create or replace
  2  view scott.all_the_queryable_columns as
  3  select ... [everything but MY_SECRET_IMPORTANT_PRIVATE_COLUMN]
  4  from emp;

View created.

That changes things, because now the use of DBMS_UTILITY.EXPAND_SQL_TEXT could reveal both the existence of the column, and a potentially sensitive value within it. If your security is set up rigorously, then you still have not really increased the risk of a user seeing rows they are not meant to see, but simply knowing about the column’s existence and values in it, might still lead a malicious party to try find exploits in other parts of your application stack.

Thus if you are going be building VPD predicates, even simple ones that are just a constant, then perhaps look at using a CONTEXT object to reduce the visibility of those values.


SQL> create context MY_CONTEXT using scott.pkg_context;

Context created.

SQL> create or replace
  2  package scott.pkg_context is
  3    procedure set_val(p_key varchar2, p_val varchar2);
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace
  2  package body scott.pkg_context is
  3    procedure set_val(p_key varchar2, p_val varchar2) is
  4    begin
  5      dbms_session.set_context(
  6        namespace=>'MY_CONTEXT',
  7        attribute=>p_key,
  8        value=>p_val);
  9    end;
 10  end;
 11  /

Package body created.

SQL>
SQL> grant execute on scott.pkg_context to demo;

Grant succeeded.

SQL>
SQL> create or replace
  2  function scott.emp_policy(user_name in varchar2,tab_name in varchar2)
  3  return varchar2 is
  4  begin
  5   return q'~deptno=sys_context('MY_CONTEXT','DEPTNO')~';
  6  end;
  7  /

Function created.

SQL>
SQL> grant execute on scott.emp_policy to demo;

Grant succeeded.

Now that you have a context and a package API to set appropriate values in place, the predicate values can be hidden away. In the example below, I’m allowing DEMO to set their own values, but in a real world scenario, this would be blocked from ad-hoc use so that the setting of values would be controlled in whatever way your application requires.


SQL> conn demo/demo@pdb21a
Connected.
SQL> exec scott.pkg_context.set_val('DEPTNO','20');

PL/SQL procedure successfully completed.

SQL>
SQL> select * from scott.emp;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7566 JONES      MANAGER         7839 02-APR-81       2975                    20
      7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
      7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20

5 rows selected.

We haven’t stopped DEMO from being able to use the DBMS_UTILITY.EXPAND_SQL_TEXT routine, but now the value of the DEPTNO predicate is no longer so readily visible.


SQL> conn demo/demo@pdb21a
Connected.
SQL> variable c clob
SQL> begin
  2    dbms_utility.expand_sql_text('select * from scott.emp',:c);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> set long 5000
SQL> print c

C
----------------------------------------------------------------------------------------------------------------------------------
SELECT
  "A1"."EMPNO" "EMPNO",
  "A1"."ENAME" "ENAME",
  "A1"."JOB" "JOB",
  "A1"."MGR" "MGR",
  "A1"."HIREDATE" "HIREDATE",
  "A1"."SAL" "SAL",
  "A1"."COMM" "COMM",
  "A1"."DEPTNO" "DEPTNO"
FROM (
    SELECT
      "A2"."EMPNO" "EMPNO",
      "A2"."ENAME" "ENAME",
      "A2"."JOB" "JOB",
      "A2"."MGR" "MGR",
      "A2"."HIREDATE" "HIREDATE",
      "A2"."SAL" "SAL",
      "A2"."COMM" "COMM",
      "A2"."DEPTNO" "DEPTNO"
    FROM
      "SCOTT"."EMP" "A2"
    WHERE
      "A2"."DEPTNO" = SYS_CONTEXT('MY_CONTEXT','DEPTNO')
  )  "A1"

Note that DEMO can still chose to query the context directly, so we are not totally blocking access to the predicate values, we are just making it less “glaringly obvious”. If you need tighter control, you could look at removing public privileges to DBMS_UTIILITY

7 responses to “Security predicates exposed #JoelKallmanDay”

Duncan & Julie

October 15, 2025 at 8:25 am

Good point. Should we be using VPD, or migrate to Real Application Security?

1. Connor McDonald
  
  October 15, 2025 at 1:58 pm
  
  In either case, don’t expose sensitive literals in security predicates
  
Anton

October 15, 2025 at 9:19 pm

Isn’t the DEMO user able to get the value by simply running

select SYS_CONTEXT(‘MY_CONTEXT’,’DEPTNO’) the_secret_value from dual;

How does this method help protect that value?

aminaa49ed2e27e

October 15, 2025 at 10:57 pm

Does the same thing apply when using OLS?

Jang

October 21, 2025 at 6:06 pm

Sorry for the OT. but how does the @pr work in

SQL> select * from v$vpd_policy
2 @pr

1. Connor McDonald
  
  October 21, 2025 at 7:32 pm
  
  https://github.com/connormcd/misc-scripts/blob/master/sql/pr.sql
  
  1. Jang
    
    October 22, 2025 at 12:46 pm
    
    Brilliant hack of SQL*Plus line editing! Maybe this is a topic for Asktom OH