Database Access Method and Device

Abstract

The present invention discloses a database access method and device. The method includes receiving a user identifier and an SQL statement sent by a front-end interface; encapsulating and identifying the SQL statement to generate a corresponding SQL function; determining the access right of the user to the target database to be accessed according to the user identifier and the parameters in the SQL function; when it is determined that the user has the access right to the target database, writing the data tables and data table fields in the target database into a pre-created intermediate database; accessing the intermediate database by using the generated SQL function, and returning the return value to the front-end interface. By creating an intermediate database containing a system identifier and user identifiers with access rights to the database, the present invention enables multiple systems to share a front-end interface and access databases corresponding to different systems through the intermediate database, while maintaining the independence of each system and ensuring the front-end performance.

Description

Technical Field

[0001] The present invention relates to the technical field of system development, and particularly to a database access method and device. Background Art

[0002] This section aims to provide background or context for the embodiments of the present invention described in the claims. The description herein is not admitted to be prior art merely because it is included in this section.

[0003] In different organizational structures of a project system, there are the same functional departments. Their requirements for the same system have both commonalities and particularities. Overall, the system is the same, but specifically for function points, reports, data, etc., there are some subtle differences. As a risk - operating enterprise, banks with branch institutions abroad are strictly regulated by the regulatory authorities of each country. For example, for banks with branches (institutions) abroad, local regulatory authorities require that some regulatory reporting systems be independent and not allow mutual access to databases. For some important regulatory reporting data, such as when a bank calculates its capital adequacy ratio, the data must be retrieved by the system, and the system must maintain independence. Therefore, for the same functional module, each branch in a different country has to develop an independent system, which will cause huge resource waste.

[0004] The system is mainly divided into the front - end JavaWeb part and the back - end database part. Generally, an organization uses an independent system of its own. However, if the front - end interfaces (JavaWeb) have the same functions, the traditional practice between different institutional systems is to commonly use a single JavaWeb. This JavaWeb will connect to one or more databases, and each organization accesses its own database through the JavaWeb. However, the data sources that the server can connect to are limited and cannot grow without limit. When the number of organizations using the same JavaWeb increases, the number of databases that this JavaWeb needs to connect to will increase, the memory occupied by the Java server will also increase, and the performance will seriously decline. If a separate system is developed for each organization because of this same function, the cost will be too high and there will be too much redundancy, resulting in great resource waste. Summary of the Invention

[0005] Embodiments of the present invention provide a database access method to ensure the front - end performance while keeping multiple systems independent. The database access method includes:

[0006] Receiving a user identifier and an SQL statement in the user login information sent by the front - end interface;

[0007] Encapsulating and identifying the received SQL statement to generate an SQL function corresponding to the SQL statement;

[0008] Determine the access permission of the user to the target database to be accessed according to the user identification and the parameters in the generated SQL function;

[0009] When it is determined that the user has the access permission to the target database, write the data tables and data table fields in the target database into the pre-created intermediate database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identification of each system and the user identification with the access permission to the database corresponding to the system identification; the data tables of the databases corresponding to each system include a database identification field with a value of the database identification;

[0010] Use the generated SQL function to access the intermediate database and return the return value to the front-end interface.

[0011] In an embodiment of the present invention, the database access method further includes:

[0012] When it is determined that the user does not have the access permission to the target database, reject the user's access to the target database.

[0013] In an embodiment of the present invention, the parameters in the SQL function include the database identification and / or system identification of the target database. Determining the access permission of the user to the target database to be accessed according to the user identification and the parameters in the generated SQL function includes: determining the target database to be accessed according to the database identification and / or system identification in the generated SQL function;

[0014] Based on the identification data table of the intermediate database, determine the access permission of the user to the target database to be accessed according to the user identification.

[0015] In an embodiment of the present invention, the database access method further includes:

[0016] Create an intermediate database according to the received database creation instruction.

[0017] In an embodiment of the present invention, the database access method further includes:

[0018] Create an identification data table in the intermediate database according to the received table creation instruction.

[0019] In an embodiment of the present invention, the database access method further includes:

[0020] Create a database identification field for the data tables included in the databases corresponding to each system according to the received field creation instruction.

[0021] In an embodiment of the present invention, the database identification of each system is the same as the system identification.

[0022] An embodiment of the present invention further provides a database access device for ensuring the foreground performance while keeping multiple systems independent. The database access device includes:

[0023] A receiving module, configured to receive a user identifier and an SQL statement in the user login information sent by the foreground interface;

[0024] A function generation module, configured to encapsulate and identify the received SQL statement to generate an SQL function corresponding to the SQL statement;

[0025] A permission determination module, configured to determine the access permission of the user to the target database to be accessed according to the user identifier and the parameters in the generated SQL function;

[0026] A storage module, configured to write the data tables and data table fields in the target database into a pre-created intermediate database when it is determined that the user has the access permission to the target database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier having the access permission to the database corresponding to the system identifier; the data tables of the databases corresponding to each system include a database identifier field with a value of the database identifier;

[0027] A database access module, configured to access the intermediate database by using the generated SQL function and return the return value to the foreground interface.

[0028] In an embodiment of the present invention, the database access device further includes:

