On Thu, Jan 3, 2013 at 9:01 PM, Michael Pedersen <[email protected]>wrote:
> Replying to both you and Kevin with this. > > I'm trying to find a good *generalized* mechanism, and it's not easy. I've > got a couple of use cases that I've got sitting in my brain that I haven't > been able to find a common answer for. Maybe the two of you can help me out > with ideas. > > I'm not sure there really is a "generalized" solution that will work for all use cases, but I think you've identified the major candidates. > Consider a web app like Magento: It needs to have levels of access that > are not necessarily hierarchical. It can handle multiple stores, with > distinct users between stores, and shared users between stores. This means > that, depending on configuration options, a customer that has an account in > one store automatically has an account in any other store in the same > installation. It could also be the case that it has no shared customers. A > person who has admin rights in one store might have no rights in another > store. And then there's machine admins, and store admins. The rights can be > all over the place, as you can see. > > Any of the approaches below will work, though with various trade-offs. > Approaches: > If I go with JSON-encoded permissions, then any query against a table has > to somehow filter based on the current user's permissions. Considering what > that list could look like, it's possible to wind up a hugely ugly query > just trying to see if somebody has permission to see something. > Note that this also includes other similar ideas like a column with a pickled dict or what have you, but they all are "permissions encoded in a single column". Listing stuff from a table is the weak point here, as you've already pointed out. In the app I'm working with now that uses this method, everyone can see every permission restricted object, and so we just list everything, and it's not a problem. Also, we only have a single table that needs permissions, though we built the permissions stuff as a SQLAlchemy mixin so we should be able to add permissions restrictions to any other table with relative ease. I really like this solution so far, but it won't work for every use case. I do think some of the problems with it might be mitigated by using something like a Postgres HSTORE, which is sort of a Postgres-specific key-value list in a column (so it maps nicely to a dict/JSON obj, etc.) The advantage is that you can query/filter/index on the keys/values (or so I've been told), so to your code, things still look something like a dict, but you can still use the DB engine do do your heavy lifting. I think this is doable, and I think you could even make it pretty seamless using SQLALchemy, as I believe 0.8 (upcoming) incorporates some built in support for HSTOREs. (just checked and yes it does: http://docs.sqlalchemy.org/en/rel_0_8/changelog/migration_08.html#postgresql-hstore-type ) > If I go with a separate table, then I can do a more straightforward join > (still not necessarily easy to read, but at least more straightforward). > That table can be one monster table for all tables in the system, or a > separate permissions table on a per-table basis. Each of these provide > their own benefits and drawbacks (especially as relates to performance), > but they both will make an audit easier to perform. > Here we have two related solutions: a) permissions on a separate table for each table of restricted objects, and b) permissions for everything in the system in a single master table. I think a) is going to be better in most cases, but I do see a couple of cases where you might have to use b) a) has the advantage of only joining a single table with your restricted objects table, and that joined table (the permissions table) won't be nearly as large as the master permissions table you might have when using b). I've worked with a system that used b) in the past, and it was not an enjoyable experience. The table kept growing and growing (with a theoretical max of numUsers x numRestrictedObjects, which in our case was enormous), and had lots of null columns in it (because every permission needs a column, but not every permission will necessarily apply to every type of restricted object). I would probably use something like a) in cases where permissions differed significantly between object types, and where each restricted object was mostly contained by a single table. One thing that might help significantly with implementing b) though, that I haven't seen done in practice, is to assign every restricted object a unique id of some kind (unique _across_ tables). You might implement this using a Oracle/Postgres sequence or something. This would make joins a lot simpler, as you wouldn't have to store the type of object in the permissions table, and thus you don't have to compare it either. Your join becomes a simple "WHERE a.security_id = b.security_id" (filtered by the permissions, of course) and if you put indexes on those columns, I bet it would be pretty perfomant. In fact I guess you could do something like this with the multiple permissions tables as well. I suppose I could also attach a bitmask field to each row, and assign each > permission a specific bit. From there, a permissions table would include > users, groups, and permissions entries. Under those conditions, permissions > restricting would involve simply precalculating what options would provide > the necessary permissions to do something, and then do a bitmask match. > With enough users, groups, etc, that could cause a performance hit, but it > should be able to be limited to a specific area of the system that could > then be heavily optimized. > I'm not sure this buys you very much. I dealt with something kind of like this (albeit on a PHP app) and it always seemed like unnecessary complexity. I say let the already highly-optimized SQL engine do set operations for you. Unless I'm misunderstanding what you mean here. Am I missing something? And while I'm thinking about it, one thing I never liked much about the TG auth system is that you can't assign permissions to groups _or_ individual users. In one project I re-vamped the TG auth classes to add a "Principal" db model, and had "User" and "Group" both inherit from that. Then I made permissions attach to Principals using SA joined-table inheritance. It's another join, but it simplified lots of other places in my code, so at least in that case I think it was worth it. And if you had something like this, your object-level permissions tables might get a little simpler, since you would be joining on a principal id rather than a user id _or_ a group id, and have to track them, decide what table to join with, etc. Irrelevant if you don't need to be able to add object-level permissions to groups though. > Those are the options I've got. What do you all think? > Summing up, I still think it depends on your app. If you don't need much in the way of filtering by permissions, go with the permissions-encoded-in-a-column. If you have similar permissions for a bunch of objects, do the single permissions table thing, ideally using a unique id across all the restricted object tables. If you have wildly different permissions for each restricted object, do a separate permissions table for each. (You might even have something in between, where most of your objects use a master table, but one or a few objects use a separate table or their own tables. This could get tricky to maintain though.) As far as a generalized solution, I think you've pretty much boiled down the three most general models, though there might be others. I've really enjoyed thinking about this stuff. If I could ever get off my ass and get a blog up, I'd be inspired to do a blog post series. > On Thu, Jan 3, 2013 at 7:44 AM, Craig Small <[email protected]> wrote: > >> On Wed, Jan 02, 2013 at 01:41:01AM -0500, Michael Pedersen wrote: >> > My question for you is this: Are you looking for view based >> permissions >> > (i.e.: possibly conditional portions of the page being rendered), or >> are >> > you looking for row based permissions (i.e.: a specific instance of >> > model.Thing is only visible to a subset of users)? >> > The first item is already covered very well by repoze.what >> predicates. You >> > can insert <py:if> statements in your code, and get the results >> you're >> > expecting. The second case, though, I don't know of a generic way to >> do >> > it. >> It's the second. >> The admin group can see everything; that's pretty standard repoze.what >> kind of task. >> >> Everyone else has "thier" instances of particular models. Imagine an >> online store, the shopwoner can see all Orders but I can only see mine. >> I can obviously do this in the controller by forcing it to filter on it >> but was wondering if there was a generic way. >> >> > Did you get anywhere with this? If so, how did you handle these >> issues? >> I've not gone far with this part so no light yet. >> >> - Craig >> -- >> Craig Small VK2XLZ http://enc.com.au/ csmall at : enc.com.au >> Debian GNU/Linux http://www.debian.org/ csmall at : debian.org >> GPG fingerprint: 5D2F B320 B825 D939 04D2 0519 3938 F96B DF50 FEA5 >> >> -- >> You received this message because you are subscribed to the Google Groups >> "TurboGears" group. >> To post to this group, send email to [email protected]. >> To unsubscribe from this group, send email to >> [email protected]. >> For more options, visit this group at >> http://groups.google.com/group/turbogears?hl=en. >> >> > > > -- > Michael J. Pedersen > My Online Resume: http://www.icelus.org/ -- Google+ > http://plus.ly/pedersen > Google Talk: [email protected] -- Twitter: pedersentg > > -- > You received this message because you are subscribed to the Google Groups > "TurboGears" group. > To post to this group, send email to [email protected]. > To unsubscribe from this group, send email to > [email protected]. > For more options, visit this group at > http://groups.google.com/group/turbogears?hl=en. > -- Kevin Horn -- You received this message because you are subscribed to the Google Groups "TurboGears" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/turbogears?hl=en.
