How should I design access rights for folders and subfolders?

Hi!

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

My apologies for not explaining correctly.

I would like to design a database for a folder tree.

I store the folder information in a database table. The table looks something like this:

folder table:

id, folderName, parentFolderId
1, rootFolder, 0
2, folderA, 1
3, folderB, 1
4, folderC, 3

I have another table called user table.

User table:

id, username, rights
1, John, admin
2, Mary, normal_user
3, Peter, normal_user

I have a third table called user_to_folder table which provides many-to-many relationship between the user table and the folder table. It gives the access rights to each sub-folders.

user_to_folder table:

fkFolderId, fkUserId
1, 2
1, 3
2, 3
3, 2,
4, 2

In this way, Mary has the access rights to folders rootFolder and folderB and folderC. She has no access rights to folderA. Peter has the access rights to rootFolder and folderA. He has no access rights to folderB and folderC.

Condition: The admin, John have access rights to all folders.

What would my SQL statement be?

SELECT * FROM folder
INNER JOIN user_to_folder
ON folder.id = fkFolderId
INNER JOIN user
ON user.id = fkFolderId
WHERE user.id = 2
OR user.rights = admin
AND folder.parentId = 1

Please help. Thanks.

what exactly are you trying to return with the query? all folders for a given user?

Folders that are allowed to be given access to the user as specified by user_to_folder table.

for all users? or for a specific user?

Specific user. But the administrator will have access to all folders. Thanks

SELECT folder.folderName
  FROM user
INNER
  JOIN user_to_folder
    ON user_to_folder.fkUserId = user.id
INNER
  JOIN folder
    ON folder.id = user_to_folder.fkFolderId
 WHERE user.id = 2
UNION
SELECT folder.folderName
  FROM user
CROSS
  JOIN folder
 WHERE user.id = 2
   AND user.rights = 'admin'

Hi. Thanks for your answer. But when I put in order by folder.name, it says “unknown folder.name in order clause”.

Please help. Thanks.

that’s because your folder table doesn’t actually have a column called “name”

please check post #2 for the actual column name

Hi!

I have renamed it “ORDER BY folder.folderName ASC” but it is still the same error message.

Please help. Thanks.

does the query run without the ORDER BY clause?

yes.

Additional, is there a way specify to return records that correspond to parentFolderId = 1?

I put “WHERE user.rights = ‘admin’ AND folder.parentFolderId = 1”, it says “unknow column parentFolderId in where clause”.

Please help. Thanks.

please show the exact query that you ran that worked

as for the parentFolderId=1 restriction, which of the two SELECTs should get it?

Hi!

SELECT folder.folderName
FROM user
INNER JOIN user_to_folder
ON user_to_folder.fkUserId = user.id
INNER JOIN folder
ON folder.id = user_to_folder.fkFolderId
WHERE user.id = 3 AND parentFolderId = 1
UNION
SELECT folder.folderName
FROM user
CROSS JOIN folder
WHERE user.id = 3
AND user.rights = ‘admin’ AND parentFolderId = 1

I need to have ORDER BY clause. Please help. Thanks.

SELECT folder.folderName
  FROM user
INNER 
  JOIN user_to_folder
    ON user_to_folder.fkUserId = user.id
INNER 
  JOIN folder
    ON folder.id = user_to_folder.fkFolderId
 WHERE user.id = 3 
   AND folder.parentFolderId = 1
UNION
SELECT folder.folderName
  FROM user
CROSS 
  JOIN folder
 WHERE user.id = 3
   AND user.rights = 'admin' 
   AND folder.parentFolderId = 1
[B]ORDER 
    BY folderName[/B]

in a UNION query, you cannot use a table prefix on the column name in the ORDER BY clause

Hi!

Thanks for your script.

Can you explain it?

I understand the first select statement is for normal users. Second SELECT is for users with admin rights.

Why do you use cross join for the second SELECT?
Can I use CROSS JOIN in combination with an INNER JOIN if my user.rights is used in combination with other tables?

Thanks.

because i assumed that for admins, there was no need to register all their folders in the many-to-many table (there would be an entry for every folder, right?)

so the CROSS JOIN simply matches the specified admin with all folders that meet the parent=1 criterion

i’m not sure, i’d have to see the exact scenario

Hi.

For example, select folder.folderName from user cross join folder inner join rightsTable on user.rightsid = rightsTable.id

RightsTable will have id, roles :1, admin. id, roles :2 , normal user

Instead of putting the user rights in the user table.

Is that possible? Thanks

yeah, but now you have to load up the rights table with all folders for each admin