MySQL public network encryption transmission method and system based on X.509 certificate and Stunnel

Abstract

The invention discloses a MySQL public network encryption transmission method and system based on an X.509 certificate and Srunnel, and relates to the technical field of network security and database communication, secure communication of a MySQL database in a public network environment is realized by deploying a Srunnel encryption agent module at a client and a server and establishing a bidirectional authentication TLS encryption tunnel based on the X.509 digital certificate, and the security of the MySQL database in the public network environment is improved. The method comprises the steps that a certificate issuing mechanism root certificate is generated, a server certificate and a client certificate are signed and issued, a Srunnel server agent module and a client agent module are configured, a certificate path and a verification level are set, a MySQL client carries out TLS handshake and two-way certificate verification through a local Srunnel agent and a server Srunnel agent, after an encryption tunnel is established, data are transmitted in an encrypted mode, and the data are transmitted to the server Srunnel agent. The method supports an RSA and SM2 dual-certificate system, end-to-end encryption and strong identity authentication are realized, man-in-the-middle attack and data leakage risks are effectively defended, and the method is suitable for the fields of finance, government affairs and the like which have strict requirements on data security.

Description

Technical Field

[0001] This invention relates to the fields of network security and database communication technology, specifically to a method and system for encrypted transmission of MySQL over the public network based on X.509 certificates and Stunnel. Background Technology

[0002] With the widespread application of cloud computing and distributed systems, the demand for remote access to MySQL databases via public networks is increasing. However, traditional MySQL databases have serious security vulnerabilities in public network communication. In existing technologies, the default communication mode of MySQL databases uses plaintext transmission. When the client and server connect through a public network, sensitive data, user credentials, SQL query statements and other information in the database are transmitted in plaintext form over the network. Attackers can easily intercept this sensitive information through network sniffing, man-in-the-middle attacks and other means, causing data leakage and security threats. Although MySQL natively supports SSL / TLS encrypted connections, this approach has the following shortcomings: First, MySQL's native SSL configuration only supports one-way authentication, which means that the client cannot effectively verify the server's true identity and is vulnerable to attacks from fake servers. Second, certificate management relies on the database's own configuration system and lacks a unified certificate management mechanism, resulting in high costs for certificate updates and maintenance in multi-node, multi-database environments. Third, MySQL's native SSL does not support Chinese cryptographic algorithms, which cannot meet the requirements for commercial cryptographic applications stipulated in my country's Cryptography Law, and faces compliance issues in government affairs, finance and other fields. Fourth, directly exposing the MySQL service port to the public internet expands the attack surface for brute-force attacks and SQL injection. Even with SSL encryption enabled, there is still a risk of password brute-force attacks. Traditional VPN solutions, while providing network layer encryption, have the following problems: First, VPNs perform overall encryption at the network layer, which cannot achieve fine-grained application-layer access control, and the security requirements of different applications cannot be managed separately. Second, the establishment and maintenance of VPN connections introduces higher network latency, which affects the real-time performance of database queries. Third, VPN solutions require additional dedicated equipment and complex network configurations, resulting in high deployment and maintenance costs. Therefore, there is an urgent need for a MySQL public network secure communication solution that can provide strong authentication and end-to-end encryption, as well as flexible deployment and support national cryptographic algorithms. Summary of the Invention

[0003] The purpose of this invention is to provide a method and system for encrypted transmission of MySQL over the public network based on X.509 certificates and Stunnel.

[0004] The method for encrypted public network transmission of MySQL based on X.509 certificates and Stunnel includes the following steps: S1: Generate a certificate system on the server side, including: generating a certificate authority private key and a certificate authority root certificate, and using the certificate authority private key and certificate authority root certificate to issue server-side certificates and client-side certificates; S2: Configure the Stunnel server proxy module on the server side, including: configuring the listening address and listening port of the Stunnel server proxy module, configuring the storage path of the server certificate, server private key and certificate authority root certificate, and setting the certificate verification level to mandatory two-way verification mode; S3: Configure the Stunnel client proxy module on the client side, including: configuring the local listening port and remote connection address, configuring the storage path of the client certificate, client private key and certificate authority root certificate, and setting the certificate verification level to forced two-way verification mode; S4: Establish an encrypted transmission channel, including: the MySQL client initiates a connection request to the local Stunnel client proxy module, the Stunnel client proxy module initiates a TLS handshake to the Stunnel server proxy module, and both parties perform bidirectional verification of X.509 certificates; S5: After successful verification, the Stunnel client proxy module establishes an encrypted tunnel with the Stunnel server proxy module. Data from the MySQL client is encrypted by the Stunnel client proxy module and transmitted to the Stunnel server proxy module via the public network. The Stunnel server proxy module decrypts the data and forwards it to the MySQL server.

