Proxy User Authentication: Create DBLINK in another schema

Problem

We need to create private database link in another schema. But we don’t know the password for this user.

Solution (Proxy user)

For example, my username is marik and database link should be created in dbcleanup’s schema.

SQL> alter user DBCLEANUP grant connect through marik;
SQL> connect marik[DBCLEANUP]/123@orcl
SQL> show user
USER is "marik[DBCLEANUP]"
SQL> create database link MYLINK 
connect to LINKUSER identified by "password" using 'ORCL2';

Proxy users can be identified using the PROXY_USERS view.

SQL> select * from proxy_users;

The proxy authentication can be revoked using the following command.

SQL> alter user DBCLEANUP revoke connect through marik ;