The need for securing data from unauthorized access is not new. It has been required by laws for handling personally identifiable information (PII) for quite a while. But the increasing use of data services in the cloud for all kinds of proprietary data that is not PII now makes data security an important part of most data strategies.

This is the start of a series of blog posts that take a detailed look at how data security can be ensured with Actian Vector. The first post explains the basic concept of encryption at rest and how Actian Vector’s Database Encryption functionality implements it.

Understanding Encryption at Rest

Encryption at rest refers to encryption of data at rest, which means data that is persisted, usually on disk or in cloud storage. This encryption can be used in a database system that is mainly user data in tables and indexes, but also includes the metadata describing the organization of the user data. The main purpose of encryption at rest is to secure the persisted data from unauthorized direct access on disk or in cloud storage, that is without a connection to the database system.

The encryption can be transparent to the database applications. In this case, encryption and decryption is managed by the administrator, usually at the level of databases. The application then does not need to be aware of the encryption. It connects to the database to access and work with the data as if there is no encryption at all. In Actian Vector, this type of encryption at rest is called database encryption.

Encryption at the application level, on the other hand, requires the application to handle the encryption and decryption. Often this means that the user of the application has to provide an encryption key for both, the encryption (e.g. when data is inserted) and the decryption (e.g. when data is selected). While more complicated, it provides more control to the application and the user.

For example, encryption can be applied more fine grained to specific tables, columns in tables, or even individual record values in table columns. It may be possible to use individual encryption keys for different data values. Thus, users can encrypt their private data with their own encryption key and be sure that without having this encryption key, no other user can see the data in clear text. In Actian Vector, encryption at the application level is referred to as function-based encryption.

Using Database Encryption in Actian Vector

In Actian Vector, the encryption that is transparent to the application works at the scope of a database and therefore is called database encryption. Whether a database is encrypted or not is determined with the creation of the database and cannot be changed later. When a database is created with database encryption, all the persisted data in tables and indexes, as well as the metadata for the database, is encrypted.

The encryption method is 256-bit AES, which requires a 32 byte symmetric encryption key. Symmetric means that the same key is used to encrypt and decrypt the data. This key is individually generated for each encrypted database and is called a database (encryption) key.

To have the database key available, it is stored in an internal system file of the database server, where it is protected by a passphrase. This passphrase is provided by the user when creating the database. However, the database key is not used to directly encrypt the user data. Instead, it is used to encrypt, i.e. protect, yet another set of encryption keys that in turn are used to encrypt the user data in the tables and indexes. This set of encryption keys is called table (encryption) keys.

Once the database is created, the administrator can use the chosen passphrase to “lock” the database. When the database is locked, the encrypted data cannot be accessed. Likewise, the administrator also uses the passphrase to “unlock” a locked database and thus re-enable access to the encrypted data. When the database is unlocked, the administrator can change the passphrase. If desired, it is also possible to rotate the database key when changing the passphrase.

The rotation of the database key is optional, because it means that the whole container of the table keys needs to be decrypted with the old database key to then re-encrypt it with the new database key. Because this container of the table keys also contains other metadata, it can be quite large and thus the rotation of the database key can become a slow and computationally expensive operation. Database key rotation therefore is only recommended if there is a reasonable suspicion that the database key was compromised. Most of the time, changing only the passphrase should be sufficient. And it is done quickly.

With Actian Vector it is also possible to rotate the table encryption keys. This is done independently from changing the passphrase and the database key, and can be performed on a complete database as well as on individual tables. For each key that is rotated, the data must be decrypted with the old key and re-encrypted with the new key. In this case, we are dealing with the user data in tables and indexes. If this data is very large, the key rotation can be very costly and time consuming. This is especially true when rotating all table keys of a database.

A typical workflow of using database encryption in Actian Vector:

• Create a database with encryption:

  1. 1. createdb -encrypt <database_name>

This command prompts the user twice for the passphrase and then creates the database with encryption. The new database remains unlocked, i.e. it is readily accessible, until it is explicitly locked or until shutdown of the database system.

It is important that the creator of the database remembers the provided passphrase because it is needed to unlock the database and make it accessible, e.g. after a restart of the database system.

• Lock the encrypted database:
  1. 1. Connect to the unlocked database with the Terminal Monitor:

        sql <database_name>

     2. SQL to lock the database:

        DISABLE PASSPHRASE '<user supplied passphrase>'; g

The SQL statement locks the database. New connect attempts to the database are rejected with a corresponding error. Sessions that connected previously can still access the data until they disconnect.

To make the database lock also immediately effective for already connected sessions, additionally issue the following SQL statement:

1. 1. 1. CALL X100(TERMINATE); g

• Unlock the encrypted database:
  1. 1. Connect to the locked database with the Terminal Monitor and option “-no_x100”:

        sql -no_x100 <database_name>

     2. SQL to unlock the database:

        ENABLE PASSPHRASE '<user supplied passphrase>'; g

The connection with the “-no_x100” option connects without access to the warehouse data, but allows the administrative SQL statement to unlock the database.

• Change the passphrase for the encrypted database:
  1. 1. Connect to the unlocked database with the Terminal Monitor:

        sql <database_name>

     2. SQL to change the passphrase:

        ALTER PASSPHRASE '<old user supplied passphrase>' TO
        '<new passphrase>'; g

Again, it is important that the administrator remembers the new passphrase.

After changing the passphrase for an encrypted database, it is recommended to perform a new database backup (a.k.a. “database checkpoint”) to ensure continued full database recoverability.

• When the database is no longer needed, destroy it:

  1. 1. destroydb <database_name>

Note that the passphrase of the encrypted database is not needed to destroy it. The command can only be performed by users with the proper privileges, i.e. the database owner and administrators.

This first blog post in the database security series explained the concept of encryption at rest and how transparent encryption — in Actian Vector called Database Encryption — is used.

The next blog post in this series will take a look at function-based encryption in Actian Vector.

The post Securing Your Data With Actian Vector appeared first on Actian.