Proxy Authentication (Oracle DB)

Sometimes administrators need to connect to an application schema to perform maintenance. Sharing the application schema password among several administrators would provide no accountability. Instead, proxy authentication allows the administrators to authenticate with their own credentials first and then proxy to the application schema. In such cases, the audit records show the actual user who performed the maintenance activities. This form of proxy authentication is supported in Oracle Call Interface (OCI), JDBC, and on the SQL*PLUS command line.

Here is an example where the user scott is allowed to connect to the database and act as hr.

[oracle@bigdatalite ~]$ sqlplus sys/welcome1@orcl as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 03:52:04 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> alter user hr grant connect through scott;

User altered.

[oracle@bigdatalite ~]$ sqlplus scott[hr]/tiger@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Feb 7 03:54:15 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Sun Feb 07 2016 03:51:48 -05:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> show user

USER is "HR"

SQL> 

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

SYS_CONTEXT('USERENV','PROXY_USER')
--------------------------------------------------------------------------------
SCOTT

Actions taken by proxy connections can be audited with standard database auditing commands such as the following:

AUDIT SELECT TABLE ON employees BY SCOTT ON BEHALF OF HR;


AUDIT SELECT TABLE ON employees by SCOTT ON BEHALF OF all;

You can revoke as below.

SQL> alter user HR revoke connect through SCOTT;

User altered.

SQL>