Today, we will see how to hide sensitive data in your Oracle database from other users with the “SELECT ANY TABLE” privilege using Oracle Virtual Private Database (VPD).

First of all: in a perfect world, this situation should never happen!
If you need to store sensitive data in your database, you should place it in a dedicated location, without any other schema and with strict security rules.

Anyway, if you choose to tread this post, you probably have no other choice.

Context:

I want to create a new user in an existing database. This user will hold confidential information that no other users in this database are allowed to view. Problem:there is plenty of schemas in this database, and some of these have the “SELECT ANY TABLE” privilege so they can freely query the table and access the sensitive data.

• New schema containing sensitive data:CONFIDENTIAL
• Old schema in the same database with “SELECT ANY TABLE” privilege:CURIOUS
• The data access rule must be enforced each time user CONFIDENTIAL creates a new table

Prerequisites:

Oracle VPD is available only with Oracle Database Enterprise Edition (tested on 10g, 11g, 12c). This feature comes with no additional cost (see Oracle licensing information).

How-to

Schema creation

Nothing complicated here. Only the “CONFIDENTIAL” schema is created with the required quota and privileges. Of course, it is for demonstration purpose only so I simplify with PASSWORD=USER.

SQL> create user CONFIDENTIAL identified by "CONFIDENTIAL" quota unlimited on USERS;

User created.

SQL> grant create session, create table, create view to "CONFIDENTIAL";

Grant succeeded.

VPD setup

Here is the interesting part but we need a little background to understand the basics of Oracle VPD.

Oracle VPD allows you to add a dynamic “where” clause to any SQL statement that is issued against a table to which an Oracle Virtual Private Database security policy was applied. These policies also apply to views and synonyms.

Security policies are enforced with one package:”DBMS_RLS” and has a lot of options available (see DBMS_RLS reference for a complete overview).

In my example, I’m going to use a “policy_function” that will work that way:each time a user queries a table from the CONFIDENTIAL schema, the policy_function will check that the user is CONFIDENTIAL:

• If the user is CONFIDENTIAL, nothing happens and the query will return what the user is asking for.
• If the user is not CONFIDENTIAL (CURIOUS for example), the function will add a dynamic “where clause” to the query: “where 1=0”. As you can imagine, the query will never return anything to that user.

One inconvenience:user CURIOUS can still describe the table and display column definition.

So, let’s get started and create the function:

# sqlplus / as sysdba

SQL> CREATE OR REPLACE FUNCTION HIDE_TABLE_FROM_CURIOUS (object_schema IN VARCHAR2, object_name in VARCHAR2)
RETURN VARCHAR2 AS
begin
	if (user = 'CONFIDENTIAL') then
		return '';
	else
		return '1=0';
	end if;
end;
/

Very simple, isn’t it? (The function requires 2 parameters even though they are not used inside your function code). You can add more users in the “if” condition if others users need to access to the sensitive data

For example:

if (user = 'CONFIDENTIAL') OR (user = 'CONFIDENTIAL_RO') then

At this point, the function is useless. Remember the context:I need to apply the security rule to all newly created tables inside “CONFIDENTIAL” schema. To achieve this, I must create a trigger that will automatically add a policy to each newly created table.

# sqlplus / as sysdba

SQL> CREATE OR REPLACE TRIGGER new_table_trigger
AFTER CREATE ON CONFIDENTIAL.SCHEMA
DECLARE
	OBJNAME VARCHAR(30) := ORA_DICT_OBJ_NAME;
	OBJTYPE VARCHAR(20) := ORA_DICT_OBJ_TYPE;
	OBJOWNER VARCHAR(30) := ORA_DICT_OBJ_OWNER;
	RLSNAME VARCHAR(50);
BEGIN
	IF OBJTYPE = 'TABLE' THEN
		RLSNAME := 'RLS_'||OBJNAME;
		dbms_rls.add_policy(object_schema=>OBJOWNER,object_name=>OBJNAME,policy_name=>RLSNAME,function_schema=>'SYS',policy_function=>'HIDE_TABLE_FROM_CURIOUS');
	END IF;
END;
/

