Database agent protection and dynamic setting method

Abstract

The invention relates to a database agent protection and dynamic setting method. The method comprises the following steps: acquiring a data source, user configuration information and rule configuration information in a manner of calling an external interface at regular time, and dynamically creating database proxy services of different proxy ports; the method comprises the following steps of: regularly acquiring related data of which the proxy port is key from a process of creating the proxy service in the proxy service, and regularly acquiring a current data source, user configuration information and rule configuration information; after the proxy service obtains the information, comparing the information with the last cache information, modifying the data source generation library, and setting an access user of the proxy database according to the change of user data; and for a user needing to be configured through yaml, yaml Config in a context part is read in a source code is modified, so that information in an actually read java program memory variable is changed, and dynamic modification on the user is realized. According to the scheme, the database proxy with security protection can be realized, and meanwhile, the user 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 an upper-level operation for database access. Current open source components are generally set up through configuration files to map corresponding tables according to the configuration after startup. Therefore, it is impossible to provide SQL protection and 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 the existing database proxies, the present invention proposes a new method for database proxy protection and dynamic setting. Through this scheme, SQL is dynamically intercepted according to the 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 can audit whether the relevant SQL is compliant with relevant requirements, etc.

[0004] This solution transforms the shardingsphere-proxy component, obtains the required data source, user configuration information, and rule configuration information by calling the external interface regularly, and dynamically creates database proxy services with different proxy ports. At the same time, the proxy service periodically obtains the relevant data with its own proxy port as the key from the process of creating the proxy service, and periodically obtains the current data source, user configuration information, and rule configuration information. After obtaining the information regularly, it compares the cache saved last time, modifies the data source generation library, and sets the access user of the proxy database according to the user data change. For dynamic modification of users, that is, for users who originally need to be configured through yaml, modify the yamlConfig in the context reading part of the source code, so that the information in the memory variable of the java program changes after the actual configuration is read; at the same time, when the yamlConfig global variable needs to be refreshed when modifying the user, the information of the library information storage unit that is flushed needs to be refreshed, and the jdbc method is used to ensure the normal release of the number of database connections, so as not to affect the original database. At the same time, intercept SQL according to the rules at the conversion point in the source code and record SQL as a log for auditing.

[0005] If the actual database is on port 3306 of the ip1 machine, and the proxy service is on the ip2 machine, start the gateway service, and the gateway service obtains the corresponding data source data according to the configured startup parameters, calls the startup script of shardingsphere-proxy according to the data source data, and passes in the gateway service port and proxy port. The shardingsphere-proxy agent starts the proxy service according to the proxy port, and reads the user information and rule information at a fixed time, modifies the user information configuration in the context, and intercepts SQL at the same time.

[0006] In summary, the present invention obtains information such as users and data sources by calling interfaces at regular intervals, and dynamically refreshes memory variable information in the proxy service based on the obtained information, thereby solving the problem 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 comprises the following steps:

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

[0009] S2. The proxy service periodically obtains the relevant data with its own proxy port as the key from the process that creates 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 the cache saved last time, modifies the data source generation library, and sets the access user of the proxy database according to the user data changes;

[0011] S4. For users who need to be configured through yaml in the proxy service, modify the yamlConfig in the context reading part in the source code so that the information in the java program memory variables is changed after the actual configuration is 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 method for database proxy protection and dynamic setting 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 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 reads the user configuration information and the rule configuration information at a regular interval, modifies the user information configuration in the context and intercepts SQL at the same time.

[0014] Furthermore, the method for 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 for the gateway service to obtain relevant information for starting the proxy service of the relevant configuration.

[0015] Furthermore, the configuration service in the method for database proxy protection and dynamic setting 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, password;

[0017] The account information includes: account name and 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: the proxy service proxy1 port and the 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 the 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 in the database so that the account accessing the proxy database can access all tables in the corresponding database;

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

[0028] On the other hand, the present invention also provides a system for database proxy protection and dynamic settings, which implements the steps of the above-mentioned method for database proxy protection and dynamic settings 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, and 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 briefly introduces 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 creative work.

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

[0033] Figure 2 The method of the present invention is to dynamically modify the virtual database and account logic diagram by shardingsphere-proxy.

[0034] Figure 3 It is a schematic diagram of the implementation process of the method of the present invention. DETAILED DESCRIPTION

[0035] In order to make the purpose, technical solution and advantages of the present invention clearer, the technical solution of the present invention will be clearly and completely described below in conjunction with specific embodiments. Obviously, the described embodiments are only part of the embodiments of the present invention, not all of the embodiments, and the present invention can also be implemented or applied through other different specific implementation methods. The details in this specification can also be modified or changed in various ways 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 protection scope 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 protection scope of the present invention.

[0037] Embodiment: A method for database proxy protection and dynamic setting

[0038] like Figure 1 As shown, this method is used to access the source database by accessing the proxy database and dynamically configure the account. 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 to start the corresponding port service by the gateway.

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

[0044] 6. Access the proxy database with 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 started 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 fixed time.

[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 relevant 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 contents disclosed above without departing from the scope of the technical solution of the present invention to obtain equivalent embodiments of equivalent changes. However, any simple modification, 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 source, user configuration information, and rule configuration information by calling external interfaces at regular intervals, and dynamically create database proxy services for different proxy ports; S2. The proxy service periodically obtains the relevant data with its own proxy port as the key from the process that creates 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 the cache saved last time, modifies the data source generation library, and sets the access user of the proxy database according to the user data changes; S4. For users who need to be configured through yaml in the proxy service, modify the yamlConfig in the context reading part in the source code so that the information in the memory variables of the java program actually read is changed to realize dynamic modification of the user.

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 refreshed library information storage unit information is refreshed again, and the jdbc method is used to execute 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: 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 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 reads the user configuration information and the rule configuration information at a fixed time, modifies the user information configuration in the context and intercepts SQL at the same time.

4. 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 of the relevant configuration.

5. The method for database proxy protection and dynamic setting according to claim 4, 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, password; The account information includes: account name and password; The proxy gateway information includes: proxy gateway name, server and gateway service port, and corresponding data source; The proxy service information includes: the proxy service proxy1 port and the corresponding gateway service; the proxy service is started by the corresponding gateway service on the same machine.

6. The method for database proxy protection and dynamic setting according to claim 4, characterized in that: The implementation process of the proxy 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 the 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 corresponding to the link source database according to the library list information in the data source information read at a fixed time; f) The proxy service executes the LOAD SINGLE TABLE*.* SQL statement in each database of the source data source to map the tables in the database so that the account accessing the proxy database can access all tables in the corresponding database; g) The client uses the configured account and password to access the proxy database and perform relevant SQL operations on the source database.

7. A database proxy protection and dynamic setting system, characterized in that: When the system is running, the steps of the method for database proxy protection and dynamic setting described in any one of claims 1 to 6 are implemented.

8. 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 6 are implemented.

Images