[0005] Furthermore, the certificate system generation in step S1 also includes: A dual certificate system is used to generate RSA algorithm certificates and SM2 national cryptographic algorithm certificates. The RSA key length is 4096 bits, and the SM2 algorithm uses sm2p256v1 elliptic curve parameters. Both the server certificate and the client certificate contain a subject information field, which includes the country code, province, organization name, and common name.

[0006] Furthermore, configuring the Stunnel server proxy module in step S2 also includes: Configure the TLS protocol version to TLSv1.2 and TLSv1.3, configure the cipher suite to support SM2-WITH-SMS4-SM3 Chinese cryptographic suite and ECDHE-RSA-AES256-GCM-SHA384 cipher suite, configure the session cache timeout to 300 seconds, and configure the session cache capacity to 500 sessions; set the certificate verification level value to 3, indicating that the peer certificate is required and verified.

[0007] Furthermore, the two-way verification of the X.509 certificate in step S4 includes: The Stunnel client proxy module sends the client certificate to the Stunnel server proxy module. The Stunnel server proxy module uses the certificate authority root certificate to verify the signature validity of the client certificate and extracts the subject information field and fingerprint information from the client certificate. The Stunnel server-side proxy module sends the server certificate to the Stunnel client-side proxy module. The Stunnel client-side proxy module uses the certificate authority root certificate to verify the signature validity of the server certificate and extracts the subject information field and fingerprint information from the server certificate. Verify that the validity period of the client certificate and the server certificate is within the current time frame, and verify that the certificate has not been revoked.

[0008] Furthermore, it also includes the step of binding certificate fingerprints with MySQL user permissions: When creating a database user on the MySQL server, setting the user's access permissions requires verification of the client certificate's principal information field. The principal information field includes the specified country code, province, organization name, and common name, and also requires verification of the certificate issuer information. After the Stunnel server-side proxy module completes the client certificate verification, it transmits the subject information of the client certificate to the MySQL server. The MySQL server then performs user identity matching and permission verification based on the certificate information.

[0009] This invention also provides a MySQL public network encrypted transmission system based on X.509 certificates and Stunnel, comprising: The certificate management module is used to generate certificate authority private keys and certificate authority root certificates, issue server-side and client-side certificates, and manage the storage and distribution of certificates. The server-side encryption proxy module is deployed on the server where the MySQL server is located. It is configured with the Stunnel server-side proxy program, server-side certificate, server-side private key and certificate authority root certificate. It is used to listen for TLS connection requests on a specified port, perform server-side certificate verification and client-side certificate verification, and forward the decrypted data to the MySQL server. The client-side encryption proxy module is deployed on the terminal where the MySQL client is located. It is configured with the Stunnel client proxy program, client certificate, client private key and certificate authority root certificate. It is used to receive connection requests from the MySQL client, initiate TLS connections to the server-side encryption proxy module, perform client certificate verification and server certificate verification, and encrypt data transmission. The MySQL server is deployed on the local network of the server and receives database operation requests forwarded by the server-side encrypted proxy module. The MySQL client initiates a database connection request to the local client encryption proxy module.

[0010] Furthermore, the certificate management module includes: The certificate generation unit is used to generate a dual certificate system using the RSA algorithm and the SM2 national cryptographic algorithm. The RSA key length is set to 4096 bits, and the SM2 algorithm uses the sm2p256v1 elliptic curve parameter. The generated certificate includes a subject information field and a validity period field. The certificate issuance unit uses the certificate authority's private key to sign the certificate signing request, generates server-side and client-side certificates, and sets the certificate validity period to 365 days. The certificate storage unit stores the generated certificate authority root certificate, server certificate, client certificate, and corresponding private key files in a specified directory.

[0011] Furthermore, the server-side encrypted proxy module includes: The listening configuration unit is configured with a listening address of 0.0.0.0, a listening port number, and a local MySQL server address of 127.0.0.1 and port number 3306 for forwarding. The security configuration unit configures the TLS protocol version to support TLSv1.2 and TLSv1.3, configures the cipher suite to include SM2-WITH-SMS4-SM3 Chinese cryptographic suite and ECDHE-RSA-AES256-GCM-SHA384, configures the certificate verification level to 3, and configures the session cache timeout to 300 seconds. The certificate loading unit loads the server certificate file, the server private key file, and the certificate authority root certificate file.

[0012] Furthermore, the client-side encryption proxy module includes: Connect to the configuration unit, configure the local listening address as 127.0.0.1, configure the local listening port number, and configure the public IP address and port number of the remote server-side encrypted proxy module; The security configuration unit configures the TLS protocol version to support TLSv1.2 and TLSv1.3, configures the cipher suite, configures the certificate verification level to 3, and enables server certificate subject name verification. The certificate loading unit loads the client certificate file, the client private key file, and the certificate authority root certificate file.

