<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h3 class="title">
      <a name="enterprise-encryption-usage">
      </a>
      8.6.3 MySQL Enterprise Encryption Usage and Examples
     </h3>
    </div>
   </div>
  </div>
  <p>
   To use MySQL Enterprise Encryption in applications, invoke the functions that are
      appropriate for the operations you wish to perform. This section
      demonstrates how to carry out some representative tasks.
  </p>
  <p>
   MySQL Enterprise Encryption functions are provided by a MySQL component
   <code class="literal">
    component_enterprise_encryption
   </code>
   . For
      information about these functions, see
   <a class="xref" href="enterprise-encryption-function-reference.html" title="8.6.4 MySQL Enterprise Encryption Function Reference">
    Section 8.6.4, “MySQL Enterprise Encryption Function Reference”
   </a>
   .
  </p>
  <p>
   The following general considerations apply when choosing key
      lengths and encryption algorithms:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      The strength of encryption for private and public keys
          increases with the key size, but the time for key generation
          increases as well.
     </p>
    </li>
    <li class="listitem">
     <p>
      Component functions support RSA keys only.
     </p>
    </li>
    <li class="listitem">
     <p>
      Asymmetric encryption functions consume more resources
          compared to symmetric functions. They are good for encrypting
          small amounts of data and creating and verifying signatures.
          For encrypting large amounts of data, symmetric encryption
          functions are faster. MySQL Server provides the
      <a class="link" href="encryption-functions.html#function_aes-encrypt">
       <code class="literal">
        AES_ENCRYPT()
       </code>
      </a>
      and
      <a class="link" href="encryption-functions.html#function_aes-decrypt">
       <code class="literal">
        AES_DECRYPT()
       </code>
      </a>
      functions for
          symmetric encryption.
     </p>
    </li>
   </ul>
  </div>
  <p>
   Key string values can be created at runtime and stored into a
      variable or table using
   <a class="link" href="set-variable.html" title="15.7.6.1 SET Syntax for Variable Assignment">
    <code class="literal">
     SET
    </code>
   </a>
   ,
   <a class="link" href="select.html" title="15.2.13 SELECT Statement">
    <code class="literal">
     SELECT
    </code>
   </a>
   , or
   <a class="link" href="insert.html" title="15.2.7 INSERT Statement">
    <code class="literal">
     INSERT
    </code>
   </a>
   , as shown here:
  </p>
  <div class="copytoclipboard-wrapper">
   <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa62148874"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@priv1</span> <span class="token operator">=</span> <span class="token function">create_asymmetric_priv_key</span><span class="token punctuation">(</span><span class="token string">'RSA'</span><span class="token punctuation">,</span> <span class="token number">2048</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> <span class="token function">create_asymmetric_priv_key</span><span class="token punctuation">(</span><span class="token string">'RSA'</span><span class="token punctuation">,</span> <span class="token number">2048</span><span class="token punctuation">)</span> <span class="token keyword">INTO</span> <span class="token variable">@priv2</span><span class="token punctuation">;</span>
