@Freddy
This would require storing the encryption key in IAM, which is something that I would not recommend doing. Furthermore, this solution risks data loss as well, because it would require the key to constantly be deleted and recreated.
@Gabriela Andes
I understand what you are trying to achieve, but there is currently no secure way to do this. The SESSION_CONTEXT is not ideal for this purpose because it is cleared after every database command. It is important to be aware that Indicium uses connection pooling and that database connections are shared and reused by multiple users throughout their lifetime. There is no real concept of a ‘user session’ at the database level. It is for this reason that the SESSION_CONTEXT needs to be reset between commands. Freddy is correct that, at this moment, the only way to persist values in the SESSION_CONTEXT is to have the platform set these values and the only way to do this is through extended properties in IAM. However this is arguably less secure and definitely more risky than my original suggestion and it also allows system administrators to have, at the very least, temporary access to these encryption keys.
Your requirements call for some kind of mechanism where user-bound values are set in the SESSION_CONTEXT by Indicium, without being stored anywhere where system administrators might see them. Storing them in memory would not be good enough, because when scaling Indicium horizontally, the memory is not shared between different instances of Indicium. Storing them in a Redis cache or a KeyVault, as we would do for other data that needs to be shared between Indicium instances, would run into the same issue of system administrators having access to them.
The only solution I can think of is storing SESSION_CONTEXT parameters in cookies and having Indicium set them on the database connection every time. This would cause the encryption key to be stored by the browser (as cookies) and passed over the line between the browser and Indicium (as request headers), which, while not necessarily unsafe, does increase the attack surface. A solution like this would have to be thought out very carefully and it is questionable if it would ever be more secure than my original suggestion.
My strong recommendation remains my earlier suggestion of using our Encrypt connector. Please note that the encryption keys for this connector will not be stored in the database. They can be stored on a file system, in an Azure KeyVault or in an AWS Secret Manager. It is true that there must always be someone with access to these systems, but that is quite difficult to avoid when combined with your other requirements, such as using views for decryption. One solution could be to have separate administrators for an Azure KeyVault (for example) and the database.
I hope this helps.
