How should I design access rights for folders and subfolders?
I would like to design a database with folders and sub-folders and all access rights given to admin and only certain folders are accessible to certain users.
I have a folder table with a id and parentId and name of folder. ParentId point to the ID of the parent subfolder.
I also have a user table with admin and normal users with IDs.
I have a user_to_folder table to allow multiple to multiple relationship.
Question is how should I allow admin for all folders but restrict certain users to certain folders. Do I keep a list of userIDs who are allowed to certain folders or should I keep a list who are not allowed.
My SQL would be:
SELECT * FROM folderTable
LEFT JOIN user_to_folder ON user.id = user_to_folder.fkuerId
WHERE parentID = 1 OR user.right = 'admin' and (isnull(user_to_folder.fkUserId)) or user.id = 45
But I am getting folders which are not supposed to be accessible to user 45.
Is my design correct?
Your comments please. Thanks