leapster

@xyph - you make it sound so simple I want to say that what we are using is similar to the table structure you are explaining. I have attached my excel worksheet (as a pdf) I was keeping all that table structure data in so you can take a peek. I really appreciate the help and support!!! I actually just found out the deadline to fix this is due by next Tuesday so my time crunch just got even crunchier? lol Yes if you feel that adding this thread to the SQL forum would gather more help on the topic that is okay with me. Is it possible to leave it here as well I wouldn't want to lose support from you guys already being so helpful. Also I'd like to understand better how to get out of the query/loop inside a query/loop type scenarios. I do realize that we are going multiple levels deep and that does seem to be the major burden on the system. I honestly have no clue how to handle that better... could one of you or anyone please elaborate a bit on how to mend that situation? (I think that would possibly relieve enough time delay in the system for the company to keep the project. They understand it is in development and will be okay with a few minor snags but the 1-2min delay is just insanely long) 18717_.pdf

@PFMaBiSmAd Ah. Sorry about that I c/p the code I was trying to use to pinpoint the problem. When I remove the @ symbols it still has a very significant time delay they didn't seem to improve the performance much. That makes sense and my next question would be should I handle checking the user's permission level upon login and save it to a session id? or would that just create future issues for example if one user was logged in and so was an admin and they granted that user access to a specific folder, the user would have to then log out and back in to re-save a new session id with that permission now available? @xyph Yes technically it could be a potential for each user to have unique permissions per file. I would like to find a way to handle the user permission as a group format such as all managers have X Y Z access. Then certain managers might be excluded from some files in Folders A, B, C and say doesn't have access to Folder D but needs to access 2 files from folder D. Yes I'd like to be able to edit the bulk permissions per group such as adding a full drawer->folder->sub-folder However again for the exclusion/exception issue I'd still need to be able to say All Publishers can have access to drawer News -> folders april and may (but exclude them from others months such as march and jan) or conversely they have no access overall but have an exception to just add their news to the month of October. (hoping I didn't make that seem overly complicated) - The project was flowing very nicely until they needed such a detailed permissions system that in a way needs to be able to handle almost any case-scenario of giving any user access to any possible file and/or folder while still being able to control the rest of the contents they can view/access within those drawers/folders.

I see a good handful of views and no replies. Did I not explain myself well enough or is the question overly complicated and should be shortened?

