I'm starting to write my first serious web application and am thinking about how to store username and password information. There are plenty of articles detailing how storing plain text passwords is a very bad idea, and salted encrypted passwords are the way to go. However, I can find very little about restricting access to the user data itself.

I'm thinking about a situation where my web app is compromised and an attacker can read any information from my database. Even if I store usernames with encrypted passwords, the attacker would be able to lift all the usernames from the database (but at least they wouldn't get the passwords). However, I could restrict access so even my app didn't have access to this data. I was thinking of preventing my database user from viewing the table with usernames and passwords. You could then give access either via a stored procedure which takes the username and password - returning true if a username with the password is found, or presenting a view of hashed usernames and passwords, so even if compromised the real usernames can't be found.

Given I'm new to this, and I've failed to find any articles recommending anything similar, I'm guessing I've missed something simple that makes the above ideas insecure.


migrated from stackoverflow.com Jun 21 '11 at 12:28

This question came from our site for professional and enthusiast programmers.

  • You need to control who has access to passwords as much as you need to control how they have access. Make sure that username and password access and control functions are logged, reviewed, and audited. – this.josh Jun 22 '11 at 20:21

From a security standpoint, the threat is what happens when the underlying storage is exposed in any way, not just a sql injected select * from users; statement. Do you trust your administrators? Even the one you just had to let go? Who else has access to the box? Is it a virtual machine at a third party company? In the cloud? That is why getting hashing right is often emphasized first and foremost, so that user passwords have some defence even against an authorised user.

That said, I think controlling access via views would be beneficial, such that you can only execute pseudo-instructions "insert user", "delete user", "does user exist" and "are these credentials valid" from the perspective of your web app. This prevents enumeration except by repeated calls to the database with a known user identifier, which is better than allowing the attacker to grab the entire database. You're reducing the ease with which an attacker can grab that table in the case of any potential compromise/unforeseen vulnerability in your code or framework. The only question is compatibility with existing frameworks, although many provide the ability to extend or implement custom authentication backends.

On the subject of hashing, use PBKDF2 or bcrypt or an equivalent slow hash key derivation function with salt, not just a hash with salt. A decent article on the subject will explain this stage (sometimes they recommend repeating a hash for several thousand iterations; it's essentially the same idea).

  • Very well written. To add, since your tag is Ruby-on-Rails, Devise uses the bcrypt method of encryption. – ardavis Jun 21 '11 at 0:17

Can I be flippant and risk down votes by saying don't?

Best way to avoid your usernames and passwords getting hacked. Don't store them in the first place.

Use a federated authentication like Facebook Connect (oAuth) or Google Open-ID. I write more on the benefits here.

  • 1
    +1 but it should be noted that your advice makes sense in many cases, but not all cases. I don't want my bank to switch to OpenID – Justin C Jun 21 '11 at 13:51
  • @Justin-c how about if they became the Identity Provider (IDP)? And if you still don't like it... in a few years you may need to move to a smaller bank. – Rakkhi Jun 21 '11 at 13:56
  • 3
    @Rakkhi, that makes no sense, why would I want my bank to become an IdP? The only reasonable use for that Id would be - on the bank itself, so what did we accomplish with that? Simply inserting the newest buzztech du jour, but still effectively keeping the status quo (where the bank site keeps its own identity for you)... – AviD Jun 21 '11 at 14:04
  • @avid banks collect a lot of information, do know your customer, most have Internet banking, resource providers may want to use that for certain products. sorry to be vague but I can't give you a specific answer yet. Will be public in about 6 months. – Rakkhi Jun 21 '11 at 14:13
  • 2
    Having individual service providers manage identification and authentication builds implicit seperation. I have a lot of different usernames and passwords, because I don't want a single point of failure to expose all my resources. It is a pain, but for more valuable resources I want separation. For less valuable resources I am willing to use identity providers for the convenience. I don't believe that typical consumers understand the risks implicit with Identity Providers, and are therefore very attracted to it's convenience. – this.josh Jun 22 '11 at 19:03

Check out Devise. It greatly assists with managing users.

Also Railscast from Ryan Bates:

Introducing Devise

Customizing Devise

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy