Insufficient privileges while creating database link

User_4CGCS

Hi I have created a apex instance in oracle cloud, now from this apex I am trying to create a database link, but its showing me the insufficient privileges error. I tried giving all the role grant to this user from the database which is provisioned while creating the apex, I also ran the command separately " GRANT CREATE DATABASE LINK TO USERNAMEHERE " from the autonomous db as admin, still I am getting same error:

This is the command I am running:

CREATE DATABASE LINK horus_ro

CONNECT TO XXXX IDENTIFIED BY XXXXX

USING

'(DESCRIPTION=

(ADDRESS=(PROTOCOL=TCP)

(HOST=xxx.oraclecorp.com)

(PORT=1521))

(CONNECT_DATA=(SERVICE_NAME=xxx.oraclecorp)))';

Solomon Yakobson

Are you connected as USERNAMEHERE?

SY.

User_4CGCS

that is just the name i replaced with original one.

Solomon Yakobson

I understand, but are you connected as user you granted CREATE DATABASE LINK to?

SY.

User_4CGCS

no while granting the CREATE DATABASE LINK the user is admin.

User_4CGCS

Solomon Yakobson

Then you are trying to create database link in schema admin. Oracle doesn't allow creating database link in other schema. You must connect as user who will own database link (or execute definer rights procedure owned by database link owner that createds database link).

SY.

User_4CGCS

Okay i think i might have confused you:

Here are two pics which will clear things:

First pic showing the creation of db link in apex,schema is different :

second is this pic, which is from the oracle cloud, I am giving priv from there as admin to the user which is in apex:

Solomon Yakobson

This is APEX, not SQL issue. Post this on APEX forum.

SY.

Solomon Yakobson

And according to Oracle:

APEX Service is built on top of Autonomous Database (Shared). ADB-Shared has a specific way to create database links - using DBMS_CLOUD_ADMIN and not CREATE DATABASE LINK statement. So it looks like you need to use DBMS_CLOUD_ADMIN.CREATE_DATABASE_LINK.

SY.

User_4CGCS

Yeah i already tried that, but think of this as apex has its own schema, and if I form a dblink outside of this schema, will I be able to access that from inside the apex, I don't think so.Any way it was lot of help. thanks a lot. and the issue is SQL only as apex will interact with the db and will post the error which the db will return.