[0013] Furthermore, it also includes: The session management module is used to manage the establishment, maintenance and destruction of TLS sessions. It is configured to have a session cache capacity of 500 sessions and will automatically clear the session cache after the session cache timeout period reaches 300 seconds. The audit log module is used to record certificate verification results, connection establishment time, data transmission volume, and connection closure time, as well as the fingerprint information and subject information fields of the client certificate.

[0014] Compared with the prior art, the beneficial effects of the present invention by adopting the above technical solution are as follows: 1. Achieve strong two-way authentication: Through the X.509 digital certificate system, two-way identity authentication is achieved between the client and the server, effectively preventing attacks from counterfeit servers and unauthorized client access. Compared with MySQL's native one-way SSL authentication, security is significantly improved.

[0015] 2. End-to-end encryption protection: MySQL communication data is encrypted end-to-end through a TLS encrypted tunnel established by Stunnel. Even when transmitted in an untrusted public network environment, attackers cannot steal or tamper with the data content, effectively defending against network sniffing and man-in-the-middle attacks.

[0016] 3. Supports national cryptographic algorithms: The system supports the SM2, SM3, and SM4 national cryptographic algorithm suites, meeting the compliance requirements of the Cryptography Law of the People's Republic of China for commercial cryptographic applications, and can be deployed in sensitive fields such as government affairs and finance.

[0017] 4. Flexible certificate management: Adopting a unified certificate management module, it supports centralized generation, issuance, storage and distribution of certificates, which simplifies certificate maintenance in multi-node environments and makes lifecycle management such as certificate validity period and revocation more standardized.

[0018] 5. Simple and convenient deployment: As an independent encryption proxy layer, Stunnel does not require modification of the MySQL database configuration itself, has zero intrusion into existing database systems, low deployment and migration costs, and good compatibility.

[0019] 6. Fine-grained access control: By binding certificate fingerprints to MySQL user permissions, fine-grained certificate-based access control is achieved. Different clients can use different certificates and be assigned different database access permissions, making security policies more flexible.

[0020] 7. Performance Optimization: By configuring a TLS session caching mechanism, the overhead of repeated handshakes is reduced. The session cache capacity is set to 500 sessions and the timeout is 300 seconds, which improves connection efficiency while ensuring security.

[0021] 8. Audit and traceability capabilities: The system records audit logs for all connections, including certificate information, connection time, and data transmission volume, providing complete data support for the tracing and analysis of security incidents. Attached Figure Description

[0022] Figure 1 This is a schematic diagram of the overall system architecture of the present invention; Figure 2 This is a schematic diagram of the certificate generation and configuration process of this invention; Figure 3 This is a schematic diagram of the data transmission encryption process of the present invention; Figure 4 This is a schematic diagram of the certificate verification process for this invention. Detailed Implementation

[0023] The present invention will now be described in further detail with reference to the accompanying drawings and specific embodiments. Example

[0024] like Figure 1 As shown, this embodiment provides a MySQL public network encrypted transmission system based on X.509 certificates and Stunnel. The system includes a certificate management module, a server-side encryption proxy module, a client-side encryption proxy module, a MySQL server, and a MySQL client.

[0025] The certificate management module is deployed on the server side and is responsible for the management of the entire certificate system. This module includes a certificate generation unit, a certificate issuance unit, and a certificate storage unit.

[0026] The certificate generation unit is used to generate key pairs and certificates. In this embodiment, to meet the needs of different scenarios, a dual certificate system of RSA algorithm and SM2 national cryptographic algorithm is generated. The RSA key adopts a length of 4096 bits to provide high-strength cryptographic security. The SM2 algorithm uses sm2p256v1 elliptic curve parameters and conforms to the "SM2 Elliptic Curve Public Key Cryptography Algorithm" standard issued by the State Cryptography Administration.

[0027] Specifically, the certificate generation unit first generates a certificate authority private key; for the RSA algorithm, it generates a 4096-bit private key. For the SM2 algorithm, a private key is generated based on the sm2p256v1 elliptic curve. Then, a self-signed certificate authority root certificate is generated using this private key. The certificate validity period is set to 365 days. The certificate authority root certificate contains the following main information fields: country code is CN, organization name is the company name, and common name is the certificate authority name.

[0028] The certificate issuance unit receives certificate signing requests from both the server and the client. Each request contains the applicant's public key and subject information. After verifying the validity of the request, the unit digitally signs it using the certificate authority's private key, generating server and client certificates. The server certificate's subject information includes: country code, province, organization name, and common name, where the common name is set to the server's domain name or IP address. The client certificate's subject information includes: country code, province, organization name, and common name, where the common name uniquely identifies the client. All certificates have a validity period of 365 days.

