MySQL PHP User/Group Permissions

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

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