5 Things About Oracle Proxy User You Wish You Knew Before

5 Things About Oracle Proxy User You Wish You Knew Before

October 26, 2016 8 By dianarobete

Are you a senior DBA that knows about proxy users, and never got a chance to implement them?
Or maybe you are just starting out in the DBA world, and haven’t even heard about Oracle proxy accounts and how to set them up.
No matter which group you fit in, you are not alone. You are not the only DBA that hasn’t used proxy before.

Maybe you have used proxy and know all about it. (In this case this article could be a good refresher for you).

Until about 18 months ago, I didn’t use proxy either. Once I set it up, I had one of those AHA moments, and was asking myself, why haven’t I tried this before.

I used to think setting up the proxy user is complicate and time consuming. I was wrong.

Once I understood the process, and created a cheat sheet for myself, setting up the proxy was easy!

Before diving into the details of the proxy account, I’d like you to think about the process of connecting to the database as user SCOTT, when you don’t have the password for user SCOTT.

What do you do?

  • Step 1 – get encrypted password for SCOTT from user$ (I am always having a hard time remembering the columns I need to combine to get the full encrypted password)
  • Step 2 – change SCOTT’s password to a known value. (risky, and not always possible)
  • Step 3 – connect SCOTT/new_password
  • Step 4 – in a different session, change SCOTT’s password back to previous encrypted value. The best case scenario is that SCOTT’s profile allows you to reset password to a previous value.
    If not, you either have to temporarily assign SCOTT to a different profile that allows this change, or alter the existing profile.

At the end, you can only hope that nothing has been broken by the temporary password change.

Can you see how complicate, and dangerous this is?

The proxy user is here to the rescue!

1. What Is A Proxy User And A Connecting User?
2. What Are The Benefits Of The Proxy User?
3. Who Can Set It Up?
4. How To Set It Up?
5. Session Settings And The Audit Trail.

1. What Is A Proxy User And A Connecting User?

A proxy user allows you to connect as another user, without knowing the password. In other words, I need to connect as user SCOTT, and I do not know the password for SCOTT.
The proxy user allows me to connect through SCOTT user, with my username and my password, if all is setup correctly.

On the other hand, a connecting user, is “the other” user. In the example above, SCOTT is the connecting user, and my username is the proxy user

2. What Are The Benefits Of The Proxy User?

  • Connect to the database as a different user, without knowing the password.
  • Setup developers, or other groups to connect as a proxy user, in order to have the same set of privileges. No need to reinvent the wheel around security. Remember the requests to create a shared account for developers? No need to do that anymore.
  • Using the same connecting user, different roles can be granted to different proxy users.
  • Security and auditing is in place. You know exactly who is doing what action in the database, even when connected as a proxy user.
  • Private database links creation, testing and deletion in a different schema.
    This is huge! How many times you wished you could recreate database links in other schemas, without connecting as the owner? DB Links are the objects that need to be created as the owner. You cannot pre-qualify a db link with the owner. Proxy is here to help!

3. Who Can Set It Up?

You need to have a special privileges to be able to setup proxy users.

Here is what you need (as per Oracle’s documentation):

  • CREATE USER system privilege to create a database user account that will be used as a proxy user account
  • DV_ACCTMGR role if Oracle Database Vault is enabled, to create the proxy user account
  • Ability to grant the CREATE SESSION system privilege to the proxy user account
  • ALTER USER system privilege to enable existing user accounts to connect to the database through the proxy account

4. How To Set It Up?

Setting up a proxy user, is a 3 step process, plus a bonus testing step:

  • Create the proxy user account
  •  create user drobete identified by password; 
  • Grant create session to the proxy user. It is recommended that you only grant this permission. However, other permissions and roles are allowed.
  •  grant create session to drobete; 
  • Alter the connecting user, to connect through the proxy account
  •  alter user scott grant connect through drobete; 

    If you only want certain roles of the connecting user to be active, when the proxy connects, you can do that too:

     alter user scott grant connect through drobete with role scott_ro;
  • As always, test the newly created proxy account, to confirm it’s working.
  •  connect drobete[scott]/password 

If you are curious to see the existing proxy users in the database, there is a data dictionary view that can be queried:

PROXY_USERS

 select * from proxy_users; 

PROXY          CLIENT        AUT FLAGS
-------------- ------------- --- -----------------------------------
DROBETE        SCOTT         NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

5. Session Settings And The Audit Trail.

Did you know the sys_context will return the following when connected as proxy?

session_user: connecting user
session_schema: connecting user
current_schema: connecting user
proxy_user: proxy user

?View Code ENGLISH
 
connect drobete[scott]/password
 
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
-------------------- -------------------- -------------------- --------------------
SCOTT                SCOTT                SCOTT                DROBETE
 
 
SQL>show user
USER is "SCOTT"

AUD$, or DBA_AUDIT_TRAIL will have PROXY$SID column populated with the proxy session id.

?View Code ENGLISH
select a.username connecting_user ,
       a.action_name,
       a.timestamp, 
       a.comment_text, 
       b.username proxy_user, 
       b.action_name proxy_action, 
       b.comment_text proxy_comment
from dba_audit_trail a, dba_audit_trail b
where a.proxy_sessionid=b.sessionid
order by a.timestamp,desc
 
-- You can see that user SCOTT connected through proxy user DROBETE. All actions recorded under SCOTT, were actually performed by DROBETE
 
USER  ACTION_NAME TIMESTAMP   COMMENT_TEXT             PROXY_USER  PROXY_ACTION                PROXY_COMMENT
----- ----------  ----------  ------------------------ ----------- ----------- --------------------------- ---------------
SCOTT LOGON       17-NOV-15   Authenticated by: PROXY; DROBETE     PROXY AUTHENTICATION ONLY   Authenticated by: DATABASE; 
                              Client address:(ADDRESS=                                         Client address:(ADDRESS=
                              (PROTOCOL=tcp)                                                   (PROTOCOL=tcp)
			      (HOST=x.x.x.x)						       (HOST=x.x.x.x)
			      (PORT=41xxx))						       (PORT=41xxx))

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive
The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!

–Diana