[0029] A denied access module, configured to deny the user access to the target database when it is determined that the user does not have the access permission to the target database.

[0030] In an embodiment of the present invention, the parameters in the SQL function include the database identifier and / or system identifier of the target database, and the permission determination module includes:

[0031] A target database determination unit, configured to determine the target database to be accessed according to the database identifier and / or system identifier in the generated SQL function;

[0032] A permission determination unit, configured to determine the access permission of the user to the target database to be accessed according to the user identifier based on the identification data table of the intermediate database.

[0033] In an embodiment of the present invention, the database access device further includes:

[0034] A database creation module, configured to create an intermediate database according to the received database creation instruction.

[0035] In an embodiment of the present invention, the database access device further includes:

[0036] A data table creation module, configured to create an identification data table in an intermediate database according to a received table creation instruction.

[0037] In an embodiment of the present invention, the database access device further includes:

[0038] A field creation module, configured to create a database identification field of a data table included in a database corresponding to each system according to a received field creation instruction.

[0039] In an embodiment of the present invention, the database identification of each system is the same as the system identification.

[0040] An embodiment of the present invention further provides a computer device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the computer program, the above-mentioned database access method is implemented.

[0041] An embodiment of the present invention further provides a computer-readable storage medium, which stores a computer program for executing the above-mentioned database access method.

[0042] In an embodiment of the present invention, a user identifier and an SQL statement in user login information sent by a front-end interface are received; the received SQL statement is encapsulated and recognized to generate an SQL function corresponding to the SQL statement; the access permission of the user to a target database to be accessed is determined according to the user identifier and the parameters in the generated SQL function; when it is determined that the user has the access permission to the target database, the data tables and data table fields in the target database are written into a pre-created intermediate database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier having the access permission to the database corresponding to the system identifier; the data tables of the databases corresponding to each system include database identification fields with values being database identifications; the generated SQL function is used to access the intermediate database, and the return value is returned to the front-end interface. In the embodiment of the present invention, by creating an intermediate database including the system identifier and the user identifier having the access permission to the database corresponding to the system identifier, multiple systems can share a front-end interface and access databases corresponding to different systems through the intermediate database, while maintaining the independence of each system and ensuring the front-end performance. Description of the Drawings

[0043] To more clearly illustrate the technical solutions in the embodiments of the present invention or the prior art, the following briefly introduces the accompanying drawings required for use in the description of the embodiments or the prior art. Obviously, the accompanying drawings in the following description are only some embodiments of the present invention. For those of ordinary skill in the art, without creative efforts, other accompanying drawings can be obtained based on these drawings. In the accompanying drawings:

[0044] Figure 1 It is a flowchart of the implementation of the database access method provided by the embodiment of the present invention;

[0045] Figure 2 It is another flowchart of the implementation of the database access method provided by the embodiment of the present invention;

[0046] Figure 3 It is a flowchart of the implementation of step 103 in the database access method provided by the embodiment of the present invention;

[0047] Figure 4 It is yet another flowchart of the implementation of the database access method provided by the embodiment of the present invention;

[0048] Figure 5 It is a functional module diagram of the database access device provided by the embodiment of the present invention;

[0049] Figure 6 It is another functional module diagram of the database access device provided by the embodiment of the present invention;

[0050] Figure 7 It is a structural block diagram of the permission determination module 503 in the database access device provided by the embodiment of the present invention;

[0051] Figure 8 It is yet another functional module diagram of the database access device provided by the embodiment of the present invention. Detailed implementation manners

[0052] To make the objectives, technical solutions, and advantages of the embodiments of the present invention clearer and more understandable, the following further elaborates on the embodiments of the present invention in conjunction with the accompanying drawings. Herein, the illustrative embodiments of the present invention and their descriptions are used to explain the present invention, but not to limit the present invention.

[0053] Figure 1 The implementation process of the database access method provided by the embodiment of the present invention is shown. For ease of description, only the parts related to the embodiment of the present invention are shown and are described in detail as follows:

[0054] As Figure 1 shown, the database access method includes:

[0055] Step 101, receiving the user identifier and the SQL statement in the user login information sent by the front-end interface;

[0056] Step 102: Encapsulate and identify the received SQL statement to generate an SQL function corresponding to the SQL statement.

[0057] Step 103: Determine the access right of the user to the target database to be accessed according to the user identifier and the parameters in the generated SQL function.

[0058] Step 104: When it is determined that the user has the access right to the target database, write the data tables and data table fields in the target database into a pre-created intermediate database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier with the access right to the database corresponding to the system with the system identifier; the data tables in the database corresponding to each system include a database identifier field with a value of the database identifier;

[0059] Step 105: Use the generated SQL function to access the intermediate database and return the return value to the front-end interface.

[0060] In the embodiment of the present invention, a corresponding database is built for each system. To ensure the independence between systems, the present invention pre-creates an intermediate database between the front-end interface and the database of each system, and accesses the databases of each system through the intermediate database to ensure the independence between systems.

[0061] When creating the intermediate database, further create an identification data table in the intermediate database. The identification data table includes at least two fields, one is the system identifier of each system, and the other is the user identifier with the access right to the database corresponding to the system with the system identifier. In addition, the identification data table may further include the corresponding relationship between the system identifier of the system and the user identifier with the access right to the database corresponding to the system with the system identifier, that is, only the user identifier corresponding to a certain system identifier has the access right to the database of a certain system.

