What are the fields that the user table should contain from the security/authenication perspective?

Question

When designing user table what would be the must have fields from the security/user authentication point of view for a Web based Application (.NET and SqlServer 2005)

I came with with the following fields:

userID
username -- preferably email
passwordHash 
onceUsePassword -- to indicate that the password should be changed after login
alternativeContactEmail 
userStatusID -- FK to a lookup table with statuses like: active, diabled etc 
dateCreated
dateUpdated
lastPasswordUpdate
lastLogon
-- and then the rest like :forename, surname etc which are not of the interest in this question

Am I missing something?

Is standard identity (INT) sufficient for userID or should the GUID be used instead (the userID is not going to be exposed anywhere)?

EDIT:

• I am limited to the use of .NET 1.1 (don't ask...)
• The salt info will be merged with passwordHash
• the account would be unlocked by sending a temporary, single use system generated password to the user email address (hence onceUsePassword field)

Answer 1

Why not just use the built-in SQL Membership Provider if you're using SQL Server anyway? It's much better than rolling your own since it's been tested by a lot of people.

In any case, you should think about adding a salt field your table.

Salting

Update: .NET 1.1? I guess that answers my question. Is your application for the consumption of the general public? If so, you might want to add a way for them to unlock their accounts via a secret question.

Answer 2

onceUsePassword -- to indicate that the password should be changed after login

If you have to explain it that much, you should rename it. Something like "forceChangePasswordOnLogin".

Answer 3

You should add a "salt" field to use password salting to avoid dictionary attacks with rainbow tables if your database ever got compromised.

I'm not sure what you mean by "The salt info will be merged with passwordHash". Does that mean that the same salt is used for all password hashs? Would make more sense to generate a random salt for each hash, and store it in a separate field.