Skip to Main Content


Question and Answer

Tom Kyte

Thanks for the question, ben.

Asked: May 31, 2000 - 1:36 pm UTC

Answered by: Tom Kyte - Last updated: June 24, 2011 - 8:46 am UTC

Category: SqlPlus - Version:

Viewed 10K+ times! This question is

You Asked

Seems similar to a couple of other questions that you have been asked, but not identical enough!

I have to implement "C2" security features within our application.

Unfortunately the 3rd party suppliers are not yet ready for Oracle 8 let alone 8i.

They have a Forms 4.5 Application. Which already allows the user to change thier password, and ONLY ensures that the New password is not the same as the OLD.

(I have copied the idea from ORACLE 8 (PASSWORD_VERIFY_FUNCTION))

I am faced with having to implement (the following):

Password must be at least 6 chars long, they should contain at least one figure (or special character) and one letter,they should comprise at least four different characters, they may not remain valid for more than 90 days

I have persuaded the 3rd party to call a procedure (eg MINE) from their FORM, passing a USERNAME and PASSWORD IN, and taking a success/failure status, and erorr text (on failure) OUT.

Using this method I have coded the above restrictions without any problem.

However, the I need some help with the last restriction:
the password may not be changed to any of the last 12 passwords used

I have read your other "PASSWORD" atricle's and they come close...

I envisage a NEW table, one row per user,
TABLE (username,passuser1,passuser2,pass.....,passuser12)

Obviously the passwords needs to be non "plain text" so that they hidden from the 3rd party and any SQL-users...

So this is where the DIGEST bit comes in...

I would code in MY_PROC
simply SELECT * where username = pin_USERNAME..
and then IF pin_NEW_PASS IN (nvl(passuser1,'X), nvl(passuser2,'Y',,....etc) THEN 'SAME AS OLD' ; return FAIL;

--On successful password change I would then have to move the
--ORACLE DBA_USERS.passord column into my table...

set passuser1=NEWPASSWORD, passuser2=passuser1, etc....
WHERE username = pin_USERNAME

However, I need to guarantee that the method of digest that I use is the same as ORACLE's.... eg What is their SALT! (as you put it!)

So after all that....

If I am given a USERNAME and PASSWORD how do I generate the ORACLE 'DBA_USERS.PASSWORD' column!


and we said...

The only way to do that is to temporarily alter the users password to the one you want to digest. You can then put back the current digest.

I have a package called "check_password" that implements the mechanics you need. It was designed to verify a password supplied by a user is in fact their current password (many people writing these change password screens want to get the OLD_PASSWORD and NEW_PASSWORD, NEW_PASSWORD like Unix. They verify the old_password is correct and new_password = new_password and then change it. The function below allows this check to take place).

It works by:

1) saving the current digest from dba_users
2) alter the user identified by "password to get digest of"
3) read that digest out
4) compare the 2
- if the same return TRUE
- if not the same, put the digest from (1) BACK and return

You can use steps 1, 2, and the "failure" part of 4. The result of 2) is what you want.

Here is the code to "check a password". You'll just need to modify it slightly. Note that if the system crashes between steps 2 and 4 -- the users password will be changed. There is no way to avoid this.

SQL>create or replace package check_password
2 as
3 function is_valid( p_username in varchar2,
4 p_password in varchar2 )
5 return boolean;
7 end;
8 /

Package created.

SQL>create or replace package body check_password
2 as
4 g_alter_cmd varchar2(50)
5 default 'alter user $U identified by $P';
7 g_reset_cmd varchar2(50)
8 default 'alter user $U identified by values ''$P''';
11 function rep_up( p_str in varchar2,
12 p_u in varchar2,
13 p_p in varchar2 )
14 return varchar2
15 is
16 begin
17 return replace(replace(p_str,'$U',p_u),'$P',p_p);
18 end;
20 procedure execute_immediate( stmt in varchar2 )
21 as
22 exec_cursor integer default dbms_sql.open_cursor;
23 rows_processed number default 0;
24 begin
25 dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );
26 rows_processed := dbms_sql.execute(exec_cursor);
27 dbms_sql.close_cursor( exec_cursor );
28 exception
29 when others then
30 if dbms_sql.is_open(exec_cursor) then
31 dbms_sql.close_cursor(exec_cursor);
32 end if;
33 raise;
34 end execute_immediate;
39 function is_valid( p_username in varchar2,
40 p_password in varchar2 )
41 return boolean
42 is
43 l_dba_users1 dba_users%rowtype;
44 l_dba_users2 dba_users%rowtype;
45 begin
46 select * into l_dba_users1
47 from dba_users
48 where username = upper(p_username);
50 execute_immediate(
51 rep_up( g_alter_cmd, p_username, p_password ) );
53 select * into l_dba_users2
54 from dba_users
55 where username = upper(p_username);
57 if ( l_dba_users1.password <> l_dba_users2.password )
58 then
59 execute_immediate(
60 rep_up( g_reset_cmd, p_username,
61 l_dba_users1.password));
62 end if;
64 return l_dba_users1.password = l_dba_users2.password;
65 end is_valid;
67 end;
68 /

