I don't think this is possible using TSQL. 

In theory you would I guess be able to store as session variable dynamically in the IAM extended property for the application during the process flow (don't forget to de-set it at the end).. via de IAM API endpoint 

/gui_appl({model_id},{branch_id},{gui_appl_id})/detail_ref_gui_appl_gui_appl_extended_property

https://docs.thinkwisesoftware.com/docs/sf/extended_properties

Hello Gabriella,

I would strongly recommend against attempting to implement your own encryption/decryption mechanism and especially one that uses a GUID, which is not intended to be cryptographically strong per se, as the password. The only technical requirement imposed on the guid session variable is uniqueness, but it is no stronger than an identity value.

For encrypting and decrypting, I would recommend using our Encrypt and Decrypt connectors. For limiting availability of data to specific users, I would recommend using Data authorization prefilters based on tsf_user(). For limiting availability of data to specific session, I would recommend adding an additional data authorization prefilter on session_context(n'guid').

With this solution, the encryption itself will not be specific to any user or session, but that doesn’t need to be a problem if the encrypted value can only be accessed by that user/session in the first place. What’s important is that the data is encrypted at rest and in transit with a cryptographically strong key. The user will still access the data with a password by virtue of the data being tied to their account and the fact that they need to log in to reach it.

I hope this helps.

/gui_appl({model_id},{branch_id},{gui_appl_id})/detail_ref_gui_appl_gui_appl_extended_property

Hi Freddy, 
We tried this approach already and added/overwritten the value of a session_context inside a process flow. But the problem we faced is that the data for that session_context is only available on the specific process flow it was created. We will be using this in a view, so we are finding a way to transfer the session_context data from one process flow to another without saving it in the database.

Hi Vincent,

We have already added a pre-filter based on the tsf_user() and we also tried using the Encrypt and Decrypt connectors but it is not advisable in our project, since system administrators might be able to access it. This is why we want to use the session_context(n'guid') in storing the password, because we don’t need to store it in the database. Additionally, we are also using the views for encryption and decryption so we are finding a way to transfer the data of the session_context per tsf_user() from one process flow to another without storing it in the database.

Hi Gabriela, 

The idea is not to overwrite the SESSSION_CONTEXT yourself but let the platform do it by storing a temporary extended property per user session.GUID whereas the platform will persist the session variable. Just don't forget to destroy the session variable when it’s no longer needed.

@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.

@Vincent Doppenberg agreed, but I am reading this with a lot of interest, as we have a running opportunity for a system that would require this as well, a user set encryption key preferably not to be stored in a DB but in a key vault or to be provided by the user to fetch encrypted data, do something with it and encrypt it again for storage. 

Besides the data filter, which I would always recommend in this scenario, one of other requirements is that some user-entered info is always stored encrypted via a key only known by the user, which can never be stored in the system itself.. just in the user-session to be used to en/decrypt.  

Hi Freddy,

We tried to create a new extended property called Session.GUID but it didn’t return any guid value just a null value. I’ve also tested adding a value under the extended property (newid()) but it didn’t generate a new guid and only returned the string “newid()” is there a way to set a sql function value under the extended property? 

We tried to create a new extended property called Session.GUID but it didn’t return any guid value just a null value. I’ve also tested adding a value under the extended property (newid()) but it didn’t generate a new guid and only returned the string “newid()” is there a way to set a sql function value under the extended property? 

No this does not work. Extended properties are a fixed set of values.

The answer Vincent has given is the most complete answer, hence it is marked as best answer.

(Topic closed to prevent to come up with solutions that we do not advise)