Would like help figuring out a query/if MySQL can/is supposed to do this

[Keleth]

I've been posting a lotta questions recently it seems... probably because its the first time I'm engrossed in a SQL complicated project. Hope its not an issue.

So I'm stuck trying to figure out how to do something if its even possible. Basically, I have a table of permissions (permissionID, type, typeID, to, toID, read, write, moderate). Type can be general, user, group, or role. typeID points to the ID of the type in question (userID, groupID, roleID), which are foreign keys. I have two other tables groupMemberships (groupID, userID), and roleMemberships (roleID, userID). I also have a table of forums.

Basically what I'm trying to do is select all the forums and figure out what permissions a user has to each forum. There is no hierarchy at the moment, so if a user gets read from general, and write from group, they should get read and write. I guess where I'm stuck is I'm obcessing about proper methodology and efficiency.

I've gotten as far as to select all permissions using joins (to check which roles/groups a user was in then what permissions the user had in those groups/roles). It returns a bunch of rows with permissions. I'd like to get the max for each set. I thought to use the MAX function, but how do I get the max among columns from different tables? Can I actually do MAX(A.column, B.column,...)? After this, I'd need to join it to the forums, to see what permissions each forum has. Should I be doing a bunch of separate queries then using PHP to do the computation? Someone suggested using a temporary table, but another friend said that's not a good idea. I'm pretty lost.

[Old Pedant]

Well, you have a problem in the design, to start with.

typeID points to the ID of the type in question (userID, groupID, roleID), which are foreign keys.
That's not proper DB design, using a single field as a foreign key to multiple tables.

You'd be better off simply using 3 separate fields, where a NULL in the field means "not this one" (which means you could probably then get rid of your TYPE field, but might be worth keeping).

Having said all that...

I really don't follow how all this stuff is related. Would be helpful, I think, to see some sample data.

Should I be doing a bunch of separate queries then using PHP to do the computation?
Probably not. Can't guarantee the answer is "no", but it's likely.
Someone suggested using a temporary table, but another friend said that's not a good idea.
Creating a temp table each time you need to hit a forum???? <shudder>I should hope to say that's not a good idea!</shudder>

I think we need more info to understand exactly what is going on.

[Keleth]

Ok, I can do that (3 sep fields). I actually used one just because I was thinking, why have 3 when I can have 1? But I see your point. I would need 4 however, a binary "general" column, for general permissions.

Um... as I'm not sure how to get my database to out put those pretty little text tables I see in examples, how this isn't too hard on the eyes.

Example data
Forums:

forumID | title | categoryID | parentID
1 | Announcements | 0 | 0
2 | Game 1 | 0 | 0
3 | Game 2 | 0 | 0


permissions (slight update as I realized I only need to give permissions to forums, not individual threads):

permissionID | general | userID | groupID | roleID | forumID | read | write | moderate
1 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0
2 | 0 | 1 | 0 | 0 | 1 | 1 | 1 | 1
3 | 0 | 0 | 1 | 0 | 2 | 1 | 1 | 0
4 | 0 | 0 | 2 | 0 | 3 | 1 | 0 | 0


groupMembership:

groupID | userID
1 | 1
2 | 2


So to explain the data, 3 forums exist. Everyone should be able to read or write in Announcements. User 1 should also be able to moderate Announcements. User 1 also belongs to Group 1, and User 2 belongs to Group 2. User 1 should be able to read and write in Game 1, and User 2 should only be able to read Game 2.

So if User 1 was logged in, he should only see Announcements and Game 1 (as he has no permission entries granting him read to Game 2). And I'd like the data that he can write to both, and moderate 1.

Ideally, get something back like
forumID | title | categoryID | parentID | read | write | moderate

Eventually, I'd like to actually be able to setup priorities (where a column would now be 1, 0, or -1 (or NULL), 1 being granted, 0, being revoked, the third being inherit permission), but I figure I should start easy *cough* then get harder.

EDIT: Thanks, formatted so you don't go blind tryin to read it.

[Old Pedant]

I use [ code ] ... [ /code ] tags to get my tables to line up.

That forces the font to Courier and also preserves the spaces.

I'll look at this in a bit...gotta ruminate on it first.

[Old Pedant]

Okay, finally realized the permissionid is just an autonumber field with no use except when you need to edit one of those records.

So I would have put forumid as first field after it, as that's the first thing you need to match on when checking access to a forum.

But never mind. That's just how I think. Not necessary at all.

Questions:
(a) Can a userid appear in multiple groupid's?? If so, then does that user get the LEAST restrictive permissions for all groups he is in or the MOST restrictive? (I would assume "least", but you never know...)

