Mir Sayeed Hassan – Oracle Blog

How to login the user schema without knowing the password in Oracle Database 19c

Note:

– This scenario is to login to the user without knowing the password & perform some test operation purpose only.
– You can change/re-set the user password with new password but inconvenience to the end user.
– Therefore the Oracle DBA can extract the excrypted password for the user for saferside & keep the password to re-set it back.
– After successfully login to the user with new password & done with test operation., revert back to the old password.

Consider you need to login to the “MIR” user & perform some test operation onit.

Connect to database

[oracle@stag-db4 ~]$ sqlplus / as sysdba

SQL> show pdbs

CON_ID  CON_NAME     OPEN MODE    RESTRICTED
---------- ----------------------------------
1       PDB$SEED     READ ONLY        NO
2       PDB1         READ WRITE       NO

SQL> alter session set container=pdb1;
Session altered.

Check the user exist & its status

SQL> select username, account_status from dba_users where username='MIR';

USERNAME      ACCOUNT_STATUS
----------------------------
MIR              OPEN

Extract the encrypted password for the user “MIR”

SQL> select 'alter user "'||username||'" identified by values '''||extract(xmltype(dbms_metadata.get_xml('USER',username)),'//USER_T/PASSWORD/text()').getStringVal()||''';' existing_password from dba_users where username = 'MIR';

EXISTING_PASSWORD
-----------------------------------------------------------
alter user "MIR" identified by values 'C8D8C658C74A4C4D';

Now temporary change the “MIR” password & perform the test operation.

SQL> alter user mir identified by mir_test_purpose;
User altered.

Connect to the user “MIR”

SQL> connect mir/mir_test_purpose@pdb1;
Connected.

SQL> show user
USER is "MIR"

Note: Here you can perform all the your testing operation & then revert back to the existing password.

Connect to the respective PDB & re-set the existing password by using the sys user.

SQL> alter session set container=pdb1;
Session altered.

SQL> show user;
USER is "SYS"

SQL> alter user "MIR" identified by values 'C8D8C658C74A4C4D';
User altered.

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