Geek Answers Handbook

MySQL database schema for user and group access control

I’ve been trying to find a solution to this problem for a couple of days, and I just can’t come up with something that works. The problem is this:

I am currently developing a statistics tool that shows some graphs and data for several applications. Obviously, access to this data should be limited, because User A owns the applications "One" and "Two" and should not see "Three" or "Four".

Now each user can be a member of several groups and inherit permissions from this group, but can also have individual permissions. These permissions must be set for each application. Access to the data set of one application is granted if:

  • The user himself has permission to access this part of the data of this application.
  • Any of the groups to which the user belongs has permission to access this part of the data of this application.

http://i.stack.imgur.com/y4W6E.png

The goal is to have a table that stores the actual permissions that each user currently has for each application, calculated from group memberships and individual permissions, and that this information is always consistent due to relationships with other tables.

I don’t know if this helps to find a solution, but here is the SQL for getting active user permissions with id 1:

( SELECT u.perm_id AS perm, u.user_id AS uid, u.app_id AS app FROM daUsers_has_daPermissions AS u WHERE u.user_id = 1 ) UNION ( SELECT g.perm_id AS perm, u.user_id AS uid, g.app_id AS app FROM daUsers_has_daPermissions AS u, daUsergroup_has_daPermissions AS g, daUsergroup_has_daUsers AS g_has_u WHERE u.user_id = 1 AND u.user_id = g_has_u.user_id AND g.group_id = g_has_u.group_id );

This is what I want to keep in an additional table (only for all users).

+7
source share
1 answer

Sounds to me, you have to use a view. If you already have a query, use the query to create the view.

+3
source

More articles:


All Articles