SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Whistler, Canada
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    design of a multilevel permission system

    Hi,

    For a project I'm working on I need to create a new permission system for a folder hierarchy, and I'm not quite sure what the best way is to do this. I have a few ideas and would like to hear your thoughts on them. Or perhaps some new ideas :-)
    I'll explain the scenario for you:
    The application works with a folder structure very much like a filesystem: folders can be created inside other folders without restrictions. The structure is MySQL based. One db table with a unique folder id and a parent id. An example would be:
    - Folder 1
    -- Folder 1.1
    -- Folder 1.2
    -- Folder 1.3
    --- Folder 1.3.1
    ---- Folder 1.3.1.1
    - Folder 2
    -- Folder 2.1
    --- Folder 2.1.1
    --- Folder 2.1.2
    - Folder 3

    At the moment we only have permissions on the top level folders. The new system should allow permissions for all levels. We work with user groups, so a user can be member of multiple groups, and the permissions are assigned to the group. So, what's a proper way to do this with MySQL/PHP? My thoughts are:

    Option 1a
    When the administrator selects the folders for a group, I store the lowest level folder ids that have been selected. For instance, the admin selects Folder 1, Folder 1.2, Folder 1.3 and Folder 1.3.1.1. In this case I would only store the ids for Folder 1.2 and Folder 1.3.1.1, because I can use their parent_id to look up the folders above.
    Then the user logs in and selects a folder. Because I don't have all folder ids stored in the database, I can't "simply" join the folder table with the permissions table. I'd have to come up with pretty complex joins to get that done (if it can be done at all?). Personally I'm a fan of doing most of the "work" in PHP rather than MySQL because it's so much easier to scale up the web servers instead of the db servers, but could this be done in pure MySQL and would it be worth consideration? In PHP I'd first have to retrieve all the group's lowest-level folders, then loop through it and create a list of all folder ids (this time including parent ids) that the user is allowed to view. Then I can check whether the selected folder id is in the list of allowed folder ids.


    Option 1b
    Because I have to query the db for every folder to get it's parent, this could potentially cause a lot of queries when choosing for option 1a. To reduce this I could retrieve them once and store them in the user's session. I would have to create some kind of flag to update the user's session if an administrator changes the permissions while the user is logged in.


    Option 2.
    When the admin selects the folders for the group, I store all folder ids (so not just for Folder 1.2 and Folder 1.3.1.1, but also for Folder 1, Folder 1.3, Folder 1.3.1). Now I can perform a select on my folder table with the selected folder id, joining the group and permissions table on folder id and group id, and I'm done.
    A good thing about this is that I need few queries to get a result and no PHP loops, on the other hand I'm storing same data multiple times in the db. Another thing to keep in mind is what happens if a user moves a folder? For example, the user moves Folder 1.3 to become a child of Folder 2. With this option I would have to add my Folder 2 to my permissions table, while with Option 1 I wouldn't have to do anything.

    Option 3.
    Store the folder ids the same way as Option 1a, but also create a sort of cache table that will look like the table in option 2. This cache table would be recreated when the permissions are updated. This is very much like 1b, except that what would be stored in the session is now stored in the cache table and I would still query the db.


    Personally I'm thinking of going for option 1b with the PHP loop. But I'm just not sure if there are other/better ways to do this. Again, any thoughts on this matter are most welcome. Thanks in advance for taking the time to read this.

    Cheers,
    Pep

  2. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    So obvious question.
    Do permissions cascade? (IE: Given access to Folder 1.3, does the user automatically gain access to 1.3.1, 1.3.1.1, etc)

  3. #3
    Passionate Web Developer Egyptechno's Avatar
    Join Date
    Jan 2004
    Location
    Dubai
    Posts
    259
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would go with Ooption 2

    Once an administrator selects folders ( whatever relations to eachother, parents, same level, sons .. ) I would have prepared a relational table ( folder id - user id ) and I add all folders ids to the specific userid or usergroupid.

    However this way would result alot of rows especially with many folders and users, but it still so simple and high performance as the table contains 2 columns and already foreign keys.


    Except if the answer on the above question is YES ! then you would just need to save the root folder id for the specific user or group, and you will need to implement a function called " getRootFolderID " to check for the root folder id against the logged in user .. and so on !
    I adore PHP

  4. #4
    SitePoint Enthusiast
    Join Date
    Mar 2002
    Location
    Whistler, Canada
    Posts
    51
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    So obvious question.
    Do permissions cascade? (IE: Given access to Folder 1.3, does the user automatically gain access to 1.3.1, 1.3.1.1, etc)
    No the permissions do not cascade. A user can have access to Folder 1.3 without access to it's subfolders.


    Quote Originally Posted by Egyptechno View Post
    I would go with Ooption 2

    Once an administrator selects folders ( whatever relations to eachother, parents, same level, sons .. ) I would have prepared a relational table ( folder id - user id ) and I add all folders ids to the specific userid or usergroupid.

    However this way would result alot of rows especially with many folders and users, but it still so simple and high performance as the table contains 2 columns and already foreign keys.


    Except if the answer on the above question is YES ! then you would just need to save the root folder id for the specific user or group, and you will need to implement a function called " getRootFolderID " to check for the root folder id against the logged in user .. and so on !
    That does make sense. It would indeed be a simple and high performance search and it's easy to index the table. Thanks. I'll think about it.

  5. #5
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Well, technically Option 2 is a Reverse Cascade (since you have to be able to see the tree above it)

    Be careful how you store your structure in the database, or you'll have a hell of a time trying to navigate back to the Root.


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
  •