Create private dblink from another user

Question

I have the following situation:

• ADMIN_USER - privileged user that can execute whatever he wants and also we use it to deploy scripts to different envs
• REGULAR_USER - just a regular schema that holds executable code

I want to create private dblink for REGULAR_USER under ADMIN_USER (while deploying my scripts).

Questions:

1. Can I achieve this?
2. If so then what privileges should I give to REGULAR_USER/what actions should I do?

Oracle version is 11gR2

Answer 1

Privileged users can indirectly create database links for other users. The privileged user must temporarily grant the regular user CREATE DATABASE LINK, create a temporary procedure in the regular user's schema that will create the database link, execute that procedure, and then drop the temporary procedure and privilege.

create user regular_user identified by regular_user;
grant create session to regular_user;

grant create database link to regular_user;

create or replace procedure regular_user.create_db_link is
begin
    execute immediate
    q'[
        create database link test_link
        connect to regular_user
        identified by "regular_user"
        using 'orcl'
     ]';
end;
/

begin
    regular_user.create_db_link;
end;
/

drop procedure regular_user.create_db_link;

revoke create database link from regular_user;