How to create a proxy authenticated user in Oracle

Create a general user for authentication:

CREATE USER APP_POOL
IDENTIFIED BY app_pool_users123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

GRANT CREATE SESSION TO APP_POOL;

To illustrate how proxy authentication can be used efficiently, I create two roles:

create role app_pool_role1 not identified;
create role app_pool_role2 not identified;

Grant object privileges on two different tables to the two new roles:

grant select on SCOTT.DEPT to app_pool_role1;
grant select on SCOTT.EMP to app_pool_role2;

Create a user:

CREATE USER VEGARD
IDENTIFIED BY vegard123
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP
PROFILE APP_USERS
ACCOUNT UNLOCK;

Grant the ability to create sessions, and both the previously created roles to the new user, and enable them by default:

GRANT CREATE SESSION TO VEGARD;
GRANT APP_POOL_ROLE1 TO VEGARD;
GRANT APP_POOL_ROLE2 TO VEGARD;
ALTER USER VEGARD DEFAULT ROLE ALL;

Change the user so it connects through a proxy user.
Make sure that only the role APP_POOL_ROLE1 is enabled whenever the user connects through the proxy:

ALTER USER VEGARD
GRANT CONNECT THROUGH APP_POOL
WITH ROLE APP_POOL_ROLE1;

Let's connect to the database:

sqlplus app_pool[vegard]/app_pool_users123@pdb01

Verify the connection and session settings by using sys_context:

select sys_context('USERENV','AUTHENTICATED_IDENTITY') "AuthIdentity used", 
       decode(sys_context('USERENV', 'AUTHENTICATION_METHOD'), 'NONE', 'proxy') "auth method",
       sys_context('USERENV','CURRENT_SCHEMA') "current schema",
       sys_context('USERENV','CURRENT_USER') "grantee of privileges used",
       decode(sys_context('USERENV','IDENTIFICATION_TYPE'), 'LOCAL', 'Password') "identification type",
       sys_context('USERENV','PROXY_USER') "proxy user"
from dual;

Results shows that user VEGARD is authenticated by proxy, the current schema is VEGARD, the privileges for the session belongs to user VEGARD, the identification type is password and the proxy user is APP_POOL:

AuthIdentity used	auth method	current schema	grantee of privileges used	identification type	proxy user
VEGARD	proxy	VEGARD	VEGARD	Password	APP_POOL

Which roles are enabled?

select 'APP_POOL_ROLE1: ' || sys_context('sys_session_roles','APP_POOL_ROLE1') "Role granted?"
from dual
union
select 'APP_POOL_ROLE2: ' || sys_context('sys_session_roles','APP_POOL_ROLE2')
from dual
;

Result shows that only APP_POOL_ROLE1 is enabled:

Role granted?
APP_POOL_ROLE1: TRUE
APP_POOL_ROLE2: FALSE

Let's verify that the roles are actually working.
Logged in as user VEGARD, I now expect to be able to query the table scott.dept and nothing else:

VEGARD@pdb01 SQL> select count(*) from scott.emp;
select count(*) from scott.emp
                           *
ERROR at line 1:
ORA-00942: table or view does not exist


VEGARD@pdb01 SQL> select count(*) from scott.dept;

  COUNT(*)
----------
         4

There is nothing preventing the user VEGARD from connecting directly to the database:

sqlplus vegard/vegard123@pdb01
VEGARD@pdb01 SQL> show user
USER is "VEGARD"

When bypassing the proxy user, the users have access to both roles APP_POOL_ROLE1 as well as APP_POOL_ROLE2, and may now access both scott.emp and scott.dept:

VEGARD@vegdb01 SQL> select count(*) from scott.emp;

  COUNT(*)
----------
        14

Some advantages with using proxy connections:

* No more password administration for individual users
* Proxy authentication allows for role based access control
* Identity preservation of the real user behind the proxy user is automatically set up

Keep in mind that you still have to create and provision the end user accounts.