[0029] The certificate storage unit stores the generated certificate authority root certificate, server certificate, client certificate, and corresponding private key files in a specified directory. In Linux systems, certificate files are usually stored in the / etc / stunnel / certs directory. The directory permissions are set to 700, and the certificate file permissions are set to 600 to ensure that the private key files are not accessed without authorization.

[0030] The server-side encryption proxy module is deployed on the server where the MySQL server is located. It serves as an encryption proxy layer between the MySQL server and the public network. This module includes a listening configuration unit, a security configuration unit, and a certificate loading unit.

[0031] The listening configuration unit configures the network parameters of the Stunnel server agent. The listening address is set to 0.0.0.0, which means listening to all network interfaces of the server and allowing connection requests from any network. The listening port number is set to 3307, which is used to receive TLS encrypted connections from clients. The forwarding address is set to 127.0.0.1, and the port number is set to 3306, which means forwarding the decrypted data to the default port of the local MySQL server.

[0032] The security configuration unit configures the TLS protocol and encryption parameters. The TLS protocol versions supported are TLSv1.2 and TLSv1.3, while TLSv1.0 and TLSv1.1, which have security vulnerabilities, are disabled. The cipher suite configuration includes the SM2-WITH-SMS4-SM3 Chinese cryptographic suite and the ECDHE-RSA-AES256-GCM-SHA384 cipher suite. The former is used for Chinese cryptographic algorithm scenarios, and the latter for international standard algorithm scenarios. The certificate verification level is set to 3, which means that the peer is required to provide a certificate and undergo strict verification. The session cache timeout is configured to 300 seconds, and the session cache capacity is configured to 500 sessions. By reusing established TLS sessions, handshake overhead is reduced, and connection efficiency is improved.

[0033] When the Stunnel server agent starts, the certificate loading unit loads the server certificate file, server private key file, and certificate authority root certificate file from the specified path. The server certificate and private key are used to prove the server's identity to the client, and the certificate authority root certificate is used to verify the legitimacy of the client's certificate.

[0034] The client-side encryption proxy module is deployed on the terminal device where the MySQL client is located. It acts as a bridge between the MySQL client and the server-side encryption proxy module. This module includes a connection configuration unit, a security configuration unit, and a certificate loading unit.

[0035] The connection configuration unit configures the connection parameters for the Stunnel client agent program. The local listening address is set to 127.0.0.1, and the local listening port number is set to 3306. The MySQL client program connects to this local port. The remote connection address is set to the public IP address of the server where the server-side encrypted agent module is located, and the remote connection port number is set to 3307, which corresponds to the listening port of the server-side encrypted agent module.

[0036] The security configuration unit configures the TLS protocol and encryption parameters, and the configuration content is consistent with the server, including the TLS protocol version, cipher suite, and certificate verification level. In addition, server certificate subject name verification is enabled to ensure that the common name in the server certificate matches the actual server address being connected, preventing DNS hijacking attacks.

[0037] When the Stunnel client agent program starts, the certificate loading unit loads the client certificate file, client private key file, and certificate authority root certificate file from the specified path. The client certificate and private key are used to prove the client's identity to the server, and the certificate authority root certificate is used to verify the legitimacy of the server certificate.

[0038] The MySQL server is deployed on the local network of the server, listening on port 3306 at the local address 127.0.0.1. This port is not directly exposed to the public Internet and only accepts connection requests from the local server's encrypted proxy module, thus avoiding the security risks of directly exposing the database port.

[0039] The MySQL client is a standard MySQL client program. It is configured to connect to the local 127.0.0.1 and the port 3306. It actually connects to the local client encryption proxy module. The MySQL client does not need to modify any configuration and is unaware of the underlying encryption transmission process. It is used in exactly the same way as connecting directly to the database.

[0040] like Figure 2 As shown, the certificate generation and configuration process includes the following steps: Step 1: The administrator uses the certificate generation unit on the server to generate a certificate authority private key. For the RSA algorithm, the generation command parameters include key type RSA and key length 4096 bits. For the SM2 algorithm, the generation command parameters include key type EC and curve parameter sm2p256v1.

[0041] Step 2: Use the certificate authority's private key to generate a self-signed certificate authority root certificate. Fill in the subject information in the certificate, including the country code (CN), organization name, and the common name (Enterprise CA). Set the certificate validity period to 365 days.

[0042] Step 3: Generate a server-side private key on the server side. The key parameters are the same as the certificate authority's private key. Choose either RSA or SM2 depending on the algorithm used.

[0043] Step 4: Use the server's private key to generate a certificate signing request file. Fill in the server's principal information in the certificate signing request. The common name should be the server's domain name or public IP address, such as db.example.com.

[0044] Step 5: Using the certificate signing unit, based on the certificate authority's private key and root certificate, sign the server's certificate signing request to generate a server certificate. The certificate validity period is set to 365 days.

