预过滤用户访问sqlalchemy查询的最佳方式是什么？ [英] What is the best way pre filter user access for sqlalchemy queries?
问题描述
每行在我的表中有一个user_id与它关联。现在，对于每个查询，我查询当前登录的用户的id，然后按照我感兴趣的标准进行查询。我担心的是，开发人员可能忘记将此过滤器添加到查询中（巨大的安全风险）。因此，我想根据当前用户的管理权限设置一个全局过滤器来过滤登录用户可以看到的内容。
感谢您的帮助。谢谢。下面是简化的重新定义的查询构造器来过滤所有的模型查询（包括关系）。您可以将它作为 query_cls 参数传递给 sessionmaker 。用户ID参数并不需要是全局的，只要它已经可用就构建好了。
class HackedQuery（Query） ：
get get（self，ident）：
＃在没有条件时使用默认实现
如果不是self._criterion：
return Query.get（self ，ident）
＃从Query实现复制一些更改。
如果hasattr（ident，'__composite_values__'）：
ident = ident .__ composite_values __（）
mapper = self._only_mapper_zero（
get（）只能用于单个）
key = mapper.identity_key_from_primary_key（ident）
如果ident是None：
如果key不是None：
ident = key [1]
else：从sqlalchemy导入的
使用
ident = util.to_list（ident）
如果ident不是的话：
列= list（mapper.primary_key）
if len （列）！= len（ident）：
raise TypeError（数值不匹配数字
'主键''）
params = {} $ b $ （列，标识）：
params [column.key] = value
返回self.filter_by（** params）.first（）
def QueryPublic（entities，session = None）：
＃这与问题没有直接关系，但是也是有用的。
query = HackedQuery（实体，会话）.with_polymorphic（'*'）
＃几个实体的版本需要彻底的测试，所以我们
＃还没有使用它。
assert len（entities）== 1，entities
cls = _class_to_mapper（entities [0]）。class_
public_condition = getattr（cls，'public_condition'，None）
if public_condition不是：
query = query.filter（public_condition）
返回查询
它仅适用于单一模型查询，并且有很多工作可以使其适用于其他情况。我想看到一个精心制作的版本，因为它对大多数Web应用程序都必须具有功能。它使用存储在每个模型类中的固定条件，所以你必须根据你的需要修改它。
I have been looking at the sqlalchemy recipes on their wiki, but don't know which one is best to implement what I am trying to do.
Every row on in my tables have an user_id associated with it. Right now, for every query, I queried by the id of the user that's currently logged in, then query by the criteria I am interested in. My concern is that the developers might forget to add this filter to the query (a huge security risk). Therefore, I would like to set a global filter based on the current user's admin rights to filter what the logged in user could see.
Appreciate your help. Thanks.
Below is simplified redefined query constructor to filter all model queries (including relations). You can pass it to as query_cls parameter to sessionmaker. User ID parameter don't need to be global as far as session is constructed when it's already available.
class HackedQuery(Query):
def get(self, ident):
# Use default implementation when there is no condition
if not self._criterion:
return Query.get(self, ident)
# Copied from Query implementation with some changes.
if hasattr(ident, '__composite_values__'):
ident = ident.__composite_values__()
mapper = self._only_mapper_zero(
"get() can only be used against a single mapped class.")
key = mapper.identity_key_from_primary_key(ident)
if ident is None:
if key is not None:
ident = key[1]
else:
from sqlalchemy import util
ident = util.to_list(ident)
if ident is not None:
columns = list(mapper.primary_key)
if len(columns)!=len(ident):
raise TypeError("Number of values doen't match number "
'of columns in primary key')
params = {}
for column, value in zip(columns, ident):
params[column.key] = value
return self.filter_by(**params).first()
def QueryPublic(entities, session=None):
# It's not directly related to the problem, but is useful too.
query = HackedQuery(entities, session).with_polymorphic('*')
# Version for several entities needs thorough testing, so we
# don't use it yet.
assert len(entities)==1, entities
cls = _class_to_mapper(entities[0]).class_
public_condition = getattr(cls, 'public_condition', None)
if public_condition is not None:
query = query.filter(public_condition)
return query
It works for single model queries only, and there is a lot of work to make it suitable for other cases. I'd like to see an elaborated version since it's MUST HAVE functionality for most web applications. It uses fixed condition stored in each model class, so you have to modify it to your needs.
这篇关于预过滤用户访问sqlalchemy查询的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
