Solved

SESSION_CONTEXT(N'guid') to be used globally.

  • 8 September 2023
  • 9 replies
  • 179 views
Gabriela Andes
Rank
Badge

Hi, I would like to know if it is possible to use the generated session variable: SESSION_CONTEXT(N'guid') globally, and the guid will not be stored in the database.

We are creating an encryption & decryption task that will use the generated SESSION_CONTEXT(N'guid') of the logged in user as the password for their certificate and key. 

We have tried using the SESSION_CONTEXT(N'guid') on its own but upon generating the guid of the user this is the guid that was returned: (only 1s and 0s). We also tried logging in a different user and checking the GUID but the result is still the same.

 

With that, we tried a different approach and created our own session_context (i.e SESSION_CONTEXT(N'dbo.tsf_user()')) inside a process flow. This new session_context will hold a generated guid using newid(). But we encountered an issue with this approach since the SESSION_CONTEXT(N'dbo.tsf_user()') is only accessible for that specific process flow and if we tried to call it in another process flow it returns a null value.

Is there a way or a needed configuration for the SESSION_CONTEXT(N'guid') to display a different GUID for each tsf_user()? 

Thanks!

icon

Best answer by Vincent Doppenberg 12 September 2023, 14:16

View original
This topic has been closed for comments

9 replies

Freddy
Userlevel 5
Badge +14

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
Session.[session_variable] Universal GUI Indicium Indicium Basic Add session variables. A guid session variable is available by default to uniquely identify a user session. You can use the value of a session variable in your SQL business logic or prefilters using the SESSION_CONTEXT function, e.g. select session_context(n'guid').

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

LEF - The future favors the brave. [lef.tec.br]
V
Rank
Userlevel 6
Badge +4

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.

Gabriela Andes
Rank
Badge

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
Session.[session_variable] Universal GUI Indicium Indicium Basic Add session variables. A guid session variable is available by default to uniquely identify a user session. You can use the value of a session variable in your SQL business logic or prefilters using the SESSION_CONTEXT function, e.g. select session_context(n'guid').

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

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.

Gabriela Andes
Rank
Badge

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.

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.

Freddy
Userlevel 5
Badge +14

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

 

 

LEF - The future favors the brave. [lef.tec.br]
V
Rank
Userlevel 6
Badge +4

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

Freddy
Userlevel 5
Badge +14

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

LEF - The future favors the brave. [lef.tec.br]
Gabriela Andes
Rank
Badge

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.

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? 

 

Mark Jongeling
Rank
Userlevel 7
Badge +21

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)

"Wake me up before you low-code"
Terms & ConditionsCookie settings