1

I am trying to configure a mysql database table that I can use as a per user/group permissions table. Almost like what Facebook has, for a local company intranet site.

What is the best possible way to design this table, that would allow me to find user permissions with the fewest amount or queries?

4

There are several ways to manage permissions. Wanting to validate these in the database, one of the possible options is to establish a list of permissions, and associate these permissions to possible actions by the user (the creation of groups is a consequence of this report).

Assuming then a very simple case, where the user decides to perform an action on the interface, for example, "add a comment to a post," a possible workflow is as follows

Simple Work Flow

System

Interface

Pressing a button in the interface invokes the "Add Comment" (which acts on the interface to create a text field and a button, if that user has permission to do so. Otherwise the error message will be shown.)

API

All of the methods, not public, in the backend, provide for the control of permissions before running the rest of the code. So the query is executed in the database to see if the user has permission for action "Comment".

Database

The database performs a select in the table where are recorded users and actions. Responding with a NULL (there is no permission) or ID (allowed).

At this point we can assume that:

  1. Any private action (method exposed in the API), after the validation of input parameters, performs the validity check.
  2. The function in the backend, to authorize the execution of code takes 2 parameters (minimum): UserID, ActionID;
  3. The function in the database (or the query to be executed in the database) involves reading in a table that has 3 fields (minimum): ID, user ID, ActionID;

Groups

The groups should (could) be managed as presets of permission. There are so many ways to manage the permissions of the group. Probably one of the least expensive in terms of development, is to create a table for managing the relationship between Groups and Users.

Conclusions

The structures of our database will therefore be as follows:

Users

  1. UserID
  2. Username
  3. ...

Actions

  1. ActionID
  2. ActionName
  3. ...

Permissions

  1. ID
  2. UserID
  3. ActionID

Groups

  1. ID
  2. GroupID
  3. UserID
  • Thank you very much for the very detailed, and descriptive answer. I appreciate that. – Justin E Jan 18 '14 at 12:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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