<span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> t <span class="token punctuation">(</span>key_col<span class="token punctuation">)</span> <span class="token keyword">VALUES</span><span class="token punctuation">(</span><span class="token function">create_asymmetric_priv_key</span><span class="token punctuation">(</span><span class="token string">'RSA'</span><span class="token punctuation">,</span> <span class="token number">1024</span><span class="token punctuation">)</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
  </div>
  <p>
   Key string values stored in files can be read using the
   <a class="link" href="string-functions.html#function_load-file">
    <code class="literal">
     LOAD_FILE()
    </code>
   </a>
   function by users who
      have the
   <a class="link" href="privileges-provided.html#priv_file">
    <code class="literal">
     FILE
    </code>
   </a>
   privilege. Digest and
      signature strings can be handled similarly.
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      <a class="xref" href="enterprise-encryption-usage.html#enterprise-encryption-usage-create-key-pair" title="Create a private/public key pair">
       Create a private/public key pair
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enterprise-encryption-usage.html#enterprise-encryption-usage-encrypt-decrypt" title="Use the public key to encrypt data and the private key to decrypt it">
       Use the public key to encrypt data and the private key to decrypt it
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enterprise-encryption-usage.html#enterprise-encryption-usage-create-digest" title="Generate a digest from a string">
       Generate a digest from a string
      </a>
     </p>
    </li>
    <li class="listitem">
     <p>
      <a class="xref" href="enterprise-encryption-usage.html#enterprise-encryption-usage-digital-signing" title="Use the digest with a key pair">
       Use the digest with a key pair
      </a>
     </p>
    </li>
   </ul>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enterprise-encryption-usage-create-key-pair">
       </a>
       Create a private/public key pair
      </h4>
     </div>
    </div>
   </div>
   <p>
    This example works with both the component functions and the
        legacy functions:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa23396150"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">-- Encryption algorithm</span>
<span class="token keyword">SET</span> <span class="token variable">@algo</span> <span class="token operator">=</span> <span class="token string">'RSA'</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Key length in bits; make larger for stronger keys</span>
<span class="token keyword">SET</span> <span class="token variable">@key_len</span> <span class="token operator">=</span> <span class="token number">2048</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">-- Create private key</span>
<span class="token keyword">SET</span> <span class="token variable">@priv</span> <span class="token operator">=</span> <span class="token function">create_asymmetric_priv_key</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token variable">@key_len</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Derive corresponding public key from private key, using same algorithm</span>
<span class="token keyword">SET</span> <span class="token variable">@pub</span> <span class="token operator">=</span> <span class="token function">create_asymmetric_pub_key</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token variable">@priv</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    You can use the key pair to encrypt and decrypt data or to sign
        and verify data.
   </p>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enterprise-encryption-usage-encrypt-decrypt">
       </a>
       Use the public key to encrypt data and the private key to decrypt it
      </h4>
     </div>
    </div>
   </div>
   <p>
    This example works with both the component functions and the
        legacy functions. In both cases, the members of the key pair
        must be RSA keys:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa29571998"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SET</span> <span class="token variable">@ciphertext</span> <span class="token operator">=</span> <span class="token function">asymmetric_encrypt</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token string">'My secret text'</span><span class="token punctuation">,</span> <span class="token variable">@pub</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SET</span> <span class="token variable">@plaintext</span> <span class="token operator">=</span> <span class="token function">asymmetric_decrypt</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token variable">@ciphertext</span><span class="token punctuation">,</span> <span class="token variable">@priv</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enterprise-encryption-usage-create-digest">
       </a>
       Generate a digest from a string
      </h4>
     </div>
    </div>
   </div>
   <p>
    This example works with both the component functions and the
        legacy functions:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa15033726"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">-- Digest type</span>
<span class="token keyword">SET</span> <span class="token variable">@dig_type</span> <span class="token operator">=</span> <span class="token string">'SHA512'</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">-- Generate digest string</span>
<span class="token keyword">SET</span> <span class="token variable">@dig</span> <span class="token operator">=</span> <span class="token function">create_digest</span><span class="token punctuation">(</span><span class="token variable">@dig_type</span><span class="token punctuation">,</span> <span class="token string">'My text to digest'</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
  <div class="simplesect">
   <div class="titlepage">
    <div>
     <div class="simple">
      <h4 class="title">
       <a name="enterprise-encryption-usage-digital-signing">
       </a>
       Use the digest with a key pair
      </h4>
     </div>
    </div>
   </div>
   <p>
    The key pair can be used to sign data, then verify that the
        signature matches the digest. This example works with both the
        component functions and the legacy functions:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa40540720"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">-- Encryption algorithm; keys must</span>
<span class="token comment" spellcheck="true">-- have been created using same algorithm</span>
<span class="token keyword">SET</span> <span class="token variable">@algo</span> <span class="token operator">=</span> <span class="token string">'RSA'</span><span class="token punctuation">;</span>
–<span class="token operator">-</span> Digest <span class="token keyword">algorithm</span> <span class="token keyword">to</span> sign the <span class="token keyword">data</span>
<span class="token keyword">SET</span> <span class="token variable">@dig_type</span> <span class="token operator">=</span> <span class="token string">'SHA512'</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">-- Generate signature for digest and verify signature against digest</span>
<span class="token keyword">SET</span> <span class="token variable">@sig</span> <span class="token operator">=</span> <span class="token function">asymmetric_sign</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token variable">@dig</span><span class="token punctuation">,</span> <span class="token variable">@priv</span><span class="token punctuation">,</span> <span class="token variable">@dig_type</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Verify signature against digest</span>
<span class="token keyword">SET</span> <span class="token variable">@verf</span> <span class="token operator">=</span> <span class="token function">asymmetric_verify</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token variable">@dig</span><span class="token punctuation">,</span> <span class="token variable">@sig</span><span class="token punctuation">,</span> <span class="token variable">@pub</span><span class="token punctuation">,</span> <span class="token variable">@dig_type</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    For the legacy functions, signatures require a digest. For the
        component functions, signatures do not require a digest, and can
        use any data string. The digest type in these functions refers
        to the algorithm that is used to sign the data, not the
        algorithm that was used to create the original input for the
        signature. This example is for the component functions:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa42100611"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token comment" spellcheck="true">-- Encryption algorithm; keys must</span>
<span class="token comment" spellcheck="true">-- have been created using same algorithm</span>
<span class="token keyword">SET</span> <span class="token variable">@algo</span> <span class="token operator">=</span> <span class="token string">'RSA'</span><span class="token punctuation">;</span>
–<span class="token operator">-</span> Arbitrary <span class="token datatype">text</span> <span class="token keyword">string</span> <span class="token keyword">for</span> signature
<span class="token keyword">SET</span> <span class="token variable">@text</span> <span class="token operator">=</span> <span class="token function">repeat</span><span class="token punctuation">(</span><span class="token string">'j'</span><span class="token punctuation">,</span> <span class="token number">256</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
–<span class="token operator">-</span> Digest <span class="token keyword">algorithm</span> <span class="token keyword">to</span> sign the <span class="token keyword">data</span>
<span class="token keyword">SET</span> <span class="token variable">@dig_type</span> <span class="token operator">=</span> <span class="token string">'SHA512'</span><span class="token punctuation">;</span>

<span class="token comment" spellcheck="true">-- Generate signature for digest and verify signature against digest</span>
<span class="token keyword">SET</span> <span class="token variable">@sig</span> <span class="token operator">=</span> <span class="token function">asymmetric_sign</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token variable">@text</span><span class="token punctuation">,</span> <span class="token variable">@priv</span><span class="token punctuation">,</span> <span class="token variable">@dig_type</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token comment" spellcheck="true">-- Verify signature against digest</span>
<span class="token keyword">SET</span> <span class="token variable">@verf</span> <span class="token operator">=</span> <span class="token function">asymmetric_verify</span><span class="token punctuation">(</span><span class="token variable">@algo</span><span class="token punctuation">,</span> <span class="token variable">@text</span><span class="token punctuation">,</span> <span class="token variable">@sig</span><span class="token punctuation">,</span> <span class="token variable">@pub</span><span class="token punctuation">,</span> <span class="token variable">@dig_type</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
  </div>
 </div>
 <br/>
</div>