Hi all. Long time reader / lurker; first time poster. I'll give a little background on my situation and any help possible is extremely appreciated! A friend of mine has been trying to code out a web based file management system with multiple users, permissions, and levels of access (folders, files, sub-folders, drawers) where all the data will be stored. (He is designing this for moderately sized company - They plan to have around 60 users and so far have around 6.5gig of data that is managed through this app) The company has a parent company who would possibly be interested in this piece as well - so it has the potential to grow to hundreds or a thousand users. He was having a lot of speed issues on recalling the data. I came in and have been trying to help to the best of my knowledge. So far at the super admin level (no permission checks - because they have full access) we have eliminated almost any hang-up of speed and all data is returned in less than 1sec. However as I go deeper into the user permissions Super Admin > Admin >Manager > Publisher It instantly begins to compound the time it takes to run the checks on user permissions and return the data. At some points it takes 50s - 2.5mins which begins to make the software unusable at that point. I'll paste in the coding I believe might be the hang-up if you need any more pieces or have questions please let me know. This is our check_drawer_access.php file: $drawers = NULL; $i = 1; if ( $title == 'FileCab: Cabinet' || $title == 'FileCab: Upload' ) { $query = "SELECT cabinetid, cname, cdesc, cseclvl, cseclvl_default, cactive FROM filecab_cabinet WHERE clayer=1 AND cactive=1 ORDER BY cname"; } else { $query = "SELECT cabinetid, cname, cdesc, cseclvl, cseclvl_default, cactive FROM filecab_cabinet WHERE clayer=1 ORDER BY cactive, cname"; } $result = @mysql_query( $query ); // RUN THE QUERY while ( $row = @mysql_fetch_array( $result, MYSQL_ASSOC ) ) { if ( $useclvl == 1 || $useclvl == 2 && $row['cseclvl'] >= $useclvl ) { # ADMIN SEE EVERYTHING $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => FALSE, 'E' => 1, 'M' => 1, 'D' => 1 ); $i++; } else { # MORE CHECKING $cabinetid = $row['cabinetid']; $query2 = "SELECT ulid FROM filecab_user_links WHERE cabinetid=$cabinetid AND ulexc=3 AND ulactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY $allow_only = @mysql_num_rows( $result2 ); if ( $allow_only != 0 ) { # ALLOW ONLY $query2 = "SELECT uledit, ulmove, uldelete FROM filecab_user_links WHERE userid=$uid AND cabinetid=$cabinetid AND ulexc=3 AND ulactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY $allow_only2 = @mysql_num_rows( $result2 ); $allow_only2_G = 0; $query2 = "SELECT groupid FROM users_groups_links WHERE userid=$uid AND ugactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY while ( $row2 = @mysql_fetch_array( $result2, MYSQL_ASSOC ) ) { $groupid = $row2['groupid']; $query3 = "SELECT uledit, ulmove, uldelete FROM filecab_user_links WHERE groupid=$groupid AND cabinetid=$cabinetid AND ulexc=3 AND ulactive=1"; $result3 = @mysql_query( $query3 ); // RUN THE QUERY $allow_only2_G = $allow_only2_G + @mysql_num_rows( $result3 ); } if ( $allow_only2 != 0 ) { # ALLOW ONLY: YES $query2 = "SELECT uledit, ulmove, uldelete FROM filecab_user_links WHERE userid=$uid AND cabinetid=$cabinetid AND ulexc=3 AND ulactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY $row2 = @mysql_fetch_array( $result2, MYSQL_ASSOC ); $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => TRUE, 'E' => $row2['uledit'], 'M' => $row2['ulmove'], 'D' => $row2['uldelete'] ); $i++; } else if ( $allow_only2_G != 0 ) { # ALLOW ONLY GROUP: YES $query2 = "SELECT groupid FROM users_groups_links WHERE userid=$uid AND ugactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY while ( $row2 = @mysql_fetch_array( $result2, MYSQL_ASSOC ) ) { $groupid = $row2['groupid']; $query3 = "SELECT uledit, ulmove, uldelete FROM filecab_user_links WHERE groupid=$groupid AND cabinetid=$cabinetid AND ulexc=3 AND ulactive=1"; $result3 = @mysql_query( $query3 ); // RUN THE QUERY $row3 = @mysql_fetch_array( $result3, MYSQL_ASSOC ); if ( $row3 ) { $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => TRUE, 'E' => $row3['uledit'], 'M' => $row3['ulmove'], 'D' => $row3['uldelete'] ); $i++; } } } else { # DO NOTHING # ALLOW ONLY: NO } } else { # MORE CHECKING $query2 = "SELECT ulexc, uledit, ulmove, uldelete FROM filecab_user_links WHERE userid=$uid AND cabinetid=$cabinetid AND ulactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY $row_num2 = @mysql_num_rows( $result2 ); $row2 = @mysql_fetch_array( $result2, MYSQL_ASSOC ); if ( $row_num2 != 0 && $row2['ulexc'] == 1 ) { # EXCEPTION $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => TRUE, 'E' => $row2['uledit'], 'M' => $row2['ulmove'], 'D' => $row2['uldelete'] ); $i++; } else if ( $row_num2 != 0 && $row2['ulexc'] == 2 ) { # EXCLUSION if ( $row2['uledit'] != 1 || $row2['ulmove'] != 1 || $row2['uldelete'] != 1 ) { if ( $row2['uledit'] == 1 ) { $E = 2; } else { $E = 1; } if ( $row2['ulmove'] == 1 ) { $M = 2; } else { $M = 1; } if ( $row2['uldelete'] == 1 ) { $D = 2; } else { $D = 1; } $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => TRUE, 'E' => $E, 'M' => $M, 'D' => $D ); $i++; } } else { # MORE CHECKING $exception_G = 0; $exclusion_G = 0; $query3 = "SELECT groupid FROM users_groups_links WHERE userid=$uid AND ugactive=1"; $result3 = @mysql_query( $query3 ); // RUN THE QUERY while ( $row3 = @mysql_fetch_array( $result3, MYSQL_ASSOC ) ) { $groupid = $row3['groupid']; $query4 = "SELECT ulid FROM filecab_user_links WHERE groupid=$groupid AND cabinetid=$cabinetid AND ulexc=1 AND ulactive=1"; $result4 = @mysql_query( $query4 ); // RUN THE QUERY $exception_G = $exception_G + @mysql_num_rows( $result4 ); $query4 = "SELECT ulid FROM filecab_user_links WHERE groupid=$groupid AND cabinetid=$cabinetid AND ulexc=2 AND ulactive=1"; $result4 = @mysql_query( $query4 ); // RUN THE QUERY $exclusion_G = $exclusion_G + @mysql_num_rows( $result4 ); } if ( $exception_G != 0 ) { # EXCEPTION GROUP $query2 = "SELECT groupid FROM users_groups_links WHERE userid=$uid AND ugactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY while ( $row2 = @mysql_fetch_array( $result2, MYSQL_ASSOC ) ) { $groupid = $row2['groupid']; $query2 = "SELECT uledit, ulmove, uldelete FROM filecab_user_links WHERE groupid=$groupid AND cabinetid=$cabinetid AND ulexc=1 AND ulactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY $row2 = @mysql_fetch_array( $result2, MYSQL_ASSOC ); if ( $row2 ) { $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => TRUE, 'E' => $row2['uledit'], 'M' => $row2['ulmove'], 'D' => $row2['uldelete'] ); $i++; } } } else if ( $exclusion_G != 0 ) { # EXCLUSION GROUP $query2 = "SELECT groupid FROM users_groups_links WHERE userid=$uid AND ugactive=1"; $result2 = @mysql_query( $query2 ); // RUN THE QUERY while ( $row2 = @mysql_fetch_array( $result2, MYSQL_ASSOC ) ) { $groupid = $row2['groupid']; $query3 = "SELECT uledit, ulmove, uldelete FROM filecab_user_links WHERE groupid=$groupid AND cabinetid=$cabinetid AND ulexc=2 AND ulactive=1"; $result3 = @mysql_query( $query3 ); // RUN THE QUERY $row3 = @mysql_fetch_array( $result3, MYSQL_ASSOC ); if ( $row3 ) { if ( $row3['uledit'] != 1 || $row3['ulmove'] != 1 || $row3['uldelete'] != 1 ) { if ( $row3['uledit'] == 1 ) { $E = 2; } else { $E = 1; } if ( $row3['ulmove'] == 1 ) { $M = 2; } else { $M = 1; } if ( $row3['uldelete'] == 1 ) { $D = 2; } else { $D = 1; } $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => TRUE, 'E' => $E, 'M' => $M, 'D' => $D ); $i++; } } } } else if ( $row_num2 != 0 && $row2['ulexc'] == 4 || $row_num2 != 0 && $row2['ulexc'] == 5 ) { # HAS DEFAULT LEVEL ACCESS OR HAS TRICKLE ACCESS $drawers[$i] = array( 'cabinetid' => $row['cabinetid'], 'cname' => $row['cname'], 'cdesc' => $row['cdesc'], 'cseclvl' => $row['cseclvl'], 'cseclvl_default' => $row['cseclvl_default'], 'cactive' => $row['cactive'], 'EEA' => FALSE, 'E' => 3, 'M' => 3, 'D' => 3 ); $i++; } } } } } if ( isset( $loader ) && $loader == TRUE ) { $Dnum = count( $drawers ); } $num = count( $drawers ); This is the file that populates the data to the UI: require_once('scripts/functions.php'); // INCLUDE ALL FUNCTIONS require_once('scripts/library/session.class.php'); // INCLUDE SESSION CLASS include_once('scripts/system_checks/check_session.php'); // CHECK FOR SESSION require_once('db.php'); // CONNECT TO THE DATABASE include_once('scripts/system_checks/check_install_filecab.php'); // CHECK FOR FILECAB INSTALLATION include_once('scripts/system_checks/check_publisher.php'); // CHECK FOR EDITOR STATUS $title = 'FileCab: Drawers'; // SET TITLE $message = NULL; // CREATE AN EMPTY NEW VARIABLE TO DISPLAY ERROR MESSAGES $clayer = 1; if ( isset( $_GET['select'] ) ) { $select = $_GET['select']; // SET USER ID } else if ( isset( $_POST['select'] ) ) { $select = $_POST['select']; // SET USER ID } else { $select = NULL; } include_once('scripts/cabinet/active_nonactive.php'); include_once('scripts/cabinet/new_cabinet_script.php'); include_once('scripts/cabinet/edit_cabinet_script.php'); include_once('scripts/cabinet/add_user_script.php'); include_once('scripts/cabinet/cabinet_link.php'); include_once('scripts/cabinet/user_link.php'); include_once('scripts/cabinet/add_email_notify.php'); include_once('scripts/cabinet/active_nonactive_email_notify.php'); include_once('scripts/system/perm_list_array.php'); include_once('header.php'); // INCLUDES HEADER MODULE include_once('menu.php'); // INCLUDES MENU MODULE include_once('mainbody.php'); // INCLUDES MAIN BODY MODULE ?> <table border="0" width="100%" cellpadding="0" cellspacing="0"> <tr> <td valign="top" width="50%"> <br /> <?php include('scripts/cabinet/check_drawer_access.php'); for ( $i = 1; $i <= $num; $i++ ) { if ( $select == $drawers[$i]['cabinetid'] ) { $pageE = $drawers[$i]['E']; $pageM = $drawers[$i]['M']; $pageD = $drawers[$i]['D']; } } ?> <table border="0" align="center" width="475px" cellpadding="0" cellspacing="0"> <form action="<?php echo $_SERVER['PHP_SELF']; ?>?select=0" method="post"> <tr class="box_top" height="25px"> <td class="box_top" align="center" colspan="3"> DRAWERS (<?php echo $num; ?>) <input class="add" type="submit" name="new_drawer" value="" title="Create New Drawer" alt="Create New Drawer" /> </td> </tr> </form> <tr class="box_middle"> <td align="left" style="text-decoration: underline"> Name </td> <td align="center" width="120px" style="text-decoration: underline"> Security Level </td> <td align="center" width="60px" style="text-decoration: underline"> Status </td> </tr> <?php for ( $i = 1; $i <= $num; $i++ ) { ?> <!-- DISPLAY DRAWERS --> <tr class="box_middle"> <form action="<?php echo $_SERVER['PHP_SELF'] . '?select=' . $select; ?>" method="post"> <td align="left"> <a href="fc_drawers.php?select=<?php echo $drawers[$i]['cabinetid']; ?>" title="<?php echo $drawers[$i]['cdesc']; ?>"> <?php echo $drawers[$i]['cname']; ?> </a> </td> <td align="center"> <?php echo $system_seclvl[$drawers[$i]['cseclvl']]['sl_name']; ?> </td> <input type="hidden" name="cabinetid" value="<?php echo $drawers[$i]['cabinetid']; ?>" /> <td align="center"> <?php if ( $drawers[$i]['D'] == 2 ) { if ( $drawers[$i]['cactive'] == 1 ) { // CHECK IF DRAWER IS ACTIVE OR NOT echo '<img src="pics/icons/active.png" title="Active" alt="Active" />'; // DISPLAY ACTIVE } else { echo '<img src="pics/icons/nonactive.png" title="Non-Active" alt="Non-Active" />'; // DISPLAY NONACTIVE } } else { if ( $drawers[$i]['cactive'] == 1 ) { // CHECK IF DRAWER IS ACTIVE OR NOT echo '<input class="active" type="submit" name="nonactive_submit" value="" title="Active" alt="Active" />'; // DISPLAY ACTIVE } else { echo '<input class="nonactive" type="submit" name="active_submit" value="" title="Non-Active" alt="Non-Active" />'; // DISPLAY NONACTIVE } } ?> </td> </form> </tr> <?php } ?> <tr class="box_bottom" height="10px"> <td colspan="3"> </td> </tr> </table> <br /> </td> <td valign="top" width="50%"> <br /> <?php if ( isset( $select ) && $select == 0 ) { include('scripts/cabinet/new_cabinet.php'); } else if ( isset( $select ) && $select != 0 ) { $edit_drawer = FALSE; for ( $i = 1; $i <= $num; $i++ ) { if ( $select == $drawers[$i]['cabinetid'] ) { $edit_drawer = TRUE; } } if ( $edit_drawer == TRUE ) { include('scripts/cabinet/edit_cabinet.php'); } } ?> <br /> </td> </tr> </table> <?php include_once('footer.php'); My best guess is there is a flaw in the logic of handling the user permissions mixed with the different levels of access. I have to admit (and I told him this as well) but it seems over complicated in the way they are handling the permissions but it might just be me.... Permissions have been divided into a few pieces: With further customization of single files for EEA - Allow only E - edit M - move D - delete Then people can also have exclusions and exceptions... for example: Person A might have Access to Folder A but is excluded from File X but has access to all other files in that Folder. or Person A might not have access to Folder D but has an exception to access File G from folder D. or Person A has an "Allow Only" file in Folder E and is the only person who can access that file. But 20 other people use Folder E and can access all their respective files besides the "allow only" file from Person A. :'( Is this an extremely messy way to handle this situation? Please any help is appreciated. I found out today we might only have 2 weeks to fix this issue before they begin to look into other solutions and would like to just get this right for my own sake of learning at this point. Edit: I've also been reading a lot from these links as well. http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ http://www.sitepoint.com/hierarchical-data-database/ http://www.tonymarston.net/php-mysql/role-based-access-control.html I just don't want to seem like that person who just asks for help and doesn't also take some initiative. Plus if I can figure out a solution I might have a full time job and I would love to code all day! So you are also helping someone pursue their future and interest in computers!!