On Dec 6, 2012, at 11:59 AM, Paul Johnston wrote: > Hi, > > I hope everyone's keeping well. It's been ages since I've been on the list. I > do use SQLAlchemy from time to time, but now it generally works so well, that > I don't have any questions to ask! > > But I would appreciate some thoughts on the approach I've taken with a > multi-tennant SaaS web app. It's a multichannel stock management system for > online retailers. All the user data is attached to a merchant - products, > variations, categories, orders, etc. It's important that one merchant cannot > access data belonging to another merchant. When handling a request, the > active merchant can be determined from the logged-in user, which is kept in > thread local storage. > > So I started with lots of code like: > db.Order.query.filter_by(merchant_id = twa.get_user().merchant_id) > > Now, this is fine, but it's repetitive, and it's risky for security - it just > takes me to forget one filter_by merchant_id and we've got a security > vulnerability. > > So, what I wanted to do is create a custom session that will do this > automatically. It needs to do two things: > 1) Any query object against an entity that has a merchant_id property is > filtered on that > 2) Any new object that has a merchant_id property has the property > automatically set > > I don't think a session extension can do (1), so I created MySession > subclassing Session, and passed this as class_ to sessionmaker. Here's my > initial attempt at MySession: > > class MySession(sa.orm.Session): > def query(self, *entities, **kwargs): > query = super(MySession, self).query(*entities, **kwargs) > for e in entities: > if e.tables[0].name == 'user': > continue > if e.has_property('merchant_id') and twa.get_user(): > query = query.filter(e.class_.merchant_id == > twa.get_user().merchant_id) > return query > > Now, I faced on major problem - seeing these errors: > > InvalidRequestError: Query.get() being called on a Query with existing > criterion.
we have a recipe that's all about the "built in filter" which also illustrates how to work around that existing criterion thing: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/PreFilteredQuery -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.