Proxy users

1. If we don’t want to share application user password to all the users who need to connect to our schema.
2. For better audit
   proxy users can be created

CREATE USER app_user_unknown_passwd IDENTIFIED BY unknown_password;
GRANT CREATE SESSION TO app_user_unknown_passwd;

CREATE USER connect_user_known_password IDENTIFIED BY known_password;
GRANT CREATE SESSION TO connect_user_known_password;

ALTER USER app_user_unknown_passwd GRANT CONNECT THROUGH connect_user_known_password;

Check through proxy_users view

SELECT * FROM proxy_users;

PROXY	CLIENT	AUTHENTICATION	FLAGS
CONNECT_USER_KNOWN_PASSWORD	APP_USER_UNKNOWN_PASSWD	NO	PROXY MAY ACTIVATE ALL CLIENT ROLES

Try to connect from SQL*Plus and check some context parameters

sqlplus connect_user_known_password[app_user_unknown_passwd]/known_password@db122

SQL> select USER, 
  sys_context('USERENV','PROXY_USER'), 
  sys_context('USERENV','CURRENT_SCHEMA') 
from dual;

USER
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')
--------------------------------------------------------------------------------
APP_USER_UNKNOWN_PASSWD
CONNECT_USER_KNOWN_PASSWORD
APP_USER_UNKNOWN_PASSWD