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 “JAMES” 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 james identified by PasswordProxy;
User created.
  • Alter the destinatiom user ( APPDBA) to connect through the proxy user (JAMES).
SQL> alter user appdba grant connect through james;
User altered.
  • Test the connection for proxy user
SQL> select count(*) from dba_users;

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


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


SQL> select * from proxy_users;

PROXY      CLIENT       AUT FLAGS
---------- ------------ --- -----------------------------------
JAMES      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          JAMES


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;EXTERNAL NAME: oracle      222208723


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

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

  • If you don’t want the proxy user ( James ) 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 JAMES.
SQL> alter user appdba grant connect through james with role connect, resource;

User altered.

SQL> connect james[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
Advertisements
This entry was posted in User Privileges and Roles and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s