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,
Password is case sensitive in 11g
Related Documents
No comments:
Post a Comment