[0045] Step 6: Generate a client-side private key on the client side, using the same method as the server-side private key.

[0046] Step 7: Use the client's private key to generate a certificate signing request file. Fill in the client's subject information in the certificate signing request, and fill in the client's unique identifier in the common name, such as client-001.

[0047] Step 8: Transmit the client's certificate signing request file to the server in a secure manner, and use the certificate signing unit to sign it to generate the client certificate.

[0048] Step 9: Securely distribute the Certificate Authority root certificate and client certificate to the client. The client stores the Certificate Authority root certificate, client certificate, and client private key.

[0049] Step 10: Configure the Stunnel server proxy module on the server side, fill in the certificate file path, including the server certificate path, server private key path and certificate authority root certificate path, configure the listening port as 3307 and the forwarding address as 127.0.0.1:3306.

[0050] Step 11: Configure the Stunnel client agent module on the client side. Fill in the certificate file path, including the client certificate path, client private key path, and certificate authority root certificate path. Configure the local listening port as 3306 and the remote connection address as the server's public IP address and port 3307.

[0051] Step 12: Start the Stunnel agent program on both the server and client sides. The certificate system configuration is now complete.

[0052] like Figure 3 As shown, the data transmission encryption process includes the following steps: Step 1: The MySQL client program initiates a connection request to port 3306 of the local address 127.0.0.1. This request is received by the local Stunnel client proxy module.

[0053] Step 2: The Stunnel client proxy module initiates a TCP connection to the public IP address and port 3307 of the server-side encrypted proxy module.

[0054] Step 3: After the TCP connection is established, the Stunnel client proxy module initiates a TLS handshake request to begin the TLS protocol negotiation process.

[0055] Step 4: The server-side encryption proxy module responds to the TLS handshake, and both parties negotiate the TLS protocol version and cipher suite. Based on the algorithms supported by the client, either the SM2-WITH-SMS4-SM3 national cryptographic suite or the ECDHE-RSA-AES256-GCM-SHA384 cipher suite is selected.

[0056] Step 5: The server-side encryption proxy module sends the server certificate to the client. The client-side proxy module uses the locally stored certificate authority root certificate to verify the signature of the server certificate. The verification includes: digital verification of the certificate signature, the certificate validity period being within the current time range, the certificate not being revoked, and the certificate's common name matching the connected server address.

[0057] Step 6: The client encryption proxy module sends the client certificate to the server. The server proxy module uses the locally stored certificate authority root certificate to verify the signature of the client certificate. The verification includes: digital verification of the certificate signature, the certificate validity period being within the current time range, and the certificate not being revoked.

[0058] Step 7: After successful two-way certificate verification, the client and server use the Diffie-Hellman key exchange algorithm or the elliptic curve Diffie-Hellman key exchange algorithm to negotiate and generate a session key.

[0059] Step 8: The TLS handshake is completed, the encrypted tunnel is established, and all subsequent communication data is symmetrically encrypted using the session key.

[0060] Step 9: The SQL query statements and data sent by the MySQL client are first received by the Stunnel client agent module. This module encrypts the data using a session key and adds a message authentication code to ensure data integrity.

[0061] Step 10: The encrypted data is transmitted to the server-side encryption proxy module via the public network. Since the data is encrypted, even if it is intercepted by a third party, the plaintext content cannot be deciphered.

[0062] Step 11: After receiving the encrypted data, the server-side encryption proxy module decrypts it using the session key, verifies the message authentication code to confirm the data integrity, and then forwards the decrypted plaintext data to port 3306 of the local MySQL server.

[0063] Step 12: The MySQL server processes the request, generates response data, and returns it to the server-side encrypted proxy module.

[0064] Step 13: The server-side encryption proxy module encrypts the response data and transmits it to the client-side encryption proxy module through an encryption tunnel.

[0065] Step 14: The client-side encryption proxy module decrypts the response data and forwards it to the MySQL client program. The MySQL client receives the data and completes a query operation.

[0066] like Figure 4 As shown, the detailed steps of the certificate verification process are as follows: Step 1: The Stunnel client proxy module receives the server certificate during the TLS handshake process.

[0067] Step 2: Extract the certificate issuer information from the server certificate.

[0068] Step 3: Use the public key in the certificate authority root certificate stored locally to verify the signature of the server certificate. The verification process is as follows: use the public key to decrypt the signature and obtain the hash value H1 of the certificate content. Perform a hash calculation on the certificate content to obtain the hash value H2; Compare H1 and H2 to see if they are equal. If they are equal, the signature verification is successful.

[0069] Step 4: Extract the validity period field of the server certificate, including the start time and end time.

