Designing a multiple level user permission system

php mysql web-applications user-roles user-permissions
Vanessa picture Vanessa · May 20, 2011 · Viewed 12.8k times · Source

I am working on a site that has a database schema like a tree. From top to bottom I have:

  • One Company can have many
  • Locations that can have many
  • Fleets that can have many
  • Vehicles

The user permission system has to be flexible in that someone can gain or have restricted access at any level of the tree. For example, a person that manages location x will by default have full access to all fleets below location x and all vehicles below location x. But I should also be able to restrict the user's access to any node below location x.

What would be the proper way to develop a schema for a permission system such as this? Would it be realistic to store each node a user has access to even though it would be kind of redundant? The goal is for me to be able to easily run queries for example to get all locations a user has access to.

Answer

Denis de Bernardy picture Denis de Bernardy · May 20, 2011

You might find a number of threads on SO interesting. Among them:

Implementing permissions based on reputation

Database schema for ACL

Related questions

PHP Fatal error: Class 'PDO' not found

PHP Fatal error: Class 'PDO' not found in /home/bd/public_html/app/webroot/Cake/Model/Datasource/Database/Mysql.php on line 177 PHP INFO: PDO PDO support => enabled PDO drivers => sqlite, sqlite2, mysql pdo_mysql PDO Driver for …

Read More
How can I improve this PHP/MySQL news feed?

Let me start right off the bat by saying that I know this is not the best solution. I know it's kludgy and a hack of a feature. But that's why I'm here! This question/work builds off some discussion …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More