Proxy Authentication

In this post I will describe a little but very useful feature in Oracle named Proxy Authentication.

A proxy user can be used to login into the database on behalf of another user without knowing their password. Of course, to do this you must be a sys user.

How does it work?

First you must create a new user:

create user new_user identified by new_user_pass;

Then we do the magic:

alter user exists_user grant connect through new_user;

After that you can log into the database:

connect new_user[exists_user]/new_user_pass

If you give someone these kind of privileges it’s also important to issue  a good audit, so,here is an example of the command:

CONNECT sys



AUDIT SESSION



AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY new_user ON BEHALF OF exists_user;

AUDIT SELECT TABLE, UPDATA TABLE, INSERT TABLE, DELETE TABLE BY exists_user BY ACCESS;

After that you can select information about a proxy users activity from the dba_audit_trial table.

Here’s an example of a select statement:

SELECT tr2.username           PROXY,

       tr1.username           USERNAME,

       tr1.extended_timestamp,

       tr1.owner,

       tr1.obj_name,

       tr1.action_name,

       tr1.returncode,

       tr1.SESSIONID,

       tr1.PROXY_SESSIONID

  FROM dba_audit_trail tr1, dba_audit_trail tr2

 WHERE tr1.obj_name not in

       ('DUAL', 'SQLPLUS_PRODUCT_PROFILE', 'PRODUCT_PRIVS')

   AND tr1.proxy_sessionid = tr2.sessionid

   AND tr2.action_name = 'PROXY AUTHENTICATION ONLY'

 ORDER BY tr1.timestamp;