Ask the Expert

Should passwords in table be encrypted?

I have one table where username and password are stored. I wanted to know if the password entered in that table should be encrypted?

    Requires Free Membership to View

Don't encrypt passwords--hash them. You do not need to know what they are and if the user forgets their password, just reset it and send them a new one. This is essentially the same way that *nix hosts have stored passwords for many years.

Oracle has a builtin function for hashing a string at DBMS_UTILITY.GET_HASH_VALUE. However, in order to use that, you'd have to pass the cleartext password to the database server in order to make the function call. Unless you're using the Advanced Security Option (ASO), SQL*Net will transmit that data over the network in clear text. So, here are your best options:

  1. Use a hash function in your application to do the hashing in the application before the data is sent to the database for insert, update, etc. This could be on the application server for a greater than 2-tier application, or in the application code for a 2-tier application.
  2. Use the database function, but secure the SQL*Net traffic using SQL*Net encryption (part of the Advanced Security Option). The drawback to this approach is that you'd have to purchase ASO if you don't already have it and it is an add-on option.
  3. Use the database function, but secure the SQL*Net traffic using a SSH tunnel. If your application is greater than 2-tier (i.e. there is one or a small number of application servers), then you could establish SSH tunnels from the application servers to the database server and run SQL*Net traffic through these tunnels. This takes some more setup and maintenance, but can be done without purchasing any additional software (there are totally free SSH implementations for all platforms).

Regardless of which option you use to get the data to and from the database, here's how you'd go about using this approach to set a password:

  1. request the password from the user
  2. hash the password
  3. store the hashed password in the database table

For logins:

  1. request the password from the user
  2. hash the password
  3. obtain the hashed password in the database table
  4. compare the strings from #2 and #3. If they match, this is the correct password.

Tom Kyte answered some questions regarding this (in his usual, thorough way) at: http://asktom.oracle.com/pls/ask/f?p=4950:8:16985564963218549155.


This was first published in October 2004

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: