This article presents proxy connections that enables to login to other schema using own user authentication.

CONNECT user[schema to connect]/user password

Before 11G it was quite complicated yet possible using ALTER USER .. IDENTIFIED BY VALUES . Read following article: Oracle password reset

Now you just have to grant GRANT CONNECT THROUGH. Following statement enables user HR to connect to user TOMASZ using HR password.

ALTER USER tomasz GRANT CONNECT THROUGH hr;

New view PROXY_USERS shows the privilege

SELECT * FROM proxy_users;

PROXY CLIENT AUTHENTICATION FLAGS                              
----- ------ -------------- -----------------------------------
HR    TOMASZ NO             PROXY MAY ACTIVATE ALL CLIENT ROLES

So now it’s possible to login to schema TOMASZ using HR password

CONNECT hr[tomasz]/hr
SHOW USER
USER is "TOM"

Revoke privilege

ALTER USER tomasz REVOKE CONNECT THROUGH hr;

Have a fun 🙂

Tomasz