Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to model a User Authentication module for a MS SQL Server database that will be the back end to a Delphi UI Application. Basically, I want to have user accounts where the user belongs to only one group. A group can have "n" number of rights.

I also want to add password history to the database, as the user will be required to change their password based on a application setting (example, every 90 days).

I also want to log an event for each time a user logs in and out. I may extend this to additional events in the future.

Below you will find my first crack at it. Please let me know any suggestions to improve upon it, as this is my first time doing this.

Do you see any need for additional attributes for role-based security and constraints for the password rules/expiration periods?

db-design

share|improve this question

1 Answer 1

up vote 5 down vote accepted

Based on your stated requirements, your model is in pretty good shape.

Here are some suggestions for improvement:

  • You don't say so explicitly, so it's hard to say - but it looks like you might be storing the user password directly. This would be very bad! If you look at common authentication databases, passwords are stored in encrypted form. You often see both a password column and a password_salt column.

  • Your USER_LOGS table has an Event column. You aren't clear about how this is to be populated. Should there be an EVENT_TYPE table which USER_LOGS references? This might make for friendlier reporting. Typical events would include log-in, log-out, password fail, password change, password reset, lock-out, unlock, ...

  • Your GROUP_RIGHTS table doesn't indicate who granted the rights. For audit trail purposes, people often keep a log of who changed what record and when. That may not be an issue for you.

Here's a couple of questions around your stated business requirements, which differ from the "text book" role-based security pattern in a couple of ways:

  • Are you sure you want users to be in only one group? The advantage of role-based security is that the roles tend to be pretty static, whereas the people fulfilling roles come and go pretty often. Included in this is that some people often "wear two hats".

  • Your design is grant-only. Some systems include grant and revoke. This allows you to say that a widely available right is not available to a particular group.

  • You have users and accounts comingled as USERS in your design. There is often a distinction between people and user IDs. Some user IDs are for teams or machines and some people have multiple user IDs for different purposes. Is this a distinction that would be helpful to you?

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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