Designing a user authenication (Roles & Rights) module

Question

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?

Answer 1

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?

Answer 2

I Think bitwise operator are the best way to implement user permission. Here I am showing how we can implement it with Mysql.

Below is a sample tables with some sample data:

Table 1 : Permission table to store permission name along with it bit like 1,2,4,8..etc (multiple of 2)

CREATE TABLE IF NOT EXISTS `permission` (
  `bit` int(11) NOT NULL,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`bit`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert some sample data into the table.

INSERT INTO `permission` (`bit`, `name`) VALUES
(1, 'User-Add'),
(2, 'User-Edit'),
(4, 'User-Delete'),
(8, 'User-View'),
(16, 'Blog-Add'),
(32, 'Blog-Edit'),
(64, 'Blog-Delete'),
(128, 'Blog-View');

Table 2: User table to store user id,name and role. Role will be calculated as sum of permissions.
Example :
If user 'Ketan' having permission of 'User-Add' (bit=1) and 'Blog-Delete' (bit-64) so role will be 65 (1+64).
If user 'Mehata' having permission of 'Blog-View' (bit=128) and 'User-Delete' (bit-4) so role will be 132 (128+4).

CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `role` int(11) NOT NULL,
  `created_date` datetime NOT NULL
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

Sample data-

INSERT INTO `user` (`id`, `name`, `role`, `created_date`)
   VALUES (NULL, 'Ketan', '65', '2013-01-09 00:00:00'),
   (NULL, 'Mehata', '132', '2013-01-09 00:00:00');

Loding permission of user After login if we want to load user permission than we can query below to get the permissions:

SELECT permission.bit,permission.name  
   FROM user LEFT JOIN permission ON user.role & permission.bit
 WHERE user.id = 1

Here user.role "&" permission.bit is a Bitwise operator which will give output as -

User-Add - 1
Blog-Delete - 64

If we want to check weather a particular user have user-edit permission or not-

  SELECT * FROM `user` 
     WHERE role & (select bit from permission where name='user-edit')

Output = No rows.

You can see also : http://goo.gl/ATnj6j