Search options

I realise that I'm resurrecting a dead & done topic, but I thought this was worth pointing out.

First, a preface. I run several forums, all of which were using phpBB, and I was quite sick of it, so I changed all but one to punBB. The one that I didn't change was due to it having several (three) user groups, each of which had its own private forum. Now, I could have set up seven different user groups corresponding to the permissions and everything, but that seems like a bit of a pain to manage, and is really just a fudge. What punBB has at the moment isn't really user groups as much as it is flexible user 'classes'.

Anyway, there's no reason why a simple permissions system couldn't be done primarily in SQL. It just has to be designed carefully.

We already have a table for holding users and for forums. What needs to be done is to tie them together via some more flexible relations. The four current user groups could be considered user types or user classes. So, if we consider a new usergroup table:

table 'usergroups':
- INT group_id
- VARCHAR group_name

Relating the groups to the users is then really easy, just using a simple table for relations:

table 'usergroup_users':
- INT group_id
- INT user_id

This adds a great deal of flexibility, in that a user can be part of any group, and they can be in multiple groups at once. Now, we need to represent which forums each group is allowed to access. This is basically relating groups to forums, and then giving a value to what they can do. For the permission itself, we can use just a number from 0 - 3, where 0 = no permission (can't view), 1 = read permission, 2 = read and post replies, 3 = read, post replies, post topics. I suppose if you wanted you could take it an extra step and add in edit permissions as a separate value in this table too, or even add moderation permissions to allow a usergroup to moderate certain forums, but that's getting beyond the scope of this example, which is pretty simplified. Here's the table:

table 'usergroup_perms':
- INT group_id
- INT forum_id
- TINYINT permission

So that's basically it for our table structure. The most pertinent question now is how we go about doing as much work in the database server as possible to tie this all together. Now, I'm running under the assumption that we're using a logical permissions system, in that a user's permission for a forum is going to be the highest permission of any group he or she belongs to. Setting this up differently would mean it doesn't work according to the way that the average user would expect it to. As an example, let's consider two groups. One group is 'house'. These are the people who have access to a house. Let's say for example that there's four people in this group. Also consider another group, car. These are the people who are allowed to drive a car. The two groups are not related, but two of the users in 'house' are also in 'car', but not every user in car is able to access house, and vice versa. The logical thing to say about the two people who are in both groups is that they can access both house and car. They get the highest level of permission from each group. Having a setup where owning a house meant you weren't allowed to drive, for example, is not the way permissions systems work in the real world.

With that out of the way, quite simply we need to somehow get a list of every forum for which there is at least one group that a certain user belongs to that has permission level > 0.

SELECT forums.* , max( usergroup_perms.permission ) AS perm
FROM `usergroup_users`
LEFT JOIN `usergroup_perms` ON usergroup_perms.group_id = usergroup_users.group_id
LEFT JOIN `forums` ON forums.forum_id = usergroup_perms.forum_id
WHERE usergroup_users.user_id = '<user_id>'
AND permission >0
GROUP BY forums.forum_id

That query should achieve the desired result, and it's not overly complex. This is the most complex query, because this will show or hide the forums based on whether a user can see them. Once they're inside a forum, checking if the user is allowed to view the threads is basically the same query, but with an added clause in WHERE, since we know which forum_id we're looking at. When a user asks to read a thread, we check for permission > 0, when they ask to post a reply, we check for permission > 1, and when they ask to post a new thread, we check for permission > 2. Of course, in the case of replies, from my memory of the punBB code we only know which thread we're replying to, so there would need to be an extra join to go from thread_id to forum_id. Basically though, the only major thing that would change would be the SQL statements themselves, and perhaps the addition of a function to check the user's permission before performing a task (though I think that code is already there, it would probably just need modification).

As far as the administration side of things goes, you would need to maintain an exhaustive set of permissions for each group. Administration of this isn't actually as hard as it looks at first. Whenever a new group is added, you insert a new permissions table row for each forum, probably with each permission set to 3. When you add a new forum, you need to add a new row to the permissions table for each group, again probably with permissions set to 3. You would also need to make sure that there is always a 'Members' and a 'Guests' usergroup. Whenever a new forum is added, the Members group should get permission 3 and the Guests permission 1. Also, whenever a user registers, they need to be added to the Members usergroup.

Maintaining the permissions is easy enough to do, in essence it would probably be exactly the same as the current setup.

I was actually considering coding all this up myself as a mod, but I unfortunately don't have enough spare time - doing my honours year in Computer Science so I'm pretty busy. Also, while I've tested the query I posted here in MySQL and it appears to give the desired results, I'm not exactly an SQL wiz so it's possible I made a flub in there somewhere. I've also got no idea if it will work on databases other than MySQL (though I don't see any reason why it shouldn't). This is just dealing with forum access permissions, but there's no reason why other permissions values couldn't be added to the table too. For example, you might have a second value which controls whether a group is allowed to search certain forums, or whether they can edit their posts, or if they have moderator privileges as mentioned earlier... the assumption is that permissions are attached to the forums, of course. Either way, this is really all just speculative stuff since I don't have time to code it, but I thought it was worth posting here since someone else might be interested in exploring it further.

Or I could have missed the bus totally and someone's already done all this.