Mir Sayeed Hassan – Oracle Blog

How to login another database user without knowing a password in Oracle database 11gR2

This operation can perform by DBA, if & only if it’s approved from higher management & it’s not a good practice to break the Oracle Security rule.

Consider we want to perform the activity with different user but you don’t have the password of that user, In this case can we do it – Yes – Here my 1^st user is MIR1 and another user is MIR2

Now the user MIR1want to connect to MIR2 user & perform some operation like creates a table/insert/update where we don’t have the password of MIR2 user.

In such cases the MIR1 need some privilege as grant connect through

Database Status

sys@TESTDB> select instance_name,status,version from v$instance;

INSTANCE_NAME    STATUS       VERSION
---------------- ------------ -----------------
testdb           OPEN         11.2.0.4.0

[oracle@testdb ~]$ !sq
sqlplus / as sysdba

sys@TESTDB> sho user
USER is "SYS"

Grant the require privilege to user MIR2

sys@TESTDB> alter user MIR2 grant connect through MIR1;
User altered.

Check the status of the mir2 user with table exist.

sys@TESTDB> conn mir2
Enter password:
Connected.

mir2@TESTDB> sho user
USER is "MIR2"

mir2@TESTDB> select table_name from user_tables;

TABLE_NAME
-----------
TEST1

Connect MIR2 through MIR1 – Here we assume that MIR2 Password is not known

 Syntax:
SQL > conn MIR1[MIR2]
Enter password:< Give password for MIR1>

sys@TESTDB>connect mir1[mir2]
Enter password: *********
Connected.

mir2@TESTDB> sho user
USER is "MIR2"

mir2@TESTDB> create table test2(eno number(20));
Table created.

mir2@TESTDB> insert into test2 values(1);

1 row created.
mir2@TESTDB> commit;
Commit complete.
mir2@TESTDB> select * from test2;
       ENO

----------

       1

Verify:

mir2@TESTDB> conn sys/sys as sysdba
Connected.

sys@TESTDB> select table_name from dba_tables where owner='MIR2';

TABLE_NAME
------------------------------
TEST1

TEST2  -- This table is created by login through from the MIR1 user.

After completing this operation revoke the privileges by using the sys user.

sys@TESTDB> alter user MIR2 revoke connect through MIR1;
User altered.

Therefore it’s proving that we can perform the operation from one user to another user without knowing the password of another user, therefore it’s not recommended to do this action without prior to the permission from another user (owner of the user).

Also this can be used in case if you want to create or drop a private dblink & you don’t have the password of that dblink owner of another user.

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