Proxy authentication

articles: 

Proxy authentication has been around since release 9i, but it isn't widely used. It can be a very useful facility for giving certain users access to high privileges without having to give them any direct grants or roles, and avoids many of the problems of using shared accounts. It is of course fully audited.

The syntax can be as simple as this:

orclz>
orclz> alter user system grant connect through scott;

User altered.

orclz> connect scott[system]/tiger
Connected.
orclz> sho user
USER is "SYSTEM"
orclz>

So now, without needing to expose the SYSTEM password, user SCOTT can do everything that a DBA needs to do. If you want to limit the privileges available when connecting in this way, you can. For example:
orclz>
orclz> create user jw identified by jw;

User created.

orclz> grant create session to jw;

Grant succeeded.

orclz> alter user system grant connect through jw with roles all except dba;

User altered.

orclz> conn jw[system]/jw
Connected.
orclz> sho user
USER is "SYSTEM"
orclz> select * from session_roles;

ROLE
--------------------------------------------------------------------------------------------------------------
AQ_ADMINISTRATOR_ROLE

orclz>

Audit is important: you may have several DBAs making use of the SYSTEM account in this way, and you need to know who the real user was. If using traditional audit, something like this will do:

orclz> audit all statements by scott on behalf of system;

Audit succeeded.

orclz>

Tracking the activity is a bit awkward (you have to join DBA_AUDIT_TRAIL to itself on PROXY_SESSION_ID=SESSIONID) but if you enable Unified Audit, it is simpler: just create your audit policies as normal, and you'll see both the DB_USERNAME and the DBPROXY_USERNAME in the UNIFIED_AUDIT_TRAIL view.

Proxy authentication isn't just for client-server connections and giving DBA-type privileges (as demo'ed above). It is accessible through OCI, and can be a nice way to implement three tier environments where many users connect to a common schema. It isn't widely used - perhaps it should be.
--
John Watson
Oracle Certified Master DBA
http://skillbuilders.com

Comments

Thanx for the information