[0070] Step 5: Obtain the current system time and determine if the current time is within the certificate's validity period. If the current time is earlier than the start time or later than the end time, the certificate has expired and verification fails.

[0071] Step 6: Extract the Common Name field from the server certificate.

[0072] Step 7: Match the general name with the address of the currently connected server. If the general name is a domain name, compare it with the domain name of the connected server. If the generic name is an IP address, it is compared with the IP address of the connection; if a match is found, the verification is successful.

[0073] Step 8: Check if the certificate is in the certificate revocation list. If a certificate revocation list is configured, check if the certificate's serial number is in the revocation list. If it is in the list, the certificate has been revoked and the verification fails.

[0074] Step 9: After all verification steps are passed, the server certificate verification is successful.

[0075] Step 10: The Stunnel server-side proxy module receives the client certificate and performs the same verification process, including signature verification, validity period verification, and revocation status verification.

[0076] Step 11: After successful client certificate verification, extract the client certificate's subject information and fingerprint information and record them in the audit log.

[0077] Step 12: Pass the client certificate information to the MySQL server. If certificate-based user access control is configured in MySQL, the MySQL server will match the user identity based on the certificate subject information.

[0078] On the MySQL server, users requiring certificate authentication can be created in the following ways: The user creation statement specifies the REQUIRE SUBJECT clause, requiring the client certificate's subject information field to include the specified country code (CN), province, organization name, and common name (client-001). It also specifies the REQUIRE ISSUER clause, requiring the certificate to be issued by the specified certificate authority, and the certificate issuer's organization name and common name must match.

[0079] When a client connects to MySQL through the Stunnel encrypted tunnel, the MySQL server retrieves the client's certificate subject information from the connection information and compares it with the requirements defined in the user definition. Only clients whose certificate information matches completely can log in to the database as that user and perform the corresponding database operations.

[0080] The present invention also includes a session management module for managing the lifecycle of TLS sessions. After a successful TLS handshake to establish an encrypted tunnel, the session management module caches session information in memory, including session identifier, session key and client certificate information. The session cache capacity is set to 500 sessions, and a first-in-first-out strategy is adopted. When the cache is full, the earliest session is deleted. The session cache timeout is set to 300 seconds. When the session idle time exceeds this value, the session is automatically deleted from the cache.

[0081] When the same client initiates a connection again within the session cache timeout period, the cached session can be reused to perform a simplified TLS handshake process. There is no need to perform a complete certificate verification and key exchange again, which significantly reduces the time overhead of connection establishment. In the case of session reuse, the connection establishment time is reduced from 80 milliseconds of the initial handshake to 20 milliseconds, which is a performance improvement of 75%.

[0082] This invention also includes an audit log module for recording detailed information about all connections, providing data support for security auditing and event tracing. The audit log content includes: Connection establishment time: Records the precise timestamp of the start of the TLS handshake, with an accuracy in milliseconds.

[0083] Client certificate information: Records the main information fields of the client certificate, including the country code, province, organization name and common name, and records the fingerprint information of the certificate. The fingerprint algorithm uses SHA256.

[0084] Server certificate information: Records the subject information and fingerprint information of the server certificate.

[0085] Certificate verification results: Record whether each step of the certificate verification process is successful, such as signature verification, validity period verification, and revocation status verification.

[0086] TLS protocol parameters: Records the TLS protocol version and cipher suite used in the negotiation.

[0087] Data transmission volume: Records the total number of bytes sent and received for this connection.

[0088] Connection close time: Records the timestamp of when the TLS connection is closed.

[0089] Connection closing reason: Record the reason code for whether the connection was closed normally or disconnected abnormally.

[0090] Audit logs are stored in a specified log file in text format, with each log record occupying one line. The log file is automatically rolled over once a day, and historical log files are compressed and saved. Administrators can use log analysis tools to analyze connection frequency, traffic distribution, certificate usage, and detect abnormal access behavior. Example

[0091] This embodiment, based on Embodiment 1, further illustrates the application scenarios of the dual-certificate system.

[0092] In actual deployment, different clients may support different encryption algorithms. Domestic government systems require the use of national cryptographic algorithms, which require the use of the SM2, SM3, and SM4 algorithm suites. International business systems typically use RSA, AES, and SHA algorithm suites. To meet the needs of different clients, this invention deploys both the RSA certificate system and the SM2 certificate system.

[0093] On the server side, the certificate management module generates two sets of certificate authority root certificates: one based on the RSA algorithm and the other based on the SM2 algorithm. Correspondingly, it generates two sets of server-side certificates and client-side certificates.

[0094] In the configuration of the server-side encrypted proxy module, two sets of certificate files are specified at the same time. During the TLS handshake process, the Stunnel server-side proxy program automatically selects the matching certificate and cipher suite according to the list of supported algorithms sent by the client. If the client supports the SM2 algorithm, the SM2 certificate and SM2-WITH-SMS4-SM3 cipher suite are used. If the client only supports the RSA algorithm, then use an RSA certificate and the ECDHE-RSA-AES256-GCM-SHA384 cipher suite.