[0062] Secondly, it is necessary to add a database identifier field to each data table in the database corresponding to each system. The default value of the database identifier field is the database identifier of the database to which the data table belongs, so as to ensure that when data is added to this data table, the value of the database identifier field is the database identifier of the database to which the data table belongs. In addition, to ensure that the user can access the databases of each system through the intermediate database, the databases of each system need to authorize the intermediate database to ensure that the user can operate on the databases of each system through the intermediate database.

[0063] Specifically, when accessing the databases of each system through an intermediate database, the user first logs in to a certain system through the front-end interface. The user login information contains a user identifier (with the permission to access the database of a certain system). After the user logs in to the system, the front-end interface also receives the SQL statements input by the user, and then receives the user identifier in the user login information sent by the front-end interface and the SQL statements input by the user. Then, the received SQL statements are encapsulated and recognized to generate SQL functions corresponding to the SQL statements.

[0064] Among them, the target database to be accessed by the user can be determined through the parameters in the generated SQL functions. Since a user with a user identifier can only have access to the database of the logged-in system, based on this, it can be determined whether the user has access to the target database to be accessed according to the user identifier and the parameters in the generated SQL functions. When it is determined that the user has access to the target database, it means that the user can safely access the target database. At this time, the data tables and data table fields in the target database can be written into the pre-created intermediate database; at this time, the intermediate database not only contains all the data tables and data table fields of the target database, but also includes the database identification fields of the data tables included in the target database.

[0065] Finally, the generated SQL functions can be used to access the intermediate database. Actually, the data of the target database is accessed, and the return value is returned to the front-end interface.

[0066] In the embodiment of the present invention, the user identifier and SQL statements in the user login information sent by the front-end interface are received; the received SQL statements are encapsulated and recognized to generate SQL functions corresponding to the SQL statements; the access permission of the user to the target database to be accessed is determined according to the user identifier and the parameters in the generated SQL functions; when it is determined that the user has access to the target database, the data tables and data table fields in the target database are written into the pre-created intermediate database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier with the permission to access the database of the system corresponding to the system identifier; the data tables of the databases corresponding to each system include database identification fields with values of database identifiers; the generated SQL functions are used to access the intermediate database, and the return value is returned to the front-end interface. By creating an intermediate database containing the system identifier and the user identifier with the permission to access the database of the system corresponding to the system identifier in the embodiment of the present invention, multiple systems can share a front-end interface and access the databases corresponding to different systems through this intermediate database, while maintaining the independence of each system and ensuring the front-end performance.

[0067] Figure 2Another implementation process of the database access method provided by the embodiments of the present invention is shown. For ease of description, only the parts related to the embodiments of the present invention are shown and are described in detail as follows:

[0068] In an embodiment of the present invention, in order to ensure the security of the database, as Figure 2 shown, on the basis of the above method steps, it further includes:

[0069] Step 201, when it is determined that the user does not have the access right to the target database, reject the user's access to the target database.

[0070] Among them, the user may or may not have the access right to the target database. When it is determined that the user does not have the access right to the target database, at this time, in order to ensure the security of the database, the user's access to the target database can be rejected.

[0071] In the embodiments of the present invention, when it is determined that the user does not have the access right to the target database, rejecting the user's access to the target database can ensure the security of the database.

[0072] Figure 3 The implementation process of step 103 in the database access method provided by the embodiments of the present invention is shown. For ease of description, only the parts related to the embodiments of the present invention are shown and are described in detail as follows:

[0073] In an embodiment of the present invention, the parameters in the SQL function include the database identifier and / or system identifier of the target database. In order to improve the efficiency of determining the access right to the target database, as Figure 3 shown, step 103, determining the access right of the user to the target database to be accessed according to the user identifier and the parameters in the generated SQL function, includes:

[0074] Step 301, determining the target database to be accessed according to the database identifier and / or system identifier in the generated SQL function;

[0075] Step 302, based on the identification data table of the intermediate database, determining the access right of the user to the target database to be accessed according to the user identifier.

[0076] In view of the fact that each system has a corresponding database, there is a corresponding relationship between the system identifier of the system and the data identifier of the system database. Accordingly, the corresponding database or database identifier can be determined through the system identifier. Therefore, when the user accesses the target database, the parameters in the generated SQL function can include the database identifier and / or system identifier of the target database.

[0077] Specifically, when determining the access permission of a user to the target database to be accessed, the target database to be accessed can be determined first based on the database identifier and / or system identifier in the generated SQL function. Given that the identifier data table includes the system identifier corresponding to the user identifier (the system identifier corresponds to the database identifier), further, based on the identifier data table of the intermediate database, the access permission of the user to the target database to be accessed can be determined according to the user identifier.

[0078] For example, when the system identifier (or database identifier) corresponding to the user identifier determined based on the identifier data table of the intermediate database is consistent with the system identifier (or database identifier) included in the parameters in the generated SQL function, it indicates that the user has the access permission to the target database; when the system identifier (or database identifier) corresponding to the user identifier determined based on the identifier data table of the intermediate database is inconsistent with the system identifier (or database identifier) included in the parameters in the generated SQL function, it indicates that the user does not have the access permission to the target database.

