User Authentication and permissions table structure

Question

I am creating a user authentication library (for the exercise).

One of the things I am adding is is, a user can be assigned to multiple roles.

Each role has set of permissions (think editUser, createUser etc).

If a user is part of two groups if any group has permission to do an action then the user can do the action.

I am wondering the best way to store this information from a mysql database point of view.

I was thinking

users            : ID            | username | etc  
groups           : ID            | name     | etc  
user_group       : group_ID      | user_ID  
permissions      : ID            | name     | description (lookup table)
group_permission : permission_ID | group_ID

and basically if a group has a permission then it gets an entry in group_permission.

My question is, is this the most productive way to do this, or would I be better to have each permission as a column in the groups table and drop the group_permission table?

Answer 1

You approach looks nice and normalized, kudos for that.
One thing that I'm missing is a non-permission table, i.e. a table that disallows actions.
Active directory has this and this allows you to quickly block permissions to an object.

This allows you to allow access to all accounts, except .....
If you do it the other way round, you have to allow access to each object whilst leaving out the HR data.
The first way sets permissions on 2 objects (1 permission on parent, 1 dismissal on child), the second way can run into dozens of permissions.

Personally I would update the permissions table to also include exclusions.
This would allow you to attach exclusions to both groups and users.

Use a blackhole table to simplify addition of new permissions
In order to simplify adding new permissions, you can create a new blackhole table.
This does not store anything, but will fire a trigger instead that does the insertion for you, in this manner you can hide the fact that your DB is normalized from the insertion code.

CREATE TABLE bh_permission (
  user_or_group_id unsiged integer not null,
  isuser ENUM('user','group') not null default 'user',
  permission_description varchar(255) not null,
  allow_or_not ENUM('allow','forbid') not null default 'allow'
) ENGINE = BLACKHOLE;

Now you can insert into the table specifying either group or user_id

INSERT INTO bh_permission VALUES ('123','group','p_HR_files_2011','forbid');

And have a trigger to handle the technical details:

DELIMITER $$

CREATE TRIGGER ai_bh_permission_each AFTER INSERT ON bh_permission FOR EACH ROW
BEGIN
  DECLARE Mypermission_id INTEGER;
  //like is always case-insensitive, `=` is not.
  SELECT p.id INTO Mypermission_id FROM permissions p 
    WHERE name LIKE NEW.permission_description LIMIT 1;
  IF isuser = 'user' THEN
    INSERT IGNORE INTO user_permission (user_id, permission_id, allow_or_not)
      VALUES (NEW.user_or_group_id, Mypermission_id, NEW.allow_or_not);
  ELSE
    INSERT IGNORE INTO group_permission (group_id, permission_id, allow_or_not)
      VALUES (NEW.user_or_group_id, Mypermission_id, NEW.allow_or_not);
  END IF;
END $$

DELIMITER ;