Proxy user authentication allows you to access another schema via a different user. In other words, dba can connect on behalf of a specific user without knowing the password. So, let’s demonstrate this feature with an example. Oracle 11g is used for test part.
SQL> CONN / AS SYSDBA
CONNECTED.
SQL>
SQL> CREATE USER PROXY_USER IDENTIFIED BY "PASS";
USER CREATED.
SQL>
SQL> ALTER USER HR GRANT CONNECT THROUGH PROXY_USER;
USER ALTERED.
SQL>
Now, we can connect to HR via PROXY_USER with the following command.
SQL> CONN PROXY_USER[HR]/PASS
CONNECTED.
SQL>
SQL>
SQL> SHOW USER
USER: "HR"
You can query PROXY_USERS table in order to list all proxy users in your database.
SQL> select * from proxy_users;
PROXY CLIENT AUT FLAGS
---------- ---------- --- -----------------------------------
PROXY_USER HR NO PROXY MAY ACTIVATE ALL CLIENT ROLES
The proxy privilege can be revoked like below.
ALTER USER HR REVOKE CONNECT THROUGH PROXY_USER;
Moreover, you you can limit the proxy user as follows.
ALTER USER HR GRANT CONNECT THROUGH PROXY_USER WITH ROLE ANY_ROLE;
ALTER USER HR GRANT CONNECT THROUGH PROXY_USER WITH ROLE ALL EXCEPT ANY_ROLE;
ALTER USER HR GRANT CONNECT THROUGH PROXY_USER WITH NO ROLES;