Connecting to Another Schema using a Proxy Connection

In certain situations the Oracle DBA still needs to connect to the database using application schemas.  activities such as adding or dropping database links , execution of query etc.

One common workaround that DBAs have commonly used involves temporarily changing the user password, connecting, and then changing the password back using the previously recorded password hash.  However there are numerous problems with this approach:

1.             The schema may be locked

2.             The password may be controlled by a PROFILE that may also need to be adjusted

3.             Account intrusion detection tools may detect the connection

4.             The connection may not be properly audited via Oracle or external autditing tools

5.             The application may unsuccessfully try to connect while the password is temporarily changed causing an application failure!

Solution

The proxy connection allows the user to connect to another schema using their own username and password.  The schema being proxied is simply provided in square brackets. proxy connection can be made the GRANT CONNECT THROUGH privilege is required.

The following example illustrates how a proxy connection is configured and used:

in my e.g i am creating one user with name  admin who want to access all object of scott user by logging admin ID with his own password.

SQL>

SQL> create user admin identified by admin;    

User created.

SQL> grant create session to admin;

Grant succeeded.

SQL> conn admin[scott]/admin  //method to login with proxy account in [] you have to pass the user ID.

ERROR:

ORA-28150: proxy not authorized to connect as client

Warning: You are no longer connected to ORACLE.

SQL> conn /as sysdba

Connected.

SQL> alter user scott grant connect through admin;

User altered.

SQL> conn admin[scott]/admin   //pass the schema name while logging with your ID.

Connected.

SQL> show user                 //now u ca see even u have pass the credentials for admin but showing as scott

USER is "SCOTT"

SQL> select * from tab;                 //can see all the table of scott

TNAME                          TABTYPE  CLUSTERID

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

BONUS                          TABLE

DEPT                           TABLE

EMP                            TABLE

SALGRADE 

                      TABLE

SQL> select count(1) from emp;  //can query to those table also.

  COUNT(1)

----------

        14

SQL>