By Pass the User Password Using Grant Connect Through clause



In some cases we require the application or some specific user credential for testing purpose though if you have DBA privileged user. We will not be able to change the password for this application user on the fly because it will be hard corded or depended to the application. In such situations you can use this privilege grant connect through clause.


This clause comes with alter user command. Meaning ofalter user user1 grant connect through user2” is Gives the power to user2 to connect as user1 without knowing the password user1’s password. If you specify AUTHENTICATION REQUIRED clause then it will ask the password for both users.

See an example here.
I am going to create two users say user1 and user2.
SQL> show user
USER is "SYS"

SQL> create user user1 identified by user1 default tablespace users temporary tablespacetemp;

User created.

SQL> create user user2 identified by user2 default tablespace users temporary tablespacetemp;

User created.

SQL> grant connect , resource to user1;

Grant succeeded.

SQL> grant connect , resource to user2;

Grant succeeded.

SQL> alter user user1 grant connect through user2;

User altered.

Connecting to the user1 using the user2 credentials.

SQL> conn user2/user2
Connected.
SQL> show user
USER is "USER2"

SQL> conn user2[user1]/user2
Connected.
SQL> show user
USER is "USER1"

Revoking the access from user2
SQL> alter user user1 revoke connect through user2;

User altered.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google