Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Sunday, February 07, 2016

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> 


2 comments:

богдан ян said...

I really had a great time awriter.org with your post! I am looking forward to read more blog post regarding this! Well written!

technode said...

Nice article , thanks for sharing , very informative and presented nice , keep updating morebest linux training institute in chennai|chennai linux training|linux courses in chennai|best linux training center in chennai|linux training centers in chennai|red hat training in chennai