Proxy User Authentication in Oracle

Proxy user authentication is a powerful feature in Oracle that lets one database user (proxy user) connect to the database "on behalf of" another user (client user).

With proxy login, the proxy user can act as if it is the client user with all of the client user’s access rights, without ever knowing the client user’s password.

Here’s some information about the usage and sample setup of proxy user authentication.

Proxy authentication — a bit of history

In older versions of Oracle, proxy authentication was available as a solution for middle-tier authentication via OCI/JDBC programming.

In 10G, Oracle’s proxy login capability was extended to the CONNECT command. Tools such as SQL*Plus could thereafter work with this nifty feature.

What’s the benefit of proxy authentication?

Proxy authentication is great for building user accountability especially in a multi-developer work environment.

In a typical development scenario, a few database schemas own the bulk of database objects and have a high level of privileges. Developers connect to these schemas for their work: creating views, changing packages, dropping indexes, and so on.

Many developers may be logging in to the same schema, and the organization would like to know the identity of the actual user who accessed the schema. Herein lies the problem as, in Oracle, user and schema are effectively the same.

Proxy authentication solves this problem.

Each developer can be provided "proxy login" to the high-privilege schema: in this way, the developer’s activities can be traced back to the actual user, while the developer can act as if it is the high-privilege schema user and perform activities as required in the database.

Configuring a proxy user

In this example:

MAIN is the schema with the powerful privileges – the one that owns database objects, the one to which the developers must connect: MAIN is the client user

JOHN is a developer who needs to connect to MAIN and work as if it is MAIN: JOHN is the proxy user

To allow the proxy user to connect as the client user, all that’s needed is to create the proxy user and:

A run-time example of creating user JOHN, then configuring JOHN to connect on behalf of MAIN:

SQL> conn / as sysdba
Connected.
SQL> -- Create proxy user
SQL> create user john identified by pwd2;

User created.

SQL> --  Allow proxy user to connect as main user
SQL> -- without knowing main user's password
SQL> alter user main grant connect through john;

User altered.

With GRANT CONNECT THROUGH, JOHN is allowed to connect with all of MAIN user’s privileges. JOHN need not be directly granted any privileges at all, as long as MAIN has got the CREATE SESSION privilege either directly or via a role that will be enabled at the time of proxy login.

To restrict JOHN’s access to a specific role only, add a WITH ROLE clause to GRANT CONNECT THROUGH:

Here, role_name should be a role assigned to client_user.

Proxy login

To login as proxy user, the syntax is:

So to login with user JOHN’s credemtials as proxy for user MAIN:

SQL> -- Connect proxy user as main user
SQL> conn john[main]/pwd2
Connected.
SQL>

Now, JOHN has connected to the database "on behalf of" MAIN.  JOHN will be able to do in this session, exactly what MAIN is allowed to do. In fact, if you check the value of the USERENV parameter SESSION_USER, you will find that its value is not JOHN but MAIN!

SQL> -- Session user
SQL> select sys_context('USERENV','SESSION_USER') session_user
  2  from dual;

SESSION_USER
--------------------------------------------------------------
MAIN

The name of the actual user — the one who opened the session on behalf of SESSION_USER — will be available in the USERENV parameter PROXY_USER.

SQL> -- Proxy user
SQL> select sys_context('USERENV','PROXY_USER') proxy_user
  2  from dual;

PROXY_USER
----------------------------------------------------------
JOHN

The list of proxy users configured in the database is available in the view PROXY_USERS:

SQL> select * from proxy_users;

PROXY      CLIENT     AUT FLAGS
---------- ---------- --- -----------------------------------
JOHN       MAIN       NO  PROXY MAY ACTIVATE ALL CLIENT ROLES

If a proxy user creates a DB object, who owns it — proxy user or session user?

Let’s try this.

JOHN the proxy user, connected on behalf of MAIN, creates a table.

SQL> -- Connect proxy user as main user
SQL> conn john[main]/pwd2
Connected.
SQL>
SQL> create table test (n number);

Table created.

Check who owns it:

SQL> Owner who: proxy user or session user?
SQL> select object_name, owner from all_objects
  2  where object_name = 'TEST';

OBJECT_NAME                    OWNER
------------------------------ ----------------
TEST                           MAIN

SQL>

We see that it’s the session user MAIN who owns the object, even though it was created by the proxy user JOHN.

Summary

This article provides an overview of proxy user authentication in Oracle and gives a working example of how to configure proxy authentication for a user. The solution can be extended for multiple proxy users connecting to a single client user.