Using grant connect through to manage database links

Nobody can say that I am not current and topical with my posts. This post refers to functionality that was introduced in 9i, however I have just come across it and thought it useful enough to blog about it.

The command ALTER USER USERB GRANT CONNECT THROUGH USERA allows a proxy connection to be made using the username and password of USERA but  to connect in as USERB. The purpose is so that management of a user can be done without knowing that users password or changing it. This is most commonly going to be used by support teams.

I will give an example

SQL> create user test1 identified by password;

User created.

SQL> grant create session , resource , unlimited tablespace to test1;

Grant succeeded.

SQL> create user test2 identified by password;

User created.

SQL> grant create session , resource , unlimited tablespace to test2;

Grant succeeded.

SQL> alter user test2 grant connect through test1;

User altered.

SQL> connect test1[test2]/password
Connected.
SQL> show user
USER is "TEST2"

Here is a way that I can create a database link as user2 without knowing that users password. User2 has to have create database link privilege.

SQL> connect / as sysdba
Connected.
SQL> grant create database link to test2;

Grant succeeded.

SQL> connect test1[test2]/password
Connected.
SQL> show user
USER is "TEST2"
SQL> create database link TSTAUDIT connect to remote1 identified by password using 'TSTSAUDIT';

By the way as a privileged user you can apparerently create a link in another schema

SQL> connect / as sysdba
Connected.
SQL> create database link test2.TSTAUDIT connect to remote1 identified by password using 'TSTSAUDIT';

Database link created.

But the link is still owned by SYS but has the name TEST2.TSTAUDIT

SQL> select owner, DB_LINK username, host from dba_db_links;

OWNER                          USERNAME    HOST
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SYS                            TEST2.TSTAUDIT TSTSAUDIT

drop database link "TEST2.TSTAUDIT";