Oracle Proxy User Configurations

A proxy user is a user who is able to connect as another user without password required. In this example, user “TESTUSER” needs connect as user “APPDBA” without knowing the password of user “APPDBA”.

• Create destination user with appropriate role(s).

SQL> create user appdba identified by Password;
User created.

SQL> grant connect,resource,dba to appdba;
Grant succeeded.

• Create the proxy user account.

SQL> create user testuser identified by PasswordProxy;
User created.

• Alter the destinatiom user ( APPDBA) to connect through the proxy user (TESTUSER).

SQL> alter user appdba grant connect through testuser;
User altered.

• Test the connection for proxy user.

SQL> select count(*) from dba_users;

 COUNT(*)
----------
 31


SQL> connect testuser[appdba]/PasswordProxy
Connected.
SQL> show user
USER is "APPDBA"


SQL> select * from proxy_users;

PROXY      CLIENT       AUT FLAGS
---------- ------------ --- -----------------------------------
TESTUSER   APPDBA       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES


SQL> select sys_context('userenv','session_user') session_user,
            sys_context('userenv','session_schema') session_schema,
            sys_context('userenv','current_schema') current_schema,
            sys_context('userenv','proxy_user') proxy_user
    from dual;

SESSION_USER    SESSION_SCHEMA   CURRENT_SCHEMA PROXY_USER
--------------- --------------- --------------- ---------------
APPDBA          APPDBA          APPDBA          TESTUSER


SQL>select USERNAME,ACTION_NAME,COMMENT_TEXT,PROXY_SESSIONID 
      from dba_audit_trail 
      where PROXY_SESSIONID is not null

USERNAME ACTION_NAM COMMENT_TEXT               PROXY_SESSIONID
-------- ---------- -------------------------- ---------------
APPDBA   LOGON      Authenticated by: PROXY;    222208723
                    EXTERNAL NAME: oracle      


SQL> select USERNAME,ACTION_NAME,COMMENT_TEXT ,PROXY_SESSIONID 
     from dba_audit_trail 
     where SESSIONID=222208723;

USERNAME  ACTION_NAME               COMMENT_TEXT     PROXY_SESSIONID
--------  ------------------------  ---------------- ---------------
TESTUSER  PROXY AUTHENTICATION ONLY Authenticated by: 
                                    DATABASE

• If you don’t want the proxy user (testuser ) has all the privilege of user APPDBA, alter the proxy user by giving the certain roles ( CONNECT, RESOURCE ). In this case, DBA_USERS is not accessible to user TESTUSER.

SQL> alter user appdba grant connect through testuser with role connect, resource;

User altered.

SQL> connect testuser[appdba]/PasswordProxy
Connected.

SQL> select count(*) from dba_users;
select count(*) from dba_users
 *
ERROR at line 1:
ORA-00942: table or view does not exist

• The proxy authentication can be revoked using the following command.

ALTER USER appdba REVOKE CONNECT THROUGH testuser;