permission and pagination logic

bike5

I have a question about pagination and permissions working together. Say the permission system has groups and permissions can be attached to groups along with individual users. Groups can also be assigned under other groups to create a hierarchy which children inherent parents permissions etc. Now to me it seems trying to run a permission check inside of a query would be too complicated if not impossible because of needing recursion on the group permissions, but what is the standard with handling pagination now. Say I want to query 20 results out of 10k total. Normally I do a limit in the sql but now I can't because when doing a permission check over those 20 they user might say only have access to 5 results. So now I have a page with 5 results, but it should show 20. I don't want to query and loop through all 10k records counting up 20 that the user has access to and trying to figure out where to start the next page at but I can't see a way around that. It also eludes me as to how you can get an accurate page count without doing a check on all the items first outside of the query itself.

So is there another way to handle this? Or am I stuck with having to process 10k+ records just to fill each page with results and display a correct page count based on what the user has access to. I don't think software with millions or records does this, but how do they do it then?

Any input would be great, thank you.

CoderDan

I believe you're stuck with your current methods.

My suggestion is to redesign the permission system and table structures so that you can pull a page count with a single query.

It may not be as hard to achieve this as you think - You can do pre-queries to establish a user's set of permissions, until you are at a point where you can query with all permissions as filters.

giving more specific help would require seeing exactly how your permission system is coded/structured, and may take more time than most are willing to give, but it doesn't hurt to post it all to ask if you're still stuck.

bike5

I am in a position to create a new system from scratch. What database structure did you have in mind and what would a example query look like to retrieve data while doing a permission check? I have a few different systems I am working with but they all seem to have issues.

Thanks.

CoderDan

What's the details on the permission system? It really all hinges around that.
Either there's a pre-existing permission system in place, or a set of requirements for it to match...

bike5

I need to assign read/write/edit/delete permission to users groups on categories which sets those permission to the articles within the categories (recursively on the categorizes would be nice but not necessary if it means double the queries). Also the ability to assign the same permissions to individual users. Also, the articles can be assigned to multiple categories (relational tables), it was decided that the user is granted access if they have at least 1 access via any of the categories the article is in. This is my minimal requirements I have for now.

In the future the we want the ability to assign the same permissions to individual articles (groups and users). Also creating arbitrary permissions to use on other modules, maybe limit groups to see select invoices based on a buyers state or something. But these are things I don't "have" to take into account now but it would be nice to get a system that can expand to fit these needs in the future.

CoderDan

ok, it still is fairly complex, but breaks down to:
users
articles
categories
groups (permissions)

and then as they're multiple association, lookup tables for:
articles to categories
users to groups
categories to groups
articles to groups

and, a group has it's read/write/edit/delete settings, rolling the permissions into the groups themselves - and a group can only have 1 article, thus giving you the permissions for an individual article.

the tricky part is categories can belong to categories 😛 and I'm assuming an infinite nesting ability, with something like a category being able to have a parent category.

In order to simplify the queries, it might be as simple as ensuring that when a category is assigned to a permission group (or when that category is removed/added to a different parent) the group association relationship is cascaded down. This would mean that all categories are actually in the groups they should be (or not, if removed) without needing to check the category cascade at time of query - ie. for the purposes of figuring out of an article is viewable by a user, you just check if article's category is in a group that the user is in (that has read permission), and don't need to worry about the article's category's parent category, etc.

you're still going to be left with some complex queries, as there's multiple groups that all need checking, but it would be able to become one query at this point, i think.

make sense?