column encryption support

[Venkat Sadasivam]

I want to encrypt a few sensitive data like social security number in
database.

Does jOOQ supports them? Looks like I can use custom data type (i.e.
converter) to do. Can you confirm?

Thanks,
Venkat

[Lukas Eder]

I have recently thought of this myself. It would be nice to be able to
encrypt data centrally through jOOQ.

You can implement this using Converters, indeed. But Converters currently
have a couple of design drawbacks. Assume a Converter<T, U> where T is the
database type (e.g. String) and U is the user type (e.g. EncryptedString).
jOOQ maintains a U -> T type registry with Class<U> keys. This means that
currently:

- Mappings are global, based on U types. I.e. you can only have a single
mapping per U type.
- Mappings are mostly type-based, although column-based mappings can
override global mappings. But column-based mappings are only available if a
column from the generated table is used.

What you would probably want is a column-based String -> String mapping.
This is currently not really possible, as such a String -> String converter
would:

- Be applied globally, to all String values
- Be applied in the wrong direction, as it is not clear which String is T
and which String is U

Post by Venkat Sadasivam
I want to encrypt a few sensitive data like social security number in
database.
Does jOOQ supports them? Looks like I can use custom data type (i.e.
converter) to do. Can you confirm?
Thanks,
Venkat
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/groups/opt_out.

[Venkat Sadasivam]

I can create a new class called StringCrypt and handle the encryption using
converter.

Post by Lukas Eder
I have recently thought of this myself. It would be nice to be able to
encrypt data centrally through jOOQ.
You can implement this using Converters, indeed. But Converters currently
have a couple of design drawbacks. Assume a Converter<T, U> where T is the
database type (e.g. String) and U is the user type (e.g. EncryptedString).
jOOQ maintains a U -> T type registry with Class<U> keys. This means that
- Mappings are global, based on U types. I.e. you can only have a single
mapping per U type.
- Mappings are mostly type-based, although column-based mappings can
override global mappings. But column-based mappings are only available if a
column from the generated table is used.
What you would probably want is a column-based String -> String mapping.
This is currently not really possible, as such a String -> String converter
- Be applied globally, to all String values
- Be applied in the wrong direction, as it is not clear which String is T
and which String is U

Post by Venkat Sadasivam
I want to encrypt a few sensitive data like social security number in
database.
Does jOOQ supports them? Looks like I can use custom data type (i.e.
converter) to do. Can you confirm?
Thanks,
Venkat
--
You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/groups/opt_out.

[Lukas Eder]

Hi Venkat,

That's good news! How did you implement it?

Post by Venkat Sadasivam
I can create a new class called StringCrypt and handle the encryption
using converter.

Post by Lukas Eder
I have recently thought of this myself. It would be nice to be able to
encrypt data centrally through jOOQ.
You can implement this using Converters, indeed. But Converters currently
have a couple of design drawbacks. Assume a Converter<T, U> where T is the
database type (e.g. String) and U is the user type (e.g. EncryptedString).
jOOQ maintains a U -> T type registry with Class<U> keys. This means that
- Mappings are global, based on U types. I.e. you can only have a single
mapping per U type.
- Mappings are mostly type-based, although column-based mappings can
override global mappings. But column-based mappings are only available if a
column from the generated table is used.
What you would probably want is a column-based String -> String mapping.
This is currently not really possible, as such a String -> String converter
- Be applied globally, to all String values
- Be applied in the wrong direction, as it is not clear which String is T
and which String is U

Post by Venkat Sadasivam
I want to encrypt a few sensitive data like social security number in
database.
Does jOOQ supports them? Looks like I can use custom data type (i.e.
converter) to do. Can you confirm?
Thanks,
Venkat
--
You received this message because you are subscribed to the Google
Groups "jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send
For more options, visit https://groups.google.com/**groups/opt_out<https://groups.google.com/groups/opt_out>
.

--

You received this message because you are subscribed to the Google Groups
"jOOQ User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an
For more options, visit https://groups.google.com/groups/opt_out.

[c***@gmail.com]

Hello Venkat,

Would you be able to share your implementation please? We are facing the
same issue and the converter approach seems like the cleanest, most
transparent of other options (like Envelope Encryption)

Thank you,

Sergio

Post by Lukas Eder
Hi Venkat,
That's good news! How did you implement it?