Column Level Encryption - filtering and searching encrypted fields

Security… Encryption… Data sensitivity… Data protection… fancy expressions nowadays.

Customers are interested in how sensitive data can be managed and what are the provided solutions by ServiceNow. There are several possibilities offered by ServiceNow, but let's narrow down the topic a bit and focus on a specific detail (peculiarity), which is Data encryption. Nowadays it is a common and real expectation that a system must be able to encrypt and decrypt data. We can demonstrate this capability to the customers, but sometimes it is not sufficient for them. They are interested in the details, special possibilities or even limitations. This article is about to introduce a special limitation of the Column Level Encryption (CLE) module of ServiceNow.

The challenge

Usually, when somebody starts studying an area of ServiceNow, most of the time the Product Documentation is the barrier of entry. In the case of CLE you can read on the first page, that the search functionality is quite limited. People can "just accept" this limitation and move on, or you may ask the following question:

What can be the reason behind this functionality limitation??

Based on my experience, there are two types of customers:

• Who accepts the fact, that this is not a bug but a feature ??
• Who wants to understand the reason behind this limited capability and dig a bit deeper.

Let's dive in to understand the search functionality limitation, based on my research and personal opinion.

Encryption Scheme

I would highlight a very important definition, which we have to understand, before we jump into the details, this is called Encryption Scheme. This defines the settings which describe the encryption logic and the action which was done by the system during the encryption process. ServiceNow CLE supports two types of schemes:

• Non-deterministic encryption
• Equality preserving encryption

Important difference between the two schemes is the content of the encrypted data. In case of Non-deterministic encryption, the encrypted data will be different each time, when the algorithm is executed on the same text (or date). The Equality preserving encryption keeps the same encrypted text each time.

The picture above explains, that in case of Non-deterministic encryption, the encrypted text of XYZ will be different (“SnifgNi+uk0=”, “Dm=ghza+bbk2”), but using the Equality preserving encryption, it will be the same (“Btw+ddh1p0”).

Side note: The predefined encryption schemes are part of ServiceNow's Key Management Framework (KMF).

"Okay, it’s clear, but how is it connected to the filtering limitations?"

Maybe some of you have already known the answer, but let me go deeper.

To understand the reason, we have to get a clearer picture of how the CLE works in ServiceNow.

The encryption part of CLE is based on the KMF (remember Key Management Framework). This is responsible for encrypting and decrypting data. It is important, that these actions are executed on the application server side, not on a database level.

Before the content is committed to the database it will be encrypted. The decryption action happens after the data is pulled from the database and before it is shown to the user.

Use case

Let’s see a simple example. We have a field in the Incident table, which is called Special sensitive information. The content of this field is managed by the assignee. Only a small set of users (having the corresponding role) are able to see the content of this field.

When a user fills it out and submits the form, the content is sent from the browser to the application server. The KMF does the encryption action and executes the SQL operation (INSERT). So, the data will arrive to ServiceNow’s database in an encrypted format.

When the Incident is opened (by a person, who has rights to see this field), first the system will get the record from the database. The KMF executes the decryption action. The Business Rules / Client Scripts / Policies are executed and finally the form appears for the user and the content of the encrypted text will be visible. If the user does not have permissions to see the content, the field won’t be visible. In this case, if a business rule contains a logic related to the decrypted content of this field, it won’t work, because only the encrypted content is available.

Okay, we know how KMS does its job, but why is it important from a search point of view? The key is the Encryption Scheme. Remember, in case of non-deterministic encryption the encrypted text will be different in each time, but in case of equality preserving it will be the same.

Call to action

Let’s see how the search functionality works in ServiceNow. Somebody wants to find the Incidents where the content of the Short description is "Email server is down".

At first the system executes an SQL query to check the number of records based on the searching condition.

SELECT 
  count(*) AS recordcount 
FROM 
  task task0 
