Securing Your Data With Actian Vector, Part 2

Following up on my first blog post about Actian Vector’s database encryption capabilities, the next blog post in the series on data security explains function-based encryption in Actian Vector.

Function-Based Encryption in Actian Vector

The encryption that is completely controlled by a database application is called function-based encryption in Actian Vector. The application uses a pair of built-in SQL functions to encrypt and decrypt individual data values in table columns. Apart from applying these functions on the data as told by the SQL statements, the database server does not handle the encrypted data in any specific way. In particular, the database server is not capable of decrypting the encrypted data on its own.

The application specifies a passphrase with the encrypt function and needs to specify the same passphrase again with the decrypt function. The database server derives a symmetric encryption key from the given passphrase and uses this key to encrypt or decrypt the data. Neither the passphrase nor the derived encryption key are stored anywhere in the database server. The application is always responsible for supplying the correct passphrase to the encrypt and decrypt function.

Typically, the application chooses a passphrase and uses the encrypt function when inserting data values into a table. The data gets encrypted based on the chosen passphrase and then stored. When accessing the stored data, either during select or update, the application uses the decrypt function with the same chosen passphrase to get the data in cleartext. Accessing encrypted data without the decrypt function simply retrieves the encrypted data, which normally is not very useful.

The application can specify different passphrases with individual invocations of the encrypt and decrypt functions. For example, different database users can each use their own passphrase to insert data with encryption into the same table. The users can be sure that no other user can see the cleartext of their own data. Or, an application can encrypt data for different table columns with different passphrases, and provide the different passphrases to different sets of users. That way, one group of users can see the cleartext data only in one column whereas another group of users can see the cleartext of data in the other column.

The encrypt function can be applied multiple times with different passphrases on the same data. To access the data in cleartext, the same passphrases must be used in reverse order to decrypt the data. When updating encrypted data, the decrypt function is applied first, the cleartext value is updated, then the encrypt function is used to newly encrypt the updated data. These are just a few examples of the endless possibilities to combine the use of the encrypt and decrypt functions, all controlled by the application.

All the function-based encryption is done by using the crypto functions in regular SQL statements. This also means that the cleartext passphrases are provided to the crypto functions in the SQL statements. Proper use of function-based encryption therefore should take care that the SQL statements are kept sufficiently secure, e.g. that they are not traced or made visible in some SQL command history.

Also, communication between the application and the database server should be secured to avoid leaking the passphrases. With this regard, the “sql” Terminal Monitor utility is not really a secure example of an application because the typed-in cleartext passphrases in the SQL statements are visibly echoed in the terminal Window.

Examples of Function-Based Encryption:

• Definition of the table used in the following examples:

CREATE TABLE t1 (name CHAR(20), salary VARCHAR(50)); \g

Even though the salary is a numerical value, the “salary” column is defined as a sufficiently large character column to accommodate the bytes of the encrypted value.

• Insert data with encryption into a table:

INSERT INTO t1 values ('Joe', AES_ENCRYPT_IV(40000, 'Secret passphrase')); \g
INSERT INTO t1 values ('Adam', AES_ENCRYPT_IV(38000, 'Secret passphrase')); \g
INSERT INTO t1 values ('CEO', AES_ENCRYPT_IV(350000, 'Super passphrase')); \g

The first two statements encrypt the salary value based on the passphrase ‘Secret passphrase’, whereas the third statement uses the different passphrase ‘Super passphrase’.

• Selecting encrypted data:

1. 1. Select without using the decrypt function:

SELECT name, salary FROM t1; \g
name          salary
Joe           XnE_msRe1?Uh]!cUUerQn4opT(C[!)O~yTrzE%JS
Adam          lyr*M%o53]O(5d$2`!3a:Bg$zW40wMySO5[-E7+u
CEO           a*#eE!~p(1-euLx?K$w(2)@FHk+dzM`5}Q#=lD@g
(3 rows)

The encrypted data in the “salary” column is shown encrypted and therefore meaningless. The length of the output for the salary column demonstrates that encrypted data usually takes more space than the corresponding cleartext values.

1. 2. Select using the decrypt function with the first passphrase:

SELECT name, AES_DECRYPT_IV(salary, 'Secret passphrase') FROM t1; \g

name          col2
Joe           40000
Adam          38000
CEO                  \020���x���
(3 rows)

The values encrypted with the first passphrase are correctly decrypted. The value encrypted with a different passphrase is not shown in cleartext.

1. 3. Select a specific data record using its correct passphrase:

SELECT name, AES_DECRYPT_IV(salary, 'Super passphrase')
  FROM t1 WHERE name = 'CEO'; \g

name		col2
CEO		350000
(1 row)

The encrypted value is shown in cleartext.

When selecting encrypted data, the decrypt function needs the correct passphrase to show the cleartext. When using different passphrases for different data records, it is better to use a WHERE clause that restricts the result to those data records encrypted with the passphrase specified in the SELECT.

• Updating encrypted data:

When updating encrypted data, the data needs to be decrypted and usually should be encrypted again after the update. For this, both functions, decrypt and encrypt, are used in the same UPDATE statement. At the same time it is also possible to change the passphrase.

UPDATE t1 SET salary = AES_ENCRYPT_IV(
AES_DECRYPT_IV(salary, 'Super passphrase') + 20000,
'Secret passphrase' ) WHERE name = 'CEO'; \g

The statement adds 20000 to the salary of ‘CEO’ and at the same time changes the passphrase to ‘Secret passphrase’. The decrypt function requires the original passphrase and the encrypt function is provided with the new passphrase. As the new passphrase is the same one that was used to encrypt the salary of the other two data records, a SELECT using this passphrase now can show the cleartext for all records:

SELECT name, AES_DECRYPT_IV(salary, 'Secret passphrase') FROM t1; \g

name          col2
Joe           40000
Adam          38000
CEO           370000
(3 rows)

The next blog post in this series will explain how encryption keys for the database encryption are used in Actian Vector.

About Martin Fuerderer

Martin Fuerderer is a Principal Software Engineer for HCLSoftware. He has worked in many different areas of database server development over the last 25 years. In recent years, he has dived into the topic of security in database servers.