General Development Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DB proxy user and switching between user and proxy user question

AllenS.11 hours ago

Hi,

We have this scenario.

create user apps identified by password;
create user hr identified by password;
alter user hr grant connect through apps;

Now I can connect using

sqlplus apps[hr]/password

But what I'm hoping to be able to do is something like:

C:\sqlplus apps[hr]/password

SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 18 09:11:41 2025

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

Last Successful login time: Wed Sep 17 2025 13:40:43 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>alter session set current_user = apps;
alter session set current_user = apps
                  *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION

Of course, set current_user is not a valid option. But is there some command to switch between HR and APPS once connected?

Reasons for doing this:

There are privs in HR that are not in APPS and vice versa. For ex: we need to create table in HR and grant SELECT privs to APPS or other schemas. Then we need to switch back to APPS to create synonym for the table.
APPS password is technically not given to us devs and our scripts are executed by a tool that we use.

Is there such a command?

Added 11 hours ago

#database, #oracle

0 comments

6 views