Very good routine
Jgbb, May 30, 2002 - 4:43 pm UTC
Why can't Oracle make this be easier and more secure
Michael O'Neill, February 10, 2003 - 11:30 pm UTC
When executing check_password, isn't there a potential significant security risk during the period of time between altering to the offered (potential) password and the switching back to the original password digest?
What if there where an instance failure in between the two alter commands?
A more sensible alternative would be for Oracle to document what function converts/hashes plain text into the the password digest that is stored in user$. I'm not asking for the algorithm, just a chance to call the "blackbox" directly without monkeying around with altering users via inefficient and security-hole commands like alter user.
The only risk I can see is that it would be easier to build cracking tools, but since the same algorithm has been used for many many years - I'm confident someone has already cracked it. The same cracking tools could be built with the altering user mumbo jumb, with less efficiency.
Just my two cents worth. Thanks for all the great insights.
February 11, 2003 - 8:16 am UTC
The real way to check a password is to LOG IN WITH IT.
This is a 99% solution -- yes, there is a chance that if you fail between the alters -- you leave the password in a known, different state.
I've done implementations that queue a job to put it back in the event it fails -- so before we alter it, we queue a job, check it, remove job (before it runs). if we fail, the job will run shortly after startup -- exposing a window of opportunity - but it would take alot of things conspiring against you for this to be an "attack" ...
check against different user
Scott, February 16, 2003 - 11:13 pm UTC
G'day
We attempted this algorithm, but to overcome the potential hole, we compared the password to test by changing the password dummy account that cannot logon.
ie
SCOTT/TIGER.
Change dummy account (PWCHECK) pwd to TIGER
Compare encrypted passwords.
However these encrypted passwords differ. I presume this is because the encryption algorithm does not allow passwords of different users to be the same encrypted, or the encryption is unique to each user somehow...
February 17, 2003 - 10:27 am UTC
well, first -- passwords are not stored encrypted. they are stored using a one way hash (you cannot get the password back)
the password for user1 will necessarily hash differently then the password for user2. Now, an interesting phenomena is that we just glue the user/password together to hash. So we:
hash( scotttiger ) => password hash
that means a user "S" with a password cotttiger will hash to the same value.
So, if you don't mind setting up 26 dummy accounts (LOCK THEM), you can:
create or replace package check_password
as
function is_valid( p_username in varchar2,
p_password in varchar2 )
return boolean;
end;
/
create or replace package body check_password
as
function is_valid( p_username in varchar2,
p_password in varchar2 )
return boolean
is
l_dba_users1 dba_users%rowtype;
l_dba_users2 dba_users%rowtype;
begin
select * into l_dba_users1
from dba_users
where username = upper(p_username);
execute immediate
'alter user ' || substr( p_username, 1, 1 ) || ' identified by ' ||
substr(p_username,2) || p_password;
select * into l_dba_users2
from dba_users
where username = upper(substr(p_username,1,1));
return l_dba_users1.password = l_dba_users2.password;
end is_valid;
end;
/
No more window, no more "known password".
Only problem would be if you used really long passwords and usernames ;)
ops$tkyte@ORA920> declare
2 l_username varchar2(30) default 'abcdefghijklmnopqrstuvwxyz1234';
3 l_password varchar2(30) default 'abcdefghijklmnopqrstuvwxyz1234';
4 begin
5 if ( check_password.is_valid( l_username, l_password ) ) then
6 dbms_output.put_line( l_username || '/' || l_password || ' is valid' );
7 else
8 dbms_output.put_line( l_username || '/' || l_password || ' is NOT valid' );
9 end if;
10 end;
11 /
declare
*
ERROR at line 1:
ORA-00972: identifier is too long
ORA-06512: at "OPS$TKYTE.CHECK_PASSWORD", line 15
ORA-06512: at line 5
the username || password part would have to be 30 characters or less.
user&password
zsergeant, November 24, 2004 - 1:26 am UTC
It's interesting idea, but seems to operate with table sys.user$ will be more easy
November 24, 2004 - 7:17 am UTC
go ahead, show us how you might do that given that
a) you don't know how to turn a supplied password into a hashed value to compare
it would be infinitely hard, not easier.
Customizing password_verify_function
A reader, October 18, 2005 - 9:32 am UTC
Tom,
Been finding it difficult to customize the password verify function for the follwoing requirement:
§ Match any re-combination using all characters in the users account/ login name.
Do you have anything to cater this?
thanks
October 18, 2005 - 9:46 am UTC
I'm sure others will pipe in, but I found this an interesting approach ;)
ops$tkyte@ORA9IR2> variable str1 varchar2(20);
ops$tkyte@ORA9IR2> variable str2 varchar2(20);
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :str1 := 'tkyte'; :str2 := 'eyttk';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> with dg
2 as
3 (select level n from dual connect by level <= length(:str1) )
4 select count(*)
5 from (select substr(:str1,n,1) s,
6 row_number() over (order by substr(:str1,n,1)) r
7 from dg ) a,
8 (select substr(:str2,n,1) s,
9 row_number() over (order by substr(:str2,n,1)) r
10 from dg ) b
11 where a.r = b.r
12 and a.s = b.s
13 /
COUNT(*)
----------
5
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> exec :str1 := 'tkyte'; :str2 := 'eyttx';
PL/SQL procedure successfully completed.
ops$tkyte@ORA9IR2> with dg
2 as
3 (select level n from dual connect by level <= length(:str1) )
4 select count(*)
5 from (select substr(:str1,n,1) s,
6 row_number() over (order by substr(:str1,n,1)) r
7 from dg ) a,
8 (select substr(:str2,n,1) s,
9 row_number() over (order by substr(:str2,n,1)) r
10 from dg ) b
11 where a.r = b.r
12 and a.s = b.s
13 /
COUNT(*)
----------
3
if the lengths of str1 and str2 are different - don't run query - else if this query returns the LENGTH(:str1) then they are the same, just in a possibly different order....
Thank you
Anil, October 18, 2005 - 10:23 am UTC
Tom,
Thanks for your quick reply! Let me try this approach, completely new as compared to the one I have been trying! Thanks again...
password easily compromised?
Dawar Naqvi, October 27, 2005 - 2:05 pm UTC
Tom,
Here I got some thing from our security group.
I want from you to give your feed back on it.
"I just attended an insightful presentation by Joshua Wright at SANS -LA on how Oracle passwords may be easily compromised. He discussed how he cracked any "normal" Oracle passwords, in mere minutes, using brute force. Seriously, several men in suits and black glasses were waiting for him at the end of the talk.
Basically, it is relatively easy in most environments to attain access to the hash tables for Oracle passwords. He discussed several very common methods that work in an Oracle environment to obtain these hashes, some of which could apply in our environment.
Wright's research showed that Oracle has an extremely weak salt selection, does not preserve alphabetic case selection (!) in the passwords, and has a very weak hashing algorithm. Apparently, little of this was known before. Security through obscurity? He decrypted the algorithm, and not by reverse engineering, which would be illegal. Basically, the username is the only salt used for the hash calculation! Many Oracle applications do not allow use of special characters; further, non-alpha passwords need to be quoted. The algorithm itself is weak: Oracle uses two rounds of DES-CBC encryption (itself very unusual), and use the IV from the first round of encryption as the hash(!) - very unusual - most use the output - to generate the new key. The secret key used for the first encryption? 0123456789ABCDEF, which was actually published. All this applies to versions through 10g, and apparently 11, which isn't even released!
He then wrote some simple code that allows Josh to generate 850,000 hashes per second. That may be further optimized. Exhaustive dictionary attacks may take some time, especially with longer passwords (8 + characters, but are weakened by requiring one number and one special character). Josh then showed use of specially created Rainbow tables to reduce the cracking time for 8 char (alpha only)passwords to 98.1% probability within 30-45 minutes on an underpowered laptop machine.
IMHO, this is a fairly serious vulnerability. There are only so-so mechanisms to mediate this risk: enforcing 12 + character passwords, encrypting TNS traffic, and absolutely enforcing non-privileged users for all public applications. You probably do not need their Advanced Security suite.
Josh disclosed this vulnerability 3 months ago to Oracle, and this week, Northcutt called his counterpart at Oracle to tell them they were publishing this, and their response was nil.
The atmosphere was very electric, and everyone, especially Oracle dbas, appeared very concerned.
Regards,
Dawar
October 27, 2005 - 2:28 pm UTC
Basically, it is relatively easy in most environments to attain access to the
hash tables for Oracle passwords.
that would not be a true statement, and if it were -- well? umm, that might be the problem perhaps?
I fail to see how this is security through obscurity
most of the above is actually *documented*
tell me what a "weak hashing algorithm" is? I don't understand that at all. It hashes, it hashes using known techniques. You have to brute force it.
If you capture the unix password file, can you brute force it? Well, hmm. So, what do you do? You secure it (hence the /etc/password AND /etc/shadow file, sort of just like ALL_USERS (/etc/password) and DBA_USERS (/etc/shadow)
Do you think that perhaps the problem lies in the statement:
Basically, it is relatively easy in most environments to attain access to the
hash tables for Oracle passwords.
Can you log into your companies production system and steal this tables contents (dba_users). If so, might that be the root cause of the problem here?
If you know the hash algorith used (can you spell O.P.E.N S.O.U.R.C.E. for example, might it be rather well known) and steal the usernames and hashes - well, there you go.
There have been Oracle password checkers (eg: crackers) for years and years - Braintree software was the first I heard of *years* ago (back in version 7). You would run it against the data dictionary as a user authorized and allowed to access the dictionary - dba_users and it would try to brute force guess passwords using a dictionary of common words. This is nothing new?
You always have to protect the password file (dba_users). Just like /etc/shadow.
Care to share the "common methods" for stealing DBA_USERS?
I fairly strongly disagree with:
...
There are only so-so mechanisms to mediate this risk
......
secure your database (dba_users) and, well, that seems to be a pretty gung-ho method to remove this risk.
(no comments about sys.link$?)
I agree the Oracle DBAs should be concerned, if they have policies in place that make it easy to obtain access to dba_users? full database exports? the oracle password file? or sys.link$?
password easily compromised?
Dawar, October 31, 2005 - 12:46 pm UTC
Tom,
Please give your comments on below analysis.
Weak Salt Selection
---------------------
Oracle password hashes use a non-conventional technique for salt selection by prepending the username to the password before calculatig the hash. there are a number of weaknesses with this procedure.Firstly, it is quite possible to obtain information about a user password based solely on its hash value and the known credentials of another user. we can illustrate that by creating two accounts with similar username and password, as shown below:
sql> create user oracle identified by password;
User created
SQL> ccreate user oracl identified by epassword;
User created
SQL> select username, password from dba_users where username like 'ORACL%';
USERNAME PASSWORD
------------------------------ -------------------------
ORACL 427458AF9CC65444
ORACLE 427458AF9CC65444
By simply inspecting the password has values from dba_users table, an adversary would have string evidence of the relationship between both users' passwords.
A second weakness is the use of non-random salt values. Although the salt used can still reduce the effectiveness of a precomputed dictionary attack against a large password hash table, an attacker could still precompute a table of possible password using a common username (e.g. SYSTEM), and use it to attempt to recover the password for this particular user in amany different systems.
Lack of Case Preservation
---------------------------
Another weakness in the Oracle password hashing mechanisim is the lack of alphabetic case preservation. Before the password hash is calculated, the user's password is converted to all uppercase characters, regardless of the input case selection. We can observe this behavior by attempting to use mixed case in multiple passwords, viewing the hash values that are produced with each password.
SQL> alter user oracle identified by "PaSSwOrD";
User altered
SQL> select username, password from dba_users where username ='ORACLE';
USERNAME PASSWORD
--------------------------- --------------
ORACLE 427458AF9CC65444
SQL> alter user oracle identified by "password";
User altered
SQL> select username, password from dba_users where username ='ORACLE';
USERNAME PASSWORD
--------------------------- --------------
ORACLE 427458AF9CC65444
SQL> alter user oracle identified by "PASSWORD";
User altered
SQL> select username, password from dba_users where username ='ORACLE';
USERNAME PASSWORD
--------------------------- --------------
ORACLE 427458AF9CC65444
Regards,
Dawar
November 01, 2005 - 5:16 am UTC
I'll say it as simply as I can:
if you steal my hash table, you can brute force attack me.
"my hash table" applies to "many systems" (think /etc/shadow - would your SA's be upset if you said "I have your /etc/shadow file, see you later...." If not, ask them "why not")
This information listed above is nothing new as the author of the paper would lead you to believe. This information has been widely known for many many years.
This information does not, should not distract you from the point that
if you steal my hash table, you can brute force attack me, at your leisure, on another machine. Regardless of the "strength" of the "hash"
I believe I have commented to that affect right above. This is about securing that data which needs be secured. If you have a full export (all of the data from your database) that has the oracle hashes in it. If someone steals that export - why even BOTHER with the hashes, heck, you *got the data*.
My comment - the statement "it is relatively easy in most environments to attain access to the hash tables for Oracle passwords" is the problem here. If you give me the hash and sufficient resources (ask this consultant how many weeks they spent setting up their rainbow tables for this exercise?), I can brute force it.
Re: password easily compromised?
Andrew, November 01, 2005 - 11:13 am UTC
I read the same article. It raises some curious stuff. Has lots of "if"s. Like if they can scan the system datafile, if they can scan the password file, if they can read the dba_users (sys.user$) table, and so forth. The problem is like leaving the huge janator's key ring laying around. It may take a while to find the right key to the door, but eventually the intruder will.
If you select password from any of the Oracle databases in our shop for SYS, SYSTEM, and other internal schemas you will get 'DoNotUse-UseYourOwnAccount'. Try any brute force or rainbow table attack against that account -- it will NEVER succeed.
It is all about how you secure the keys to the kingdom. Who can log into the server, and what can they do/see when they are there? Who can log into the database and what can they see/do there?
There is not one magic bullet to protection. You MUST protect everything and sometimes you have to be a bit of tough about it. IMNSHO, truly responsible and professional DBAs and SysAdmins MUST know thier systems, their vulnerabilities and be doggedly determined to keep every portal -- every exposure -- closed as tight as possible.
Now, I find that in-house developers are the easiest to work with in building secure applications. Temporary contractors are somewhat more difficult becuase they want to get the job done as easily as possible, collect their pay and move on. But the worst of all, seems to be the OTP products.
I am sure Tom has seen his share of -- I know I have -- applications that do the following:
o Store user passwords in clear text within their own application secrurity system. I am in the process of having our app support people open bug tickets with the vendors -- I consider clear text passwords a defect.
o _Require_ DBA role assigned to schema owner. I attempt, with varying degrees of success, to eliminate this role for these apps.
o Using the sys.user$, sys.obj$, etc. tables instead of the supported views. This means that in a shared app database, the OTP app has access to _everytining_ in the database instead of just what it needs. Also makes upgradin the database harder because Oracle may change these tables and then the app will not work.
o Making the schema owner the same as the data owner. We try to seperate the schema owner from the data owner so that we can limit the exposure of the database internals to the application. Usually we can manage to do this with views, etc.
o Application front ene that will not trap and process expired passwords.
Ultimately, security is not just a hash table. It is a whole way of running the shop. Use tight and reasonable controls. Give the user -- or DBA, or sysadmin -- only what he needs to do his job and NO MORE. If everyone can read you password file, then that is your own silly fault. If anyone other than a DBA can read the DBA_USERS or sys.user$ table then that is you own silly fault. If you use SYSTEM account to manage your databse, then if someone cracks that account, it is your own silly fault.
Lock the doors to your databses and keep the master key in your pocket.
Alberto Dell'Era, November 10, 2005 - 4:15 pm UTC
For anyone interested, metalink note 340240.1, <Customer Update Regarding "An Assessment of the Oracle Password Hashing Algorithm" by Joshua Wright and Carlos Cid> addresses the concerns expressed above in detail.
password dba_users
Frank, January 25, 2006 - 2:07 pm UTC
Is there any way to decode password column values in dba_users tables?
January 25, 2006 - 4:40 pm UTC
it is a one way hash - you cannot "unhash" it backwards.
password
A reader, November 21, 2008 - 4:01 pm UTC
Tom:
Is there a way to do the following from the oracle server.
We have client machines with powerbuilder application software version 5.0. All clients connect using one DB account hardcoded in the exe.
I want to upgrade client software to 6.0. I want to make sure no client with 5.0 can connect unless he upgrades.
How can i control that from the oracle server if the client only sends userid/password application account when they login and client check what the return value that came in to allow login or not.
password
A reader, November 21, 2008 - 8:28 pm UTC
Tom:
On the above, i was thinking of changing the existing passwords and adding a new olumn for new client software to use so that old client will not work
OR
change the database account password so that old client machines with old password account will not work.
what do you think is the best way to do this.
November 24, 2008 - 4:31 pm UTC
if changing the password works for you - go for it.
But I hope you do realize that by placing the password in the client, you have utterly defeated "security" in any way shape or form. Might as well just post it on the wall...
db
A reader, November 24, 2008 - 5:41 pm UTC
Tom:
Yes, i realize that. The next step is to remove the password from client and create a db account per user instead of the One big appliation user model (which is commonly used BTW)
For now, the DB password change worked for a workaround. It blocks old client versions with old password hardcoded in it.
thank you
November 24, 2008 - 7:53 pm UTC
... (which is commonly used BTW) ...
so is string concatenation to build sql without bind variables...
password
A reader, November 25, 2008 - 12:58 am UTC
Tom:
<<But I hope you do realize that by placing the password in the client, you have utterly defeated "security" in any way shape or form. Might as well just post it on the wall... >>
Can this be true! The password is stored in binary executable. How can an average user figure it out like posting it on a wall. Not that easy unless you are a good hacker.
November 25, 2008 - 10:48 am UTC
not very hard, not very much work.
password
Sam, December 02, 2008 - 2:46 pm UTC
Tom:
I changed the password for the Database A and i also had to drop and recreate the db links poitinig to this Database A using the old password.
The problem is that when i log in using the new password usnig sql*plus and i mistype it it locks the account after 3 attempts. This is a problem because everyone and application uses the same account.
This did not use to happen with old password. Does oracle has something by defaul that locks account after a few several login atempts. How can disable this?
December 09, 2008 - 9:18 am UTC
sigh no version, no information.
Suggestion: talk to your DBA - the configure this stuff (password routines). They might well have put one in place. They would know how to configure this and would be able to tell you either "oh, sorry, we'll get rid of it" or "sorry, but that is the security policy - you'll need to change your approach"
http://docs.oracle.com/docs/cd/B28359_01/network.111/b28531/authentication.htm#i1006575
trouble with function
Zephyr, September 09, 2010 - 11:32 am UTC
Hi Tom
first, thank you for sharing your deep Oracle knowledge with us,
it is more than helpful.
I have tried your function is_valid on my Oracle but somehow it fails to compile,
saying dba_users table or view does not exist.
I bet I must be missing some grant there, but since I'm not a good Oracle DBA
and since a simple "select * from dba_users" works in TOAD with my user,
I don't quite get why it fails ?
Thanks for your help.
password verify in java
Zephyr, September 10, 2010 - 3:16 am UTC
Hello Tom
Thank you so much for you very fast answer !
We learn every day, I didn't know that roles were not
applied when executing procedures and function....
Thank you for the version warning about password location,
but on client side, some IT still use old age version like 9 or 10g...
Anyway, I was just searching for a way to validate a user's password against Oracle,
I found Pete Finnigan SQL db audit tool to verify Oracle database security
mixing brute force and dictionnary and use it as a base to calculate hash in an Oracle function.
But, as you said, the simplest way to achieve it is simply to logon.
So I built a simple java code loaded in Oracle to connect
using plain user / password through embeded jdbc driver,
it works fine but is a bit "ugly" (not mentionning the security issue
to pass the password in "clear",
though it could be encrypted using the dbms_crypto in next version).
Would you advise a simpler way to achieve this password verification ?
Thank you.
September 13, 2010 - 7:11 am UTC
passwords have been encrypted in Oracle from the client side for many many many years (as long as I've worked at Oracle...) So, I don't know what you mean about passing them in the clear - unless YOU are passing the password from a client outside the database to us in the database - in which case - YOU are the security risk - your CODE is the problem, not ours. The 'password' is just data to us if you are passing it as data from a client to a java stored procedure.
You can use network encryption (also available for many many years) available with the Advanced Security Option (ASO) to encrypt the transfer of data between the client and server.
Beware of just trying to login in current releases too by the way - the DBA might have set up password functions so that after N failed login attempts - you lock the account. You could be locking people out by just guessing and trying.
determine Old password
Gerry Bush, June 23, 2011 - 5:19 pm UTC
Tom,
Our new security requirements now require that we validate that the user's password (new) when changed must be at least 4 characters different from the current (old) password. This is_valid procedure is executed under our defined profile would violate the rule that a password cannot be reused. I had hoped that including the "REPLACE" verb on the "ALTER USER" command would suffice but that is only evaluated when one is changing there own password. I have a VERIFY_FUNCTION enabled to address all of our requirements, but without the OLD password there is little chance of meeting that requirement.
Suggestions?
Thanks
Gerry
June 24, 2011 - 8:46 am UTC
you would have to keep the old password somewhere in the clear (even if encrypted, you have to decrypt it at some point, it must be stored retrievable). I'd think that would be a MUCH larger security issue than your 4 character rule. Meaning - I'd change the requirement given the facts that must be true to support the request (when I see the word requirement, my brain always silently replaces it with the word request, because that is what it really means).
Make sure the requester of this request - and everyone on the planet for that fact (strength in numbers) is aware of this fact - that the password must be stored in a fashion that makes it readable at some point - in order for this requirement to be satisfied and that the inherit risks involved in that far far far outweigh a four character rule (which by the way, does nothing to make a password more secure as far as I know).
storing passwords in a reversible form, while done by lots of people, is a really bad idea. It is impossible to steal a password from you if you don't have them. If you have them - then you can obviously steal it.
One approach would be to make it so that a user attempting to change their password would have to provide their old password - and you store it then, so you are only storing an old password. I'd still not like you to do that - since it still might give insight into what my current password is - and might be a password I've used elsewhere.