How to Hide Columns with Column-Level VPD (DBMS_RLS)

Virtual Private Database adds a WHERE condition to every query on a table, through a policy function, so users see only their rows. Column-level VPD goes further: all rows stay visible, but sensitive columns are shown only in the rows the policy allows, and NULL elsewhere. DBMS_RLS.ADD_POLICY sets up both.

Code for This Guide

The main examples are in the examples/pkg-security folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

dbms_rls.add_policy(
  object_schema         => 'SCHEMA',
  object_name           => 'TABLE',
  policy_name           => 'NAME',
  policy_function       => 'FUNCTION_RETURNING_PREDICATE',
  statement_types       => 'SELECT',
  policy_type           => dbms_rls.context_sensitive,
  sec_relevant_cols     => 'COL1, COL2',
  sec_relevant_cols_opt => dbms_rls.all_rows);

The policy function takes the schema and object name and returns a predicate as text. Managing policies needs EXECUTE on DBMS_RLS.

Row-Level Policy First

Each travel agent works for one country, set in an application context by a trusted procedure. The policy adds country_code = the agent's country to every query on CUSTOMERS.

Example:

-- the application sets the agent's country in a context; only this procedure can
create or replace context agent_ctx using set_agent_country;

create or replace procedure set_agent_country (p_country varchar2) is
begin
  dbms_session.set_context('AGENT_CTX', 'COUNTRY', p_country);
end;
/
-- the policy function returns the predicate that Oracle adds to every query
create or replace function agent_country_filter (p_schema varchar2, p_object varchar2)
  return varchar2 is
begin
  return 'country_code = sys_context(''AGENT_CTX'', ''COUNTRY'')';
end;
/
begin
  dbms_rls.add_policy(object_schema   => 'NIMBUS',
                      object_name     => 'CUSTOMERS',
                      policy_name     => 'AGENT_COUNTRY',
                      policy_function => 'AGENT_COUNTRY_FILTER',
                      statement_types => 'SELECT, UPDATE, DELETE',
                      policy_type     => dbms_rls.context_sensitive);
end;
/
exec set_agent_country('IN')
select count(*) as customers, min(country_code) as country from customers;

exec set_agent_country('AE')
select count(*) as customers, min(country_code) as country from customers;

Output:

Context AGENT_CTX created.

Procedure SET_AGENT_COUNTRY compiled

Function AGENT_COUNTRY_FILTER compiled

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

   CUSTOMERS COUNTRY
____________ __________
           8 IN

PL/SQL procedure successfully completed.

   CUSTOMERS COUNTRY
____________ __________
           7 AE

The same query counts 8 customers for India and 7 for the UAE: the rows outside the agent's country are not there at all.

Hide Columns Instead of Rows

The policy is replaced by one with SEC_RELEVANT_COLS and ALL_ROWS. Now the predicate decides only where EMAIL and PHONE are shown.

Example:

begin
  dbms_rls.drop_policy('NIMBUS', 'CUSTOMERS', 'AGENT_COUNTRY');
  -- all rows stay visible; the sensitive columns are only shown for the agent's country
  dbms_rls.add_policy(object_schema         => 'NIMBUS',
                      object_name           => 'CUSTOMERS',
                      policy_name           => 'AGENT_COUNTRY',
                      policy_function       => 'AGENT_COUNTRY_FILTER',
                      statement_types       => 'SELECT',
                      policy_type           => dbms_rls.context_sensitive,
                      sec_relevant_cols     => 'EMAIL, PHONE',
                      sec_relevant_cols_opt => dbms_rls.all_rows);
end;
/
exec set_agent_country('AE')
select customer_id, country_code, email, phone
from   customers
where  customer_id between 1 and 5
order  by customer_id;

select object_name, policy_name, function, sel, upd from user_policies;

Output:

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

   CUSTOMER_ID COUNTRY_CODE    EMAIL                      PHONE
______________ _______________ __________________________ ___________________
             1 AE              diego.lopez@example.com    +971 22 414 1147
             2 IN
             3 GB
             4 BR
             5 IE

OBJECT_NAME    POLICY_NAME      FUNCTION                SEL    UPD
______________ ________________ _______________________ ______ ______
CUSTOMERS      AGENT_COUNTRY    AGENT_COUNTRY_FILTER    YES    NO

All five customers are listed, but only customer 1, in the UAE, shows an e-mail address and phone number; the others show NULL in those columns. USER_POLICIES lists the policy for SELECT only. The example drops the policy afterward.

Things to Know

• Without ALL_ROWS, a column-level policy filters rows, and only when a query uses one of the sensitive columns.
• The masked columns are NULL, so applications must not treat NULL as no data for them.
• Users with the EXEMPT ACCESS POLICY privilege, and SYS, are not affected by VPD policies.

Cleanup:

drop procedure set_agent_country;
drop function agent_country_filter;
drop context agent_ctx;

Related Guides

• Row-Level Security in Oracle APEX with VPD: Show Each User Only Their Own Data
• How to Use DBMS_SESSION in Oracle

Conclusion

Column-level VPD keeps every row visible and shows sensitive columns only where the policy predicate is true. Add the policy with SEC_RELEVANT_COLS and ALL_ROWS, drive the predicate from an application context, and remember that masked values appear as NULL.