Skip to main content

Hi all,

I have a requirement for which I need to encrypt and decrypt column data for a specific table or view. The application is on Universal GUI.

  1. The user needs to click
    • a task button
    • provide the password
    • save it in the session cookie.
  1. When the user will want to see the decrypted information of the specific encrypted related View.
    • A function will need to have access to the session cookie to retrieve the entered password of the user.
    • This view will utilize the function to decrypt the column values upon refreshing the document.
  2. If No Password OR Wrong password was entered the view will show the encrypted information. 

 

Any other idea how to implement something like this differently would be useful.

 

*Note: The database is already encrypted and that protects the data from outside theft, however it does not protect the data from Administrators within the company or people that have dattabase access. This solution is for covering the later part.


Edit:

Below functionality of Azure SQL will be used:

 

--Create fist a certificate -- Self signed
CREATE CERTIFICATE <certificate name>   
   ENCRYPTION BY PASSWORD = <my password>  
   WITH SUBJECT = <subject>,   
   EXPIRY_DATE = <expiry date>;  
GO  
--Verify certificate creation
select * from sys.certificates 

--Create a symmetric Key --Use self signed certificate
CREATE SYMMETRIC KEY <testing key >
WITH ALGORITHM = AES_256 
ENCRYPTION BY CERTIFICATE <certificate name>
GO

--Verify the symmetric key creation
select * from sys.symmetric_keys

-- Open the symmetric key with which to encrypt the data.  
OPEN SYMMETRIC KEY <testing key >  
   DECRYPTION BY CERTIFICATE <certificate name> WITH PASSWORD = <my password>;  

Example select:
​​​​​​​
select    non_encrypted_column_in_table
        ,EncryptByKey(Key_GUID( <testing_key>), convert(nvarchar(100),non_encrypted_column_in_table)) as EncryptedCompName
        ,convert(nvarchar(100),DecryptByKey(EncryptByKey(Key_GUID(<testing_key>), convert(nvarchar(100),non_encrypted_column_in_table)))) as DecryptedCompName  
from my_table  
 

Hi Michalis,

In our oppinion an application running on the client is per definition unsave. Indicium and the HTTPS connection between them make it save. It is not save to share a the encryption key spread over all available client with a high change of leaking it somewhere.

When stored save in the database, indeed even your admins shouldn't get access to the encrypted data, not knowing the encryption key eigther.

Our latest platform release offers possibilities to do so. You can read about it on our docs:
https://docs.thinkwisesoftware.com/docs/sf/process_flows_actions#encrypt
https://docs.thinkwisesoftware.com/docs/deployment/indicium_encryption

It is fine to send a typed password once, unencrytped over the secured netwerk connection to Indicium, were Indicium will take care of the encryption, before storing it in the database savely.

Ps. Indicium is stateless, so beside a authentication cookie, there is not something like a session cookie. A session cookie isn't required to store it safely, if you ask me.

Is this bringing you somewhere?

Kind regards,
Erik Brink

Hi @mperrott,

Has Erik's reply helped you out?

Hi Mark, Erik,

I saw the encrypt and decrypt process flow actions and also the options for the storage of the encryption keys. It will help in encrypting based on the key used in my case it will be on Azure and seems my open case the admins will not be able to see the data directly in the database is solved.

So I can protect the app with:

  1. The overall DB from outside threads by having it encrypted entirely.
  2. I can have the above to protect from Admins seeing directly in DB for sensitive data.
  3. I can have the related IAM permissions in such a way that only authorised people can see sensitive data.

Question:

  1. The key and encryption is handled by the IAM so there is no need for User Password or anything like that as I see from the actions themselves right ?
  1. Will a simulated authorized user to sensitive data from an admin, Will the Admin have visibility on the sensitive data? or there is a setting that can prevent that?

Hi @Mark Jongeling , @Erik Brink ,

 

Additional question:

If encryption/decryption is within the process flows as process actions, how can it be used in Views with multiple fields to encrypt/decrypt? Are there available functions for the same ?

Hello mperrot,

