A method for database proxy protection and dynamic setting

Abstract

The present invention relates to a method for database proxy protection and dynamic configuration. The method includes: obtaining data sources, user configuration information, and rule configuration information by periodically calling an external interface, and dynamically creating database proxy services with different proxy ports; the proxy service periodically obtains relevant data with its own proxy port as a key from the process that created the proxy service, and periodically obtains the current data source, user configuration information, and rule configuration information; after the proxy service obtains the information, it compares it with the last cached information, modifies the data source generation library, and sets the access users of the proxy database according to changes in user data; for users who need to be configured through YAML, the yamlConfig in the context reading part of the source code is modified, so that the information in the Java program memory variables actually read is changed, thereby realizing dynamic modification of the user. Through this solution, a secure database proxy can be realized, and users accessing the proxy database can be dynamically controlled.

Description

Technical Field

[0001] The present invention relates to the technical field of database proxy security protection, and in particular to a method and system for database proxy protection and dynamic setting. Background Art

[0002] The database proxy is a high-level operation for database access. Current open-source components are generally configured through configuration files to map corresponding tables according to the configuration after startup. Therefore, it cannot provide SQL protection or dynamically set access users and data sources. It cannot meet the needs of dynamically modifying access users or dynamically auditing the execution of SQL. Summary of the Invention

[0003] In order to overcome the above-mentioned defects of existing database proxies, the present invention proposes a new method for database proxy protection and dynamic setting. Through this solution, SQL is dynamically intercepted according to configuration rules, a secure database proxy can be implemented, and users accessing the proxy database can be dynamically controlled. At the same time, because it can dynamically intercept SQL according to configurable rules, it is possible to audit whether the relevant SQL complies with relevant requirements.

[0004] This solution restructures the shardingsphere-proxy component. It retrieves the required data source, user configuration, and rule configuration information by periodically calling external interfaces, dynamically creating database proxy services with different proxy ports. Simultaneously, the proxy service periodically retrieves data related to its own proxy port as the key from the process that created the proxy service, and periodically obtains the current data source, user configuration, and rule configuration information. After periodically obtaining this information, it compares it with its previously saved cache, modifies the data source generation library, and configures the proxy database access user based on user data changes. Dynamic user modification, i.e., for users that originally required YAML configuration, modifies the yamlConfig in the context read section of the source code, resulting in changes to the information in the Java program's memory variables after the configuration is actually read. Furthermore, when user modifications require refreshing the yamlConfig global variable, the stored library information storage unit must also be refreshed. This is executed using JDBC to ensure that database connections are released properly without affecting the original database. Furthermore, SQL is intercepted according to rules at the source code conversion point and recorded as a log for audit purposes.

[0005] For example, if the actual database is on port 3306 on machine IP1 and the proxy service is on machine IP2, start the gateway service. The gateway service obtains the corresponding data source data according to the configured startup parameters, calls the shardingsphere-proxy startup script based on the data source data, and passes in the gateway service port and proxy port. The shardingsphere-proxy proxy starts the proxy service according to the proxy port, periodically reads user information and rule information, modifies the user information configuration in the context, and intercepts SQL.

[0006] In summary, the present invention obtains information such as users and data sources by calling interfaces at regular intervals, and dynamically refreshes the memory variable information in the proxy service based on the obtained information, thereby solving the problems of database gateway and audit rule configuration, and access account configuration.

[0007] Specifically, the present invention provides a method for database proxy protection and dynamic setting, such as Figure 3 As shown, the method includes the following steps:

[0008] S1. Obtain data source, user configuration information, and rule configuration information by regularly calling external interfaces, and dynamically create database proxy services for different proxy ports;

[0009] S2. The proxy service periodically obtains relevant data with its own proxy port as the key from the process that created the proxy service, and periodically obtains the current data source, user configuration information, and rule configuration information;

