Mir Sayeed Hassan – Oracle Blog

How to use the Proxy only connect in Oracle Database 19c

Usage of the proxy only connect as if you want the application schema can be only access through a proxy user, in this case no user connects directly to the application schema even though if you known the user password.

Check the status of database

SYS @ ora19cdb > select status, version, open_mode from V$database, v$instance;

STATUS      VERSION         OPEN_MODE
------------ -------------------------
OPEN      19.0.0.0.0       READ WRITE

Check the Pluggable database

SYS @ ora19cdb > show pdbs

CON_ID   CON_NAME       OPEN MODE      RESTRICTED
---------- ---------------------------------------
2        PDB$SEED       READ ONLY        NO
3        PDB_TEST1      READ WRITE       NO

Connect to the PDB Database

SYS @ ora19cdb > alter session set container=PDB_TEST1;
Session altered.

Create a application user.

SYS @ ora19cdb > create user user_app1 identified by userapp1;
User created.

Grant the connect privileges to above user created.

SYS @ ora19cdb > grant connect to user_app1;
Grant succeeded.

Connect to user

SYS @ ora19cdb > connect user_app1@pdb_test1
Enter password: userapp1
Connected.

Verify the user

USER_APP1 @ pdb_test1 > show user
USER is "USER_APP1"

Login to the SYS User and alter the user

SYS @ ora19cdb > alter user user_app1 PROXY ONLY CONNECT;
User altered.

Try to connect to the user

SYS @ ora19cdb > connect user_app1@pdb_test1
Enter password:
ERROR:
ORA-28058: login is allowed only through a proxy
Warning: You are no longer connected to ORACLE.

Note: The above error occur due to the above proxy set for the user

SYS @ ora19cdb > select username, proxy_only_connect from dba_users where username='USER_APP1';

USERNAME       P
-------------------
USER_APP1      Y

Revert back

SYS @ ora19cdb > alter user user_app1 cancel proxy only connect;
User altered.

SYS @ ora19cdb > select username, proxy_only_connect from dba_users where username='USER_APP1';

USERNAME        P
------------------
USER_APP1       N

SYS @ ora19cdb > connect user_app1@PDB_TEST1
Enter password: userapp1
Connected.

=====Hence tested & verified in our test env=====