Proxy access is a great feature provided by Oracle to ensure security and give flexibility to users. It practically allows you to log in as a different user without knowing his password (thus there is no security issue at all).
Not only you can allow users to act as another user (very useful in case of a shared application) but you can also track all their activity. Moreover, you do not have to set all security measures and settings all over again for each user.
Say we have two users in a database: JOHN and MARRY.
I will now allow John to access Marry’s account.
ALTER USER MARRY GRANT CONNECT THROUGH JOHN;
You can check the changes in the dba_proxies catalog.
Once logged in, you can check who is the proxy user and original user (for security/logging purposes) easily by running the command below
SELECT USER AS act_user
, nvl(sys_context('USERENV', 'PROXY_USER'), USER) AS proxy_name
FROM dual;
ACT_USER | PROXY_NAME
-------------------------
MARRY | JOHN
To revoke this privilege use the same command as for granting but use REVOKE instead
ALTER USER MARRY REVOKE CONNECT THROUGH JOHN;
This way of accessing is very helpful when you need to share an application account which will store all business logic per department/application. Instead of granting multiple privileges or/and roles to every user, you just set up this application account and grant proxy access to all privileged users.