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.
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
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).
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.
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
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:
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!
There are several limitations with VPD. Some are due to Oracle VPD design and others appear if somebody messed with security on the database.
- 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!