Today I post This Topic About how Oracle Store Password, I choose this topic since i will write on other topics that related to this one, Understand this topic will make you understand the basics how to secure oracle database.
As i mention before there’s are simple steps to secure database before start buying products and use them.
Also Please note that article will not show you how to hack or attacks any algorithms it’s only show the Title of this topic.I used in this topic Database version 10g,11g.
When You Enter Username which is stored in Plain Text ( Clear Text ) in DBA_USERS view on other hand the password stored as hash, when you log in to The Password is concatenated to the end of your username, and when you type the password , it will be compared to Stored hash password if they match then welcome to your schema otherwise you don’t have access to the schema.
I love to keep asking this question since its really good question regarding to password, why Using Hash instead of encrypt it ? The Answer is Simple you can’t reverse the hash while you can do that in encrypted.
still difficult … if you have encrypted file you can decrypt it it will not be easy but POSSIBLE , Otherwise in hash you need other age to extract the password from it.Adding to your information that hashing store any amount of data
for example : hashing could be 16 bytes and its could be different related to hashing algorithms so you cannot retrieve the original data from the hash, so when you enter the password in oracle this password will be hashed and comparing to Stored Hashing password ( in DBA_USERS ) you will log on, actually lot of systems using this way Unix one of them.
and you check all this information username, password and others in Tables called SYS.USER.
SQL> select user#,Name,Password from SYS.USER$;
USER# NAME PASSWORD
———- —————————— ——————————
0 SYS 8A8F025737A9097A
1 PUBLIC
2 CONNECT
3 RESOURCE
4 DBA
5 SYSTEM 2D594E86F93B17A1
The algorithm that generates that hash is the same in all versions and platforms and does not include any identifying info from the database, host, instance or other information beyond the username and password.Thus, if your username and password are the same on multiple databases they will all have the same hash value.
I always consider and inform client and customers for grant users privileges that they don’t need to what it’s called “ESCALATE PRIVILEGES” Check the below example that describe If you don’t know the other user’s password you can temporarily change the password then restore it by saving the hash and then using the “IDENTIFIED BY VALUES” clause of the ALTER USER command to restore the hash value when you are done.
SQL> conn test/test_123;
Connected.
SQL> select user#,Name,Password from SYS.USER$ where name=’TEST’;
USER# NAME PASSWORD
———- —————————— ——————————
85 TEST BB68AA665B7F31FB
SQL> alter user test identified by test_osama_123 ;
User altered.
SQL> conn test/test_osama_123;
Connected.
SQL> alter user test identified by Values ‘BB68AA665B7F31FB’;
User altered.
you can controlled this error by password_reuse_time/password_reuse_time parameter in profile.Change password different than test_123. and if you choose the same password the below error appeared :
ORA-28007: the password cannot be reused
You can check the description for the error :
ORA-28007:the password cannot be reused
Cause: The password cannot be reused for the specified number of days or for the specified number of password changes
Action: Try the password that you have not used for the specified number of days or the specified number of password changes Refer to the password parameters in the CREATE PROFILE statement.
if you don’t modify the profile you can be aware of the below :
- you should be aware with alter user Privileges.
- Check who has Access to DBA_USERS.
Notice the above information for Database 10g.
Now I will talk about Database 11g which get enhance for storing Database By adding new column to DBA_USER called PASSWORD_VERSION to understand it if this column contain 10g this the above information can be applied. if no 10g mention in that column you should search for new methods 🙂
Include to this new features 11g Introduce the ability of Store password In CASE_SENSITIVE and password hash no more appeared in DBA_USERS and to check it you should have access to SYS.USER$
Now Let’s Back to PASSWORD_VERSION Column if this column contain 10g then SYS.USER$ Column PASSWORD will not be reliable Why ? Because hashing in this case will not support case sensitive so Upper or lower will be the same will be explain with example.
On Other hand if the PASSWORD_VERSION contain only 11g then you will need to look in the SYS.USER$.SPARE4 column and you will see a much larger hex number. This is because Oracle has switched to the SHA-1.
Another Interesting Thing that you could see in 11g if you assign the same password to user the hash value in the column will be Totally different but it will be usable in an “IDENTIFIED BY VALUES” amazing algorithm and new way to store oracle password and more secure.
Note : “IDENTIFIED BY VALUES” command is not supported which mean maybe will gone in Next Releases.Also SPARE 4 Column is not documented by oracle So Any next Patch or new Version Oracle may change how the password information is exposed.
Let’s work On Examples Now:
SQL> show parameters sec_case;
NAME TYPE VALUE
———————————— ———– ——————————
sec_case_sensitive_logon boolean TRUE
SQL> alter user test identified by test ;
User altered.
SQL> select username,password,password_versions from dba_users where username = ‘TEST’;
USERNAME PASSWORD PASSWORD_VERSION
—————————— —————————— ——–
TEST 10G 11G
SQL> select name,password,spare4 from sys.user$ where name = ‘TEST’;
NAME PASSWORD SPARE4
—————————— ——————————
TEST 7A0F2B316C212D67 S:6129354E59DE31C3554426823BB98CC0CDC3A9F5495076D46305BB73ECD
Now Let’s Test Our Work on this User 🙂
SQL> conn / as sysdba
Connected.
SQL> alter user test identified by TesT ;
User altered.
SQL> conn test/test;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn test/TesT ;
Connected.
Let’s Check Case_sensitive if it’s enabled :
SQL> conn / as sysdba
Connected.
SQL> alter user test identified by TesT ;
User altered.
SQL> conn test/test;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn test/TesT ;
Connected.
let’s use Identified By Values and see changes happened to DBA_USERS and SYS.USER$, Please notice that in the below example when i use Hash Value that stored in password The User able to connect using lower/upper Letters even Case_senentive parameter is enabled but when i used Hash that stored in SPARE4 everything is changed
SQL> conn / as sysdba
Connected.
SQL> alter user test identified by values ‘7A0F2B316C212D67’;
User altered.
SQL> select username,password,password_versions from dba_users where username = ‘TEST’;
USERNAME PASSWORD PASSWORD
—————————— —————————— ——–
TEST 10G
SQL> select name,password,spare4 from sys.user$ where name = ‘TEST’;
NAME PASSWORD SPARE4
—————————— ——————————
TEST 7A0F2B316C212D67
SQL> conn test/test ;
Connected.
SQL> conn test/TesT ;
Connected.
SQL> alter user test identified by values ‘S:6129354E59DE31C3554426823BB98CC0CDC3A9F5495076D426305BB73ECD’;
User altered.
SQL> select username,password,password_versions from dba_users where username = ‘TEST’;
USERNAME PASSWORD PASSWORD
—————————— —————————— ——–
TEST 11G
SQL> select name,password,spare4 from sys.user$ where name = ‘TEST’;
NAME PASSWORD SPARE4
—————————— ——————————
TEST S:6129354E59DE31C3554426823BB98CC0CDC3A9F5495076D426305BB73ECD
SQL> conn test/test ;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
SQL> conn test/TesT ;
Connected.
Now The Let’s Turn off the Case_sensitive parameter and set it to False what will be happened
SQL> show parameter case ;
NAME TYPE VALUE
———————————— ———– ——————————
sec_case_sensitive_logon boolean TRUE
SQL> alter system set sec_case_sensitive_logon=FALSE scope=both ;
System altered.
SQL> select username,password,password_versions from dba_users where username = ‘TEST’;
USERNAME PASSWORD PASSWORD
—————————— —————————— ——–
TEST 11G
SQL> select name,password,spare4 from sys.user$ where name = ‘TEST’;
NAME PASSWORD
—————————— ——————————
SPARE4
——————————————————————————–
TEST
S:6EC2440ABAAC65F5883FF868BBC50757FB722E260633A417B04A982E1F15
SQL> conn test/test ;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Until This moment i am still unable to connect even i turned off the Case_sensitive parameter.
SQL> conn test/TesT ;
ERROR:
ORA-01017: invalid username/password; logon denied
Warning: You are no longer connected to ORACLE.
Also Same Error which indicate you should enable Parameter again or change password again 🙂
I wish it was useful article and full of demonstration if you have question Post it in comment please.
Thank you
Osama Mustafa
Technology Geek 