Skip to content
logo
Percona Server for MySQL
Encryption functions
Initializing search
    percona/psmysql-docs
    percona/psmysql-docs
    • Home
      • The Percona XtraDB storage engine
      • List of features available in Percona Server for MySQL releases
      • Percona Server for MySQL feature comparison
      • Changed in Percona Server 8.0
      • Understand version numbers
      • Install Percona Server for MySQL
        • Install using APT repository
        • Files in DEB package
        • Build APT packages
        • Install from downloaded DEB packages
        • Apt pinning
        • Run Percona Server for MySQL
        • Uninstall
        • Install using a RPM repository
        • Files in RPM package
        • Install from downloaded RPM packages
        • Run Percona Server for MySQL
        • Uninstall
        • Install with binary tarballs
        • Binary tarballs available
        • Install Percona Server for MySQL from a source tarball
        • Compile Percona Server for MySQL 8.0 from source
      • Post-installation
      • Percona Server for MySQL in-place upgrading guide: from 5.7 to 8.0
      • Upgrade using the Percona repositories
      • Upgrade from systems that use the MyRocks or TokuDB storage engine and partitioned tables
      • Upgrade using Standalone Packages
      • Running Percona Server for MySQL in a Docker Container
      • Docker environment variables
      • Improved InnoDB I/O scalability
      • Adaptive network buffers
      • Multiple page asynchronous I/O requests
      • Thread pool
      • XtraDB performance improvements for I/O-bound highly-concurrent workloads
      • Prefix index queries optimization
      • Limit the estimation of records in a Query
      • Jemalloc memory allocation profiling
      • The ProcFS plugin
      • Binary logs and replication improvements
      • Compressed columns with dictionaries
      • Extended SELECT INTO OUTFILE/DUMPFILE
      • Extended SET VAR optimizer hint
      • Improved MEMORY storage engine
      • Suppress warning messages
      • Limiting the disk space used by binary log files
      • Support for PROXY protocol
      • SEQUENCE_TABLE(n) function
      • Slow query log rotation and expiration
      • Trigger updates
      • Extended mysqlbinlog
      • Extended mysqldump
      • InnoDB full-text search improvements
      • Too many connections warning
      • Handle corrupted tables
      • Percona Toolkit UDFs
      • Kill idle transactions
      • XtraDB changed page tracking
      • Enforcing storage engine
      • Expanded fast index creation
      • Backup locks
      • Audit log plugin
      • Start transaction with consistent snapshot
      • Extended SHOW GRANTS
      • Utility user
      • Working with SELinux
      • Working with AppArmor
      • PAM authentication plugin
      • Server variables
      • SSL improvements
      • Data masking
        • Using LDAP authentication plugins
        • LDAP authentication plugin system variables
        • Data at Rest Encryption
        • Use the keyring component or keyring plugin
          • Using the Key Management Interoperability Protocol (KMIP)
          • Use the Amazon Key Management Service (AWS KMS)
          • FIDO authentication plugin
        • Encryption functions
          • Install component_encryption_udf
          • User-defined functions described
          • asymmetric_decrypt(algorithm, crypt_str, key_str)
            • Returns
            • Parameters
          • asymmetric_derive(pub_key_str, priv_key_str)
            • asymmetric_derive output
            • asymmetric_derive parameters
          • asymmetric_encrypt(algorithm, str, key_str)
            • asymmetric_encrypt output
            • asymmetric_encrypt parameters
          • asymmetric_sign(algorithm, digest_str, priv_key_str, digest_type)
            • asymmetric_sign output
            • asymmetric_sign parameters
          • asymmetric_verify(algorithm, digest_str, sig_str, pub_key_str, digest_type)
            • asymmetric_verify output
            • asymmetric_verify parameters
          • create_asymmetric_priv_key(algorithm, (key_len | dh_parameters))
            • create_asymmetric_priv_key output
            • create_asymmetric_priv_key parameters
          • create_asymmetric_pub_key(algorithm, priv_key_str)
            • create_asymmetric_pub_key output
            • create_asymmetric_pub_key parameters
          • create_dh_parameters(key_len)
            • create_dh_parameters output
            • create_dh_parameters parameters
          • create_digest(digest_type, str)
            • create_digest output
            • create_digest parameters
            • Encryption threshold variables
            • encryption_udf.dh_bits_threshold
            • encryption_udf.dsa_bits_threshold
            • encryption_udf.rsa_bits_threshold
            • Examples
          • Uninstall component_encryption_udf
        • Encrypt file-per-table tablespace
        • Encrypt a schema or a general tablespace
        • Encrypt the system tablespace
        • Encrypt temporary files
        • Encrypt binary log files and relay log files
        • Encrypting the Redo Log data
        • Encrypt the undo tablespace
        • Rotate the Master key
        • Work with Advanced Encryption Key Rotation
        • Encrypt doublewrite buffers
        • Verify the encryption for tables, tablespaces, and schemas
      • User statistics
      • Slow query log
      • Extended show engine InnoDB status
      • Show storage engines
      • Process list
      • Misc. INFORMATION_SCHEMA tables
      • Thread based profiling
      • InnoDB page fragmentation counters
      • Stacktrace
      • Libcoredumper
      • Manage group replication flow control
      • Group replication system variables
      • Percona MyRocks introduction
      • Percona MyRocks installation guide
      • Updated supported features
      • MyRocks limitations
      • Differences between Percona MyRocks and Facebook MyRocks
      • MyRocks Information Schema tables
      • MyRocks server variables
      • MyRocks status variables
      • Gap locks detection
      • Data loading
      • Installing and configuring Percona Server for MySQL with ZenFS support
      • TokuDB introduction
      • TokuDB installation
      • Use TokuDB
      • Fast updates with TokuDB
      • TokuDB files and file types
      • TokuDB file management
      • TokuDB background ANALYZE TABLE
      • TokuDB variables
      • TokuDB status variables
      • TokuDB fractal tree indexing
      • TokuDB troubleshooting
      • TokuDB Performance Schema integration
      • Frequently asked questions
      • Migrate and removing the TokuDB storage engine
      • Percona TokuBackup
      • Release notes index
      • Percona Server for MySQL 8.0.30-22 Update (2022-11-21)
      • Percona Server for MySQL 8.0.30-22 (2022-11-21)
      • Percona Server for MySQL 8.0.29-21 (2022-08-08)
      • Percona Server for MySQL 8.0.28-20 (2022-06-20)
      • Percona Server for MySQL 8.0.28-19 (2022-05-12)
      • Percona Server for MySQL 8.0.27-18 (2022-03-02)
      • Percona Server for MySQL 8.0.26-17 (2022-01-26)
      • Percona Server for MySQL 8.0.26-16 (2021-10-20)
      • Percona Server for MySQL 8.0.25-15 (2021-07-13)
      • Percona Server for MySQL 8.0.23-14 (2021-05-12)
      • Percona Server for MySQL 8.0.22-13 (2020-12-14)
      • Percona Server for MySQL 8.0.21-12 (2020-10-13)
      • Percona Server for MySQL 8.0.20-11 (2020-07-21)
      • Percona Server for MySQL 8.0.19-10 (2020-03-23)
      • Percona Server for MySQL 8.0.18-9
      • Percona Server for MySQL 8.0.17-8
      • Percona Server for MySQL 8.0.16-7
      • Percona Server for MySQL 8.0.15-6
      • Percona Server for MySQL 8.0.15-5
      • Percona Server for MySQL 8.0.14
      • Percona Server for MySQL 8.0.13-4
      • Percona Server for MySQL 8.0.13-3
      • Percona Server for MySQL 8.0.12-2rc1
      • List of upstream MySQL bugs fixed in Percona Server for MySQL 8.0
      • List of variables introduced in Percona Server for MySQL 8.0
      • Development of Percona Server for MySQL
      • Trademark policy
      • Index of INFORMATION_SCHEMA tables
      • Frequently asked questions
      • Copyright and licensing information
      • Glossary

    • Install component_encryption_udf
    • User-defined functions described
    • asymmetric_decrypt(algorithm, crypt_str, key_str)
      • Returns
      • Parameters
    • asymmetric_derive(pub_key_str, priv_key_str)
      • asymmetric_derive output
      • asymmetric_derive parameters
    • asymmetric_encrypt(algorithm, str, key_str)
      • asymmetric_encrypt output
      • asymmetric_encrypt parameters
    • asymmetric_sign(algorithm, digest_str, priv_key_str, digest_type)
      • asymmetric_sign output
      • asymmetric_sign parameters
    • asymmetric_verify(algorithm, digest_str, sig_str, pub_key_str, digest_type)
      • asymmetric_verify output
      • asymmetric_verify parameters
    • create_asymmetric_priv_key(algorithm, (key_len | dh_parameters))
      • create_asymmetric_priv_key output
      • create_asymmetric_priv_key parameters
    • create_asymmetric_pub_key(algorithm, priv_key_str)
      • create_asymmetric_pub_key output
      • create_asymmetric_pub_key parameters
    • create_dh_parameters(key_len)
      • create_dh_parameters output
      • create_dh_parameters parameters
    • create_digest(digest_type, str)
      • create_digest output
      • create_digest parameters
      • Encryption threshold variables
      • encryption_udf.dh_bits_threshold
      • encryption_udf.dsa_bits_threshold
      • encryption_udf.rsa_bits_threshold
      • Examples
    • Uninstall component_encryption_udf

    Encryption functions¶

    Percona Server for MySQL 8.0.28-20 adds encryption functions and variables to manage the encryption range. The functions may take an algorithm argument. Encryption converts plaintext into ciphertext using a key and an encryption algorithm.

    You can also use the user-defined functions with the PEM format keys generated externally by the OpenSSL utility.

    A digest uses plaintext and generates a hash value. This hash value can verify if the plaintext is unmodified. You can also sign or verify on digests to ensure that the original plaintext was not modified. You cannot decrypt the original text from the hash value.

    When choosing key lengths, consider the following:

    • Encryption strength increases with the key size and, also, the key generation time.

    • If performance is important and the functions are frequently used, use symmetric encryption. Symmetric encryption functions are faster than asymmetric encryption functions. Moreover, asymmetric encryption has restrictions on the maximum length of a message being encrypted. For example, for RSA the algorithm maximum message size is the key length in bytes (key length in bits / 8) minus 11.

    The following table and sections describe the functions. For examples, see function examples.

    Function Name
    asymmetric_decrypt(algorithm, crypt_str, key_str)
    asymmetric_derive(pub_key_str, priv_key_str)
    asymmetric_encrypt(algorithm, str, key_str)
    asymmetric_sign(algorithm, digest_str, priv_key_str, digest_type)
    asymmetric_verify(algorithm, digest_str, sig_str, pub_key_str, digest_type)
    create_asymmetric_priv_key(algorithm, (key_len | dh_parameters))
    create_asymmetric_pub_key(algorithm, priv_key_str)
    create_dh_parameters(key_len)
    create_digest(digest_type, str)

    The following table describes the Encryption threshold variables which can be used to set the maximum value for a key length based on the type of encryption.

    Variable Name
    encryption_udf.dh_bits_threshold
    encryption_udf.dsa_bits_threshold
    encryption_udf.rsa_bits_threshold

    Install component_encryption_udf¶

    Use the Install Component Statement to add the component_encryption_udf component. The functions and variables are available. The user-defined functions and the Encryption threshold variables are auto-registered. There is no requirement to invoke CREATE FUNCTION ... SONAME ....

    The INSERT privilege on the mysql.component system table is required to run the INSTALL COMPONENT statement. To register the component, the operation adds a row to this table.

    The following is an example of the installation command:

    mysql> INSTALL COMPONENT 'file://component_encryption_udf';
    

    Note

    If you are Compiling Percona Server for MySQL from Source, the Encryption UDF component is built by default when Percona Server for MySQL is built. Specify the -DWITH_ENCRYPTION_UDF=OFF cmake option to exclude it.

    User-defined functions described¶

    asymmetric_decrypt(algorithm, crypt_str, key_str)¶

    Decrypts an encrypted string using the algorithm and a key string.

    Returns¶

    A plaintext as a string.

    Parameters¶

    The following are the function’s parameters:

    • algorithm - the encryption algorithm supports RSA to decrypt the string.

    • key_str - a string in the PEM format. The key string must have the following attributes:

    • Valid

    • Public or private key string that corresponds with the private or public key string used with the asymmetric_encrypt function.

    asymmetric_derive(pub_key_str, priv_key_str)¶

    Derives a symmetric key using a public key generated on one side and a private key generated on another.

    asymmetric_derive output¶

    A key as a binary string.

    asymmetric_derive parameters¶

    The pub_key_str must be a public key in the PEM format and generated using the Diffie-Hellman (DH) algorithm.

    The priv_key_str must be a private key in the PEM format and generated using the Diffie-Hellman (DH) algorithm.

    asymmetric_encrypt(algorithm, str, key_str)¶

    Encrypts a string using the algorithm and a key string.

    asymmetric_encrypt output¶

    A ciphertext as a binary string.

    asymmetric_encrypt parameters¶

    The parameters are the following:

    • algorithm - the encryption algorithm supports RSA to encrypt the string.

    • str - measured in bytes. The length of the string must not be greater than the key_str modulus length in bytes - 11 (additional bytes used for PKCS1 padding)

    • key_str - a key (either private or public) in the PEM format

    asymmetric_sign(algorithm, digest_str, priv_key_str, digest_type)¶

    Signs a digest string using a private key string.

    asymmetric_sign output¶

    A signature is a binary string.

    asymmetric_sign parameters¶

    The parameters are the following:

    • algorithm - the encryption algorithm supports either RSA or DSA to encrypt the string.

    • digest_str - the digest binary string that is signed. Invoking create_digest generates the digest.

    • priv_key_str - the private key used to sign the digest string. The key must be in the PEM format.

    • digest_type - the OpenSSL version installed on your system determines the available hash functions. The following table lists these functions:

      OpenSSL 1.0.2 OpenSSL 1.1.0 OpenSSL 1.1.1 OpenSSL 3.0.x
      md5 md5 md5 md5
      sha1 sha1 sha1 sha1
      sha224 sha224 sha224 sha224
      sha384 sha384 sha384 sha384
      sha512 sha512 sha512 sha512
      md4 md4 md4 md4
      sha md5-sha1 md5-sha1 md5-sha1
      ripemd160 ripemd160 ripemd160 sha512-224
      whirlpool whirlpool sha512-224 sha512-256
      blake2b512 sha512-256 sha3-224
      blake2s256 whirlpool sha3-256
      sm3 sha3-384
      blake2b512 sha3-512
      blake2s256
      sha3-224
      sha3-384
      sha3-512
      shake128
      shake256

    asymmetric_verify(algorithm, digest_str, sig_str, pub_key_str, digest_type)¶

    Verifies whether the signature string matches the digest string.

    asymmetric_verify output¶

    A 1 (success) or a 0 (failure).

    asymmetric_verify parameters¶

    The parameters are the following:

    • algorithm - supports either ‘RSA’ or ‘DSA’.

    • digest_str - invoking create_digest generates this digest binary string.

    • sig_str - the signature binary string. Invoking asymmetric_sign generates this string.

    • pub_key_str - the signer’s public key string. This string must correspond to the private key passed to asymmetric_sign to generate the signature string. The string must be in the PEM format.

    • digest_type - the supported values are listed in the digest type table of create_digest

    create_asymmetric_priv_key(algorithm, (key_len | dh_parameters))¶

    Generates a private key using the given algorithm and key length for RSA or DSA or Diffie-Hellman parameters for DH. For RSA or DSA, if needed, execute KILL [QUERY|CONNECTION] <id> to terminate a long-lasting key generation. The DH key generation from existing parameters is a quick operation. Therefore, it does not make sense to terminate that operation with KILL.

    create_asymmetric_priv_key output¶

    The key as a string in the PEM format.

    create_asymmetric_priv_key parameters¶

    The parameters are the following:

    • algorithm - the supported values are ‘RSA’, ‘DSA’, or ‘DH’.

    • key_len - the supported key length values are the following:

      • RSA - the minimum length is 1,024. The maximum length is 16,384.

      • DSA - the minimum length is 1,024. The maximum length is 9,984.

      Note

      The key length limits are defined by OpenSSL. To change the maximum key length, use either encryption_udf.rsa_bits_threshold or encryption_udf.dsa_bits_threshold.

    • dh_parameters - Diffie-Hellman (DH) parameters. Invoking create_dh_parameter creates the DH parameters.

    create_asymmetric_pub_key(algorithm, priv_key_str)¶

    Derives a public key from the given private key using the given algorithm.

    create_asymmetric_pub_key output¶

    The key as a string in the PEM format.

    create_asymmetric_pub_key parameters¶

    The parameters are the following:

    • algorithm - the supported values are ‘RSA’, ‘DSA’, or ‘DH’.

    • priv_key_str - must be a valid key string in the PEM format.

    create_dh_parameters(key_len)¶

    Creates parameters for generating a Diffie-Hellman (DH) private/public key pair. If needed, execute KILL [QUERY|CONNECTION] <id> to terminate the generation of long-lasting parameters.

    Generating the DH parameters can take more time than generating the RSA keys or the DSA keys. OpenSSL defines the parameter length limits. To change the maximum parameter length, use encryption_udf.dh_bits_threshold.

    create_dh_parameters output¶

    A string in the PEM format and can be passed to create_asymmetric_private_key.

    create_dh_parameters parameters¶

    The parameters are the following:

    • key_len - the range for the key length is from 1024 to 10,000. The default value is 10,000.

    create_digest(digest_type, str)¶

    Creates a digest from the given string using the given digest type. The digest string can be used with asymmetric_sign and asymmetric_verify.

    create_digest output¶

    The digest of the given string as a binary string

    create_digest parameters¶

    The parameters are the following:

    • digest_type - the OpenSSL version installed on your system determines the available hash functions. The following table lists these functions:

      OpenSSL 1.0.2 OpenSSL 1.1.0 OpenSSL 1.1.1 OpenSSL 3.0.x
      md5 md5 md5 md5
      sha1 sha1 sha1 sha1
      sha224 sha224 sha224 sha224
      sha384 sha384 sha384 sha384
      sha512 sha512 sha512 sha512
      md4 md4 md4 md4
      sha md5-sha1 md5-sha1 md5-sha1
      ripemd160 ripemd160 ripemd160 sha512-224
      whirlpool whirlpool sha512-224 sha512-256
      blake2b512 sha512-256 sha3-224
      blake2s256 whirlpool sha3-256
      sm3 sha3-384
      blake2b512 sha3-512
      blake2s256 sm3
      sha3-224 blake2b512
      sha3-384 blake2s256
      sha3-512 blake2b512
      shake128 blake2s256
      shake256
    • str - String used to generate the digest string.

    Encryption threshold variables¶

    The maximum key length limits are defined by OpenSSL. Server administrators can limit the maximum key length using the encryption threshold variables.

    The variables are automatically registered when component_encryption_udf is installed.

    Variable Name
    encryption_udf.dh_bits_threshold

    encryption_udf.dh_bits_threshold¶

    The variable sets the maximum limit for the create_dh_parameters user-defined function and takes precedence over the OpenSSL maximum length value.

    Option Description
    command-line Yes
    scope Global
    data type unsigned integer
    default 10000

    The range for this variable is from 1024 to 10,000. The default value is 10,000.

    encryption_udf.dsa_bits_threshold¶

    The variable sets the threshold limits for create_asymmetric_priv_key user-defined function when the function is invoked with the DSA parameter and takes precedence over the OpenSSL maximum length value.

    Option Description
    command-line Yes
    scope Global
    data type unsigned integer
    default 9984

    The range for this variable is from 1,024 to 9,984. The default value is 9,984.

    encryption_udf.rsa_bits_threshold¶

    The variable sets the threshold limits for the create_asymmetric_priv_key user-defined function when the function is invoked with the RSA parameter and takes precedence over the OpenSSL maximum length value.

    Option Description
    command-line Yes
    scope Global
    data type unsigned integer
    default 16384

    The range for this variable is from 1,024 to 16,384. The default value is 16,384.

    Examples¶

    Code examples for the following operations:

    • set the threshold variables

    • create a private key

    • create a public key

    • encrypt data

    • decrypt data

    -- Set Global variable
    mysql> SET GLOBAL encryption_udf.dh_bits_threshold = 4096;
    
    -- Set Global variable
    mysql> SET GLOBAL encryption_udf.rsa_bits_threshold = 4096;
    
    -- Create private key
    mysql> SET @private_key = create_asymmetric_priv_key('RSA', 3072);
    
    -- Create public key
    mysql> SET @public_key = create_asymmetric_pub_key('RSA', @private_key);
    
    -- Encrypt data using the private key (you can also use the public key)
    mysql> SET @ciphertext = asymmetric_encrypt('RSA', 'This text is secret', @private_key);
    
    -- Decrypt data using the public key (you can also use the private key)
    -- The decrypted value @plaintext should be identical to the original 'This text is secret'
    mysql> SET @plaintext = asymmetric_decrypt('RSA', @ciphertext, @public_key);
    

    Code examples for the following operations:

    • generate a digest string

    • generate a digest signature

    • verify the signature against the digest

    -- Generate a digest string
    mysql> SET @digest = create_digest('SHA256', 'This is the text for digest');
    
    -- Generate a digest signature
    mysql> SET @signature = asymmetric_sign('RSA', @digest, @private_key, 'SHA256');
    
    -- Verify the signature against the digest
    -- The @verify_signature must be equal to 1
    mysql> SET @verify_signature = asymmetric_verify('RSA', @digest, @signature, @public_key, 'SHA256');
    

    Code examples for the following operations:

    • generate a DH parameter

    • generates two DH key pairs

    • generate a symmetric key using the public_1 and the private_2

    • generate a symmetric key using the public_2 and the private_1

     -- Generate a DH parameter
     mysql> SET @dh_parameter = create_dh_parameters(3072);
    
     -- Generate DH key pairs
     mysql> SET @private_1 = create_asymmetric_priv_key('DH', @dh_parameter);
     mysql> SET @public_1 = create_asymmetric_pub_key('DH', @private_1);
     mysql> SET @private_2 = create_asymmetric_priv_key('DH', @dh_parameter);
     mysql> SET @public_2 = create_asymmetric_pub_key('DH', @private_2);
    
    -- Generate a symmetric key using the public_1 and private_2
    -- The @symmetric_1 must be identical to @symmetric_2
    mysql> SET symmetric_1 = asymmetric_derive(@public_1, @private_2);
    
    -- Generate a symmetric key using the public_2 and private_1
    -- The @symmetric_2 must be identical to @symmetric_1
    mysql> SET symmetric_2 = asymmetric_derive(@public_2, @private_1);
    

    Code examples for the following operations:

    • create a private key using a SET statement

    • create a private key using a SELECT statement

    • create a private key using an INSERT statement

    mysql> SET @private_key1 = create_asymmetric_priv_key('RSA', 3072);
    mysql> SELECT create_asymmetric_priv_key('RSA', 3072) INTO @private_key2;
    mysql> INSERT INTO key_table VALUES(create_asymmetric_priv_key('RSA', 3072));
    

    Uninstall component_encryption_udf¶

    You can deactivate and uninstall the component using the Uninstall Component statement.

    mysql> UNINSTALL COMPONENT 'file://component_encryption_udf';
    

    Contact us

    For free technical help, visit the Percona Community Forum.

    To report bugs or submit feature requests, open a JIRA ticket.

    For paid support and managed or consulting services , contact Percona Sales.


    Last update: 2023-01-12
    Back to top
    Percona LLC and/or its affiliates, © 2023
    Made with Material for MkDocs

    Cookie consent

    We use cookies to recognize your repeated visits and preferences, as well as to measure the effectiveness of our documentation and whether users find what they're searching for. With your consent, you're helping us to make our documentation better.