4

I am working on my academic project. I have a table for employee records which have employee id, joining date, designation and other fields. I am trying to implement a scheme in which when record of employee is added first time in DB, a user name and temporary password will be generated so that New employee can use it to login in DB to see his details. I need to ask if it is good practice to save login id and password in employee record table? Or should I store them separately, but then I think I can not generate temporary password and id at time of inserting employee data in DB. Or is there any other way to implement this scheme?

Edit: I understand that I should never save clear text password in DB and should use salt/hash. However I am looking for a way that above task can be done in one action, i.e If I make a separate table for user id and password then how can I am able to enter employee details in employee table and generate user id / password for user and store in from separate table in one action.

  • Added a comment in my post regarding 'how'. – RLF Feb 4 '15 at 16:44
3

Whether you use one table or two, you should be able to insert all the data needed for in a single transaction.

More importantly, you should not store the password, but instead store a password hash. Here is one post on using a password hash such as SQL Server itself uses. This is using SHA_512 hashing with a 32-bit salt.

http://sqlity.net/en/2460/sql-password-hash/

This way you never store the actual password. At the time of the creation of the password, you need to share the password with your new user. And ideally force him to change the password at first use.

Each time a new password is created, you must, of course, update the hash to match the current password. Which means that you must manage all password changes, not just the initial password.

EDIT Re: "how can I am able to enter employee details in employee table and generate user id / password for user and store in from separate table in one action."

You must do the work in a transaction. Here is a sketchy outline using 3 stored procedures. Of course, you need to write the actual code:

BEGIN TRANSACTION; 
   EXEC InsertNewUser @Name='UserName', ... , @NewUserId INT OUTPUT; 
   EXEC CreateNewUserPassword @UserID=@NewUserID, ..., @Password NVARCHAR(128) OUTPUT;
   EXEC SendPasswordToNewUser @NewUserID, @Password; 
COMMIT TRANSACTION; 

By collecting the new @UserID and @Password values you can use them in the following steps.

| improve this answer | |
  • Furthermore: as well as any static salt each account should have an account specific value added to the salt. This makes it impossible to know one password by knowing another and noticing that the hashes are the same. The extra data for the salt could be the user ID, or better still an arbitrary value generated (randomly, or username+timestamp for instance) at the time the hash is made and stored with it (this means if the user reuses the same password after the period that your rules don't permit them to, again this can not be seen from inspecting hashes). – David Spillett Feb 4 '15 at 13:54
3

From a database design perspective, I generally suggest a separate table for authentication credentials. That table could include additional attributes that apply to the login itself rather than the employee (e.g. password expiration date) and avoid need to carry that baggage around on every employee table query.

Also, I strongly recommend you never store passwords used for application authentication in the database. Instead, store a salted password hash. That is more secure since clear the text password cannot be retrieved even if the database is compromised.

| improve this answer | |

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.