C##CLOUD$SERVICE Objects Cannot Be Used Within *_RDS_CUSTOM Schema Packages (Doc ID 3047771.1)

Last updated on NOVEMBER 25, 2024

Applies to:

Goal

We are experiencing a small step back regarding the usage of SDK OCI through PLSQL.
It seems that when we execute the same code through SQL Commands using an anonymous block, it works fine.
But now , that we are structuring the application code, using procedures within a package, a simple type reference it's throwing insufficient privileges to access object.

Let's take a simple example with this type: DBMS_CLOUD_OCI_LOGSRCH_LOG_SEARCH_SEARCH_LOGS_RESPONSE_T
Our first analysis states that a PUBLIC synonym exists to object C##CLOUD$SERVICE.DBMS_CLOUD_OCI_LOGSRCH_LOG_SEARCH_SEARCH_LOGS_RESPONSE_T , however the specific grant privilege to access the type is not being specify to MFCS_RDS_CUSTOM schema, which seems that we may need to grant MFCS_RDS_CUSTOM schema to OCI_SDK_ROLE, which is a DB Role that seems to be assigned to all SDK OCI objects. However this role seems to only be specified to SYS schema, and we are not able to use the SDK from RDS since the schema does not have this permission.

Below, we have a few queries that might help on this investigation:
select * from all_objects where object_name like 'DBMS_CLOUD_OCI_LOGSRCH_LOG_SEARCH_SEARCH_LOGS_RESPONSE_T';
SELECT * FROM ALL_SYNONYMS WHERE SYNONYM_NAME LIKE 'DBMS_CLOUD_OCI_LOGSRCH_LOG_SEARCH_SEARCH_LOGS_RESPONSE_T';
SELECT * FROM DBA_TAB_PRIVS WHERE grantee like 'OCI_SDK_ROLE' AND TABLE_NAME LIKE 'DBMS_CLOUD_OCI_LOGSRCH_LOG_SEARCH_SEARCH_LOGS_RESPONSE_T';
SELECT * FROM DBA_TAB_PRIVS WHERE grantee like 'OCI_SDK_ROLE' AND TABLE_NAME LIKE 'DBMS_CLOUD_OCI_%' AND TYPE <> 'TYPE';
select * from dba_role_privs where GRANTED_ROLE like 'OCI_SDK_ROLE';
 

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.