Proxy User and Connect Through

 Since Oracle 9i Release 2 it has been possible to create proxy users, allowing us to access a schema via a different username/password combination. 

This is done by using the GRANT CONNECT THROUGH clause on the destination user.

Why we need Proxy?

There are a two main reasons for using proxy users.

Some DBA tasks, like creating private database links or setting up jobs using the DBMS_JOB package, require the administrator to log in as a specific user. 

This can present a problem if the administrator doesn't know the password.

You have multiple developers working in a shared schema. Letting multiple people share the same credentials represents a security risk. Instead you create a separate proxy user for each individual, allowing them to connect to the schema owner with their own credentials. 

If a user leaves a project, you simply lock or drop their user, and they no longer have access to the shared schema.

We have Schema user "METALS"

Lets create proxy user:

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         2 PDB$SEED                       READ ONLY  NO

         3 MYPDB1                         READ WRITE NO

         4 PDB3                           READ WRITE NO

SQL> alter session set container=pdb3;

Session altered.

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

---------- ------------------------------ ---------- ----------

         4 PDB3                           READ WRITE NO

SQL> create user metalprxy identified by metalprxy;

User created.

SQL> grant create session to metalprxy;

Grant succeeded.

SQL> alter user metals grant connect through metalprxy;

User altered.

E:\my_scripts>sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Thu May 15 05:50:52 2025

Version 19.3.0.0.0

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

Connected to:

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> conn metalprxy[metals]/metalprxy@//localhost:1521/pdb3

Connected.

SQL> show user

USER is "METALS"

The proxy authentication can be revoked using the following command.

alter user metals revoke connect through metalprxy;