WHERE 
  task0.`sys_class_name` = 'incident' AND 
  task0.`short_description` = 'Email server is down.'        

It is simple, we want to see the exact matches so in the SQL query the ‘=’ character is used.

In the second use case, the user wants to see all records where the Short description contains the "Email" keyword. Now the generated SQL query looks a bit different.

SELECT 
  count(*) AS recordcount 
FROM 
  task task0 
WHERE 
  task0.`sys_class_name` = 'incident' AND 
  task0.`short_description` LIKE '%Email%'        

The content of Short description is not encrypted so it is very easy to execute the LIKE type search action on a database level. In case of an encrypted field, the system behaves a bit differently. First, we need to do some preparations. Let’s see how it works.

I created a new String type field in the Incident table, called Special sensitive information, then I setup the Field encryption configuration:

Once the Crypto module is selected, the value of Algorithm equality preserving checkbox is checked automatically.

Short detour - how it works

There is a client script called Update ‘Algorithm Equality Preserving’ which checks how the encryption key is setup in the selected Crypto module.?

If the value of Equality preserving field is true, the value of Algorithm equality preserving field on the Encrypted Field Configuration record will be true and read only.

In my case I have selected my own created Crypto module which is called va_test_module AES-256. The algorithm field of the Module key is AES 256 CBC where the value of Equality preserving is true.?

I have setup a Module Access Policy which will be used for this field's encryption. This policy describes that only users with itil_admin role are able to see the content of this field.

Going back to the topic

So, let’s open an Incident record and add content to our secure field.

After the KMF encrypts the data, it will be stored in the database in the following format:

???82975c14251021107f446e6b12eb27db? 1??0xrEGEbyzFHyhgBBqC_QAQ==??        

Now let’s do some searching. First, we use the exact match filter option like in the previous test case:

ServiceNow finds the record. The generated SQL query is similar like in the previous case, but the content appears in both plain text and encrypted format:

SELECT 
  count(*) AS recordcount 
FROM 
  task task0 
WHERE 
  task0.`sys_class_name` = 'incident' AND 
  (
    task0.`a_str_8` = 'Test content' OR 
    task0.`a_str_8` = '???82975c14251021107f446e6b12eb27db? 1??0xrEGEbyzFHyhgBBqC_QAQ==??'
  )        

We know how and in which encryption format the data is stored in the database, and exactly the same filter is executed so we got the expected result.

The next use case will be the "contains" type filter option.

There is no result. Is it the right behaviour? Let’s take a look at the generated SQL query as well.

SELECT 
  count(*) AS recordcount
FROM 
  task task0 
WHERE 
  task0.`sys_class_name` = 'incident' AND 
  (
    task0.`a_str_8` = 'Test' OR 
    task0.`a_str_8` = '???82975c14251021107f446e6b12eb27db? 1??Ck3HgV2vD-e-oJQK-w7UcQ==??'
  )        

There are two important findings in this use case:

• It can be seen, that the system ignores the “contains” operator and uses the ”is” (=) one
• It's visible in the generated SQL query, that the encrypted text is different. Our search term was "Test", which is the first word of the field's content "Test content", but the encrypted text will be totally different

Encrypted text of "Test content": 0xrEGEbyzFHyhgBBqC_QAQ==

Encrypted text of "Test": Ck3HgV2vD-e-oJQK-w7UcQ==

Because the encrypted text is different, we never find anything with the "LIKE" type search functionality and as we can see, the system doesn’t use it at all.

Results and Closing words

Now it’s clear that it doesn’t make sense to use the "contains" operator, because the search will not return any results. So it's clear now, why the filter options are limited in case of Column Level Encryption.

I hope you found this article interesting and got a deeper understanding of system behaviour in this case. The next time you come across this question hopefully you will be able to address customer concerns regarding today's topic.

Maybe my next article will be about Data protection (anonymisation) feature in ServiceNow.

#servicenow #encryption #search #filter