Using "alter user" privilege, connecting to Oracle database as different user without knowing his/her password

Linux root user can use a command "su -" to work as different user without entering a password of that user. For example, being a root user, I can enter the command,

[root@myserver ~]# su - littleboy

[littleboy@myserver ~]$

I can work as normal user littleboy without knowing the password of user littleboy. It is just possible as I am root.

Now the question, is it possible to work as normal user if I have dba role without knowing normal user password on Oracle database? In simple terms how to su on Oracle database? Here is the way.

Consider the following setup, where user bigboy is dba, and littleboy is normal user.

$ sqlplus / as sysdba

SQL> create user littleboy identified by littleboy1;

User created.

SQL> grant connect, resource to littleboy;

Grant succeeded.

SQL> create user bigboy identified by bigboy1;

User created.

SQL> grant dba to bigboy;

Grant succeeded.

If bigboy wants to connect to database as littleboy without knowing password of littleboy, the steps are,

$ sqlplus bigboy/bigboy1

SQL> alter user littleboy grant connect through bigboy;

User altered.

Now bigboy can connect to database as littleboy user like this (as if bigboy switched user to littleboy), Note that user bigboy is entering his password, not littleboy's password.

$ sqlplus bigboy[littleboy]/bigboy1

SQL>

Here bigboy is a proxy user for littleboy. List of proxy users can be found from proxy_users view.

SQL> select proxy, client from proxy_users;

PROXY                          CLIENT

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

BIGBOY                         LITTLEBOY

To revoke this connect through privilege for bigboy,

SQL> alter user littleboy revoke connect through bigboy;

User altered.

Actually to connect as different user, bigboy need not to have dba role, he just needs "alter user" privilege. Of course, "alter user" is a powerful privilege, because it allows bigboy to connect as any user who has dba privilege!