user adding users

[Destramic]

hey guys i'm currently creating role and permission for my users which looks like this:

users
------------------------
user_id
role_id
------------------------

user_permissions
------------------------
user_permission_id
name
------------------------

user_roles
------------------------
user_role_id
name
------------------------

user_role_permissions
------------------------
user_role_permission_id
role_id
permission_id
------------------------

a role can be created and permissions are added to that role, giviing user access to certian pages.

 

the problem i face is that my website has 4 types of users

 

admin (me)

general public

clients

clients employees (client employees)

 

all 4 will see different content.

 

here is my problem and what i want to achieve is for my clients to be able to add users (employees) which are linked to thier account as well as giving them certian permission

 

for instance if i had Walmart as a client, they'd have a client role...now if they wanted to add a user (employees) linked to thier account what is the best way to do this?

 

i could have 3 extra tables

clients
------------
client_id
user_id
name
------------

client_users
------------
client_user_id
client_id
user_id
------------

client_user_premissions
------------
client_user_permissions
user_id
permission_id
------------

i link a client to a user account....and link a client user to a client and user

 

also the client can pass over certian permission via the client_user_permission

 

any ideas on design pattern would be appreciated as i've never done nothing like this when it comes to users creating users

 

thank you

 

 

[Destramic]

Could I get some help with this design please guys?

[Jacques1]

Why exactly do you want three(!) sets of permissions? You have user-specific permissions, then role-specific permissions and finally employee-specific permissions. It would make a lot more sense to bind permissions to roles and allow a user to have multiple roles. A role can represent all three cases: a single user, a group and an employee. You can even reuse the employee-specific permissions (e. g. all employees of X should be able to do Y).

[Destramic]

my main concern was knowing if the user is a client or employee...here is my database diagram

 

 

i think im on the right track here...a user has a specific roles give to he/her but also able to give addition permissions out of the role.

 

i'm able to detect if the user is a client by his/her role (ie. client)...and the same with an employess (ie. client_employee) and also linking the client user_id to the emplyee user account via the client_id in the users table

 

regarding my concern of clients adding user accounts for employees, and giving that employee specific permissions, i was thinking that the client role permissions could be displayed on the employee register form...that way the client can choose specifically what permission that employee has based on his own permissions?

 

 

thank you

[Jacques1]

You still haven't explained why you think you need an extra case for user permissions when this could be handled just fine with the generic role permissions. The user permissions of Joe Blow can be implemented as the permissions of the user-specific role “Joe Blow”.

 

This will simplify both the database schema and future queries a lot. Right now, you'd have to do a UNION of both permission tables whenever you need to calculate the effective permissions.

 

As to the employee permissions, I would generally avoid assigning individual permissions to employees. It makes a lot more sense to have a generic role like “Walmart salesman” and only add employee-specific permissions when they're actually needed.

[Destramic]

the reason i have the extra user_permissions table was so that i was able to give extra permissions out of the given role...but after thinking on what you said i deleted the table...in fact i'm going to role with all you suggestions.

 

note i changed names on some tables to make more sense.

 

here is how i'm getting my permissions

SELECT p.permission
FROM permissions p
LEFT JOIN role_permission_mappings rpm ON rpm.permission_id = p.permission_id
LEFT JOIN roles r ON r.role_id = rpm.role_id
LEFT JOIN user_role_mappings urm ON urm.role_id = r.role_id AND urm.user_id = 3

and my roles

SELECT role 
FROM roles r
LEFT JOIN user_role_mappings urm ON urm.role_id = r.role_id
WHERE urm.user_id = 3

say when checking if a user has permission to edit news is checking permission for edit_news enough? or should i be checking if the user is admin too?

if ($user->has_role('admin') && $user->has_permission_to('edit news'))
{
   // yes!
}

vs

if ($user->has_permission_to('edit news'))
{
   // yes!
}

thank you for your help

[Jacques1]

The queries don't really make sense.

 

Your first query always yields all existing permissions regardless of the user (which is potentially dangerous), because for some reason you're using left joins and put the user ID check into a join condition rather than the WHERE clause. To get the actual permissions, you need

SELECT DISTINCT    -- note DISTINCT: the user may have the same permission from multiple roles
    permissions.permission
FROM    -- note: no need to join with roles table
    permissions
    JOIN role_permission_mappings ON role_permission_mappings.permission_id = permissions.permission_id
    JOIN user_role_mappings ON user_role_mappings.role_id = role_permission_mappings.role_id
WHERE
    user_role_mappings.user_id = 3
;

The second query also needs an inner join. You're already kicking out the rows added by the left join through the WHERE condition.

 

As to the last question: The hard-coded role check doesn't make sense. In fact, it will be extremely confusing if a user cannot edit news when they've been explicitly been allowed to (but don't happen to be an admin). It also means that code changes are required to introduce new roles (e. g. super admin).

 

In any case, you should definitely think your permission model through before jumping to the implementation. Some parts of the concept don't seem to be clear yet.

Edited October 29, 2016 by Jacques1

[Destramic]

ok thank you i made the changes that you said

 

 

In any case, you should definitely think your permission model through before jumping to the implementation. Some parts of the concept don't seem to be clear yet.

 

could you elaborate on this a little please jacques?

 

thank you for your help

[Jacques1]

In your previous post, you weren't sure if the permission checks should also include the role. I'm simply saying that you should make business decisions like this before the implementation.