[0079] In addition, after determining the target database to be accessed based on the database identifier and / or system identifier in the generated SQL function, the database corresponding to the user identifier can be determined based on the identifier data table of the intermediate database according to the user identifier. When the target database is consistent with the database corresponding to the user identifier, it indicates that the user has the access permission to the target database; when the target database is inconsistent with the database corresponding to the user identifier, it indicates that the user does not have the access permission to the target database.

[0080] In the embodiment of the present invention, determining the target database to be accessed according to the database identifier and / or system identifier in the generated SQL function, and determining the access permission of the user to the target database to be accessed based on the identifier data table of the intermediate database according to the user identifier can improve the efficiency of determining the access permission of the target database.

[0081] Figure 4 Another implementation process of the database access method provided by the embodiment of the present invention is shown. For the convenience of description, only the parts related to the embodiment of the present invention are shown and are described in detail as follows:

[0082] In an embodiment of the present invention, in order to ensure the independence between systems and improve the flexibility of database / data table / field configuration, as Figure 4 shown, on the basis of the above method steps, the database access method further includes:

[0083] Step 401, creating an intermediate database according to the received database creation instruction. And / or

[0084] When creating an intermediate database, the intermediate database can be created based on the received database creation instruction. Additionally, the intermediate database can be modified based on the received modification instruction to improve the flexibility of the intermediate database configuration. For example, modify the data tables included in the intermediate database, the fields of the data tables, the database name, etc. The modification can include adding data tables, deleting fields of data tables, replacing and modifying the database name, etc.

[0085] Step 402: Create an identification data table in the intermediate database according to the received table creation instruction. And / or

[0086] When creating an identification data table in the intermediate database, the identification data table in the intermediate database can be created based on the received table creation instruction. Additionally, the identification data table in the intermediate database can be modified based on the received modification instruction to improve the flexibility of the identification data table configuration. For example, modify the identification data table in the intermediate database, the fields of the data table, the name of the data table, etc. The modification can include adding fields of the identification data table, deleting fields of the data table, replacing and modifying the name of the data table, etc.

[0087] Step 403: Create a database identification field for the data tables included in the database corresponding to each system according to the received field creation instruction.

[0088] When creating a database identification field for the data tables included in the database corresponding to each system, the database identification field for the data tables included in the database corresponding to each system can be created based on the received field creation instruction. Additionally, the database identification field for the data tables included in the database corresponding to each system can be modified based on the received modification instruction to improve the flexibility of the database identification field configuration of the data table. For example, modify the database identification field, the name of the database identification field, etc. The modification can include replacing and modifying the name of the database identification field, etc.

[0089] In the embodiment of the present invention, creating an intermediate database according to the received database creation instruction can improve the flexibility of database configuration; creating an identification data table in the intermediate database according to the received table creation instruction can improve the flexibility of data table configuration; creating a database identification field for the data tables included in the database corresponding to each system according to the received field creation instruction can improve the flexibility of database identification field configuration.

[0090] In addition, in view of the fact that each system can correspond to one or more databases, when each system corresponds to one database, the system identification of the system can be the same as the database identification of the database corresponding to the system. When each system corresponds to multiple databases, the system identification of the system can correspond to multiple database identifications of multiple databases.

[0091] An embodiment of the present invention also provides a database access device as described in the following embodiments. Since the principles of these devices for solving problems are similar to those of the database access method, the implementation of these devices can refer to the implementation of the method, and the repeated parts will not be elaborated.

[0092] Figure 5 The functional modules of the database access device provided by the embodiment of the present invention are shown. For ease of description, only the parts related to the embodiment of the present invention are shown and are described in detail as follows:

[0093] Referring to Figure 5 , each module included in the database access device is used to execute Figure 1 the respective steps in the corresponding embodiment. For details, please refer to Figure 1 and Figure 1 the relevant descriptions in the corresponding embodiment, which will not be elaborated here. In the embodiment of the present invention, the database access device includes a receiving module 501, a function generation module 502, a permission determination module 503, a storage module 504, and a database access module 505.

[0094] The receiving module 501 is used to receive the user identifier and the SQL statement in the user login information sent by the front-end interface.

[0095] The function generation module 502 is used to encapsulate and identify the received SQL statement and generate an SQL function corresponding to the SQL statement.

[0096] The permission determination module 503 is used to determine the access permission of the user to the target database to be accessed according to the user identifier and the parameters in the generated SQL function.

[0097] The storage module 504 is used to write the data table and the data table fields in the target database into a pre-created intermediate database when it is determined that the user has the access permission to the target database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier with the access permission to the database corresponding to the system identifier; the data table of the database corresponding to each system includes a database identifier field with a value of the database identifier.

[0098] The database access module 505 is used to access the intermediate database by using the generated SQL function and return the return value to the front-end interface.

[0099] In the embodiment of the present invention, a corresponding database is built for each system. To ensure the independence between systems, the present invention pre-creates an intermediate database between the front-end interface and the database of each system, and accesses the databases of each system through this intermediate database to ensure the independence between systems.