If the schema “CONFIDENTIAL” already owns tables, we need to add a policy for each of them manually:

 dbms_rls.add_policy(object_schema=>'CONFIDENTIAL',object_name=>'TABLE_NAME',policy_name=>'RLS_TABLE_NAME',function_schema=>'SYS',policy_function=>'HIDE_TABLE_FROM_CURIOUS'); 

Checks:

Ok, it’s time to check if everything works as expected!

# sqlplus CONFIDENTIAL/CONFIDENTIAL

SQL> create table secret(col1 varchar(10), col2 varchar(10));

Table created.

SQL> create view v_secret as (select * from secret);

View created.

SQL> insert into secret values('secret1','topsecret');

1 row created.

SQL> insert into secret values('secret2','topsecret');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from secret;

COL1       COL2
---------- ----------
secret1    topsecret
secret2    topsecret

SQL> select * from v_secret;

COL1       COL2
---------- ----------
secret1    topsecret
secret2    topsecret

Looks good for CONFIDENTIAL, the user can query its own table.

We can validate the rls policy creation with this query:

# sqlplus / as sysdba

SQL> select OBJECT_OWNER, OBJECT_NAME, POLICY_NAME, ENABLE from dba_policies where OBJECT_OWNER='CONFIDENTIAL';

OBJECT_OWNER         OBJECT_NAME          POLICY_NAME          ENABLE
-------------------- -------------------- -------------------- ----------
CONFIDENTIAL         SECRET               RLS_SECRET           YES

The policy must be enabled (default when created) in order to prevent access to the table.

Now, let’s try to query the table with user “CURIOUS” who has “SELECT ANY TABLE” privilege granted.

# sqlplus CURIOUS/CURIOUS

SQL> desc CONFIDENTIAL.secret
 Name           Null?    Type
 -------------- -------- ----------------------
 COL1                    VARCHAR2(10 CHAR)
 COL2                    VARCHAR2(10 CHAR)

Ok, the user can describe the table but as said before, it was expected. But what about data?

SQL> select * from CONFIDENTIAL.secret;

no rows selected

SQL> select * from CONFIDENTIAL.v_secret;

no rows selected

Success! CURIOUS cannot see sensitive data!

Limitations

There are several limitations with VPD. Some are due to Oracle VPD design and others appear if somebody messed with security on the database.

Oracle limitation

• SYS is free of any security policy so SYS user can query the table without any restriction

Bad database design limitations

• If another schema in the database has “EXEMPT ACCESS POLICY” privilege granted, they can access the data
• If your database is started with “O7_DICTIONARY_ACCESSIBILITY” set to “true” and another schema has “SELECT ANY TABLE” + “EXECUTE ANY PROCEDURE” privileges granted, they can disable the policy, query the tables and enable the policy again
• Same problem with “SELECT ANY TABLE” + “EXECUTE_CATALOG_ROLE” privileges granted (without “O7_DICTIONARY_ACCESSIBILITY” set to “true”)

One example to demonstrate my second point because it is a little bit “tricky”:

# sqlplus / as sysdba

SQL> show parameter O7_DICTIONARY_ACCESSIBILITY

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
O7_DICTIONARY_ACCESSIBILITY          boolean     FALSE

# sqlplus CURIOUS/CURIOUS

SQL> select * from CONFIDENTIAL.secret;

no rows selected

SQL> exec dbms_rls.ENABLE_POLICY(object_schema=>'CONFIDENTIAL',object_name=>'SECRET',policy_name=>'RLS_SECRET',ENABLE=>FALSE);

PL/SQL procedure successfully completed.

SQL> select * from CONFIDENTIAL.secret;

COL1       COL2
---------- ----------
secret1    topsecret
secret2    topsecret

SQL> exec dbms_rls.ENABLE_POLICY(object_schema=>'CONFIDENTIAL',object_name=>'SECRET',policy_name=>'RLS_SECRET',ENABLE=>TRUE);

PL/SQL procedure successfully completed.

SQL> select * from CONFIDENTIAL.secret;

no rows selected

User CONFIDENTIAL will never know that CURIOUS had disabled the policy, queried the table and re-enabled the policy.

I hope this post has been useful. Stay tuned for more DBA stuff!