[0010] S3. After the proxy service obtains the information regularly, it compares it with its last saved cache, modifies the data source generation library, and sets the access users of the proxy database according to the user data changes;

[0011] S4. For users in the proxy service that need to be configured through YAML, modify the yamlConfig in the context reading part of the source code so that the information in the Java program memory variables is changed after the configuration is actually read, thereby realizing dynamic modification of the user.

[0012] Furthermore, step S4 of the method for database proxy protection and dynamic setting of the present invention also includes: if the yamlConfig global variable needs to be refreshed when the user is dynamically modified, the refreshed library information storage unit information is refreshed and executed in jdbc mode to ensure that the number of database connections is released normally.

[0013] Furthermore, the database proxy protection and dynamic setting method of the present invention also includes: configuring the actual database and the proxy service on different machines, starting the gateway service, the gateway service obtaining the corresponding data source data according to the configured startup parameters, calling the shardingsphere-proxy startup script according to the data source data and passing in the gateway service port and the proxy port; the shardingsphere-proxy agent starts the proxy service according to the proxy port, and simultaneously reads the user configuration information and the rule configuration information at regular intervals, modifies the user information configuration in the context and intercepts SQL at the same time.

[0014] Furthermore, the method of database proxy protection and dynamic setting of the present invention also includes providing a configuration service, which maintains the user, gateway configuration, proxy configuration, and source database information of the proxy database service and provides an interface to the gateway service to obtain relevant information for starting the proxy service of the relevant configuration.

[0015] Furthermore, the configuration service in the database proxy protection and dynamic setting method of the present invention includes: configuring data source information, configuring account information, configuring proxy gateway information, and configuring proxy service information, wherein:

[0016] The data source information includes: data source IP, port number, user name, and password;

[0017] The account information includes: account name, password;

[0018] The proxy gateway information includes: proxy gateway name, server and gateway service port, and corresponding data source;

[0019] The proxy service information includes: proxy service proxy1 port and corresponding gateway service; the proxy service is started by the corresponding gateway service on the same machine.

[0020] Furthermore, the implementation process of the proxy gateway service in the database proxy protection and dynamic setting method of the present invention is as follows:

[0021] a) The gateway service obtains the source database information provided by the configuration service every minute and queries the corresponding database list;

[0022] b) Check whether there is an interface in the local cache of the gateway service to read the source database information. If not, create a proxy service and write it to the cache;

[0023] c) The proxy service is started according to the proxy configuration port read from the gateway, and the configuration data corresponding to this port number is read from the gateway service every minute;

[0024] d) The proxy service changes the account attributes in the shardingsphere-proxy context according to the account information data read, and modifies the cache according to the data read at the last scheduled time;

[0025] e) The proxy service creates a storage unit corresponding to the link source database according to the library list information in the data source information read at a fixed time;

[0026] f) The proxy service executes the LOAD SINGLE TABLE*.* SQL statement in each database of the source data source to map the tables under the database so that the account accessing the proxy database can access all tables under the corresponding database;

[0027] g) The client uses the configured account and password to access the proxy database and perform SQL operations on the source database.

[0028] On the other hand, the present invention also provides a system for database proxy protection and dynamic setting, which implements the steps of the above-mentioned method for database proxy protection and dynamic setting when the system is running.

[0029] In addition, the present invention also provides a computer-readable storage medium, on which a computer program is stored. When the program is executed by a processor, the steps of the above-mentioned database proxy protection and dynamic setting method are implemented.

[0030] In summary, the method for database proxy protection and dynamic setting of the present invention adds specific SQL rule configuration and interception or recording audit measures to the database proxy. For access to the proxy database, multiple users can be opened in a configurable manner and a user can be prohibited from accessing the database at any time, and the ability to configure and open proxy services for different data sources can be achieved. BRIEF DESCRIPTION OF THE DRAWINGS

[0031] In order to more clearly illustrate the technical solution of the present invention, the following is a brief introduction to the drawings required for use in the description of the present invention. Obviously, the following drawings are only some embodiments recorded in the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without paying any creative work.

[0032] Figure 1 This is the overall business logic diagram of the method of the present invention.

[0033] Figure 2 The shardingsphere-proxy in the method of the present invention dynamically modifies the virtual database and account logic diagram.

[0034] Figure 3 Schematic diagram of the implementation process of the method of the present invention. DETAILED DESCRIPTION

[0035] To make the objectives, technical solutions, and advantages of the present invention more clearly apparent, the technical solutions of the present invention will be described clearly and completely below with reference to specific embodiments. Obviously, the described embodiments are only a portion of the embodiments of the present invention, not all of them. The present invention may also be implemented or applied through different specific implementation methods, and the details in this specification may be modified or altered based on different viewpoints and applications without departing from the spirit of the present invention.

[0036] At the same time, it should be understood that the scope of protection of the present invention is not limited to the specific embodiments described below; it should also be understood that the terms used in the embodiments of the present invention are for describing specific embodiments rather than for limiting the scope of protection of the present invention.

[0037] Example: A method for database proxy protection and dynamic configuration

[0038] like Figure 1 As shown, this method is used to access the source database by accessing the proxy database and dynamically configure accounts. The implementation process is as follows:

[0039] 1. Configure Java 1.8 or above environment to run related services.

[0040] 2. The configuration service maintains the user, gateway configuration, proxy configuration, and source database information of the proxy database service and provides an interface for the gateway service to obtain relevant information for starting the proxy service with relevant configuration.

[0041] 3. The gateway service is used to pull up the proxy service of the corresponding port according to the configuration.

[0042] 4. The modified proxy service program related to shardingsphere-proxy is used by the gateway to start the corresponding port service.

[0043] 5. Configure the domain name of the machine where the gateway service is located so that the gateway service can access the interface provided by the configuration service.

[0044] 6. Access the proxy database using the account configured by the configuration service.

[0045] Specific examples include:

[0046] Configure the service data source ds1 10.253.xxx.2, port 3306, username root, password root and other data source information.

[0047] Configure the service configuration account, such as ceshi1, and the password is 123456.

[0048] Configure the proxy gateway gateway1, the server where it is located, and the gateway service port, such as 10.253.xxx.5 9099, corresponding to the data source ds1.

[0049] Configure the proxy service proxy1 port, such as 3308, corresponding to the gateway gateway1. The proxy service will be pulled up by the gateway service on the same machine where the gateway service is located. That is, access the 10.253.xxx.5 3308 proxy database through ceshi1 to perform SQL operations on the 10.253.xxx.2 3306 database without accessing the original database service. New accounts can be modified, added, and deleted at any time to access the 10.253.xxx.5 3308 proxy database.

[0050] like Figure 2 As shown, the specific implementation process of the proxy gateway program in this method is as follows:

[0051] a) The gateway service obtains the source database information provided by the configuration service every minute and queries the corresponding database list.

[0052] b) Check whether there is an interface in the local cache of the gateway service to read the source database information. If not, create a proxy service and write it to the cache.

[0053] c) The proxy service is started according to the proxy configuration port read from the gateway, and the configuration data corresponding to this port number is read from the gateway service at regular intervals every minute.

[0054] d) The proxy service changes the account attributes in the shardingsphere-proxy context according to the account information data read, and modifies the cache according to the data read at the last time.

[0055] e) The proxy service creates a storage unit corresponding to the link source database according to the library list information in the data source information read at a regular interval.

[0056] f) The proxy service executes the LOAD SINGLE TABLE*.* SQL statement in each database of the source data source to map the tables under the database so that the account accessing the proxy database can access all tables under the corresponding database.

[0057] g) The client uses the configured account and password to access the proxy database and perform SQL operations on the source database.

[0058] The above description is only a preferred embodiment of the present invention and does not limit the present invention in any form. Any technician familiar with the profession can make some changes or modifications to the technical content disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments with equivalent changes. However, any simple modifications, equivalent changes and modifications made to the above embodiments based on the technical essence of the present invention without departing from the content of the technical solution of the present invention should be included in the scope of protection of the claims of the present invention.

Claims

1. A method for database proxy protection and dynamic setting, characterized in that: The method comprises: S1. Obtain data sources, user configuration information, and rule configuration information by periodically calling external interfaces, and dynamically create database proxy services for different proxy ports. S2. The proxy service periodically obtains data related to its own proxy port key from the process that created the proxy service, and periodically obtains the current data source, user configuration information, and rule configuration information; S3. After the proxy service obtains the information regularly, it compares it with its last saved cache, modifies the data source generation library, and sets the access users of the proxy database based on the user data changes. S4. For users in the proxy service that require YAML configuration, modify the yamlConfig in the context read section of the shardingsphere-proxy component to change the information in the Java program memory variables actually read, enabling dynamic modification of users. The method further includes: configuring the actual database and the proxy service on different machines, starting the gateway service, the gateway service obtaining corresponding data source data according to the configured startup parameters, calling the startup script of shardingsphere-proxy according to the data source data and passing in the gateway service port and the proxy port; the shardingsphere-proxy agent starts the proxy service according to the proxy port, and simultaneously reads user configuration information and rule configuration information at regular intervals, modifies the user information configuration in the context, and intercepts SQL at the same time.

2. The method for database proxy protection and dynamic setting according to claim 1, characterized in that: Step S4 also includes: if the yamlConfig global variable needs to be refreshed when the user is dynamically modified, the library information storage unit information that has been refreshed is refreshed again and executed in a jdbc manner to ensure that the number of database connections is released normally.

3. The method for database proxy protection and dynamic setting according to claim 1, characterized in that: The method also includes providing a configuration service, which maintains the user, gateway configuration, proxy configuration, and source database information of the proxy database service and provides an interface for the gateway service to obtain relevant information for starting the proxy service with relevant configuration.

4. The method for database proxy protection and dynamic setting according to claim 3, characterized in that: The configuration service includes: configuring data source information, configuring account information, configuring proxy gateway information, and configuring proxy service information, wherein: The data source information includes: data source IP, port number, user name, and password; The account information includes: account name, password; The proxy gateway information includes: proxy gateway name, server and gateway service port, and corresponding data source; The proxy service information includes: proxy service proxy1 port and corresponding gateway service; the proxy service is started by the corresponding gateway service on the same machine.

5. The method for database proxy protection and dynamic configuration according to claim 3, characterized in that: The implementation process of the gateway service in the method is as follows: a) The gateway service obtains the source database information provided by the configuration service every minute and queries the corresponding database list; b) Check whether there is an interface in the local cache of the gateway service to read the source database information. If not, create a proxy service and write it to the cache; c) The proxy service is started according to the proxy configuration port read from the gateway, and the configuration data corresponding to this port number is read from the gateway service every minute; d) The proxy service changes the account attributes in the shardingsphere-proxy context according to the account information data read, and modifies the cache according to the data read at the last scheduled time; e) The proxy service creates a storage unit based on the library list information in the data source information read at a fixed time, corresponding to the linked source database; f) The proxy service executes the LOAD SINGLE TABLE *.* SQL statement in each database of the source data source to map the tables under the database, allowing the account accessing the proxy database to access all tables under the corresponding database; g) The client uses the configured account and password to access the proxy database and perform SQL operations on the source database.

6. A database proxy protection and dynamic setting system, characterized in that: The system is run to implement the steps of the method for database proxy protection and dynamic setting according to any one of claims 1 to 5.

7. A computer-readable storage medium having a computer program stored thereon, wherein when the program is executed by a processor, the steps of the method for database proxy protection and dynamic setting according to any one of claims 1 to 5 are implemented.

Images