[0095] On the client side, configure the corresponding certificate according to actual needs. Configure the SM2 client certificate for the government system client and the RSA client certificate for the international business client.

[0096] Through the dual-certificate system, this invention can simultaneously support Chinese cryptographic algorithms and international standard algorithms within the same system, meeting different compliance requirements and expanding the applicability of the system. Example

[0097] This embodiment further illustrates the implementation of the certificate revocation mechanism based on Embodiment 1.

[0098] In practical applications, when client devices are lost, employees leave the company, or certificate private keys are leaked, it is necessary to revoke the issued certificates in a timely manner to prevent them from being used illegally.

[0099] The certificate management module maintains a certificate revocation list file, which records the serial number and revocation time of all revoked certificates. The certificate serial number is a unique identifier for the certificate and is generated by the certificate issuance unit when issuing the certificate.

[0100] When an administrator decides to revoke a certificate, they submit a revocation request to the certificate management module, providing the certificate's serial number and the reason for revocation. The certificate management module adds the serial number to the certificate revocation list file and records the revocation timestamp.

[0101] The certificate revocation list file is distributed securely to the server-side encrypted proxy module. The server-side encrypted proxy module loads the certificate revocation list file upon startup and checks for updates periodically.

[0102] During the TLS handshake process, when the server-side cryptographic proxy module receives the client certificate, in addition to verifying the signature and validity period, it also extracts the client certificate's serial number, queries the certificate revocation list for that serial number, and if the certificate serial number is found in the revocation list, it rejects the connection and records the certificate revocation access attempt event in the audit log.

[0103] By employing a certificate revocation mechanism, this invention can promptly prevent leaked or untrusted certificates from accessing the system, thereby enhancing security capabilities.

[0104] The above description is only a preferred embodiment of the present invention and is not intended to limit the scope of the present invention. Any modifications, equivalent substitutions, improvements, etc., made within the spirit and principles of the present invention should be included within the protection scope of the present invention.

Claims

1. A public network encrypted transmission method for MySQL based on X.509 certificates and Stunnel, characterized in that: The transmission method includes the following steps: S1: Generate a certificate system on the server side, including: generating a certificate authority private key and a certificate authority root certificate, and using the certificate authority private key and certificate authority root certificate to issue server-side certificates and client-side certificates; S2: Configure the Stunnel server proxy module on the server side, including: configuring the listening address and listening port of the Stunnel server proxy module, configuring the storage path of the server certificate, server private key and certificate authority root certificate, and setting the certificate verification level to mandatory two-way verification mode; S3: Configure the Stunnel client proxy module on the client side, including: configuring the local listening port and remote connection address, configuring the storage path of the client certificate, client private key and certificate authority root certificate, and setting the certificate verification level to forced two-way verification mode; S4: Establish an encrypted transmission channel, including: the MySQL client initiates a connection request to the local Stunnel client proxy module, the Stunnel client proxy module initiates a TLS handshake to the Stunnel server proxy module, and both parties perform bidirectional verification of X.509 certificates; S5: After successful verification, the Stunnel client proxy module establishes an encrypted tunnel with the Stunnel server proxy module. Data from the MySQL client is encrypted by the Stunnel client proxy module and transmitted to the Stunnel server proxy module via the public network. The Stunnel server proxy module decrypts the data and forwards it to the MySQL server.

2. The MySQL public network encrypted transmission method based on X.509 certificates and Stunnel according to claim 1, characterized in that: Step S1, which generates the certificate system, also includes: A dual certificate system is used to generate RSA algorithm certificates and SM2 national cryptographic algorithm certificates. The RSA key length is 4096 bits, and the SM2 algorithm uses sm2p256v1 elliptic curve parameters. Both the server certificate and the client certificate contain a subject information field, which includes the country code, province, organization name, and common name.

3. The MySQL public network encrypted transmission method based on X.509 certificates and Stunnel according to claim 1, characterized in that: The configuration of the Stunnel server proxy module in step S2 also includes: Configure the TLS protocol version to TLSv1.2 and TLSv1.3, configure the cipher suite to support SM2-WITH-SMS4-SM3 Chinese cryptographic suite and ECDHE-RSA-AES256-GCM-SHA384 cipher suite, configure the session cache timeout to 300 seconds, and configure the session cache capacity to 500 sessions; The certificate verification level is set to 3, which means that the peer's certificate is required and verified.

