Can the Oracle passwords be decoded?

Oracle username and password

The DBA views dba_users contains the encoded password for each user and it is not known how to make a sense out of it.


SQL> descr dba_users
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 USERNAME                                  NOT NULL VARCHAR2(30)
 USER_ID                                   NOT NULL NUMBER
 PASSWORD                                           VARCHAR2(30)
 ACCOUNT_STATUS                            NOT NULL VARCHAR2(32)
 LOCK_DATE                                          DATE
 EXPIRY_DATE                                        DATE
 DEFAULT_TABLESPACE                        NOT NULL VARCHAR2(30)
 TEMPORARY_TABLESPACE                      NOT NULL VARCHAR2(30)
 CREATED                                   NOT NULL DATE
 PROFILE                                   NOT NULL VARCHAR2(30)
 INITIAL_RSRC_CONSUMER_GROUP                        VARCHAR2(30)
 EXTERNAL_NAME                                      VARCHAR2(4000)

 SQL> select username, password FROM dba_users;

USERNAME                       PASSWORD
------------------------------ ------------------------------
SYS                            F48C49A3D97AE8FB
SYSTEM                         35FC112F65AF87B4
DBSNMP                         E066D214D5421CCC
LALA                           0371BE9328AFB5E3
SCOTT                          F894844C34402B67


SQL> create user test1 IDENTIFIED BY thesame;

User created.

SQL> create user test2 IDENTIFIED BY thesame;

User created.

SQL> select username, password FROM dba_users where username like 'TEST%';

USERNAME                       PASSWORD
------------------------------ ------------------------------
TEST1                          9F56C14116139736
TEST2                          FD9559219BC98C52

So, even if the users TEST1 and TEST2 have the same password, it is encoded differently.

Changing the Oracle password and resetting it

As a DBA you will every now and then need to log in as another user, for example to test his/her privileges and make sure that the rights are correct. The password is changed by the command

ALTER test1 IDENTIFIED BY newpwd;

but it is obvious that, not knowing the old password, there is no way back and this may break the application if, for example, the password is stored somewhere (in a database table or on file).

The right way to proceed is:

note down the current (old) password, as found in the table dba_users
modify the password with the command ALTER USER
connect using the new password
do what you wanted to do
reset the passord with the clause IDENTIFIED BY VALUES

Therefore:

SQL> ALTER USER test1 IDENTIFIED BY secondPwd;

User altered.

SQL> connect test1/secondPwd;
Connected.
SQL> show user
USER is "TEST1"

SQL> select count(*) from all_objects;

  COUNT(*)
----------
     22974

SQL> disconnect

Let's now connect as a DBA and check the password

SQL> connect system
Enter password:
Connected.
SQL> select username, password FROM dba_users where username = 'TEST1';

USERNAME                       PASSWORD
------------------------------ ------------------------------
TEST1                          18F098249F7D4FAE

SQL> ALTER USER test1 IDENTIFIED BY VALUES '9F56C14116139736';

User altered.

SQL> select username, password FROM dba_users where username='TEST1';

USERNAME                       PASSWORD
------------------------------ ------------------------------
TEST1                          9F56C14116139736

SQL> connect test1/thesame;
Connected.
SQL>

With this little trick is it therefore possible to reset the password without ever knowing it. Note that the syntax requires VALUES and not VALUE and single quotes:

ALTER USER test1 IDENTIFIED BY VALUES '9F56C14116139736';

[Home] [Web Design] [HTML tutorials] [Javascript] [PSP] [About us] [Links] [Anonymous email] [Best hosting] [Daily Oracle Life] [IT jobs in Switzerland] [Web Submission] [Web traffic]

Rate this article ...
Very poor	Poor	Average	Good	Very good