creating password field in oracle

Question

What is the use of "The Secure External Password Store"?

And can I create password field in my Oracle table using "The Secure External Password Store"? Or how can I create password field in my Oracle table without using "The Secure External Password Store"?

Answer 1

One method without using "The Secure External Password Store" (whatever that may be) is to add a RAW(16) column to the table to store a hashed username and password:

alter table mytable add password raw(16);

Then store the hashed username and password in it like this:

insert into mytable (username, password, ...)
values (:username, dbms_obfuscation_toolkit.md5 
                      (input => utl_i18n.string_to_raw
                                  (upper(:username)||:password))
       );

Then when a user tries to log in with a username and password you can check them like this:

select 'OK'
from   mytable
where  username = :username
and    password = dbms_obfuscation_toolkit.md5 
                      (input => utl_i18n.string_to_raw
                                  (upper(:username)||:password));

This way nobody can find out what the stored password is (other than by brute force).

Answer 2

The secure external password store is not designed for regular user accounts. It is aimed at securely holding passords for accounts which need to connect to the database from autonomic processes (like, say, shell scripts ):

The secure external password store uses an Oracle Wallet to hold one or more user name/password combinations to run batch processes and other tasks that run without user interaction.

Find out more.

Why are you wanting to store user passwords in the database? If you want users to connect through individually identifiable accounts, use Oracle's USER functionality. If you are building a web site and your users will be connecting through a generic user then the authentication is best managed through something in the middle tier which authenticates against Active Directory (or whatever) through LDAP (or whatever).

But if you really want to roll your own authentication then using a one-way hash as Tony suggests is the way to go. However, if you are on a recent release of Oracle (and your interest in the secure external password store suggests you are on 10g at least) then you ought to use DBMS_CRYPTO.HASH() with the SHA-1 algorithm rather than the older MD5.