How to inject the userId variable into a stored procedure ?

[marcgardent]

Hello,
I am trying to inject the userid variable (serverside😅) into a stored procedure using the data API builder integrated into SWA. I need to do this in order to secure the API as much as possible. The current policies are not sufficient to implement all of the necessary security scenarios ( sharing ressources between users)

I have searched for solutions to this problem, but I have not been able to find anything that works.
I would be grateful if you could help me with this issue.
Thank you for your time.

[marcgardent]

Hi,

I'm implementing a workaround, in a few words 👍 :

• Create the sessions table protected by a policy (UserId filter).
• The client generates a random $token and places it in the sessions.token field.
• The client calls the stored procedure with its $token get_books_of_my_super_secret_user_group(token).
• And so, the stored procedure obtains the UserId with the $token parameter, something like this:

select books.* from books b
        inner join usergroups ug on ug.id = b.userGroupId 
        inner join sessions s on s.token = $token
        where s.UserId = ug.UserId

Point for improvement :) server-side token expiration

If you know of a more direct solution, please stop me :)