Jump to content

Design issue - permission system


xyph

Recommended Posts

I'm wondering what you guys think would be the best way to accomplish this. It's more of a design question than any specific query.

 

I have a client that I'm building a file-management system for. They require a user/group permission system, and it's a little archaic. I'll try to explain

 

Files are 'placed' in a folder using an alias to that file. Any file can have an alias in any number of folders.

Folders/subfolders are placed in 'cabinets' using an alias to that folder/subfolder. Any folder can have an alias in any number of cabinets or folders.

 

Permissions are applied to aliases, not the files/folders themselves. Different aliases of the same file could have different permissions.

 

Permissions can target either a user, or a usergroup.

 

As of now, I have 4 tables to link user/group permissions to file/folders.

 

user_to_file_permissions

user_to_folder_permissions

group_to_file_permissions

group_to_folder_permissions

 

I like this method, because I can easily keep foreign key constraints.

I don't like it though because getting them all into a single query could be quite ugly (if possible). I'm guessing I'd use UNIONs and possibly some if cases to help determine which values came from which tables.

 

In the case of conflicting permissions, the most permissive will be used.

 

Is there a better way to do this? Should I provide more details? Ideally, I'd like to keep foreign key constraints available, but if it's not possible in a more robust or efficient system, I can program the clean-up manually :)

Link to comment
Share on other sites

Agreed, just nice to get it all over with in the SQL so I don't have to bounce data back and fourth between PHP/SQL.

 

After doing a bunch of testing and research, I ended up going with a node-style relationship anyways, using the calculated path to determine final permissions.

OQGRAPH is quite the amazing tool.

 

Thanks for the input, though.

Link to comment
Share on other sites

Well, it was a very odd problem. The client wanted a per-alias based permission system, with a combined 'base security level' requirement, and possible user/group-specific permissions.

 

They also wanted the folder structure (subfolders,etc) to go along with the alias - changes in one tree will affect all.

 

I decided to go with a node-based system. Each source->destination (parent->child) is given it's own relationship. Any given child can have multiple parents. The engine makes it extremely easy to find any path, in any direction. On my development machine and dummy data, it can calculate over 24k distinct paths (20 roots, 5 levels deep, large amount of children with multiple parents) in less than 100ms. This should be way beyond any amount of data I'll ever need to get in a single query.

 

I probably went overkill, but I was free to choose my own database engine, and MariaDB comes with OQGRAPH bundled. I probably could have done this 'fast enough' in Postgre/etc with recursion.

 

For permissions, I simply reference the path from the root to the node, and restrict the results from the graph engine to those with a matching path. The way I've done it is not completely normalized, but since I can calculate any path from node-to-node so damn quickly, it's a non-issue to me. I could see a potential problem showing up if I ever needed to modify indexes, but that should be pretty straightforward to fix anyways. I don't like the fact that I can't use a foreign key constraint, but I think normalizing the path would be overkill :P

 

It stinks, because for any given folder, I will have multiple sets of 'security levels' and overall the cardinality for that lookup will be pretty low (I hope I used that word correctly there).

 

On paper, the system works well with a fairly large amount of data on a machine nowhere near dedicated to the database. What are your thoughts?

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.