Checking Related User Performing Large Number of Queries

[kkoz]

When attempting to retrieve data about a particular user, certain fields of the Experimenter model are protected as "PrivilegedStrings"
https://github.com/ome/omero-model/blob/c6a8c91fc8114307989b4f85d9b4917d679303c7/src/main/resources/templates/object.vm#L1458C7-L1458C7
This filtering is implemented in omero-server in the PropertyFilterInitializer class

omero-server/src/main/java/ome/security/basic/PropertyFilterInitializer.java

Line 147 in 88aef0f

PrivilegedStringTypeDescriptor.setFilter(PrivilegedStringTypeDescriptor.Filter.RELATED_USER, this::isRelatedUser);

The main issue with this is that the implementation is such that when a user who isn't an admin or group owner needs to check if a different user is "related", a SQL query is executed for each PrivilegedString field, which is this case is 6 fields, resulting in 6*(# of users) queries being executed when doing these checks

omero-server/src/main/java/ome/security/basic/PropertyFilterInitializer.java

Lines 111 to 114 in 88aef0f

	final PreparedStatement statement = connection.prepareStatement(
	"SELECT 1 FROM GroupExperimenterMap m1 WHERE m1.child = ? AND m1.parent <> ? AND EXISTS " +
	"(SELECT 1 FROM GroupExperimenterMap m2 WHERE m2.child = ? AND m1.parent = m2.parent) AND EXISTS " +
	"(SELECT 1 FROM ExperimenterGroup g WHERE m1.parent = g.id AND g.permissions & 64 = 64)");

When the number of users and/or groups is large, this can result in performance issues.