A customer this week wanted to secure data by making sure the user attribute "inst" was equal to institution_id.
The issue is that, using a domain security file, it forced a join into all selects in order to make sure that the user attribute "inst" was equal to institution_id in the institution table.
The customer wanted a more efficient way to secure the data based on the attribute without always having to join on institution_id back to the institution table.
Here was a novel solution I found that creates a fake table with a single row that has the user attribute so the join is to a 'fake' table with the value of the user attribute as the only value in the table. This makes the domain queries faster since you don't have to join to the institution table now.
Export the domain design
Add the following just under resources:
<resources> <null id="constant_fields_level" datasourceId="AuditDataSource"> <fieldList> <field id="inst_attribute" dataSetExpression="str2int(groovy('Attribute("inst")'))" fieldDBName="inst_attribute" type="java.lang.Integer" /> </fieldList> </null>[/code]
This method creates a fake table that pulls in the “inst” attribute as a field of the 'fake' table and will have the value of the attribute. This negates the need for the institution table to be joined into every select for security. This should improve performance of the domain and allow you to join to this fake table to get the same security effect. Also, this allows the security to be applied without a seperate security file.