Package body created.

SQL>show errors
No errors.
SQL>set serveroutput on
2 if ( check_password.is_valid( 'scott', 'tiger' ) ) then
3 dbms_output.put_line( 'scott/tiger is valid' );
4 else
5 dbms_output.put_line( 'scott/tiger is NOT valid' );
6 end if;
7 if ( check_password.is_valid( 'scott', 'lion' ) ) then
8 dbms_output.put_line( 'scott/lion is valid' );
9 else
10 dbms_output.put_line( 'scott/lion is NOT valid' );
11 end if;
12 end;
13 /
scott/tiger is valid
scott/lion is NOT valid

PL/SQL procedure successfully completed.

SQL>connect scott/tiger
SQL>connect /

and you rated our response

  (19 ratings)

Is this answer out of date? If it is, please let us know via a Review


Very good routine

May 30, 2002 - 4:43 pm UTC

Reviewer: Jgbb from USA

Why can't Oracle make this be easier and more secure

February 10, 2003 - 11:30 pm UTC

Reviewer: Michael O'Neill from Texas

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.

Tom Kyte


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

February 16, 2003 - 11:13 pm UTC

Reviewer: Scott from Perth, WA.


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.
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...

Tom Kyte


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
    function is_valid( p_username in varchar2,
                       p_password in varchar2 )
    return boolean;


create or replace package body check_password

function is_valid( p_username in varchar2,
                   p_password in varchar2 )
return boolean
    l_dba_users1        dba_users%rowtype;
    l_dba_users2        dba_users%rowtype;
    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;


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  /
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.


November 24, 2004 - 1:26 am UTC

Reviewer: zsergeant from Krasnodar, Russia

It's interesting idea, but seems to operate with table sys.user$ will be more easy

Tom Kyte


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

October 18, 2005 - 9:32 am UTC

Reviewer: A reader from Inidia

Been finding it difficult to customize the password verify function for the follwoing requirement:

§ Match any re-combination using all characters in the user’s account/ login name.

Do you have anything to cater this?


Tom Kyte


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> 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  /


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  /


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

October 18, 2005 - 10:23 am UTC

Reviewer: Anil from India

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?

October 27, 2005 - 2:05 pm UTC

Reviewer: Dawar Naqvi from Los Angels, CA USA


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.


Tom Kyte


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$?)

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$?

password easily compromised?

October 31, 2005 - 12:46 pm UTC

Reviewer: Dawar from LA, California, USA


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



Tom Kyte


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?

November 01, 2005 - 11:13 am UTC

Reviewer: Andrew from Michigan, USA

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.

November 10, 2005 - 4:15 pm UTC

Reviewer: Alberto Dell'Era from Milan, Italy

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

January 25, 2006 - 2:07 pm UTC

Reviewer: Frank

Is there any way to decode password column values in dba_users tables?

Tom Kyte


January 25, 2006 - 4:40 pm UTC

it is a one way hash - you cannot "unhash" it backwards.


November 21, 2008 - 4:01 pm UTC

Reviewer: A reader


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.


November 21, 2008 - 8:28 pm UTC

Reviewer: A reader


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


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.
Tom Kyte


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...


November 24, 2008 - 5:41 pm UTC

Reviewer: A reader


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

Tom Kyte


November 24, 2008 - 7:53 pm UTC

... (which is commonly used BTW) ...

so is string concatenation to build sql without bind variables...


November 25, 2008 - 12:58 am UTC

Reviewer: A reader


<<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.

Tom Kyte


November 25, 2008 - 10:48 am UTC

not very hard, not very much work.


December 02, 2008 - 2:46 pm UTC

Reviewer: Sam


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?
Tom Kyte


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"

trouble with function

September 09, 2010 - 11:32 am UTC

Reviewer: Zephyr from Toulouse, France

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.
Tom Kyte


September 09, 2010 - 9:00 pm UTC

you would need direct select granted to you on dba_users.

However, password is not stored in dba_users in current releases - so this code probably won't work in the year 2010 anyway - bear that in mind.

password verify in java

September 10, 2010 - 3:16 am UTC

Reviewer: Zephyr from Toulouse, France

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.
Tom Kyte


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

June 23, 2011 - 5:19 pm UTC

Reviewer: Gerry Bush from Annapolis Junction, Maryland


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.



Tom Kyte


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.

More to Explore


More on PL/SQL routine DBMS_SQL here