RDBMS Column Adapters : encrypting fields

A feature that DataNucleus has had since day 1 has recently been documented. Let’s take the example of having a class Person something like this

public class Person
{
    Long id;
    String name;
    ...
}

By default, when we persist a field of a class to RDBMS you will see an SQL statement like

INSERT INTO PERSON (`NAME`,ID) VALUES (<‘First’>, <1>);

where the <> arguments are JDBC parameters with the associated values shown. Similarly, retrieval of such objects will see an SQL statement like this

SELECT ‘mydomain.model.Person’ AS DN_TYPE, A0.ID, A0.`NAME` FROM PERSON A0

So we select the column that represents the field.

An RDBMS Column Adapter is useful where we want to adapt the value being stored in the database column, for example, to encrypt it.

Assuming we are using MariaDB as our datastore, we can encrypt the name field like this

@PersistenceCapable(detachable="true")
public class Person
{
    @PrimaryKey
    Long id;

    @Extension(vendorName="datanucleus", key="select-function", value="AES_DECRYPT(?, 'MyKey')")
    @Extension(vendorName="datanucleus", key="insert-function", value="AES_ENCRYPT(?, 'MyKey')")
    @Extension(vendorName="datanucleus", key="update-function", value="AES_ENCRYPT(?, 'MyKey')")
    String name;

    ...}

The equivalent annotations for JPA work equally well (in which case the Extension annotation is in package org.datanucleus.api.jpa.annotations).

So we have annotated the field to be encrypted with insert-function/update-function for use when storing the object, and select-function for use when retrieving the object. In this case the persist of the object will invoke the MariaDB function AES_ENCRYPT on the value of the field, and the retrieval will invoke the MariaDB function AES_DECRYPT on the value of the column. You can clearly choose a better encryption key than the one specified, maybe by having it present in the database instance. The SQL statement executed on persist is now

INSERT INTO PERSON (`NAME`,ID) VALUES (AES_ENCRYPT(<‘First’>, ‘MyKey’),<1>)

and on retrieval is

SELECT ‘mydomain.model.Person’ AS DN_TYPE,A0.ID,AES_DECRYPT(A0.`NAME`, ‘MyKey’) FROM PERSON A0

Clearly this idea is not limited to MariaDB, and could be used with PostgreSQL pgp_sym_encrypt/pgp_sym_decrypt for example, and the equivalent on any other RDBMS. Note also, that there are many encryption types available in today’s RDBMS, so do not take this as recommendation of the above function(s), just that you can use the method outlined here to take advantage of them.

 

Enjoy!

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s