DECRYPT

Decrypts an encrypted column using the key(s) specified in the column encryption specification.

Syntax

DECRYPT ( column_expression )

Arguments

column-expression
Specifies a column belonging to a blockchain. The column must appear in the FROM clause of the SELECT statement, but is not required to appear in the SELECT list.

Return Value

Data of the column_expressions underlying data type.

Remarks

• Non-encrypted arguments will cause the query to error.
• If the login doesn't have access to the encryption key, the query will error.
• The DECRYPT function can be used in the SELECT, HAVING, and WHERE clauses.
• On creation the DECRYPT keyword can be used in the CHECK and COMPUTED column specifications.
• Columns with indexes can't be encrypted.
• Encrypted columns can't be compared without decrypting them first.

Examples

This section creates a blockchain and illustrates the functionality of the DECRYPT keyword.

The build

Lets assume that we have access to the following symmetric keys named: "k9_ssn key", "dog_name key", "verified key"

The CREATE BLOCKCHAIN statement below creates a TRADITIONAL blockchain with five columns:

• id: basic incrementing primary key.
• k9_ssn: an INT64 encrypted by the "ssn key" with LEVEL ONE validity tracking using the "k9_ssn key"
• last_name: a STRING encrypted by the "last_name key" with LEVEL TWO validity tracking. There is also a check constraint that checks, once decrypted, if the k9_ssn value is null.
• verified: a BOOL encrypted by the "verified key" with no validity tracking.
• loves_treats: a BOOL that is computed from the decrypted verified value.

CREATE BLOCKCHAIN members TRADITIONAL (
    id UINT64 PRIMARY AUTO INCREMENT, 
    k9_ssn INT64 ENCRYPT = "k9_ssn key" TRACK VALIDITY [ "k9_ssn key", LEVEL ONE],
    last_name STRING PACKED ENCRYPT = "last_name key" TRACK VALIDITY [ "last_name key", LEVEL TWO] CHECK [NOT(ISNULL(DECRYPT(ssn)))],
    verified BOOL ENCRYPT = "verified key",
    loves_treats BOOL COMPUTED [DECRYPT(verified))];

Let's assume the following mutation has been executed.

INSERT members (k9_ssn, last_name, verified) VALUES
        (1, "rolly", true),
        (2, "hank", false),
        (3, "clover", true); 

A. Querying from encrypted columns.

Reading from encrypted columns without decrypting returns columns of type BYTE ARRAY. The following query will execute but the k9_ssn, last_name, and verified columns will show the encrypted bytes.

SELECT * FROM members;

On the other hand, the below query fails when performing an operation on an encrypted column.

SELECT * FROM members WHERE verified = true

B. Decrypting in the select clause.

The following example decrypts the encrypted columns on output.

SELECT DECRYPT(k9_ssn), DECRYPT(last_name), DECRYPT(verified), loves_treats FROM members;

Response:

DECRYPT(k9_ssn) [INT64] DECRYPT(LAST_NAME) [STRING] DECRYPT(VERIFIED) [BOOL]    LOVES_TREATS [BOOL] 
1                       rolly                       true                        true    
2                       hank                        false                       false   
3                       clover                      true                        true    

C. Joining encrypted columns.

This example illustrates that the DECRYPT operation can be applied in the WHERE clause. Once the operation is applied, columns and data behave normally.

SELECT DECRYPT(m_one.last_name), DECRYPT(m_two.last_name)
        FROM members AS m_one JOIN members AS m_two
        ON DECRYPT(m_one.last_name) = DECRYPT(m_two.last_name);

Response:

DECRYPT(M_ONE.LAST_NAME) [STRING]   DECRYPT(M_TWO.LAST_NAME) [STRING]   
rolly                               rolly   
hank                                hank    
clover                              clover