I don't think that the encrypt and decrypt process actions are very suited to your use case. The data that is encrypted with the encrypt process action can only be decrypted inside of a process flow with a decrypt process action. So you could create something like a ‘Reveal’ task which decrypts data in a selected record and shows the decrypted data in a task dialog where someone could copy the values, for example.

Seamless decryption in a table or view when it is presented in the client, while remaining encrypted at rest, is not something that we support right now. Furthermore, the encrypt and decrypt process actions are not connected to sessions in any way.

So to answer your earlier questions:

  1. Correct. Key management is entirely the responsibility of Indicium and cannot be influenced from the outside in any way. Data from different sessions is encrypted with the same key. The encryption key does have an expiration date after which it will turn into a decryption-only key and a new key will be generated, but the key is global.
  2. If an admin simulates a user then this admin can decrypt the same data that the user can decrypt, by calling a process flow with a decrypt process action. Every user with access to the record and the process flow (i.e. no prefilter hiding the record, etc.) can decrypt the data.

I can think of some solutions that approach what you are looking for, but I would rather not recommend anything here, as none of them are without flaws. If you can elaborate on your use case, perhaps we can think of a fitting solution. Otherwise I would recommend posting an Idea for this. It is very challenging to build this yourself in a way that it truly secure.

Hi @Vincent Doppenberg ,

Thank you so much for the reply and for clarifying my questions. 


I would tottaly use the provided encryption/decryption from the platform if the 2 process flow actions could very well exist as functions to be used in the sql  template of the views.

 

So my case would be like below:
Security Concerns & Problems to resolve:

  1. Overall DB encryption (Implemented) - protects from Outside threads.
  2. IAM User Groups (Implemented) - protects from Un-authorised Users internaly to get sensitive data.
  3. Custom Row Based Authorization (Implemented) - Additional level to TW roles/user groups as row based permissions. Certain records are accessed by specific people by means of tagging mechanism.
  4. Column based encryption/decryption (related to this question topic) - Required to protect from internal Administrators having access to Database directly.

Pre-requisites:

  1. A form/grid page displays sensitive data (multiple fields) for which we want to encrypt from people not authorised to view them.
  2. Authorized users can edit and see the sensitive data decrypted. User providing a password which is shared among the authorised people, I know it is sketchy but it will be Users responsibility to keep it safe.
  3. Un-Authorized users should not be able to view/edit the sensitive data.

Solution A:

  1. Change the related tables/views fields to related nvarchar(xxxx) datatype.
  2. Create a mechanism to track user access as session to save it into a table X for a pre-defined time. 
  3. Have a process flow to remove any expired sessions from table X.
  4. Create a task with a process flow using TW encryption process action and store it (We avoid asking the user in each key press for entering a password) either in:
    1. in table X for the session ?
    2. in browser cookie
  5. When activating/opening a document View with sensitive data we show the decrypted data if the user provided a valid password or encrypted if invalid. We use the standard sql functions as mentioned above to open the key and encrypt / decrypt but in order to do that we need to decrypt the password via a process flow and pass it to the view standard sql functions to further decrypt the sensitive data.

Problems of Solution A:

  1. Storing the password somewhere relatively safe for the session.
  2. Passing if storing encrypted password in the DB the decrypted onto the View to further decrypt the sensitive data.

Solution B:

  1. Change the related tables/views fields to related nvarchar(xxxx) datatype.
  2. Provided we have the encrypt/decrypt as standard functions we could use those in the views and IOF to view and store data upon modification.
  3. No password would be necessary.

 

Problems of Solution B:

  1. We do not have encrypt/decrypt as standard functions of Indicium.
  2. An Admin could get access to the Azure stored Key and use it to decrypt the data ?

@Vincent Doppenberg hope I did not overcomplicate and it is somewhat clear what we try to achieve.

I will open an idea for the Solution B to have the encrypt/decrypt as standard functions of Indicium. will post link here.

Any other idea please suggest on the what and how to implement.

Thank you in advance.

Idea opened related to the above.

 

 

Terms & ConditionsCookie settings