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!