3

I'm still working in my ACL project, and I'd like some ideas for the following problem:

I'm using MySQL to store my users, roles and permissions. At first, I created a field "parent_id" in my TABLE Roles, and I was trying to manage the permissions of each user through this. It was kind of working, till I realised that if I add a new role, it was really complicated to manage the hierarchy and control who has access to which resource. I did some searches and I realise it's really complicated to use a relational database to work with hierarchy, so I gave up using hierarchy.

I'd like your help to find the best solution to manage the creation of users: I have 4 different users: SuperAdmin, CustomerAdmin, Technician, client. When I'm in the page to create new users, I don't want to let a technician creates a new user of the type CustomerAdmin, or SuperAdmin, for example.

I thought in letting only the SuperAdmin create a new user, but one of my constraints is that I must let a CustomerAdmin create users too, also a technician.

Trying to be more didactic, the SuperAdmin can be me. The customer admin is my client and he has an enterprise. In his enterprise, he can create 2 types of users: technicians and clients.

This is just an example, but if I want to create a new kind of role giving him new permissions, I must find a way to deny him the permission to create a more powerful user than him.

I'm not sure if I was objective in my question, but anyone who can talk about this with me will be welcome.

sergioviniciuss
  • 4,596
  • 3
  • 36
  • 50
  • It's really difficult to model trees in a relational database based on the nature of what it is, it's just not that well suited to modeling that data structure for retrieval in a single query. I've done this before in PostgreSQL with what's called a materialized path, but my implementation used a few features that MySQL lacks. I used what's called a materialized path and I implemented it using writable views + ltree. The best way to do this with MySQL may well be to pull your data out and build a tree by walking the data, though building a tree datastructure in PHP kind of makes me shudder. – hsanders Jun 28 '12 at 15:30
  • I got your idea.. but I really gotta find a way to solve this in a simplest way.. It wasnt supposed to be this monster – sergioviniciuss Jun 28 '12 at 15:55

2 Answers2

5

You already have your roles but each role needs to have rights to perform actions. This is almost certainly overkill but it should be highly flexible.

 | Role Table                           | 
 | Role ID | Role Name   | Access Level |
 ----------------------------------------
 |       1 | SuperAdmin  |           10 |
 |       2 | ClientAdmin |           20 |
 |       3 | Technician  |           40 |
 |       4 | Client      |           80 |

 | Action Table            |
 | Action ID | Action Name |
 ---------------------------
 |         1 | Create User |
 |         2 | Delete User |
 Etc.

 | Rights Table                   |
 | Right ID | Role ID | Action ID |
 ----------------------------------
 |        1 |       1 |         1 |
 |        2 |       1 |         2 |
 |        3 |       2 |         1 |
 |        4 |       2 |         2 |
 Etc.

 | Parameter Table                                         |
 | Param ID | Right ID | Parameter Name  | Parameter Value |
 -----------------------------------------------------------
 |        1 |        1 | Max User Access |              10 |
 |        2 |        2 | Max User Access |              10 |
 |        3 |        3 | Max User Access |              20 |
 |        4 |        4 | Max User Access |              20 |
 Etc.

The Rights table shows that both SuperAdmin and ClientAdmin can create and delete users. The Parameter table restricts ClientAdmin to creating users with a maximum access level of 20 (1 being the highest). You can match this access level back to a role to offer a list of roles for the new user where Role.Access_Level >= Max User Access.

Actions can use more than one parameter set according to right but I couldn't think of anything other than Max User Access.

SpacedMonkey
  • 2,725
  • 1
  • 16
  • 17
1

What if you used a permission value-type scheme where a number represents the permission "level" and at certain increments you get certain permissions?

E.G.

Super admin 10000 Customer admin 1000 Technician 100 Peon 10

Can read = 10 Can write settings = 100 Can create users 1000

If the target user of an action has a higher permission number than the user performing the action, deny the action. That's a fairly simple way to abstract what those mean without building a materialized path and having to build a tree datastructure.

hsanders
  • 1,913
  • 12
  • 22
  • it's maybe an interesting idea (different, I'd say, of everything I've thought), but the problem is that I'm using Zend_Acl (without the plugin 'cause I must use a personalized MVC framework), and my function to check if the user is allowed or not, gets the roleID, resource and permission), and it's verified in the database, if there's a registry in my ACL_Role table: role_id = 5, Resource_id = 2, permission = read, I check : check(5,2, read). If it's ok, returns 1, else 0. And I think following your idea I wouldnt be able to continue with my ACL. ( I might be wrong). – sergioviniciuss Jun 28 '12 at 16:04
  • You'd likely have to customize your existing ACL solution to use something along these lines. I think to do this you'll need to customize it no matter what. You could define role levels for each existing role and then just do the numeric comparison, rather than use it to define the entire system, and that would require less modification to what you're using. – hsanders Jun 28 '12 at 16:19