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:
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
parties.
generates this key. Public key on the other hand can be distributed to other
parties.
2) A
private key can be used to generate its corresponding public key, but
not
vice versa.
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.
RSA
To use functions of any algorithm, Eric and Stan need a key pair. So Eric takes up this responsibility.
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.
SELECT
CREATE_ASYMMETRIC_PRIV_KEY('RSA', 1024)
INTO @priv_key;
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:
SELECT
CREATE_ASYMMETRIC_PUB_KEY('RSA', @priv_key);
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.
ASYMMETRIC_ENCRYPT
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:
SELECT
ASYMMETRIC_ENCRYPT('RSA', 'Hi Stan! This is Eric!',
@priv_key)
INTO
@enc_priv;
Stan
has a public key. He encrypts his message to Eric this way:
SELECT
ASYMMETRIC_ENCRYPT('RSA', 'Hi Eric! This is Stan!',
@pub_key)
INTO @enc_pub;
@pub_key)
INTO @enc_pub;
ASYMMETRIC_DECRYPT
/*
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);
ASYMMETRIC_SIGN
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;
/*
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,
'SHA256')
INTO
@sign;
ASYMMTRIC_VERIFY
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.
DH
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.
SELECT
CREATE_DH_PARAMETERS(1024) INTO @dh_param;
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‥
SELECT
CREATE_ASYMMETRIC_PRIV_KEY('DH', @dh_param)
INTO @priv_eric;
SELECT
CREATE_ASYMMETRIC_PUB_KEY('DH',@priv_eric)
INTO @pub_eric;
Stan's
session:
SELECT
CREATE_ASYMMETRIC_PRIV_KEY('DH', @dh_param)
INTO @priv_stan;
SELECT
CREATE_ASYMMETRIC_PUB_KEY('DH',@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.
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
text RSA with DSA in above examples. Algorithms and functions they
support:
RSA - CREATE_ASYMMETRIC_PRIV_KEY, CREATE_ASYMMETRIC_PUB_KEY,
ASYMMETRIC_SIGN, ASYMMETRIC_VERIFY,
ASYMMETRIC_ENCRYPT, ASYMMETRIC_DECRYPT
DSA - CREATE_ASYMMETRIC_PRIV_KEY, CREATE_ASYMMETRIC_PUB_KEY,
ASYMMETRIC_SIGN, ASYMMETRIC_VERIFY
DH - CREATE_DH_PARAMETERS, CREATE_ASYMMETRIC_PRIV_KEY,
CREATE_ASYMMETRIC_PUB_KEY, ASYMMETRIC_DERIVE
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.
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.