[0100] When creating this intermediate database, an identification data table is further created in the intermediate database. The identification data table includes at least two fields. One is the system identification of each system, and the other is the user identification with database access rights to the system corresponding to the system identification. In addition, the identification data table may also include the correspondence between the system identification of the system and the user identification with database access rights to the system corresponding to the system identification, that is, only the user identification corresponding to a certain system identification has the access right to the database of a certain system.

[0101] Secondly, a database identification field needs to be added to each data table in the database corresponding to each system. The default value of the database identification field is the database identification of the database to which the data table belongs, ensuring that when data is added to this data table, the value of the database identification field is the database identification of the database to which the data table belongs. In addition, to ensure that users can access the databases of each system through the intermediate database, the databases of each system need to authorize the intermediate database to ensure that users can operate on the databases of each system through the intermediate database.

[0102] Specifically, when accessing the databases of each system through the intermediate database, the user first logs in to a certain system through the front-end interface. The user login information contains the user identification (with database access rights to the system of a certain system). The front-end interface also receives the SQL statement input by the user after the user logs in to the system. Then, the receiving module 501 receives the user identification in the user login information sent by the front-end interface and the SQL statement input by the user. Then, the function generation module 502 encapsulates and identifies the received SQL statement to generate an SQL function corresponding to the SQL statement.

[0103] Among them, the permission determination module 503 can determine the target database to be accessed by the user through the parameters in the generated SQL function. Since the user with the user identification can only have access rights to the database of the logged-in system, accordingly, it can be determined whether the user has access rights to the target database to be accessed based on the user identification and the parameters in the generated SQL function. When the storage module 504 determines that the user has access rights to the target database, it means that the user can safely access the target database. At this time, the data tables and data table fields in the target database can be written into the previously created intermediate database; at this time, the intermediate database not only includes all the data tables and data table fields of the target database, but also includes the database identification fields of the data tables included in the target database.

[0104] Finally, the database access module 505 can use the generated SQL function to access the intermediate database, actually accessing the data of the target database, and returning the return value to the front-end interface.

[0105] In an embodiment of the present invention, a receiving module 501 receives a user identifier and an SQL statement in the user login information sent by a foreground interface; a function generation module 502 encapsulates and identifies the received SQL statement to generate an SQL function corresponding to the SQL statement; a permission determination module 503 determines the access permission of a user to a target database to be accessed according to the user identifier and the parameters in the generated SQL function; a storage module 504 writes the data tables and data table fields in the target database into a pre-created intermediate database when it is determined that the user has the access permission to the target database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier having the access permission to the database corresponding to the system identifier; the data tables of the databases corresponding to each system include a database identifier field with a value of the database identifier; a database access module 505 accesses the intermediate database by using the generated SQL function and returns the return value to the foreground interface. By creating an intermediate database containing the system identifier and the user identifier having the access permission to the database corresponding to the system identifier in the embodiment of the present invention, multiple systems can share a foreground interface and access the databases corresponding to different systems through the intermediate database, while maintaining the independence of each system and ensuring the foreground performance.

[0106] Figure 6 FIG. shows another functional module of the database access device provided by the embodiment of the present invention. For the convenience of description, only the part related to the embodiment of the present invention is shown and is described in detail as follows:

[0107] In an embodiment of the present invention, in order to ensure the security of the database, refer to Figure 6 , each module included in the database access device is used to execute Figure 2 the respective steps in the corresponding embodiment. For details, please refer to Figure 2 and Figure 2 the relevant descriptions in the corresponding embodiment, which will not be elaborated here. In the embodiment of the present invention, on the basis of the module structure shown above, Figure 5 the database access device further includes a denied access module 601.

[0108] The denied access module 601 is used to deny the user access to the target database when it is determined that the user does not have the access permission to the target database.

[0109] Among them, the user may or may not have the access permission to the target database. When the denied access module 601 determines that the user does not have the access permission to the target database, at this time, to ensure the security of the database, the user can be denied access to the target database.

[0110] In an embodiment of the present invention, when the access rejection module 601 determines that the user does not have the access right to the target database, it rejects the user's access to the target database, which can ensure the security of the database.

[0111] Figure 7 The following shows a schematic structure of the permission determination module 503 in the database access device provided by the embodiment of the present invention. For the convenience of description, only the parts related to the embodiment of the present invention are shown and are described in detail as follows:

[0112] In an embodiment of the present invention, the parameters in the SQL function include the database identifier and / or system identifier of the target database. To improve the efficiency of determining the access right to the target database, refer to Figure 7 and each unit included in the permission determination module 503 is used to execute Figure 3 the respective steps in the corresponding embodiment. For details, please refer to Figure 3 as well as Figure 3 the relevant descriptions in the corresponding embodiment, which will not be elaborated here. In the embodiment of the present invention, the permission determination module 503 includes a target database determination unit 701 and a permission determination unit 702.

[0113] The target database determination unit 701 is configured to determine the target database to be accessed according to the database identifier and / or system identifier in the generated SQL function.

[0114] The permission determination unit 702 is configured to determine the access right of the user to the target database to be accessed based on the identification data table of the intermediate database according to the user identifier.

