Create a Database Link in another user's schema

From documentation:

Restriction on Creating Database Links

You cannot create a database link in another user's schema, and you cannot qualify dblink with the name of a schema. Periods are permitted in names of database links, so Oracle Database interprets the entire name, such as ralph.linktosales, as the name of a database link in your schema rather than as a database link named linktosales in the schema ralph.)

CREATE DATABASE LINK

So documentation clearly states that you cannot create database link in another user's schema.



As I am doing all my dba tasks using admin accounts it is little unconvenient for me to search for other schema passwords in my documents or asking developers for this information.
All this is little time consuming so I've found more convenient way to create database links in another user's schema when I am pretty sure that my actions won't make disturbance.

Little demo case...

Task:
- Create database link in MSUTIC schema that connects to APP schema on TEST10 database.

My intention is to temporarily change MSUTIC user password, login as MSUTIC user, quickly create database link and at the end return password as it was before changing.

First to find out encrypted version of password (remember this info).

system@TEST11> select name, password
2  from sys.user$
3  where name='MSUTIC';

NAME                           PASSWORD
------------------------------ ------------------------------
MSUTIC                         66EE6D5F9AB42E0F

1 row selected.

Changed current MSUTIC password to temporary "tmp_pwd" and granted "create database link" privilege to MSUTIC.

system@TEST11> alter user msutic identified by tmp_pwd;

User altered.

system@TEST11> grant create database link to msutic;

Grant succeeded.

Connect as MSUTIC user using temporary password and creat database link.

system@TEST11> connect msutic/tmp_pwd@test11
Connected.

msutic@TEST11> CREATE DATABASE LINK APP_DB
2   CONNECT TO APP
3   IDENTIFIED BY app123
4   USING '(DESCRIPTION =
5         (ADDRESS_LIST =
6           (ADDRESS = (PROTOCOL = TCP)(HOST = linux01)(PORT = 1521))
7         )
8         (CONNECT_DATA =
9           (SID = TEST10)
10         )
11       )';

Database link created.

Simple test:

msutic@TEST11> select count(*) from app.app_log@app_db;

COUNT(*)
----------
355412

1 row selected.

DB Link is functioning perfectly and now I can revoke "create database link privilege" and return password as it was before.

msutic@TEST11> conn system@test11
Enter password:
Connected.

system@TEST11> revoke create database link from msutic;

Revoke succeeded.

system@TEST11> alter user msutic identified by values '66EE6D5F9AB42E0F';

User altered.

The key thing is to have prepared scripts to do this actions as quick as possible because when you change current schema password to temporary other users or apps will not be able to log in.