Multitenancy, filter selects, updates and deletes in do_orm_execute events

[Frédéric CLEMENT]

Hi the list,

Version 1.4Beta (hopefully 2.0 very soon)

I am writing a multitenant application and I need to filter all requests using subqueries.

I succeeded to do it for selects doing something like that :

for desc in orm_execute_state.statement.column_descriptions:
t = desc['type']
classname = desc['name']
scope = get_scope(classname)

if scope != None:
rights = check_rights(session, infos, role, scope)
orm_execute_state.statement = orm_execute_state.statement.options(
with_loader_criteria(
t,
t.id.in_(rights),
include_aliases=True
)
)

But I didn't find out how to do it for updates and deletes.

If some of yours have an idea .... :)

Thanks a lot,

Fred

[Mike Bayer]

First off I admire your motivation to make that happen, that's all new stuff and note there's a lot of fixes to the with_loader_criteria() thing that are sitting in github master right now that will be in 1.4.0b2.

The good news is that update() and delete() both support options as well so feel free to use with_loader_criteria() with these constructs as well, using the 2.0-style execution described at https://docs.sqlalchemy.org/en/14/orm/session_basics.html#update-and-delete-with-arbitrary-where-clause .

it might be nice for us to add a universal "mappers" accessor to ORMExecuteState but for the moment you can likely work for all statements generically as:

if orm_execute_state.is_orm_statement:

    mapper = orm_execute_state.bind_arguments['mapper']

    cls = mapper.class_

see if that works out and we will likely need to add an accessor that guarantees being able to provide the target "mapper" and/or mappers for any operation.

--

SQLAlchemy -

The Python SQL Toolkit and Object Relational Mapper

 

http://www.sqlalchemy.org/

 

To post example code, please provide an MCVE: Minimal, Complete, and Verifiable Example. See http://stackoverflow.com/help/mcve for a full description.

---

You received this message because you are subscribed to the Google Groups "sqlalchemy" group.

To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/69ab301d-e223-4b5d-9e37-756ce5b04801n%40googlegroups.com.

[Mike Bayer]

In a little while the git master will have new accessors:

ORMExecuteState.bind_mapper

ORMExecuteState.all_mappers

so that you can reliably get at the Mapper objects that are the target of an ORM-enabled select(), update() , or delete()

see https://gerrit.sqlalchemy.org/c/sqlalchemy/sqlalchemy/+/2432 for the patch going through testing now.

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/7c47c079-bca5-44de-aea2-40d8eb708c7b%40www.fastmail.com.

[Frédéric CLEMENT]

Hi Mike,

Many thanks for your reply, giving a try to it as soon as it will be released.

[Frédéric CLEMENT]

Hi Mike,

In the meantime, I tested like that and it works like a charm :

if orm_execute_state.is_update:
mapper = orm_execute_state.bind_arguments['mapper']
scope = mapper.entity.__name__
rights = check_rights(session, infos, role, get_scope(scope))
orm_execute_state.statement = orm_execute_state.statement.options(
with_loader_criteria(
mapper.entity,
mapper.entity.id.in_(rights),
include_aliases=True
)
)
return

[Mike Bayer]

yeah that's great

with_loader_criteria is going to be great

To view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/ae9720a9-bf3a-432d-8ba1-5bc7e55d1c49n%40googlegroups.com.