Possible to add JMix access filtering to plain SQL?

Support
#1

Hi all,

we want to provide a plain SQL console for admins of our application. Since we are using then tenant logic with a row level access filter to separate several entities between different user groups, the SQL should only operate on data that belongs to the admin of a user group.
That would mean something like this:
SELECT b.title FROM BOOKS b WHERE b.tenantId = :currentTenantId

Since the admins won’t enter their own tenantId to their queries we would like to add that before execution of their SQL. Since the dataManager won’t work with native SQL, my question would be if there is some service method that converts the SQL back to jpql so the filtering can be added by the dataManager?

Or would it be more feasible to use a library for SQL parsing like JSqlParser and get the tables from the SQL, check if they have a tenantId column, enhance the WHERE statement and build a final SQL from that?

Somewhere on the road from the dataManager to the database, JMix needs to add the restrictions to the JPQL or SQL as well, no? How is that done (in broad terms, I can debug the transformation but it is a bit creepy in that part of the code haha :wink:

Thanks a lot in advance!

#3

Hi,

No we don’t have service to convert native SQL to JPQL query.

There are 2 criteria - tenant and soft-delete - which are implemented as io.jmix.eclipselink.persistence.AdditionalCriteriaProvider so they should be applied if you work via EntityManager which can execute native SQL.
But the rest (like entity events, cross-datastore references, data access checks, audit, dynamic attributes, lazy loading, etc) works only with DataManager. So this might not fit your goals if you need automatical security check.

If you can’t use EntityManager then you will need to implement your custom solution. Some SQL parsers may help you, but it’s up to you.

Note: not all Framework entities support multitenancy so be careful with queries (expecially with update ones if you are going to allow them within your console).

BTW, have you considered using JPQL directly as an input of your console? Or it’s required to be native SQL?

Regards,
Ivan

#4

Thank you Ivan and sorry for my late reply.

The idea was to have an admin console for easier troubleshooting. Since JPQL does not support all SQL features we wanted to use SQL. I will try to incorporate your ideas.

Thanks again!
Oliver

#5

Hi Oliver.

I think it depends on expected scenarios.
Some thoughts about possible approach:

  1. If you want to use this console to “reproduce” some Jmix operations/scenarios then you should “play by Jmix rules” - use JPQL + DataManager.
  2. If you want to use this console as internal alternative to some external SQL client to just access to database and call some troubleshooting queries - I think it’s better to not have any additional automatical conditions (maybe except the tenant one). You may need to have more manual control. Because otherwise this may potentially cause some false-positive/negative results.

Regards,
Ivan

#6

Hm I think you are right, mixing pure sql for diagnostics AND having a filter does not play well.

Thanks for the input Ivan!