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

Hi all,

This is an interesting case, specific to be understood by easy to be resolved.

The whole 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, that’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

Hm, in general, the known limitations for this approach are:

Private database links: In case this is the issue, the only alternative is using Proxy Connection. Trying this:

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, noticed this is a real table, not a synonym or view using a private database link. Why is that?

Also, this alternative creates some problems, as the RO user would now have access to DML on SCHEMA_OWNER tables, not Read-Only access anymore.

Well, the other option:

VPD or Label Security: Limit access to data depending on the current schema. That’s a match:

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

OK!

So what to do?

Here is the trick: https://docs.oracle.com/cd/B19306_01/network.102/b14266/apdvpoli.htm#i1006985

Using EXEMPT ACCESS POLICY. As per 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 Datapump 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> select count(*) from SCHEMA_OWNER.TABLE_EXAMPLE;
COUNT(*)
----------
 9255015

And what about Label Security?

That was not my case, as you could see, but as per 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 it helps you!

Author: matheusdba