9

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

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

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).

Anpan
  • 1,146
  • 1
  • 10
  • 20

1 Answers1

4

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

Edwin Stoteler
  • 1,218
  • 1
  • 10
  • 25
  • Thanks for the hint, I didn't even know what a view was until just now. I'll do that for the time being, however it doesn't *really* solve my problem, since the view also collects all the data from every relevant table every time someone requests data from it, which is precisely what I want to avoid. I want to store that information in a table, and **only** update it if there are actually changes to something that affects permissions (like a group being deleted). – Anpan Apr 12 '13 at 07:07
  • Then you could use triggers. [MYSQLtriggers](http://dev.mysql.com/doc/refman/5.6/en/triggers.html). When a table is updated that affects permissions. Make the trigger update the table where all permissions are stored. – Edwin Stoteler Apr 12 '13 at 07:14
  • What you need is a Materialised View, which is a feature that doesn't exist in MySQL. But yes you can [simulate one](http://www.fromdual.com/mysql-materialized-views) by defining a new table, and using triggers to make sure it's accurately populated. – Vince Bowdren Apr 12 '13 at 07:24