(b) If a userid has more restrictive permissions than the group (or groups?) he is in, do those permissions prevail?
Example:
permissionID | general | userID | groupID | roleID | forumID | read | write | moderate
1 | 1 | 0 | 0 | 0 | 1 | 1 | 1 | 0
2 | 0 | 1 | 0 | 0 | 1 | 1 | 0 | 0
3 | 0 | 0 | 1 | 0 | 2 | 1 | 1 | 0
4 | 0 | 0 | 2 | 0 | 3 | 1 | 0 | 0
5 | 0 | 0 | 1 | 0 | 1 | 1 | 1 | 1

row 5 says that all members of groupid 1 have complete permissions in forum 1.
But row 2 says that user1 (who is in group 1) can only read in forum 1.
What can user1 really do in forum 1??

[Keleth]

While I haven't implemented this yet, what I'd like to do in the end is have the following hierarchy, from most important to least: user -> roles -> groups -> general. Thus, in the example, as row 2 gives the user all rights, they would get all rights. However, that feels overly complex, and unless it might be easier to hit it off the go, I figured it makes sense to do it later. Of course, I didn't think I could do something like that with SQL, and figured I'd need PHP. I would also need to change the table structure, because I'd like to do it how forums like vBulletin or phpBB do it: a permission can either be allowed, disallowed, or inherit, in which case they would gain the rights of the hierarchy under them.

I apologize for the confusion over permissionID. As you said, its just in case I need to refer to it.

As to your other question: yes, a userID can appear associated to multiple groupIDs (a user can be in multiple groups), however, a user can only have 1 role at a time. And yes, the user would get the least restrictive rights.

Thank you for all your assistance up till now. Heh, in the last few days of askin questions here (and you answering), I've learned a lot :)

[Old Pedant]

Well, might as well design it to have all you want.

So is there an easy way to do allow/prohibit/inherit? Esp. just using numbers in such a way that we can do math, instead of needing to use IF conditions?

Hmmm...

Yes, I think so.

Assuming we don't need to support both groups and uber-groups, so that the permissions are truly just 2 levels: groups and users.

For a group:
-- permission denied gets a value of -1
-- permission allowed gets a value of +1
-- permission not specified gets a value of 0 (but I don't think you'd ever use this??)

For a user:
-- permission denied gets a value of -2
-- permission allowed gets a value of +2
-- permission inherited gets a value of 0

So now it's easy: A user's permission value is the *SUM* of the value for his specific userid and the MAX() value for all the groups he is a member of.

Example:
userid :: groupid
1 :: 100
1 :: 200
2 :: 100

forumid :: groupid :: userid :: write
1731 :: 100 :: 0 :: -1
1731 :: 200 :: 0 :: +1
1731 :: 0 :: 1 :: 0
1731 :: 0 :: 2 :: +2


So userid 1 gets a total score for forumid 1731 of +1 because he is +1 for the group 200 (max of write value) and 0 (inherit): +1 + 0 is +1.

So userid 2 gets a total score for forumid 1731 of +1 because he is -1 for the group 100 (max of write value) and +2 on his own: -1 + +2 is +1.

So both have write permission in forum 1731.

Does that seem to work??

[Old Pedant]

A better example:

userid :: groupid
1 :: 100
1 :: 200
2 :: 100

forumid :: groupid :: userid :: write
1731 :: 100 :: 0 :: +1
1731 :: 200 :: 0 :: -1
1731 :: 0 :: 1 :: 0
1731 :: 0 :: 2 :: -2

So let's try:

SELECT (PG.writePer + P.write ) AS writePermission
FROM usergroups AS UG,
permissions AS P,
( SELECT groupid, MAX(write) AS writePer
FROM permissions
WHERE forumid = @forumid
GROUP BY groupid ) AS PG
WHERE UG.userid = @groupid
AND P.groupid = UG.groupid
AND UG.userid = P.userid
AND P.userid = @userid
AND P.forumid = @forumid


You can see that when @userid is 1, the WHERE clause there ends up working on the first 3 rows in the permissions table, as shown above. And the MAX(write) AS writePer give +1 whereas P.write will be 0, so the sum is +1 and the user has permission.

For @userid 2, the first and last rows of the permissions apply. Here, the MAX( ) value is +1, but the P.write value is -2. So the sum is -1 and permission is denied.

Just realized that needs to be a LEFT JOIN, in case the user doesn't have any permissions outside the group. But that's minor.

Oh...and we have to insist that *EVERY* group has a record for every forum.

