04-17-2010, 04:56 AM
The place I work at has decided to, in the near future, completely rewrite its extremely extensive permissioning software (using PHP). There are literally thousands of files, each with their own permissions. There are also thousands of users. Flat file permissions in a database didn't seem to work very well with the last permissioning system, and we are looking for a better way to keep track of such huge permissions. I was trying to think of data structures that might speed up the checking of such permissions, but nothing really came to mind. Any ideas on how to store this kind of data in a database? I've never worked with such large datasets before.
I appreciate the help!
04-18-2010, 01:58 AM
PHP and MySQL seems like the logical choice.
04-18-2010, 06:11 AM
I would use a CMS that has built in permissions, if I understand what you are asking.
04-18-2010, 05:10 PM
I would use PHP MySQL. That wasn't really the question I was trying to ask. In a nut shell, how would you organize such massive permissions that wouldn't take forever? Creating a table with every single file and which permission each user has for each file seems to be a bit of an inefficient solution because of the scale of all this.
04-19-2010, 12:12 AM
What kind of files are they?
Give a list of file types (file extensions).
04-19-2010, 02:12 PM
PDF, txt, html, php, etc. Also, the old permissioning had APIs to handle permissioning on external software. The problem is that htaccess isn't enough.
04-19-2010, 11:51 PM
You have one table with just the user profiles, and yes, you'll have thousands of users:
unique id|user id|username|password|user last name|user first name|level|phone|etc.
Users can log in and change their profile. Only the admin can change the user's level.
Perhaps level 9 is admin. The admin can make anyone else an admin level.
There is another table for every permission and file. This table will end-up with
thousands of rows, but that's OK.
unique row id|userid|filename
If I have permission to access 100 files, there would be 100 rows with my userid in it.
There is another table for files that fall into a "level" permissive. That means that
perhaps every user with a level code of 3 might be able to all access the same file.
There may be only 200 or so files that end-up in this table:
unique row id|level|filename
So now, you have 3 tables and there are some "like" columns between them.
That allows you to use MySQL JOIN to cross-link the tables.
If I am one user and I log-in, a successful log-in will set a PHP SESSION variable.
Now, any queries with my userid will only yield the rows were I have permission.
This makes the query result rather small and efficient.
Because ALL of the protected file are in ONE protected directory, and nobody knows the
name or path to that directory, PHP can "stream" or "serve" the files to the user only
if they have permission. PHP can allow the files to be displayed (open) or (save-as).
Administrators can edit any user's profile, and do infinite number of various searches
and sorts on the database, and JOIN-ing of tables.
04-19-2010, 11:58 PM
Wow thank you so much for that well-thought out idea!! I will definitely give this a whirl when the time comes to change the permissions.