[0115] Since each system has a corresponding database, there is a corresponding relationship between the system identifier of the system and the data identifier of the system database. Accordingly, the corresponding database or database identifier can be determined through the system identifier. Therefore, when the user accesses the target database, the parameters in the generated SQL function can include the database identifier and / or system identifier of the target database.

[0116] Specifically, when determining the access right of the user to the target database to be accessed, the target database determination unit 701 can first determine the target database to be accessed based on the database identifier and / or system identifier in the generated SQL function. Since the identification data table includes the system identifier corresponding to the user identifier (the system identifier corresponds to the database identifier), further, the permission determination unit 702 can determine the access right of the user to the target database to be accessed based on the identification data table of the intermediate database according to the user identifier.

[0117] For example, when the system identifier (or database identifier) corresponding to the user identifier determined by the permission determination unit 702 based on the identity data table of the intermediate database is consistent with the system identifier (or database identifier) included in the parameters in the generated SQL function, it indicates that the user has the access permission to the target database; when the system identifier (or database identifier) corresponding to the user identifier determined by the permission determination unit 702 based on the identity data table of the intermediate database is inconsistent with the system identifier (or database identifier) included in the parameters in the generated SQL function, it indicates that the user does not have the access permission to the target database.

[0118] In addition, after the target database determination unit 701 determines the target database to be accessed based on the database identifier and / or system identifier in the generated SQL function, the permission determination unit 702 can determine the database corresponding to the user identifier based on the identity data table of the intermediate database according to the user identifier. When the target database is consistent with the database corresponding to the user identifier, it indicates that the user has the access permission to the target database; when the target database is inconsistent with the database corresponding to the user identifier, it indicates that the user does not have the access permission to the target database.

[0119] In the embodiment of the present invention, the target database determination unit 701 determines the target database to be accessed according to the database identifier and / or system identifier in the generated SQL function, and the permission determination unit 702 determines the access permission of the user to the target database to be accessed based on the identity data table of the intermediate database according to the user identifier, which can improve the efficiency of determining the access permission of the target database.

[0120] Figure 8 Another functional module of the database access device provided by the embodiment of the present invention is shown. For the convenience of description, only the part related to the embodiment of the present invention is shown and is described in detail as follows:

[0121] In an embodiment of the present invention, in order to ensure the independence between systems and improve the flexibility of database / data table / field configuration, refer to Figure 8 , each module included in the database access device is used to execute Figure 4 the respective steps in the corresponding embodiment. For details, please refer to Figure 4 and Figure 4 the relevant descriptions in the corresponding embodiment, which will not be elaborated here. In the embodiment of the present invention, based on the above module structure, the database access device includes a database creation module 801, a data table creation module 802, and a field creation module 803.

[0122] The database creation module 801 is used to create an intermediate database according to the received database creation instruction.

[0123] The data table creation module 802 is used to create an identity data table in the intermediate database according to the received table creation instruction.

[0124] The field creation module 803 is used to create a database identity field for the data tables included in the databases corresponding to each system according to the received field creation instruction.

[0125] When creating the intermediate database, the database creation module 801 can create the intermediate database based on the received database creation instruction. Additionally, it can also modify the intermediate database based on the received modification instruction to improve the flexibility of the intermediate database configuration. For example, modify the data tables, data table fields, database names, etc. included in the intermediate database. The modification can include adding data tables, deleting data table fields, replacing and modifying the database name, etc.

[0126] When creating the identity data table in the intermediate database, the data table creation module 802 can create the identity data table in the intermediate database according to the received table creation instruction. Additionally, it can also modify the identity data table in the intermediate database based on the received modification instruction to improve the flexibility of the identity data table configuration. For example, modify the identity data table, data table fields, data table names, etc. in the intermediate database. The modification can include adding identity data table fields, deleting data table fields, replacing and modifying the data table name, etc.

[0127] When creating the database identity fields for the data tables included in the databases corresponding to each system, the field creation module 803 can create the database identity fields for the data tables included in the databases corresponding to each system according to the received field creation instruction. Additionally, it can also modify the database identity fields for the data tables included in the databases corresponding to each system based on the received modification instruction to improve the flexibility of the database identity field configuration for the data tables. For example, modify the database identity fields, database identity field names, etc. The modification can include replacing and modifying the database identity field name, etc.

[0128] In the embodiment of the present invention, the database creation module 801 creates the intermediate database according to the received database creation instruction, which can improve the flexibility of database configuration; the data table creation module 802 creates the identity data table in the intermediate database according to the received table creation instruction, which can improve the flexibility of data table configuration; the field creation module 803 creates the database identity fields for the data tables included in the databases corresponding to each system according to the received field creation instruction, which can improve the flexibility of database identity field configuration.

[0129] An embodiment of the present invention further provides a computer device, including a memory, a processor, and a computer program stored on the memory and executable on the processor. When the processor executes the computer program, the above-mentioned database access method is implemented.

[0130] An embodiment of the present invention further provides a computer-readable storage medium storing a computer program for executing the above-mentioned database access method.

[0131] The following briefly describes the basic working principle of the present invention in conjunction with a specific example:

