Creating a Read-Only Account on Database with VPD or Label Security

I have an interesting case to share in this post.

The story started when a client asked for a read-only account (let’s call it RO_USER) with access to objects under another schema (let’s call it SCHEMA_OWNER). Easy going, right?

• Create user
• Grant select on SCHEMA_OWNER tables
• Possibly grant execute on SCHEMA_OWNER procedures/packages/functions
• Possibly private synonyms on RO_USER for SCHEMA_OWNER objects

However, when connecting with RO_USER and executing a query on a table, here’s what happened:

select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;

COUNT(*)
----------
0

When connecting with SCHEMA_OWNER and executing the same query:

select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;

COUNT(*)
----------
9255013

There are some known situations where this can happen, such as the usage of private database links. Private database links incorporate the concept of “private” and can be accessed only by their owner. Which makes sense, if we consider the security problems a database link can create.

When this is the issue, the only alternative is using proxy connection. So here’s what I tried:

SQL> alter user SCHEMA_OWNER grant connect through RO_USER;

User altered.

SQL> conn RO_USER[SCHEMA_OWNER]/***********
Connected.
SQL> select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;

  COUNT(*)
----------
   9255013

Working!

However, when checking for the scenario, I noticed this was a real table, not a synonym or view using a private database link. Why was it working then?

Also, this alternative creates some problems, as the RO user would now have access to DML on SCHEMA_OWNER tables, as opposed to read-only access.

If not a database link, the only possible explanation would be VPD (Virtual Private Database) or Label Security in place. Both of the features work by limiting the access to data depending on the current schema.

And now it’s a bingo:

SQL> select object_owner,object_name,policy_name,function, PACKAGE from dba_policies where object_name='TABLE_EXAMPLE';

OBJECT_OWNER	     OBJECT_NAME		    POLICY_NAME 		   FUNCTION	         PACKAGE
-------------------- ------------------------------ ------------------------------ --------------------- ---------
SCHEMA_OWNER	     TABLE_EXAMPLE		     POLICY_EXAMPLE	           FCN_TABLE_EXAMPLE	 PKG_EXAMPLE

So what to do?

Here’s the trick: Using EXEMPT ACCESS POLICY. As per the linked Oracle Document “… database users granted the EXEMPT ACCESS POLICY privilege, either directly or through a database role, are exempt from VPD enforcements.”

This is also valid for Data Pump and legacy Export as per MOS When Is Privilege “Exempt Access Policy” Needed For Export? (Doc ID 2339517.1).

Let’s check for it:

SQL> GRANT EXEMPT ACCESS POLICY TO RO_USER;
Grant succeeded.

SQL> conn RO_USER[SCHEMA_OWNER]/*********** 
Connected.

SQL> select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;
COUNT(*)
----------
 9255015

Considerations:

• Note this exempts the RO_USER from all applicable policies. It would be much better to use rules that provide exemptions for the objects needed.
• Also, VPD and Label Security are in place for a reason, so you should consider the risks being raised by enabling this workaround.

And what about Label Security?

Although it didn’t apply in my case, this is from the same Oracle Document: “They are also exempt from some Oracle Label Security policy enforcement controls, such as READ_CONTROL and CHECK_CONTROL, regardless of the export mode, application, or utility used to access the database or update its data.”

I hope this helps you!

If you have any questions or thoughts, please leave them in the comments.