Introduction
Proxy user authentication can be used to allow a specific proxy user to access the system on behalf of another target user, with full access or limited access to certain roles. In general, this type of authentication can be used in applications to differentiate access levels and functionalities, and in scenarios involving access management without sharing the target user’s credentials, since the proxy user connects using their own password and/or additional client-level authentication. It is important to carefully evaluate the scenarios in which this can be applied, as it is directly related to access control and security considerations.
Initial configuration - setup
Three users will be created to represent applications, along with one user that will act as the proxy, which will be able to access one of the application users. Some table objects and roles will also be created, in addition to granting privileges so that access tests can be performed in the following sections.
Note: If you wish to carry out these configurations, apply them in a test-friendly environment in order to mitigate errors in critical environments.
SQL> create user appuser01 identified by strongpassword01;
User created.
SQL> grant create session to appuser01;
Grant succeeded.
SQL> grant create table to appuser01;
Grant succeeded.
SQL> create table appuser01.table01 (
id number); 2
Table created.
SQL> create user appuser02 identified by strongpassword02;
User created.
SQL> grant create session to appuser02;
Grant succeeded.
SQL> grant create table to appuser02;
Grant succeeded.
SQL> create table appuser02.table02 (
id number); 2
Table created.
SQL> create role appuser02_select;
Role created.
SQL> grant select on appuser02.table02 to appuser02_select;
Grant succeeded.
SQL> create user appuser03 identified by strongpassword03;
User created.
SQL> grant create session to appuser03;
Grant succeeded.
SQL> grant create table to appuser03;
Grant succeeded.
SQL> create table appuser03.table03 (
id number); 2
Table created.
SQL> create role appuser03_select;
Role created.
SQL> grant select on appuser02.table02 to appuser02_select;
Grant succeeded.
SQL> create user proxyuser identified by strongpassword;
User created.
SQL> grant create session to proxyuser;
Grant succeeded.
SQL> alter user appuser01 grant connect through proxyuser;
User altered.
SQL> grant appuser02_select to appuser01;
Grant succeeded.
SQL> grant appuser03_select to appuser01;
Grant succeeded.
SQL> grant select on appuser03.table03 to appuser01;
Grant succeeded.
Some of the commands used in the upcoming sessions involve the use of scripts, which can be found on the scripts page of this site. Both the configurations that were performed and the queries/scripts require certain privileges in the database environment.
Testing access/authentication
Since the proxyuser user can access the system through appuser01, both direct access using appuser01 and access to appuser01 via the proxy user will be demonstrated. By using the sys_context function in a query script, some context information from both connections was obtained. From the information shown below, it can be observed that in the proxy connection the value of PROXY_USER corresponds to the name of the proxy user, and the value of AUTHENTICATION_METHOD is set to none.
SQL> connect appuser01/strongpassword01@pdb03
Connected.
SQL> @ora_context_info.sql
INSTANCE_NAME CON_NAME DATABASE_ROLE SESSION_USER CURRENT_USER CURRENT_SCHEMA PROXY_USER AUTHENTICATED_IDENTITY AUTHENTICATION_METHOD
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
orcl02 PDB03 PRIMARY APPUSER01 APPUSER01 APPUSER01 APPUSER01 PASSWORD
SQL>
SQL> disconnect;
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
SQL>
SQL> connect proxyuser[appuser01]/strongpassword@pdb03;
Connected.
SQL> @ora_context_info.sql
INSTANCE_NAME CON_NAME DATABASE_ROLE SESSION_USER CURRENT_USER CURRENT_SCHEMA PROXY_USER AUTHENTICATED_IDENTITY AUTHENTICATION_METHOD
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
orcl02 PDB03 PRIMARY APPUSER01 APPUSER01 APPUSER01 PROXYUSER APPUSER01 NONE
Role management in proxy user access
It is important to control the level of access that users can have in the database, and this also applies to proxy users. It is possible to fully or partially limit the roles that the target user of proxy authentication will have during proxy access. This control is performed through the configuration of proxy authentication using the ... GRANT CONNECT THROUGH ... clause, where the use of roles is specified. To verify what has been configured, the DBA_PROXIES view can be queried.
Note: Even with the possible limitation of roles, the target user will still retain any privileges that are granted directly to them.
Enable all roles
By default, in proxy authentication configuration, not specifying role assignment implies access to all roles of the target user.
SQL> alter user appuser01 grant connect through proxyuser;
User altered.
SQL> @ora_proxy_user_info.sql PROXYUSER
PROXY CLIENT AUTHENTICATION AUTHORIZATION_CONSTRAINT ROLE PROXY_AUTHORITY
-------------------- -------------------- -------------- ----------------------------------- -------------------- ---------------
PROXYUSER APPUSER01 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASE
SQL> connect proxyuser[appuser01]/strongpassword@pdb03;
Connected.
SQL> select distinct granted_role from user_role_privs;
GRANTED_ROLE
--------------------------------------------------------------------------------
APPUSER02_SELECT
APPUSER03_SELECT
SQL> select grantee || ' ' || table_name || ' ' || privilege || ' ' || grantor from user_tab_privs;
GRANTEE||''||TABLE_NAME||''||PRIVILEGE||''||GRANTOR
--------------------------------------------------------------------------------
APPUSER01 TABLE03 SELECT APPUSER03
PUBLIC APPUSER01 INHERIT PRIVILEGES APPUSER01
SQL> select * from appuser02.table02;
no rows selected
SQL> select * from appuser03.table03;
no rows selectedEnable all roles except a specific one
A specific role can be excluded. It is worth remembering that privileges granted directly to the target user remain in effect.
SQL> alter user appuser01 grant connect through proxyuser with role all except appuser03_select;
User altered.
SQL> @ora_proxy_user_info.sql PROXYUSER
PROXY CLIENT AUTHENTICATION AUTHORIZATION_CONSTRAINT ROLE PROXY_AUTHORITY
-------------------- -------------------- -------------- ----------------------------------- -------------------- ---------------
PROXYUSER APPUSER01 NO PROXY MAY NOT ACTIVATE ROLE APPUSER03_SELECT DATABASE
SQL> connect proxyuser[appuser01]/strongpassword@pdb03;
Connected.
SQL>
SQL> select distinct granted_role from user_role_privs;
GRANTED_ROLE
--------------------------------------------------------------------------------
APPUSER02_SELECT
SQL> select grantee || ' ' || table_name || ' ' || privilege || ' ' || grantor from user_tab_privs;
GRANTEE||''||TABLE_NAME||''||PRIVILEGE||''||GRANTOR
--------------------------------------------------------------------------------
APPUSER01 TABLE03 SELECT APPUSER03
PUBLIC APPUSER01 INHERIT PRIVILEGES APPUSER01
SQL> select * from appuser02.table02;
no rows selected
SQL> select * from appuser03.table03;
no rows selectedAssign a set of roles
It is possible to define one or more roles after proxy authentication.
SQL> alter user appuser01 grant connect through proxyuser with role appuser02_select;
User altered.
SQL> @ora_proxy_user_info.sql PROXYUSER
PROXY CLIENT AUTHENTICATION AUTHORIZATION_CONSTRAINT ROLE PROXY_AUTHORITY
-------------------- -------------------- -------------- ----------------------------------- -------------------- ---------------
PROXYUSER APPUSER01 NO PROXY MAY ACTIVATE ROLE APPUSER02_SELECT DATABASE
SQL> alter user appuser01 grant connect through proxyuser with role appuser02_select, appuser03_select;
User altered.
SQL> @ora_proxy_user_info.sql PROXYUSER
PROXY CLIENT AUTHENTICATION AUTHORIZATION_CONSTRAINT ROLE PROXY_AUTHORITY
-------------------- -------------------- -------------- ----------------------------------- -------------------- ---------------
PROXYUSER APPUSER01 NO PROXY MAY ACTIVATE ROLE APPUSER02_SELECT DATABASE
PROXYUSER APPUSER01 NO PROXY MAY ACTIVATE ROLE APPUSER03_SELECT DATABASEDisable roles
All roles of the target user can be disabled when accessing the database via proxy authentication.
SQL> alter user appuser01 grant connect through proxyuser with no role;
User altered.
SQL> @ora_proxy_user_info.sql PROXYUSER
PROXY CLIENT AUTHENTICATION AUTHORIZATION_CONSTRAINT ROLE PROXY_AUTHORITY
-------------------- -------------------- -------------- ----------------------------------- -------------------- ---------------
PROXYUSER APPUSER01 NO NO CLIENT ROLES MAY BE ACTIVATED DATABASEQueries related to proxy users
It is essential to understand what has been configured in terms of privileges and what is actually being used in the database at the session level. Below are some queries related to proxy users.
List proxy users
The PROXY_USERS view displays the list of proxy users.
SQL> @ora_proxy_user_info.sql PROXYUSER
PROXY CLIENT AUTHENTICATION AUTHORIZATION_CONSTRAINT ROLE PROXY_AUTHORITY
-------------------- -------------------- -------------- ----------------------------------- -------------------- ---------------
PROXYUSER APPUSER01 NO NO CLIENT ROLES MAY BE ACTIVATED DATABASEList roles assigned to proxy user connections
The DBA_PROXIES view can be queried to obtain information about proxy connections in the database.
SQL> @ora_proxy_user_info.sql PROXYUSER
PROXY CLIENT AUTHENTICATION AUTHORIZATION_CONSTRAINT ROLE PROXY_AUTHORITY
-------------------- -------------------- -------------- ----------------------------------- -------------------- ---------------
PROXYUSER APPUSER01 NO PROXY MAY ACTIVATE ALL CLIENT ROLES DATABASEIdentify proxy user sessions
The V$SESSION_CONNECT_INFO, V$PROCESS, and V$SESSION views can be combined to obtain details about sessions in the database.
SQL> @ora_query_proxy_sessions.sql
USERNAME AUTHENTICATION_TYPE INST_ID SID SERIAL# STATUS LOGON_TIME ELAPSED_TIME MACHINE||PORT||OSUSER||PROGRAM MODULE || EVENT PROCESS SPID SQL_ID PREV_SQL_ID CON_ID
-------------------- ------------------- ---------- ---------- ---------- -------- ------------------- ------------------- ------------------------------------------------------------------------------ ---------------------------------------------------------------------- ------------------------ ------------------------ ------------- ------------- ----------
APPUSER01 PROXY 1 153 49341 INACTIVE 2025-08-29 19:13:39 00d00h04m34s oracledb||61228||oracle||sqlplus@oracledb (TNS V1-V3) SQL*Plus || SQL*Net message from client 2659 2661 g4y6nw3tts7cc 4Revoke proxy user authentication
It is possible to revoke proxy user authentication.
SQL> @ora_proxy_users.sql
PROXY CLIENT AUTHENTICATION FLAGS
-------------------- -------------------- -------------- -----------------------------------
PROXYUSER APPUSER01 NO PROXY MAY ACTIVATE ALL CLIENT ROLES
SQL>
SQL> alter user appuser01 revoke connect through proxyuser;
User altered.
SQL>
SQL> @ora_proxy_users.sql
no rows selectedConsiderations
Evaluating the impact of proxy authentications and defining appropriate access levels are fundamental to database access control and security. Therefore, proxy users and their authentications must be managed with caution.