[0132] The solution of the present invention is to develop a new database (intermediate database) between JavaWeb and each database. All institutional systems use the same JavaWeb and access different databases through an intermediate database, which not only maintains the independence between different databases (systems), but also does not cause excessive memory occupation of the Java server and affect the foreground performance.

[0133] 1. Database part

[0134] Establish a new database as the intermediate database, such as the RWA_BR database. This database mainly plays a role in management and distribution.

[0135] Establish a database for each institutional system. For example, for the DC005 institution, develop the RWA_DC005 database. Each institutional database needs to authorize the RWA_BR database to ensure that the RWA_BR database can operate on each institutional database (such as the RWA_DC005 database).

[0136] Create a table in the RWA_BR database, including two fields: the multi-entity number MULTI_TENANCY_ID (system identifier) and the institutional position number POSITION_ID (user identifier). One institution (system) corresponds to one multi-entity number (system identifier). As shown in the following figure, DC005 is the number of system DC005, and the corresponding database for this institutional system is the RWA_DC005 database. The POSITION_ID (user identifier) field records the numbers of the employees in this department who need to use this system. For example, assume that the institution (system) numbered DC005 includes user numbers such as GRWA10000001, GRWA10000002... GRWA10000038, GRWA10000039, etc. A user with the employee number GRWA10000001 logs in to this system, and he belongs to the institution (system) numbered DC005.

[0137] Add a database identification field to each data table in the database of each institution (system). As shown in the figure below, for the database RWA_DC005 of the system DC005, a database identification field named RWA_USER_ID needs to be added to the data tables inside, and the value of this field is set to the default value DC005. Ensure that when adding data to this table, the value of the RWA_USER_ID field will always be DC005.

[0138] 2. JavaWeb Part

[0139] When deploying the JavaWeb project, use the RWA_BR database as the only database. Solve the problem of a JavaWeb corresponding to multiple databases;

[0140] When logging in to the system, first use the user number for login to query in the RWA_BR database which institution the user belongs to. For example, if it is learned through the query that the user belongs to the institution numbered DC005, then the user can only operate on the RWA_DC005 database and does not have access rights to other databases except the RWA_DC005 database. Based on this, the independence between databases can be maintained.

[0141] After entering the system, when the user performs any operation on the database and generates an SQL function to call the database, the institution number (system identification) of the user can be carried. For example, when performing a query operation, the business unit number DC005 of the user and the SQL statement to be queried are passed into the stored procedure in RWA_BR at the same time, and the stored procedure is used to judge and operate on the corresponding database.

[0142] Write a stored procedure in the RWA_BR database. The main function of this stored procedure is to encapsulate and identify the SQL statements for performing insert, delete, update, and query operations passed in from the front end (i.e., JavaWeb), so as to achieve operations on different databases. The implementation principle of this stored procedure is as follows: First, receive the SQL statement and parameters passed in by javaweb; generate an actual executed SQL function through the stored procedure to operate on the corresponding database; then query out all the tables and fields in the target database to be accessed, and then write them into the RWA_BR database. The written data will not only contain all the required table and field information, but also have the database identification corresponding to this table and field marked, that is, the RWA_USER_ID field in the table. For example, if the data comes from the DC005 database, then the data stored in the RWA_BR database will contain the number DC005. Finally, return the return value to javaWeb.

[0143] Through the above operations, it is possible to use only one piece of Java Web front-end code to meet the requirement of a project to manage the same business functions of multiple institutions simultaneously.

[0144] As an enterprise operating risks, a bank is regulated by local departments when opening branch institutions abroad. Therefore, for some of the most important regulatory reporting systems, the local government requires their independence. Thus, the present invention can achieve flexible switching between different systems of the same business function modules (Java Web) of multiple branch institution systems, maintain independence, and avoid wasting resources caused by repeated system development.

[0145] In summary, in the embodiment of the present invention, the user identifier and the SQL statement in the user login information sent by the front-end interface are received; the received SQL statement is encapsulated and recognized to generate an SQL function corresponding to the SQL statement; the access permission of the user to the target database to be accessed is determined according to the user identifier and the parameters in the generated SQL function; when it is determined that the user has the access permission to the target database, the data tables and data table fields in the target database are written into a pre-created intermediate database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier having the access permission to the database corresponding to the system identifier; the data tables of the databases corresponding to each system include a database identifier field with a value of the database identifier; the generated SQL function is used to access the intermediate database, and the return value is returned to the front-end interface. By creating an intermediate database containing the system identifier and the user identifier having the access permission to the database corresponding to the system identifier, the embodiment of the present invention enables multiple systems to share a front-end interface, access the databases corresponding to different systems through the intermediate database, and ensure the front-end performance while keeping each system independent.

[0146] Those skilled in the art should understand that the embodiments of the present invention can be provided as a method, a system, or a computer program product. Therefore, the present invention can take the form of a complete hardware embodiment, a complete software embodiment, or an embodiment combining software and hardware aspects. Moreover, the present invention can take the form of a computer program product implemented on one or more computer-usable storage media (including but not limited to disk storage, CD-ROM, optical storage, etc.) containing computer-usable program code.

