It’s very common that development team comes up with the issue that they have some issue from an application user. To investigate the issue DBA’s might want to login to that user but sometimes it happens that DBA’s doesn’t know the password. The following way DBA’s can connect as another user without knowing the password in Oracle:
1. Connect Through another user
2. Resetting password by Hash value
1. Connect Through another user
In that method dba can grant another user to connect to that specific user. In that example app_user is the user DBA want to connect to investigate the issue, so we created another user dba_connect to grant connect through to login as user “app_user”. You can grant “connect through” to an existing user but note that you cannot grant “connect through” to sys user otherwise it will fail with error “ORA-01017: invalid username/password; logon denied”
SQL> conn / as sysdba
Connected.
SQL> create user app_user identified by xyz007;
User created.
SQL> grant connect,resource to app_user;
Grant succeeded.
Now assume you don't know the password of "app_user" and want to connect as "app_user"
SQL> create user dba_connect identified by dba123;
User created.
SQL> alter user app_user grant connect through dba_connect;
User altered.
SQL> conn dba_connect[app_user]/dba123;
Connected.
When the investigation is done, don’t forget to revoke the grant:
SQL> alter user tmp_nazmul revoke connect through dba_connect;
User altered.
2. Resetting password by Hash value
DBA can reset the password to something else and reset back the old password by hash value when the investigation is done. Please note that this is not recommended if the application is connected and doing stuff otherwise application will fail to connect until the old password reset back.
SQL> select 'alter user "'||d.username||'" identified by values '''||u.password||''';' from dba_users d, sys.user$ u where d.username = upper('&&username') and u.user# = d.user_id;
Enter value for username: app_user
'ALTERUSER"'||D.USERNAME||'"IDENTIFIEDBYVALUES'''||U.PASSWORD||''';'
--------------------------------------------------------------------------------
alter user "APP_USER" identified by values '0F0B3A5B2068AE40';
SQL> alter user app_user identified by tmp_password;
User altered.
SQL> conn app_user/tmp_password;
Connected.
After investigation reset back the old password:
SQL> conn / as sysdba
Connected.
SQL> alter user "APP_USER" identified by values '0F0B3A5B2068AE40';
User altered.