Hibernate Encryption of Database Completely Transparent to Application

Question

I'm working on a Grails 1.0.4 project that has to be released in less than 2 weeks, and the customer just came up with a requirement that all data in the database should be encrypted.

Since encryption of every database access in the application itself could take a lot of time and will be error prone, the solution I seek is some kind of encryption transparent to the application.

Is there a way to setup Hibernate to encrypt all data in all tables (except maybie the id and version columns) or should I seek a MySQL solution (we're using MySQL 5.0) ?

EDIT: Thanks for all of your posts for alternative solutions, if the customer changes mind it would be great. As for now, the requirement is "No plain text in the Database".

Second thing I'd like to point out is that I'm using Grails, for those not fammiliar with it, It's a convention over configuration, so even small changes to the application that are not by convention should be avoided.

Please make sure you point out to the client the effect this will have on performance of absolutely everything in your application. we encrypt only the data like SSN and tax id that we legally must and it still slows things down when we need to insert into those fields or read them. To do so for every field in every table would be a huge performance hit. It's ok if they want to take that hit, but let them know it will probably make the site unacceptably slow for the users. Let them know now before you do it and have them sign off on that in writing just to protect your company. — HLGEM
– HLGEM, Commented Sep 29, 2009 at 14:48
Lots of luck getting this done and fully tested in less than 2 weeks. This is truly a months long project as it changes abosolutely everything about your application. — HLGEM
– HLGEM, Commented Sep 29, 2009 at 14:51
This sounds like a really bad idea. Think about all the implications when it comes to indexing and searching your database! — Kimble
– Kimble, Commented Sep 30, 2009 at 9:42

Burt Beckwith · Accepted Answer · 2009-09-28 20:05:28Z

If you end doing the work in the application, you can use Hibernate custom types and it wouldn't add that many changes to your code.

Here's an encrypted string custom type that I've used:

import org.hibernate.usertype.UserType
import org.apache.log4j.Logger

import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Types

class EncryptedString implements UserType {

  // prefix category name with 'org.hibernate.type' to make logging of all types easier
  private final Logger _log = Logger.getLogger('org.hibernate.type.com.yourcompany.EncryptedString')

  Object nullSafeGet(ResultSet rs, String[] names, Object owner) throws SQLException {
    String value = rs.getString(names[0])

    if (!value) {
      _log.trace "returning null as column: $names[0]"
      return null
    }

    _log.trace "returning '$value' as column: $names[0]"
    return CryptoUtils.decrypt(value)
  }

  void nullSafeSet(PreparedStatement st, Object value, int index) throws SQLException {
    if (value) {
      String encrypted = CryptoUtils.encrypt(value.toString())
      _log.trace "binding '$encrypted' to parameter: $index"
      st.setString index, encrypted
    }
    else {
      _log.trace "binding null to parameter: $index"
      st.setNull(index, Types.VARCHAR)
    }
  }

  Class<String> returnedClass() { String }

  int[] sqlTypes() { [Types.VARCHAR] as int[] }

  Object assemble(Serializable cached, Object owner) { cached.toString() }

  Object deepCopy(Object value) { value.toString() }

  Serializable disassemble(Object value) { value.toString() }

  boolean equals(Object x, Object y) { x == y }

  int hashCode(Object x) { x.hashCode() }

  boolean isMutable() { true }

  Object replace(Object original, Object target, Object owner) { original }
}

and based on this it should be simple to create similar classes for int, long, etc. To use it, add the type to the mapping closure:

class MyDomainClass {

  String name
  String otherField

  static mapping = {
    name type: EncryptedString
    otherField type: EncryptedString
  }
}

I omitted the CryptoUtils.encrypt() and CryptoUtils.decrypt() methods since that's not Grails-specific. We're using AES, e.g. "Cipher cipher = Cipher.getInstance('AES/CBC/PKCS5Padding')". Whatever you end up using, make sure it's a 2-way crypto, i.e. don't use SHA-256.

Jared · Accepted Answer · 2009-09-28 17:57:23Z

4

If the customer is worried about someone physically walking away with the hard drive then using a full disk solution like Truecrypt should work. If there worried about traffic being sniffed then take a look at this part of the mysql documentation on ssl over JDBC. Remember if someone compromises your server all bets are off.

answered Sep 28, 2009 at 17:57

Jared

40k29 gold badges116 silver badges146 bronze badges

Comments

longneck · Accepted Answer · 2009-09-28 16:03:31Z

2

the customer could easily do this without changing a thing in your application.

first, encrypt the communications between the server by turning on SSL in the mysql layer, or use an SSH tunnel.

second, store the mysql database on an encrypted volume.

any attack that can expose the filesystem of the mysql database or the credentials needed to log in to the mysql server is not mitigated by encrypting the data since that same attack can be used to retrieve the encryption key from the application itself.

answered Sep 28, 2009 at 16:03

longneck

12.3k3 gold badges39 silver badges49 bronze badges

Comments

Azder · Accepted Answer · 2010-01-30 15:10:14Z

Well it has been a long time since I've asked the question. In the meantime, thanks for all the answers. They were great when dealing with the original idea of encrypting the entire database, but the requirement changed to only encrypting sensitive user info, like name and address. So the solution was something like the code down below.

We've implemented an Encrypter which reads the encryption method from the record ( so there can be different encryption per record) and use it to connect transient duplicate fields to the ones encrypted in the database. The added bonus/drawbacks are:

The data is also encrypted in memory, so every access to the method getFirstName descrypts the data (I guess there is a way to cache decrypted data, but I dont need it in this case)

Encrypted fields cannot be used with default grails/hibernate methods for search through database, we've made custom methods in services that get data, encrypt it and then use the encrypted data in the where clause of a query. It's easy when using User.withCriteria

class User {

byte[] encryptedFirstName
byte[] encryptedLastName
byte[] encryptedAddress

Date dateCreated // automatically set date/time when created
Date lastUpdated // automatically set date/time when last updated

EncryptionMethod encryptionMethod = ConfigurationHolder.config.encryption.method

def encrypter = Util.encrypter

static transients = [ 
'firstName', 
'lastName', 
'address',
'encrypter'
]

static final Integer BLOB_SIZE = 1024

static constraints = {

    encryptedFirstName maxSize: BLOB_SIZE, nullable: false
    encryptedLastName maxSize: BLOB_SIZE, nullable: false

    encryptedAddress maxSize: BLOB_SIZE, nullable: true

    encryptionMethod nullable: false

} // constraints

String getFirstName(){
    decrypt('encryptedFirstName')
}

void setFirstName(String item){     
    encrypt('encryptedFirstName',item)
}

String getLastName(){
    decrypt('encryptedLastName')
}

void setLastName(String item){
    encrypt('encryptedLastName',item)       
}

String getAddress(){
    decrypt('encryptedAddress')
}

void setAddress(String item){
    encrypt('encryptedAddress',item)        
}

byte[] encrypt(String name, String value) {

    if( null == value ) {
        log.debug "null string to encrypt for '$name', returning null"
        this.@"$name" = null
        return
    }

    def bytes = value.getBytes(encrypter.ENCODING_CHARSET)
    def method = getEncryptionMethod()


    byte[] res 

    try {
        res = encrypter.encrypt( bytes, method )            
    } catch(e) {
        log.warn "Problem encrypting '$name' data: '$string'", e
    }

    log.trace "Encrypting '$name' with '$method' -> '${res?.size()}' bytes"

    this.@"$name" = res

}

String decrypt(String name) {

    if(null == this.@"$name") {
        log.debug "null bytes to decrypt for '$name', returning null"
        return null
    }

    def res 
    def method = getEncryptionMethod()

    try {
        res = new String(encrypter.decrypt(this.@"$name", method), encrypter.ENCODING_CHARSET )
    } catch(e) {
        log.error "Problem decrypting '$name'", e
    }

    log.trace "Decrypting '$name' with '$method' -> '${res?.size()}' bytes"

    return res
}

}

Miguel Ping · Accepted Answer · 2009-09-30 09:51:20Z

1

Another option is to use a JDBC driver that encrypts/decrypts data on the fly, two way. Bear in mind that any solution will probably invalidate searches by encrypted fields.

IMHO the best solution is the one proposed by longneck, it will make everything much easier, from administration to development. Besides, bear in mind that any solution with client-side encryption will render all your db data unusable outside of the client, ie, you will not be able to use nice tools like a jdbc client or MySQL query browser, etc.

edited Sep 30, 2009 at 9:51

answered Sep 28, 2009 at 17:38

Miguel Ping

18.4k23 gold badges92 silver badges137 bronze badges

1 Comment

HLGEM Over a year ago

" bear in mind that any solution will client-side encryption will render all your db data unusable outside of the client" Excellent point

Roshan Shrestha · Accepted Answer · 2009-10-05 17:24:49Z

1

Jasypt integrates with Hibernate: http://jasypt.org/hibernate3.html. However, queries which use WHERE clauses cannot be used

answered Oct 5, 2009 at 17:24

Roshan Shrestha

111 bronze badge

1 Comment

user2054927 Over a year ago

Queries using WHERE clause are possible when using a fixed salt

ChssPly76 · Accepted Answer · 2009-09-28 15:13:20Z

Generated ids, version, mapped foreign keys - basically everything maintained by Hibernate - are out unless you intend to declare custom CRUD for all of your classes and manually encrypt them in queries.

For everything else you've got a couple of choices:

@PostLoad and @PrePersist entity listeners will take care of all non-query operations.
Implementing custom String / Long / Integer / etc... types to handle encryption will take care of both query and CRUD operations; however the mapping will become rather messy.
You can write a thin wrapper around a JDBC driver (as well as Connection / Statement / PreparedStatement / ResultSet / etc...) to do the encryption for you.

As far as queries go you'll have to handle encryption manually (unless you're going with #2 above) but you should be able to do so via a single entry point. I'm not sure how (or if) Grails deals with this, but using Spring, for example, it would be as easy as extending HibernateTemplate.

Collectives™ on Stack Overflow

Hibernate Encryption of Database Completely Transparent to Application

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

Comments

Comments

Comments

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related