oracle-databaseoracle-apexoracle-cloud-infrastructureoracle-autonomous-db

Referencing APEX_230200 view/table from another schema


I am using Oracle Autonomous Database on OCI. I want to create a foreign key in one of my schemas that references a view in APEX_230200 schema. I executed the statement below using ADMIN user (which is AFAIK the highest privileged user in Autonomous Database - That is, no access to SYS, or SYSDBA):

grant REFERENCES on APEX_230200.apex_appl_acl_users TO myfin;

I got the following error:

grant REFERENCES on APEX_230200.apex_appl_acl_users TO myfin
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

What am I missing?

FWIW, the reason I am trying to reference the view is I want to re-use APEX user ID as an identifier in my application tables.


Solution

  • You can't create such a dependency, and you shouldn't do so no matter what level of privileges you have). Because that's basically all internal - we might change that table in the next release/patch, we might drop it, we might do anything we like with it.

    If you really need that data, create a materialized view as a copy of that data in your own schema, and then refresh that from time to time.