dynamic OQL retrieve throws SecurityRuntimeException when trying to SELECT attribute used in GROUP BY clause

0
Hi, I have a Java Action for dynamic data retrieve using OQL. The Microflow which executes the Java Action applies Entity Access. A regular SELECT works just fine: SELECT r.* AS c FROM RequestFulfilment.Request AS r returns: [ { "requestfulfilment$request.ID": "[MendixIdentifier:: id=9007199254743894 objectType=RequestFulfilment.Request entityID=32]", "System.owner": "[MendixIdentifier:: id=7318349394479859 objectType=MultiTenantAdministration.TenantUser entityID=26]", "Description": null, "createdDate": "Wed Nov 20 13:54:54 CET 2019", "State": "Initiated", "Number": 113, "Approval_Needed": false, "ApprovalTotalCount": 0, "FulfilmentTime": "Thu Nov 28 13:55:12 CET 2019", "Communication": "Mail", ... }, ... ] (Notice the “State” field in the result object indicating that read permission is granted for the current user.) The following query throws an exception: SELECT r.State, COUNT(r.id) AS c FROM RequestFulfilment.Request AS r GROUP BY r.State The exception thrown: An error occurred: com.mendix.core.objectmanagement.SecurityRuntimeException Message: Read access denied for DataRow column 'State' (0) Stacktrace: com.mendix.core.CoreRuntimeException: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.objectmanagement.SecurityRuntimeException: Read access denied for DataRow column 'State' (0) at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:383) Caused by: com.mendix.systemwideinterfaces.MendixRuntimeException: com.mendix.core.objectmanagement.SecurityRuntimeException: Read access denied for DataRow column 'State' (0) at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:156) Caused by: com.mendix.core.objectmanagement.SecurityRuntimeException: Read access denied for DataRow column 'State' (0) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:36) Caused by: Read access denied for DataRow column 'State' (0) at com.mendix.connectionbus.data.InternalDataRow.getValue(InternalDataRow.java:176) at com.mendix.connectionbus.data.InternalDataRow.getValue(InternalDataRow.java:137) at dashboard.JsonSerializer.lambda$serialize$1(JsonSerializer.java:56) at java.base/java.util.ArrayList.forEach(ArrayList.java:1540) at dashboard.JsonSerializer.lambda$serialize$2(JsonSerializer.java:56) at java.base/java.lang.Iterable.forEach(Iterable.java:75) at dashboard.JsonSerializer.serialize(JsonSerializer.java:52) at dashboard.actions.RetrieveByOQL.executeAction(RetrieveByOQL.java:51) at dashboard.actions.RetrieveByOQL.executeAction(RetrieveByOQL.java:1) at com.mendix.systemwideinterfaces.core.UserAction.execute(UserAction.java:46) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.doCall(CoreActionHandlerImpl.scala:71) at com.mendix.basis.actionmanagement.CoreActionHandlerImpl.call(CoreActionHandlerImpl.scala:49) at com.mendix.core.actionmanagement.CoreAction.call(CoreAction.java:54) at com.mendix.basis.actionmanagement.ActionManagerBase$1.execute(ActionManagerBase.java:147) at com.mendix.util.classloading.Runner.doRunUsingClassLoaderOf(Runner.java:32) at com.mendix.basis.actionmanagement.ActionManagerBase.executeSync(ActionManagerBase.java:152) at com.mendix.basis.component.InternalCoreBase.execute(InternalCoreBase.java:379) ... If I disable the Entity Access I get the desired result: [{"c":3,"State":"Initiated"}] I assume this is because “c” (the count column) is ‘generated’/added dynamically to the result view and theirfor has no access rules defined by default. Maybe the entity access check doesn’t handle this case? Obviously this must be possible. How can I do this? Am I missing something? Thanks for your help! (Working with Mendix version 8.3.0) Edit 2019-11-20T1547 Funny thing, if I remove the `r.State` portion from the SELECT statement it works: SELECT COUNT(r.id) AS c FROM RequestFulfilment.Request AS r GROUP BY r.State yields [{"c":3}] .. but this doesn’t help, I need the `r.State` column to be retrieved otherwise the “count” wouldn’t be really helpful if I don’t know what got counted. Also, giving the `r.State` column an alias doesn’t help either. Even if the aliased name is a name of a column of the entity with the same data type where the current user also has access to.   I really need some help on this issue Edit 2019-11-22T1008 Maybe this helps, but this is the Java Action code I use: IOQLTextGetRequest request = Core.createOQLTextGetRequest(); request.setQuery(query); IContext context = getContext(); IDataTable data = Core.retrieveOQLDataTable(context, request); return JsonSerializer.serialize(context, data); .. this code seems to work otherwise, but maybe you see something which is not the way it should be. Also, I tested the group by clause with other columns, same issue. It seems to appear once you try to select the attribute you used in the group by. Edit 2019-11-25T0709 Incident for Mendix support created.
asked
3 answers
2

This is a very good post. I agree with Rom that you probably do not want to apply entity access when performing the query. If you really must do it for some reason I do not understand, please make sure to do a proper performance test before considering using this in production.

This is an interesting edge case on how you would expect entity access to work and how it actually does work. You would think that the query would first find all records that you have access to according to entity access (which is: all records), and then perform the group by and return the result.

But what seems to happen here is that the platform is only willing to return a result using an aggregate function (group by), if it is guaranteed that the user has access to everything that is aggregated over. Which the platform does not know is the case here. The thing is that you know the user has read access to all records, but the platform is unable to understand that all cases you cover in your entity access rules together actually cover all possible records that might exist.

I think the platform's behaviour should be considered a bug here, but I might be easily convinced otherwise. I would hope someone from mendix R&D is able to chime in on this.

answered
1

I would assume that the user has no access to the State attribute: since your count is not in your database, that should not throw a security exception. It even says this in your stack trace:

Read access denied for DataRow column 'State' (0)

Are you sure you need to run this action with ApplyEntityAccess set to Yes?

answered
1

I applied below to Microflow calling java action:
ApplyEntityAccess set to No

and it worked!!!!

answered