[0147] The present invention is described with reference to the flowcharts and / or block diagrams of methods, apparatuses (systems), and computer program products according to embodiments of the present invention. It should be understood that each flow and / or block in the flowcharts and / or block diagrams, as well as the combination of flows and / or blocks in the flowcharts and / or block diagrams, can be implemented by computer program instructions. These computer program instructions can be provided to the processor of a general-purpose computer, a special-purpose computer, an embedded processor, or other programmable data processing devices to generate a machine, such that the instructions executed by the processor of the computer or other programmable data processing devices generate means for implementing the functions specified in one flow Figure 1 one flow or multiple flows and / or blocks Figure 1 or multiple blocks.

[0148] These computer program instructions can also be stored in a computer-readable memory that can direct a computer or other programmable data processing device to work in a specific manner, such that the instructions stored in the computer-readable memory generate a manufactured article including instruction means for implementing the functions specified in one flow Figure 1 one flow or multiple flows and / or blocks Figure 1 or multiple blocks.

[0149] These computer program instructions can also be loaded onto a computer or other programmable data processing device, such that a series of operation steps are executed on the computer or other programmable device to generate a computer-implemented process, and thus the instructions executed on the computer or other programmable device provide steps for implementing the functions specified in one flow Figure 1 one flow or multiple flows and / or blocks Figure 1 or multiple blocks.

[0150] The specific embodiments described above further elaborate on the purpose, technical solutions, and beneficial effects of the present invention. It should be understood that the above are only specific embodiments of the present invention and are not used to limit the protection scope of the present invention. Any modifications, equivalent replacements, improvements, etc. made within the spirit and principles of the present invention should be included in the protection scope of the present invention.

Claims

1. A database access method, characterized in that, including: receiving the user identifier and SQL statement in the user login information sent by the front desk interface; encapsulating and identifying the received SQL statement to generate an SQL function corresponding to the SQL statement; determining the access permission of the user to the target database to be accessed according to the user identifier and the parameters in the generated SQL function; when it is determined that the user has the access permission to the target database, writing the data tables and data table fields in the target database into a pre-created intermediate database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier with the access permission to the database corresponding to the system identifier; the data tables of the database corresponding to each system include a database identifier field with a value of the database identifier; using the generated SQL function to access the intermediate database and returning the return value to the front desk interface; the parameters in the SQL function include the database identifier and / or system identifier of the target database, and determining the access permission of the user to the target database to be accessed according to the user identifier and the parameters in the generated SQL function includes: determining the target database to be accessed according to the database identifier and / or system identifier in the generated SQL function; based on the identification data table of the intermediate database, determining the access permission of the user to the target database to be accessed according to the user identifier.

2. The database access method according to claim 1, characterized in that also including: when it is determined that the user does not have the access permission to the target database, rejecting the user's access to the target database.

3. The database access method according to claim 1, wherein also including: creating an intermediate database according to the received database creation instruction.

4. The database access method according to claim 1, characterized in that also including: creating an identification data table in the intermediate database according to the received table creation instruction.

5. The database access method according to claim 1, wherein also including: creating a database identifier field of the data table included in the database corresponding to each system according to the received field creation instruction.

6. The database access method according to claim 1, wherein The database identifier of each system is the same as the system identifier.

7. A database access device, characterized in that, including: a receiving module for receiving the user identifier and SQL statement in the user login information sent by the front desk interface; a function generation module for encapsulating and identifying the received SQL statement to generate an SQL function corresponding to the SQL statement; a permission determination module for determining the access permission of the user to the target database to be accessed according to the user identifier and the parameters in the generated SQL function; a storage module for writing the data tables and data table fields in the target database into a pre-created intermediate database when it is determined that the user has the access permission to the target database; the intermediate database includes a pre-created identification data table, and the identification data table includes the system identifier of each system and the user identifier with the access permission to the database corresponding to the system identifier; the data tables of the database corresponding to each system include a database identifier field with a value of the database identifier; a database access module for using the generated SQL function to access the intermediate database and returning the return value to the front desk interface; the parameters in the SQL function include the database identifier and / or system identifier of the target database, and the permission determination module includes: a target database determination unit for determining the target database to be accessed according to the database identifier and / or system identifier in the generated SQL function; A permission determination unit, configured to determine the access permission of a user to a target database to be accessed based on an identity data table in an intermediate database according to the user identity.

8. The database access device according to claim 7, wherein It further includes: A denied access module, configured to deny the user access to the target database when it is determined that the user does not have the access permission to the target database.

9. The database access device according to claim 7, characterized in that, It further includes: A database creation module, configured to create an intermediate database according to a received database creation instruction.

10. The database access device according to claim 7, wherein It further includes: A data table creation module, configured to create an identity data table in the intermediate database according to a received table creation instruction.

11. The database access device according to claim 7, wherein It further includes: A field creation module, configured to create a database identity field for a data table included in a database corresponding to each system according to a received field creation instruction.

12. The database access device according to claim 7, wherein The database identity of each system is the same as the system identity.

13. A computer device, comprising a memory, a processor, and a computer program stored on the memory and executable on the processor, characterized in that, When the processor executes the computer program, it implements the database access method according to any one of claims 1 to 6.

14. A computer-readable storage medium, characterized in that, The computer-readable storage medium stores a computer program for executing the database access method according to any one of claims 1 to 6.

Images