Help: Files, Users, Permissions, Extremely long time sink on returning info

leapster · July 25, 2012

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!!

leapster · July 25, 2012

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?

xyph · July 25, 2012

So each user will have potentially unique permissions for every file?

And you want to be able to modify permissions for any given user in bulk using folders/subfolders and 'drawers?' (I'm assuming that'll be a group of files)

PFMaBiSmAd · July 25, 2012

Some suggestions -

1) You need to remove the @ error suppressors from your code. They minutely slow down the execution, simply by being present, because every time they are encountered, php saves the current error_reporting level, sets the error reporting level to zero, executes the statement the @ applies to, then restores the previous error_reporting level and if the statement actually does produce a php error, the error must still be detected and the php error handler is still called (the reporting and display/logging of errors is just the final step in the error handling code.)

2) Your code should be structured to first determine what permissions the current visitor has on any page request, then get and produce JUST the output that is available for the current visitor. The code currently appears to be executing queries inside of loops inside of other query/loops, three or four levels deep, looping through every item and checking the permissions of each.

leapster · July 25, 2012

@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.

xyph · July 25, 2012

Simple, you store permissions for each file, for each user/group.

Choose which type of permission gets priority over the others in case of conflicts (something like file > folder > drawer)

Also, if user-specific perms over-write group perms (user > group)

Store the permissions in a table. I'm not sure if using a single table for all, or a separate table for user/group perms. I could move this over to the SQL forum where it might get more answers if you'd like

MY table would be something like

id - Primary key, auto increment, for easy permission deletion

owner_id - The primary key for the user or group this rule applies to

owner_type - 0 for user, 1 for group?

file_id - The primary key of the file, folder, or drawer this belongs to

file_type - 0 file/folder, 1 folder+subfolders, 2 drawer?

permission - bitwise. 1 = read, 2 = write, 4 = move, 8 = delete. so, read, write, delete would be (1+2+ = 11. Though, you cant move unless you can delete in one folder, and write to another so that gets complex.

You can then easily select all of the files that a user has at least read permissions for, and verify against the specific permission when the user tries to perform an action on the file.

TO make this quick, you're going to want to take a crash-course in proper indexing, and TEST LIKE MAD

That's the basic concept of how I'd do it though.

leapster · July 25, 2012

@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

Sign In

Help: Files, Users, Permissions, Extremely long time sink on returning info

Recommended Posts

leapster

Link to comment

Share on other sites

leapster

Link to comment

Share on other sites

xyph

Link to comment

Share on other sites

PFMaBiSmAd

Link to comment

Share on other sites

leapster

Link to comment

Share on other sites

xyph

Link to comment

Share on other sites

leapster

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information