I have to implement ACL for an existing application.
So I added the a user, group and groupmembers table to the database.
I defined a ManyToMany relationship between user and group via the association table groupmembers.
In order to protect some ressources of the app (i..e item) I added a additional association table auth_items which should be used as an association table for the ManyToMany relationship between groups/users and the specific item.

item has following columns:

• user_id --> user table
• group_id --> group table
• item_id --> item table

at least on of user_id and group_id columns are set. So it's possible to define access for a group or for a user to a specific item.

I have used the AssociationProxy to define the relationship between users/groups and items.

I now want to display all items which the user has access to and I have a really hard time doing that. Following criteria are used:

• All items which are owned by the user should be shown (item.owner_id = user.id)
• All public items should be shown (item.access = public)
• All items which the user has access to should be shown (auth_item.user_id = user.id)
• All items which the group of the user has access to should be shown.

The first two criteria are quite straightforward, but I have a hard time doing the 3rd one.

Here is my approach:

clause = and_(item.access == 'public')
if user is not None:
   clause = or_(clause,item.owner == user,item.users.contains(user),item.groups.contains(group for group in user.groups))

The third criteria produces an error.

item.groups.contains(group for group in user.groups)

I am actually not sure if this is a good approach at all.
What is the best approach when filtering manytomany relationships?
How I can filter a manytomany relationship based on another list/relationship?

Btw I am using the latest sqlalchemy (6.0) and elixir version

Thanks for any insights.