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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
