MySQL database schema for user and group access control

Question

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

I am currently developing a statistics tool that shows some graphs and data for several applications. The access to this data obviously needs to be restricted, as User A owns applications "One" and "Two" and must not see "Three" or "Four".

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

• The user himself has the permission to access this piece of data of this application
• Any one of the groups the user is member of has the permission to access this piece of data of this application

The goal is to have a table that stores the actual permissions each user currently has for every application, computed from the group memberships and individual permissions and have that information be consistent at all times due to the relations to other tables.

I don't know if this helps to find a solution, but here's the SQL to get the currently active permissions of the user 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 store in an extra table (just for all users).

Answer 1

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