SitePoint Sponsor

User Tag List

Results 1 to 22 of 22
  1. #1
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  3. #3
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Somebody, please?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by firblazer View Post
    What would my SQL statement be?
    what exactly are you trying to return with the query? all folders for a given user?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Folders that are allowed to be given access to the user as specified by user_to_folder table.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by firblazer View Post
    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?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Specific user. But the administrator will have access to all folders. Thanks

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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'
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by firblazer View Post
    "unknown folder.name in order clause".
    that's because your folder table doesn't actually have a column called "name"

    please check post #2 for the actual column name
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi!

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

    Please help. Thanks.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    does the query run without the ORDER BY clause?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes.

  14. #14
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by firblazer View Post
    Additional, is there a way specify to return records that correspond to parentFolderId = 1?
    please show the exact query that you ran that worked

    as for the parentFolderId=1 restriction, which of the two SELECTs should get it?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  17. #17
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    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
    ORDER 
        BY folderName
    in a UNION query, you cannot use a table prefix on the column name in the ORDER BY clause
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  18. #18
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  19. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by firblazer View Post
    Why do you use cross join for the second SELECT?
    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


    Quote Originally Posted by firblazer View Post
    Can I use CROSS JOIN in combination with an INNER JOIN if my user.rights is used in combination with other tables?
    i'm not sure, i'd have to see the exact scenario
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  21. #21
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,017
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by firblazer View Post
    Is that possible?
    yeah, but now you have to load up the rights table with all folders for each admin
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  22. #22
    SitePoint Zealot
    Join Date
    Jul 2008
    Posts
    154
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok. Thanks a lot


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •