Hi, I have a question to "security filters". ...

[Christoph_Bucke]

Hi, I have a question to “security filters”. Assume I have a table with a column useremail which is used in the security filter [useremail]=USEREMAIL().

Is my understanding correct that the columns used in the condition of the security filter must be a key or a part of the key of the table? So my key in the table must always include the email?

Otherwise it could happen that an already existing record of another useremail will be overwritten with my content. This might happen if I select a key already existing for the other email.

Is this intentional?

I am asking because in my view it is very complex to always work with combined kyes.

Thanks

[Christoph_Bucke]

Could it not be checked during the save on the backend if the key is already in use for another user?

[Steven_Coile]

I do not believe the security filter expression must reference the row key. Nor does the security filter need to reference the email address, nor does the email address need to be key. I have a security filter that filters on a DateTime column that is not key.

It is possible that your app could allow the creation of a row that your security filter would then prevent from returning to the app, resulting in the silent overwrite of stored data.

[Aleksi_Alkio]

Discussion is needed so you are able to see your case out from the box. It’s always a good thing!

[Aleksi_Alkio]

@Steven_Coile is correct. You can filter the data with what ever real column.

[Christoph_Bucke]

thanks @Steven_Coile and @Aleksi_Alkio. I agree completely to your responses. I know that it is possible to use any real column for the security filters. I only wanted to confirm my understanding that

• if I want to avoid unplanned overwritings

• then I have to include the primary key in the security filter and this is what you both confirm. That is fine with me, my wish would be different.

I can therefore not simply take an app to convert it to multiuser / multiteam usage where the users/teams can add own data with potentially overlapping keys. My wish would be that for the backend process to store data the security filter is somehow considered to make app development less complex.

[Aleksi_Alkio]

Okay… if you can’t find any good key column value, the UNIQUEID() is always a safe option. Are you able to use it in your case?

[Christoph_Bucke]

I think even with UNIQUEID() you could have the (theoretical) case that the key overlaps.

The issue with UNIQUEID() is that I would need to change my structure a lot. Actually my apps are working with the mentioned complexity of having the security filter as part of the key, but maintenance will become more critical in future (if you do not remember in a few month, what you did before and why). My idea with my question was to verify whether I could simplify in future. I will think about uniqueid then whether its feasible for me.

[Aleksi_Alkio]

Well… the likelihood is VERY VERY small

[Christoph_Bucke]

@Aleksi_Alkio, I agree that the likelihoid is very small. Nevertheless the probability is not zero. Therfore I wonder what are use cases where this crazy bahaviour of overwiting existing data records (outside the rows filtered by the security filter) are wanted. I believe that you have non-deterministic results if the filter is not part of the primary key. Maybe someone from +AppSheet could comment? Thanks

[Aleksi_Alkio]

Basically the key value and security filter are two different things and they are working separately. You need to find a good combination for the key value… for example CONCATENATE(USEREMAIL(),NOW()) and you can be sure that it’s unique… not in theory if one user is using two devices at the same time, but yes in real life.

Because the security filter is working as a part of security mechanism and the filtering is happening on the server side, you can’t check if the record’s Key exists or not. You could create another table with the =UNIQUE(A2:A) formula and then check the Key against of that list, but it’s not bullet proof either if your data is not synced. So… back to the designing table and solve the unique key value.

[Christoph_Bucke]

@Aleksi_Alkio Hi Aleksi, it seems to me that we share the same view. It must be carefully designed how the key shall be defined if security filters are used. And my view was that the primary key somehow should be included in the rules for the security filter. My concern with my question is that it might be too complex to handle this in a secure way. So I asked for some more support of appsheet to guarantee that no records are overwritten without intention. because in case of a fault you will never find the root cause later. Thanks for discussion