Though I suppose we could fix that, too, by having a "catchall" row for groups that don't have an assignment for a given forum. Makes the query more complex. I'd recommend against that.

[Keleth]

That's brilliant! And I can expand that to general and roles by giving general a value of 1, groups 2, roles 4, and user 8 (needs to be powers of 2 in case every setting underneath is set negative).

Thanks very much, I donno how long it would have taken me to think of that!

Ok, I guess the last part that I'm having trouble putting together, and I'll understand if you don't wanna answer (it feels like I'm askin you to outline my entire project :p), but I guess the problem I started with was how to attach/join that with the forums table.

Psudocode wise: select appropriate cols from forums and (select sum of permissions from permissions and groupmembership and role membership where general = 1 or userid = (the users id) or (groupID = the groups the member is a user of) or (roleID = the role the user has)

And if I do it that way, if the user isn't part of a group or role, the or will just be 0 and ignored?

[Old Pedant]

I'm not sure you would want to join it to the forums table.

Reason: I think you'd be better off making that check when the person first clicks on the link to the given forum. Then you set a session variable keyed to that forumid that tells what permission the user has.

This means you don't have to make the ugly query more than once per forum visited, per user.

And if you get an edit request (for example) all you have to do is check the session variable to see if "W" is allowed. (I'm assuming the session variable would be something like "RWM" for Read/Write/Moderate.)

[Old Pedant]

Kind of like these forums. You click on MySQL and it takes you to
http://www.codingforums.com/forumdisplay.php?f=7

So in the "forumdisplay.php" page, you check to see if the session id for forum 7 is alread there. If so, use it. If not, use the query to generate it. And then all other actions in the forum derive from that.

At most, a user will end up consuming one session variable for each forum. That's surely not excessive on modern servers.

[Keleth]

Makes sense. But the issue becomes when, for example, the person is looking at a list of all the forums (again, I'm basing the idea off stuff like vBulletin and phpBB). If you don't have read access to a forum, I'd rather the user not be able to see the forum at all. But you make a very good point about the ugly query, I could set a short term session var that updates every x minutes with the rights in it.

[Old Pedant]

HUH? Why does the session variable ever need to update???

Unless you are changing the permissions table behind the scenes at the same time the user is accessing the forums.

How many forums do you expect to have???

If no more than a few dozen, why not get *all* the permissions for *all* the forums when the user signs in, store them all in a single session variable, and you are done with the DB permissions for as long as the user stays active. (And if he goes inactive, the session times out and he has to sign in again.)

Especially if your forum ids are sequential, it would be trivial. Your session permissions would be a single string, maybe something like this (showing 6 forums):

:RWM::R:R:R:RW:

Which simply says ReadWriteModerate in forum 1, no permissions in forum 2, Read in 3 and 4 and 5, and ReadWrite in 6.

Presto.

[Keleth]

I see what you're sayin, makes sense, thanks!

[Keleth]

Alright, so I put in the code and its operating a bit strange, donno why...

SELECT permissions.forumID, SUM(permissions.`read`) AS "read", SUM(permissions.`write`) AS "write", SUM(permissions.moderate) AS moderate
FROM forums_permissions AS permissions, forums_groupMemberships AS groupMemberships, forums_roleMemberships AS roleMemberships
WHERE permissions.general = 1 ||
permissions.userID = 1 ||
(groupMemberships.userID = 1 && groupMemberships.groupID = permissions.groupID) ||
(roleMemberships.userID = 1 && roleMemberships.roleID = permissions.roleID)
GROUP BY permissions.forumID

When forums_roleMemberships is empty, it returns an empty row, even if there are matching userIDs/groupIDs (if I remove " || (roleMemberships.userID = 1 && roleMemberships.roleID = permissions.roleID)", I get a valid return, as expected). I don't understand it, because even if the roleID doesn't match the user (ie, roleMemberships.userID = 1 returns 0 rows), as long as there is one entry in forums_roleMembership, it works. Probably means that if forums_groupMemberships was empty, it would fail there as well.

[Old Pedant]

When you compare a NULL to a non-null value, the result is NULL! *NOT* true or false!

So I wonder if you could fix it thus:

(roleMemberships.userID = 1 && IFNULL(roleMemberships.roleID,-1) = permissions.roleID)

Ehhh...that feels like it should be unnecessary, given that you are using &&.

Ugh...

[Keleth]

I guess I would need to do that with roleMemberships.userID = 1 as well, as if there are no rows, roleMemberships.userID would be NULL too?

EDIT:
Wait, I tried this out, and I'm not sure that's the case. In this case, its not NULL, but an empty set. Or does mySQL treat the empty set as NULL? (it didn't work)