潛水珽的異想世界

最近因為工作上在整理一些線上系統的帳號,剛好遇到要重建USER帳號與改USER ID的需求,而oracle中只允許我們”搬”帳號,而不允許我們”改帳號”,以下是一篇網路上的技術文章,請參考.

ref:http://arjudba.blogspot.com/2008/05/playing-with-oracle-password-identified.html

======================================================

We can see the hash value of password in the dba_users field by querying, select password from dba_users; Now how this value is generated. Will it work if I assign the same password to another user by hash value. Or is it database dependent or user name dependent. I will try to make you clear with examples.

A.Create User A with password A

---------------------------------------------------------------------

SQL> CREATE USER a IDENTIFIED BY a;

User created.

SQL> GRANT create session TO a;

Grant succeeded.

See the hash value of username a with password a.

SQL> select username, password from dba_users where username= 'A';

USERNAM PASSWORD

------- ------------------------------

A AFCC9478DFBF9029

B.Create another user B with this hash value.

-----------------------------------------------------------------------

SQL> CREATE USER b IDENTIFIED BY VALUES 'AFCC9478DFBF9029';

User created.

SQL> GRANT create session TO b;

Grant succeeded.

SQL> select username, password from dba_users where username= 'B';

USERNAM PASSWORD

------- ------------------------------

B AFCC9478DFBF9029

C.Try to connect to database with both user.

-------------------------------------------------------------------

SQL> CONN A/A

Connected.

SQL> CONN B/A

ERROR:

ORA-01017: invalid username/password; logon denied

Warning: You are no longer connected to ORACLE.

So what we get is that the encrypted hash password can't work for another user. So hash value is dependent based on user name.

Now I will drop user A and will create user with above encrypted hash value and let's see the fact.

SQL> CONN ARJU/A

Connected.

SQL> DROP USER A;

User dropped.

SQL> CREATE USER A IDENTIFIED BY VALUES 'AFCC9478DFBF9029';

User created.

SQL> CONN A/A

Connected.

So we clearly can see that hash value of the password is user name dependent. We can experiment this on another database and will see that it does not depend on database name or OS.

Is it case sensitive. In following example I will create the user and password with different case and by default will see in neither case it is case sensitive.

SQL> CREATE USER "a" IDENTIFIED BY a;

User created.

SQL> select username, password from dba_users where UPPER(USERNAME)=UPPER('A');

USERNAM PASSWORD

------- ------------------------------

A AFCC9478DFBF9029

a AFCC9478DFBF9029

SQL> ALTER USER "a" IDENTIFIED BY VALUES 'AFCC9478DFBF9029';

User altered.

SQL> GRANT CREATE SESSION TO "a";

Grant succeeded.

SQL> CONN "a"/A

Connected.

SQL> ALTER USER "a" IDENTIFIED BY "a";

User altered.

SQL> select username, password from dba_users where UPPER(USERNAME)=UPPER('A');

USERNAM PASSWORD

------- ------------------------------

A AFCC9478DFBF9029

a AFCC9478DFBF9029

So we can conclude our experiment that we can use the encrypted password for the SAME user, but not for another user, the reason is that before calculating the hash value that is visible as the PASSWORD in DBA_USERS, Oracle adds the username to the mix and calculates the hash value on USERNAME plus PASSWORD. And neither username nor password is case sensitive in 10g .

In 11g it is case sensitive. You may have a look at.

in 2014.7.29 Update,

In 10g and 11g you can use the sql to re-modify password values.

SQL:

select 'alter user "'||d.username||'" identified by values '''||u.password||''';' c
from dba_users d, sys.user$ u
where d.username = upper('SP101369')
and u.user# = d.user_id;

Return:

alter user "SP101369" identified by values '10F82673B0E4A2B5';