Thursday, September 25, 2014

Enterprise Encryption Plugin

Before reading this blog please visit this link to understand how to install UDFs.

Basic introduction to cryptography

Cryptography is the study of techniques for secure communication. As far as computer science is concerned, below are the two major techniques of cryptography.

Symmetric Cryptography:

This is an area with set of methods where a single key is used by both the parties. This can be considered as a common secret and it is important to ensure that no other party gets possession of it. This type of cryptography is already implemented in MySQL through aes_encrypt function.

Public-key Cryptography:

This, which can also be called asymmetric cryptography, covers the methods which use a key pair for various functions. A pair contains a public key and a private key. Some basic properties:

1) Private key, as the name suggests, needs to stay with the party which
    generates this key. Public key on the other hand can be distributed to other

2) A private key can be used to generate its corresponding public key, but not
    vice versa.

Enterprise Encryption Plugin

There are many functions which openssl provides for asymmetric cryptography. This is a commercial extension which exposes those functions in a MySQL instance. You no longer have to rely on some external program to pick data from your tables, apply various functions on it and then insert it back. This extension does it all for you within MySQL instance.

This UDF provides the access to public-key cryptographic functions to the user through RSA, DSA and DH algorithms.

DSA algorithm works very similar to RSA except that few functions are not supported in the former. Please check the end notes for details.

Let us look at RSA and DH algorithms and see how we can use the functions supported by them.

For better understanding, I am taking the liberty of involving my friends Eric and Stan who are working on an application which badly needs some improvements in security. I will use these guys to demonstrate each function.


To use functions of any algorithm, Eric and Stan need a key pair. So Eric takes up this responsibility.

He uses CREATE_ASYMMETRIC_PRIV_KEY for creating a private key. This function takes two arguments. First argument takes algorithm as a string and second argument takes the key size in bits. Any key size less than 1024 is considered unsafe and the key will not be generated.

/* Returns private key as a string. */
CREATE_ASYMMETRIC_PRIV_KEY (algorithm varchar, key_length int);

This is how Eric creates a private key and stores it in @priv_key variable.

INTO @priv_key;

Eric has a private key now and he always keeps in his mind that he will never share his private key with anyone. It is private after all!

Nevertheless, he needs to supply Stan with a public key.

He can derive a public key using CREATE_ASYMMETRIC_PUB_KEY function. This function too takes two arguments. The first one holds the algorithm as a string and the second one holds the private key.

/* Returns public key as a string. */
CREATE_ASYMMETRIC_PUB_KEY (algorithm varchar,
                                                         private_key varchar);

This is how he does it:


Eric hands over the text which is produced here to Stan. Stan then safely stores the text in @pub_key variable in his MySQL instance.

Now that these guys have a public key and a private key, they cannot wait to use rest of the functions! Let us go ahead and see how they do it.


Eric and Stan want to send data to each other in such a way that no one else can read. ASYMMETRIC_ENCRYPT comes out as the best fit for the job!

/* Returns encrypted text as a string. */
ASYMMETRIC_ENCRYPT (algorithm varchar, plaintext varchar,
                                          key_pem varchar);

This function can be used to encrypt data using a private or public key (So both Eric and Stan can use it!). Maximum length of data we can encrypt depends on size of the key we are using. It is equal to 11 less than key size in bytes. In the example we are considering, the key size is 1024 bits i.e 128 bytes. So maximum length of data that can be encrypted is 128-11=117.

Eric has a private key. So this is how he encrypts text:

INTO @enc_priv;

Stan has a public key. He encrypts his message to Eric this way:

INTO @enc_pub;


/* Returns decrypted text as a string. */
ASYMMETRIC_DECRYPT (algorithm varchar, cipher varchar,
                                          key_pem varchar);

After the previous step, Eric got @enc_pub from Stan and Stan got @enc_priv from Eric.

Since Eric got some text which is encrypted with public key, he will decrypt it with his private key.

/* Returns 'Hi Eric! This is Stan!' */
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_pub, @priv_key);

Similarly, Stan will use his public key to read the text encrpyted using a private key.

/* Returns 'Hi Stan! This is Eric' */
SELECT ASYMMETRIC_DECRYPT('RSA', @enc_priv, @pub_key);


In another scenario, these guys are connected over a network and Eric keeps sending some information to Stan very often. But all of a sudden Stan began wondering if he is getting the information from proper source. For instance if Stan gets some message saying that it is from Eric, what is the guaranty that it is indeed from Eric? How can he be sure that there was no man in the middle attack?

They discussed this issue and came up with a solution. They agreed that now on, any information Eric is going to send, he will attach two extra things with it. A SHA2 hash and a signature derived on that hash with his private key.

