Skip to main content
gamedev.net
🔒 Locked 🤖 Godot

Rights-checking and optimisation

Started by klems Sep 25, 2006 at 5:33 AM 3 replies 800+ views
Original Post
klems
klems
I'm currently writing the backbone for a small Internet BBS system in PHP4 with MySQL. This backbone is supposed to be quite generic, only handling logins, rights checking and authentication, really.' The goal is to have another module provide the actual BBS functionality, but also allow for adding blogging, CMS, project management, and other things which might require authentication, to a site using this common login system. As such, I need to keep permissions quite generic. However, I'm having a little trouble with how I should make sure rights checking doesn't completely bog down the web server that runs it. Currently, this is the way I've conceived rights checking: Users are members of zero or more groups. Groups are assigned rights to resources. Group memberships are stored in a table where each item consists of a group ID, a user ID and a unique membership ID. Groups' rights to resources are stored in a table where each item consists of a group ID, a resource ID and the rights the group has to the resource. When a user wants to access a resource, a function auth_is_allowed is called with the users ID, the object's ID and the right the user has requested (for example, R_READ). Now, this function has to first figure out all of the user's memberships. This can be done with a single SQL query and isn't that bad. However, after this the permissions need to be checked for every group the user is a member of, with either a query for each one, or a long "SELECT rights FROM rights_table WHERE (group_id='2' OR group_id='7' OR ...) AND resource_id='blah'". Either way, it seems to me that the SQL server, the web server or both will be doing a lot of work if the user is a member of a lot of groups. I've thought of caching the user's permissions to all object he has previously accessed in a $_SESSION variable and only update them when they change, but I'm not too sure about the feasibility of the idea. Should I try to optimise this, or is the overhead of the queries too small to bother? If I should, do you have any suggestions as to how? Is there a better way altogether that I haven't thought of? EDIT: I can grammar.
klems
klems
This is mainly a personal project to teach myself more about web coding. As such, I'd prefer to do it myself. I'll check it out though, perhaps there's further information to be found there. Thanks.
Excors
Excors
If I'm not doing something stupid, I think you could find a user's rights to a specific resource with a single command: "SELECT r.rights FROM rights_table AS r JOIN group_memberships_table AS gm ON r.group_id = gm.group_id WHERE gm.user_id = ? AND r.resource_id = ?".

(If you're only interested in whether or not the user has R_READ, it may be best if you can add that into the SQL query ("AND r.right = 'R_READ'" or whatever) instead of passing the whole list of rights back to PHP and then searching for the correct one, since the database is likely to be faster.)

I'd guess you should create an index on gm.user_id, and on r.group_id and r.resource_id. The database's query optimiser should be able to do index lookups to find the user's groups and to find the rights associated with those groups, and then maybe intersect that list with the resource_id index. Or it might do something different - EXPLAIN should give some indication of whether it's using the indexes correctly (at least when you've got enough data in the tables for the query optimiser to work properly).

Once the database isn't doing anything unnecessarily inefficient, you should probably avoid further optimisation (like caching the results of queries) until you're able to profile your code and see that it really is too slow - it'd be good if your program could measure how much time it spends in all its various queries, and then you can decide whether it's worth adding complexity to some of them to get better performance. (Database queries should typically be fast, particularly when there's not a huge amount of data and you're using indexes sensibly, so optimisation should be based on measurements rather than intuition.)
klems
klems
Quote:
Original post by Excors
If I'm not doing something stupid, I think you could find a user's rights to a specific resource with a single command: "SELECT r.rights FROM rights_table AS r JOIN group_memberships_table AS gm ON r.group_id = gm.group_id WHERE gm.user_id = ? AND r.resource_id = ?".

(If you're only interested in whether or not the user has R_READ, it may be best if you can add that into the SQL query ("AND r.right = 'R_READ'" or whatever) instead of passing the whole list of rights back to PHP and then searching for the correct one, since the database is likely to be faster.)

I'd guess you should create an index on gm.user_id, and on r.group_id and r.resource_id. The database's query optimiser should be able to do index lookups to find the user's groups and to find the rights associated with those groups, and then maybe intersect that list with the resource_id index. Or it might do something different - EXPLAIN should give some indication of whether it's using the indexes correctly (at least when you've got enough data in the tables for the query optimiser to work properly).

Once the database isn't doing anything unnecessarily inefficient, you should probably avoid further optimisation (like caching the results of queries) until you're able to profile your code and see that it really is too slow - it'd be good if your program could measure how much time it spends in all its various queries, and then you can decide whether it's worth adding complexity to some of them to get better performance. (Database queries should typically be fast, particularly when there's not a huge amount of data and you're using indexes sensibly, so optimisation should be based on measurements rather than intuition.)
If that piece of code does what I think it does in the way I think it does it, that's pretty cool. Many thanks! Now I just need to brush up on my SQL skills a bit.

Topic Locked

This topic has been locked by a moderator. New replies are not allowed.

Sign in to reply to this topic.