Sample User Authentication Database Schema

My User/Password Database Schema for Business Applications

When I first bumped into the built in ASP.Net User Authentication Provider, I lived for a couple of hours in bliss thinking to myself “that’s convenient, I’ll never have to worry about the user authentication portion of my websites and business applications again” — until I tried to use it.

It really is pretty decent, but it is geared toward large “web 2.0″ applications where users sign up and start contributing. Not business applications where I.T. or H.R. adds users and controls groups very closely. Where there are more than just “admin” and “user” account types.

In my experience, businesses have many roles within their organization, and they want to be able to drill into the application and have a single button or text field to be hidden or disabled based on whether or not a user is in a particular group.

Some want them to be tied in to Active Directory, but some don’t even have computer logins for all their employees, and some don’t want applications (especially 3rd party applications) to be tied in to Active Directory (dispute the marketing that claims otherwise).

authActivity

authActivity

Id : Integer <<PK>> <<auto incr>>

DisplayName : String(64) <<not null>>

Detail : String

An Activity is a single thing a user might want to do in the system. For instance “Create users,” “Delete users,” “Re-activate users,” “Assign Employee ID Numbers,” or “View Social Security Numbers.”

As developers build features into the software, they create new records in this table. There is no reason to be skimpy and try to lump a bunch of activities into a single umbrella activity. You can create activities for every feature of the software if you want.

Activities are only created (or maintained) by development. The people who administrate the system do not have any power to create new activities (other than to bug development to create an activity) because it is the responsibility of the development team to make sure the code is aware of what activities could be performed in certain areas of the software and to enable/disable things based on the current user’s permission set. It wouldn’t do any good for a non-developer to create an activity (like say: “Reset Other People’s Passwords”) if the software has no concept of what that means.

The Detail field on this (and the other tables) is an XML field not just a description field. The actual data stored in that field is really up to you, and your application could support any number of configuration fields inside of that detail field. This flexible design is really convenient, but comes at the cost of not having straightforward SQL access (sorting, and filtering) to the contents of the field. You are almost required to pull it into your application and use LINQ to XML to get to the stuff you store in the details field.

authRole

authRole

Id : Integer <<PK>> <<auto incr>>

DisplayName : String(64) <<not null>>

Detail : String

A role is a set of permissions. A company might have an “HR” and an “HR Admin” role. As well as a “BookKeeper” role and a “Salesman” role. Perhaps they will also have a “Tech Support’ role and a “Systems Admin” role. Each role has any number of activities associated with it, overlap is OK (so the Systems Admin role and the Tech Support role can have a fair amount of overlap, that is OK). It is also OK for a single user to be assigned multiple roles. For instance a “Salesman” could also be a “Payroll Clerk.” This design is based entirely on what experience has dictated as I have built business software over the years.

authRoleActivity

authRoleActivity

Id : Integer <<PK>> <<auto incr>>

RoleId : Integer <<FK-authRole>>

ActivityId : Integer <<FK-authActivity>>

Detail : String

This table is simply the many-to-many join table that connects authRoles and authActivities.

authUser

authUser

Id : Integer <<PK>> <<auto incr>>

DisplayName : String(64) <<not null>>

UserName : String(32) <<not null>>

Password : String(32)

Detail : String

This should be self explanatory, except for the fact that there is not a field for keeping track of who is active. (See the next table for why.)

authUserActive

authUserActive

Id : Integer <<PK>> <<auto incr>>

UserId : Integer <<FK-authUser>>

AsOf : DateTime <<not null>>

Until : DateTime

Detail : String

As businesses get to be a little more mature, they are no longer worried about simply whether or not a user is active and therefore allowed to log in right now, but they are interested in WHEN a user was allowed to log in. They want to be able to create a user who will expire in a month. Or they want to be able to have their summer interns only be active during the summer months. And, when they are audited, they need to be able to show that a particular user had access to a particular system at a particular time.

In order to accommodate this, I created this table, which will have entries for each time period where a user is active. So, the system will look to see if there is a record in this table where the login day is between the AsOf date and the Until date. If there is one, then the user is active (or was active on the date you tested for).

You should use a constraint to make sure AsOf < Until. Also, you can either choose to allow Until to be null which means "Until we assign an end date" or you can set it to the max date for your DBMS as a default field value. Using a null is technically more "correct" but I think the logic for querying looks nicer if you can assume that Until contains a constant value guaranteed to be in the future.

authUserRole

authUserRole

Id : Integer <<PK>> <<auto incr>>

UserId : Integer <<FK-authUser>>

RoleId : Integer <<FK-authRole>>

AsOf : DateTime <<not null>>

Until : DateTime

Detail : String

This is a simple many-to-many join table assigning users to groups with one little twist: It also has the AsOf / Until logic. When you are audited, it isn’t good enough to know whether or not a user had access to the system. You need to be able to show that they had access to a particular feature or not.

And, here is a SQL script for generating the Schema in SQL Server 2008 (don’t say I never gave you anything).

create function
dbo.MaxDateTime()
returns datetime as
begin
    return convert(datetime, '99991231 23:59:59:997')
end
go

create function
dbo.MinDateTime()
returns datetime as
begin
    return convert(datetime, '17530101')
end
go

create table
authActivity
( Id int primary key identity
, DisplayName varchar(64) unique not null
, Detail text
);

create table
authRole
( Id int primary key identity
, DisplayName varchar(64) unique not null
, Detail text
);

create table
authRoleActivity
( Id int primary key identity
, RoleId int not null foreign key references authRole(Id)
, ActivityId int not null foreign key references authActivity(Id)
, Detail text
, constraint nk_authRoleActivity unique (RoleId, ActivityId)
);

create table
authUser
( Id int primary key identity
, DisplayName varchar(64) unique not null
, UserName varchar(32) unique not null
, Password char(32)
, Detail text
);

create table
authUserActive
( Id int primary key identity
, UserId int not null foreign key references authUser(Id)
, AsOf datetime not null
, Until datetime not null default dbo.MaxDateTime()
, Detail text
, constraint nk_authUserActive unique (UserId, AsOf)
, constraint ch_authUserActive_AsOfUntil check (AsOf < Until)
);

create table
authUserRole
( Id int primary key identity
, UserId int not null foreign key references authUser(Id)
, RoleId int not null foreign key references authRole(Id)
, AsOf datetime not null
, Until datetime not null default dbo.MaxDateTime()
, Detail text
, constraint nk_authUserRole unique (UserId, RoleId, AsOf)
, constraint ch_authUserRole_AsOfUntil check (AsOf < Until)
);

Did you enjoy this post? Why not leave a comment below and continue the conversation, or subscribe to my feed and get articles like this delivered automatically to your feed reader.

Comments

No comments yet.

Leave a comment

(required)

(required)