4. The MySQL public network encrypted transmission method based on X.509 certificates and Stunnel according to claim 1, characterized in that: The two-way verification of the X.509 certificate in step S4 includes: The Stunnel client proxy module sends the client certificate to the Stunnel server proxy module. The Stunnel server proxy module uses the certificate authority root certificate to verify the signature validity of the client certificate and extracts the subject information field and fingerprint information from the client certificate. The Stunnel server-side proxy module sends the server certificate to the Stunnel client-side proxy module. The Stunnel client-side proxy module uses the certificate authority root certificate to verify the signature validity of the server certificate and extracts the subject information field and fingerprint information from the server certificate. Verify that the validity period of the client certificate and the server certificate is within the current time frame, and verify that the certificate has not been revoked.

5. The MySQL public network encrypted transmission method based on X.509 certificates and Stunnel according to claim 1, characterized in that: The transmission method also includes a step of binding certificate fingerprints with MySQL user permissions: When creating a database user on the MySQL server, setting the user's access permissions requires verification of the client certificate's principal information field. The principal information field includes the specified country code, province, organization name, and common name, and also requires verification of the certificate issuer information. After the Stunnel server-side proxy module completes the client certificate verification, it transmits the subject information of the client certificate to the MySQL server. The MySQL server then performs user identity matching and permission verification based on the certificate information.

6. A MySQL public network encrypted transmission system based on X.509 certificates and Stunnel according to any one of claims 1-5, characterized in that: The transmission system includes: The certificate management module is used to generate certificate authority private keys and certificate authority root certificates, issue server-side and client-side certificates, and manage the storage and distribution of certificates. The server-side encryption proxy module is deployed on the server where the MySQL server is located. It is configured with the Stunnel server-side proxy program, server-side certificate, server-side private key and certificate authority root certificate. It is used to listen for TLS connection requests on a specified port, perform server-side certificate verification and client-side certificate verification, and forward the decrypted data to the MySQL server. The client-side encryption proxy module is deployed on the terminal where the MySQL client is located. It is configured with the Stunnel client proxy program, client certificate, client private key and certificate authority root certificate. It is used to receive connection requests from the MySQL client, initiate TLS connections to the server-side encryption proxy module, perform client certificate verification and server certificate verification, and encrypt data transmission. The MySQL server is deployed on the local network of the server and receives database operation requests forwarded by the server-side encrypted proxy module. The MySQL client initiates a database connection request to the local client encryption proxy module.

7. The MySQL public network encrypted transmission system based on X.509 certificates and Stunnel according to claim 6, characterized in that: The certificate management module includes: The certificate generation unit is used to generate a dual certificate system using the RSA algorithm and the SM2 national cryptographic algorithm. The RSA key length is set to 4096 bits, and the SM2 algorithm uses the sm2p256v1 elliptic curve parameter. The generated certificate includes a subject information field and a validity period field. The certificate issuance unit uses the certificate authority's private key to sign the certificate signing request, generates server-side and client-side certificates, and sets the certificate validity period to 365 days. The certificate storage unit stores the generated certificate authority root certificate, server certificate, client certificate, and corresponding private key files in a specified directory.

8. The MySQL public network encrypted transmission system based on X.509 certificates and Stunnel according to claim 6, characterized in that: The server-side encrypted proxy module includes: The listening configuration unit is configured with a listening address of 0.0.0.0, a listening port number, and a local MySQL server address of 127.0.0.1 and port number 3306 for forwarding. The security configuration unit configures the TLS protocol version to support TLSv1.2 and TLSv1.3, configures the cipher suite to include SM2-WITH-SMS4-SM3 Chinese cryptographic suite and ECDHE-RSA-AES256-GCM-SHA384, configures the certificate verification level to 3, and configures the session cache timeout to 300 seconds. The certificate loading unit loads the server certificate file, the server private key file, and the certificate authority root certificate file.

9. The MySQL public network encrypted transmission system based on X.509 certificates and Stunnel according to claim 6, characterized in that: The client-side encryption proxy module includes: Connect to the configuration unit, configure the local listening address as 127.0.0.1, configure the local listening port number, and configure the public IP address and port number of the remote server-side encrypted proxy module; The security configuration unit configures the TLS protocol version to support TLSv1.2 and TLSv1.3, configures the cipher suite, configures the certificate verification level to 3, and enables server certificate subject name verification. The certificate loading unit loads the client certificate file, the client private key file, and the certificate authority root certificate file.

10. The MySQL public network encrypted transmission system based on X.509 certificates and Stunnel according to claim 6, characterized in that: The transmission system also includes: The session management module is used to manage the establishment, maintenance and destruction of TLS sessions. It is configured to have a session cache capacity of 500 sessions and will automatically clear the session cache after the session cache timeout period reaches 300 seconds. The audit log module is used to record certificate verification results, connection establishment time, data transmission volume, and connection closure time, as well as the fingerprint information and subject information fields of the client certificate.

Images