Row level user permissions, help with design

Question

Hi,

Say I am creating a forums application, I understand how to design a forum level permission system with Groups.

i.e. you create a forum to group mapping, and assign users to a group to give them access to a particular forum.

How can I refine the permissions to allow for row level permissions (or in forum terms, post level).

Answer 1

You would do so in a similar manner as you've already described. It'll require a few more joins. Let's say you have a structure like so (I've intentionally kept off the constraints to make it generic and reduce the amount of code):

CREATE TABLE ForumPost (
PostID int,
ForumID int,
PostText varchar(255)
);

CREATE TABLE ForumUser (
ForumUserID int,
ForumUserName varchar(255),
NumofPosts int
);

CREATE TABLE ForumGroups (
ForumGroupID int,
ForumGroupName varchar(255)
)

CREATE TABLE ForumGroupMembership (
ForumUserID int,
ForumGroupID int
)

CREATE TABLE ForumPermissions (
ForumID int,
ForumGroupID int,
MinPosts int
)

Then you could do several joins to ensure you restrict the content accordingly:

SELECT FPost.PostID, FPost.ForumID, FPost.PostText
FROM ForumPost FPost
  JOIN ForumPermissions FPerm
    ON FPost.ForumID = FPerm.ForumID
  JOIN ForumGroupMembership FGM
    ON FPerm.ForumGroupID = FGM.ForumGroupID
  JOIN ForumUser FUser
    ON FUser.ForumUserID = FGM.ForumUserID
WHERE FUser.NumOfPosts >= FPerm.MinPosts
  AND FPost.PostID = <Some Number>