CODEWITHSUNDEEP
phpsqlarraysperformancecaching
F21
F21

Reputation: 33391

Caching permissions or use the database for each request?

I am currently building a PHP application using the MVC pattern.

I have implemented role based access control (RBAC) in the application. Currently user priveleges are determined using about 7 joins and then cached in the user's session. Sessions are stored in the database.

The privilges can be imagined to be a table containing these columns:

When I need to match a privilege, Module, Controller, Action and Status ID, must match an exact entry in the table, with "Object ID" being optional depending on "Type".

Assuming taht on average I will have 100 privileges per user, is it more efficient to cache in the sessions variable in the database, and then use PHP's array function to parse the cached result?

Or would be it be more efficient to do those joins and determine whether one is allowed to perform that action everytime there is a request?

Cheers :)

Upvotes: 0

Views: 1691

Answers (1)

Sajid
Sajid

Reputation: 4421

The joins will likely be slower, especially if you have lots of users or perms (because it will cause the server to use temporary tables once the cache space is used up).

If the permissions are rarely changed, then it makes sense to just stick it in the session for the duration of a session. Note that you can also time out the permissions and check every hour or whenever if you want, which gives a compromise.

On the other hand, if you're very paranoid or perms change often (eg, right here on stackoverflow), a solution is to use a computed table view to store the results of the joins such that the actual joins are computed on change, not on access. Another option depending on the DB is to use triggers to maintain a "computed" version of the permission that is recalculated every time the source tables change.

I would use serialize on the array to turn it into text, by the way.

Upvotes: 2

Related Questions