Eric started thinking where he can get this SHA2 digest from. Don't worry Eric! There is a function for that too. CREATE_DIGEST it is!

/* Returns digest as a string. */
CREATE_DIGEST (digest_type varchar, plaint_text varchar);

So he creates a digest for the word 'South Park'. This function gives an option to create digest of type SHA224, SHA256, SHA384 or SHA512. For now he goes with SHA256.

SELECT CREATE_DIGEST('SHA256', 'South Park') INTO @sha256_digest;

Eric can now generate a signature from this digest using ASYMMETRIC_SIGN function.

/* Returns signature as a string. */
ASYMMETRIC_SIGN (algorithm varchar, digest varchar,
                                   private_key_pem varchar, digest_type varchar);

Applying this function on his digest he gets,

SELECT ASYMMETRIC_SIGN('RSA', @sha256_digest, @priv_key,
INTO @sign;


Stan has got a message and it says it is from Eric. As previously agreed upon, he searches for a signature and a hash. He uses both of them in ASYMMETRIC_VERIFY function.

/* Returns 1 on successful verification and 0 on failure. */
ASYMMETRIC_VERIFY (algorithm varchar, digest varchar,
                                       signature varchar, public_key_pem varchar,
                                       digest_type varchar);

He stores the digest in @sha256_digest and the signature in @sign and checks for its validity using the public key as below.

SELECT ASYMMETRIC_VERIFY('RSA', @sha256_digest, @sign,
                                                  @pub_key, 'SHA256');

It returned 1. So it is indeed from Eric.


RSA and DSA are asymmetric algorithms and are very costly in terms of time they take. When performance is an important factor in an application, it is better to go with symmetric algorithm instead.

Eric and Stan reached the same stage. The use of their application grew and time mattered. So they decided to go with symmetric encryption but were unable to come up with a method on deciding what the common key should be. DH (Diffie-Hellman) came to their rescue at that point.

DH is still an algorithm which uses asymmetric keys. How will it generate a common secret for them? In the below fashion.

Unlike the previous scenario, both Eric and Stan need to have their own public and private keys in this case. But before generating them, they need to go through an extra step i.e. creating parameters.

As always, Eric took this responsibility. He created parameters using CREATE_DH_PARAMETERS function. The only argument it takes is key size in bits. Just as RSA, it doesn't accept any value less than 1024.


Now he shares the contents of @dh_param with Stan. It is to be noted that TLS does not consider this as a part of secret and is passed over the wire.

When both Eric and Stan have @dh_param they can create the key pairs with the same functions used before. However, the function signature for CREATE_ASYMMETRIC_PRIV_KEY is different from that of RSA. Below is the function signature:

/* Returns DH private key as a string. */
CREATE_ASYMMETRIC_PRIV_KEY (algorithm varchar,
                                                          parameters varchar)

Eric's session:

Following are the steps run by Eric‥

INTO @priv_eric;

INTO @pub_eric;

Stan's session:

INTO @priv_stan;

INTO @pub_stan;

Although same parameter file is used for creation of private key, the function makes sure that the end product of CREATE_ASYMMETRIC_PRIV_KEY is completely different at each instance.

After the creation of keys, Eric and Stan share their public keys.

Once both the guys have their own respective private keys and other person's public key, they can generate a common secret at their own places using ASYMMETRIC_DERIVE function.

/* Returns symmetric key as a string. */
ASYMMETRIC_DERIVE(other_public_key_pem varchar, private_key_pem varchar);

Eric's session:

SELECT ASYMMETRIC_DERIVE(@pub_stan, @priv_eric)
INTO @sym_key_eric;

Stan's session:

SELECT ASYMMETRIC_DERIVE(@pub_eric, @priv_stan)
INTO @sym_key_stan;

If you check the contents of @sym_key_stan and @sym_key_eric, you will find that they are same! Since they generated common secret, they can use it as a key for symmetric encryption.

How does symmetric encryption work? I think Joro and Todd can help them better.

Few points to be noted:

1) As mentioned, 1024 is the minimum key size for generating keys. Below are
    the maximum values allowed:

    RSA - 16384
    DSA - 10000
    DH - 10000

2) All the keys created are in PEM format. All the keys provided as input in
    various function are also expected to be in PEM format.

3) DSA works very similar to RSA except that encryption and decryption are not
    supported in the former. To execute the supported functions, just replace the
    text RSA with DSA in above examples. Algorithms and functions they




4) PKCS1 padding is used for encryption and decryption.
5) Outputs at many instances are printed in hex format in this post. Please note
    that it is just to give a better readability. Outputs must always be retained in
    their original form for them to be useful in future.