Designing a user authenication (Roles & Rights) module

database-designdatabase-tuningdatatypesrelational-theory

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

Best Answer

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?

Related Solutions

Determining user types upon authentication

For the entities and attributes you've described so far and assuming I've understood the domain, I can't see a need for anything more than the tables below. If you there are attributes specific to a Fitter or Admin, add distinct tables for those attributes.

CREATE TABLE UserType
(
    UserTypeId INT
    , UserType NVARCHAR(50)
    , CONSTRAINT PK_UserType PRIMARY KEY (UserTypeId)
)

CREATE TABLE Address
(
    AddressId INT
    , Line1 NVARCHAR(50)
    , Line2 NVARCHAR(50)
    , Line3 NVARCHAR(50)
    , Line4 NVARCHAR(50)
    , Line5 NVARCHAR(50)
    , Postcode NVARCHAR(20)
    , Country NVARCHAR(50)
    , CONSTRAINT PK_Address PRIMARY KEY (AddressId)
)

CREATE TABLE [User]
(
    UserId INT
    , UserTypeId INT
    , UserName NVARCHAR(50)
    , Password NVARCHAR(50)
    , Title NVARCHAR(50)
    , Forename NVARCHAR(50)
    , Surname NVARCHAR(50)
    , CONSTRAINT PK_User PRIMARY KEY (UserId)
    , CONSTRAINT FK_User_UserType FOREIGN KEY (UserTypeId) REFERENCES UserType(UserTypeId)
)

CREATE TABLE Client
(
    ClientId INT
    , Name NVARCHAR(50)
    , AddressId INT
    , CONSTRAINT PK_Client PRIMARY KEY (ClientId)
    , CONSTRAINT FK_Client_Address FOREIGN KEY (AddressId) REFERENCES Address(AddressId)
)

CREATE TABLE ClientUser
(
    ClientId INT
    , UserId INT
    , CONSTRAINT PK_ClientUser PRIMARY KEY (ClientId, UserId)
    , CONSTRAINT FK_ClientUser_Client FOREIGN KEY (ClientId) REFERENCES Client(ClientId)
    , CONSTRAINT FK_ClientUser_User FOREIGN KEY (UserId) REFERENCES [User](UserId)
)

Need help to design model with multiple rights

Since my question I have now a great concept.

Security, rights and access are handled by an ownership table. Each row contains :

booleans access for create, delete, read & update
Link to table Element wich contains all manageable elements in my website
Link to table OwnershipType wich tells me if it's an access on ownership, for all ressources or only a specific ressource ID
Link to table User
An ID in case OwnershipType is only a specific ressource

To manage groups, I didn't want to create more tables, what I found clever was to consider a group as a user and assign it some ownerships. To add a user to a group, I have a Parent table wich link a user to another (in this case that user is in fact a 'group'). It explains why I have a UserType table. It tells me if it's a group or a normal user.
In my code I just have to check if a user has a parent and if so add its ownership to the user's ones.
That concept let me having some global ownership on many user who are linked to a group and still have really specific ownership on certain user.

If people are interested I can explain how I implemented that concept in my website, it's quite simple and clean.

My model :

Best Answer

Related Solutions

Determining user types upon authentication

Need help to design model with multiple rights

Related Question