Entity Level Security Performance and Query Generation Logic for Azure SQL
Hey Everyone, specifically Mendix Experts, We have a few applications that are a part of Mendix Private Cloud integrations, using Azure SQL server, and one thing that we have noticed when migrating our apps from Postgres on public cloud to Azure SQL on private cloud was the performance hit we took on the DB. It seems as though entity level security generates queries that are super inefficient. We notice that tables are queried with columns that calculate the different access rules for the entity via a CASE statement returning a bit value for whether or not the user should have access or not. But that is not all. It also seems to run the SAME queries in the where clauses instead of just filtering on the bit values calculated in the case statements. So it is doubling up these queries, which is pointless, and taxing to the SQL database. Does this happen on Postgres DBs too? What is the purpose of this, is this a bug, or is it how it is supposed to work? Another thing that would make a lot of sense as an idea to improve security query performance would be to allow developers to tie access to an entity to another’s entity permissions. For example if I have an orders table, and a order line items table, it would be great to say user can see orders they created and any order line items for any order the user also has access too, instead of doing crazy upward traversals. Can anyone help with this stuff? Does anyone have any insights? Thanks
I haven't done extensive database logging analysis to determine how Mendix translates entity access rules into SQL for different RDBMS engines... however I can confirm that I have seen similar cases (of performance degradation and enormous SQL queries for entity access) on Postgres databases as well.
In my experience, the main improvements can be gained using the following changes:
Reduce the amount of module roles per project role. If a UserRole in your project is linked to multiple module roles in a specific module, Mendix will have to check access rules separately for each individual module role, this will lead to the most complex SQL queries.
If you enforce entity access through multiple entities, the lower-level entities need to traverse through multiple entities before reaching the user entity (orderline/orderline_order/order/‘Tenant/Tenant_User’). If the pathways are running too ‘deep’, it helps to add helper associations to your ‘tenant’ entity or another entity that is closer to the user object.
The idea to improve security to tie entity access to ‘allow access if you are allowed to access the parent object’ is an interesting one, and should definitely improve maintainability of your model.
Performance-wise however I don’t think it will make much of a difference as the Mendix runtime would still need to know if the user has access to the parent object(s), and I don’t see how this could be done without traversing all the way back. E.g. it should also work in a microflow where you retrieve the deepest level objects using entity access without using the intermediate objects. Imagine a datagrid showing a list of unique products that were ordered by you as a user; this would require a retrieve via orderlines/orders to which the user has access.
Also, very interested to see other’s opinions and experiences :)