If all you want to do is encrypt and decrypt columns in a database for your Grails application, look no further than the Jasypt Encryption plugin.  I have used it in the past to persist PII (personally identifiable information).  However, if you need to access the same data from outside your application, then you need to leverage the built in database encryption functions.  This allows the DBAs to use the built in functions and secret to access data for reports and other functions.

To do this, I created a user defined type that mimics the MySQL column encryption by looking at how Jasypt worked.  Sigh, this meant that I had to reverse engineer how MySQL does its column encryption via aes_encrypt() and aes_decrypt().  Of course the MySQL implementation is not standard, so it took some research online to come up with code that looks like this:

package com.wnx.crypt;

import org.apache.commons.codec.DecoderException;
import org.apache.commons.lang.ObjectUtils;
import org.hibernate.HibernateException;
import org.hibernate.engine.spi.SessionImplementor;
import org.hibernate.type.BinaryType;
import org.hibernate.usertype.UserType;
import org.springframework.beans.factory.annotation.Value;

import javax.crypto.BadPaddingException;
import javax.crypto.Cipher;
import javax.crypto.IllegalBlockSizeException;
import javax.crypto.NoSuchPaddingException;
import javax.crypto.spec.SecretKeySpec;
import java.io.Serializable;
import java.io.UnsupportedEncodingException;
import java.security.InvalidKeyException;
import java.security.NoSuchAlgorithmException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

/**
 * Created by mamsterla on 4/9/16.
 */
public class MysqlCryptType implements UserType {

    @Value("${customer.key}")
    public static String customerKey = "*secret*really*";

    public static SecretKeySpec generateMySQLAESKey(final String key, final String encoding) {
        try {
            final byte[] finalKey = new byte[16];
            int i = 0;
            for(byte b : key.getBytes(encoding))
                finalKey[i++%16] ^= b;
            return new SecretKeySpec(finalKey, "AES");
        } catch(UnsupportedEncodingException e) {
            throw new RuntimeException(e);
        }
    }

    public static String decrypt(String key, byte[] string) throws NoSuchAlgorithmException, NoSuchPaddingException, InvalidKeyException, DecoderException, IllegalBlockSizeException, BadPaddingException {
        return new String(decryptBytes(key, string));
    }

    public static byte[] decryptBytes(String key, byte[] string) throws NoSuchAlgorithmException, NoSuchPaddingException, InvalidKeyException, DecoderException, IllegalBlockSizeException, BadPaddingException {
        final Cipher decryptCipher = Cipher.getInstance("AES");
        decryptCipher.init(Cipher.DECRYPT_MODE, generateMySQLAESKey(key, "UTF-8"));
        return decryptCipher.doFinal(string);
    }

    public static byte[] encrypt(String key, byte[] string) throws NoSuchAlgorithmException, NoSuchPaddingException, InvalidKeyException, IllegalBlockSizeException, BadPaddingException {
        final Cipher encryptCipher = Cipher.getInstance("AES");
        encryptCipher.init(Cipher.ENCRYPT_MODE, generateMySQLAESKey(key, "UTF-8"));
        return encryptCipher.doFinal(string);
    }

    @Override
    public int[] sqlTypes() {
        return new int[] { BinaryType.INSTANCE.sqlType() };
    }

    @Override
    public Class returnedClass() {
        return String.class;
    }

    @Override
    public boolean equals(Object x, Object y) throws HibernateException {
        return ObjectUtils.equals(x, y);
    }

    @Override
    public int hashCode(Object x) throws HibernateException {
        assert (x != null);
        return x.hashCode();
    }

    @Override
    public Object nullSafeGet(ResultSet rs, String[] names, SessionImplementor session, Object owner) throws HibernateException, SQLException {
        byte[] value = (byte[]) BinaryType.INSTANCE.nullSafeGet(rs, names, session, owner);
        if (value != null) {
            try {
                assert (customerKey != null);
                return decrypt(customerKey, value);
            } catch (InvalidKeyException | NoSuchPaddingException | NoSuchAlgorithmException | IllegalBlockSizeException | BadPaddingException | DecoderException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public void nullSafeSet(PreparedStatement st, Object value, int index, SessionImplementor session) throws HibernateException, SQLException {
        byte[] encryptedValue = null;
        if (value != null && value instanceof String) {
            byte[] valueArray = ((String) value).getBytes();
            try {
                assert (customerKey != null);
                encryptedValue = encrypt(customerKey, valueArray);
            } catch (InvalidKeyException | NoSuchAlgorithmException | BadPaddingException | IllegalBlockSizeException | NoSuchPaddingException e) {
                e.printStackTrace();
            }
            if (encryptedValue != null) {
                BinaryType.INSTANCE.nullSafeSet(st, encryptedValue, index, session);
                return;
            }
        }
        BinaryType.INSTANCE.nullSafeSet(st, new byte[1], index, session);
    }

    @Override
    public Object deepCopy(Object value) throws HibernateException {
        return value;
    }

    @Override
    public boolean isMutable() {
        return false;
    }

    @Override
    public Serializable disassemble(Object value) throws HibernateException {
        return (Serializable) value;
    }

    @Override
    public Object assemble(Serializable cached, Object owner) throws HibernateException {
        return cached;
    }

    @Override
    public Object replace(Object original, Object target, Object owner) throws HibernateException {
        return original;
    }
}

I developed a simple test domain object to verify that this all works:

package com.wnx

import com.wnx.crypt.MysqlCryptType

class CryptTest {

 String mytext

 static constraints = {
 }

 static mapping = {
 mytext type: MysqlCryptType
 }
}

Testing was tough to automate – what I did was verify manually that the saved data could be manually retrieved with a database query like:

select aes_decrypt(mytext, "*secret*really*") from crypt_test limit 1;

Here is what the queries looked like:

mysql> select * from CryptTest;

+----+---------+------------------+----------------------------------+

| id | version | mydate           | mytext                           |

+----+---------+------------------+----------------------------------+

|  8 |       0 | j@B?????{?X????           | CtDR??,?I?

??5?)8eٍ???a?            |

|  9 |       0 | ?_?? ??^?J???)           | K?(?e?਑J?J? ?                         |

+----+---------+------------------+----------------------------------+

2 rows in set (0.00 sec)

The test of decryption to verify that the GORM encryption is the same as the built-in MySQL aes_decrypt looks like this:

mysql> select aes_decrypt(mytext, '*secret*really*') from CryptTest;

+------------------------------------------+

| aes_decrypt(mytext, '*secret*really*')   |

+------------------------------------------+

| More Text is great                       |

| MyTextIsGreat                            |

+------------------------------------------+

2 rows in set (0.01 sec)

If you need to protect columns with encryption, but also want to be able to externally access the table data using built in MySQL functions, this is the way to go!