A database table-level data isolation method and system based on SM3 national encryption

Abstract

The present invention particularly relates to a database table-level data isolation method and system based on SM3 national encryption. This database table-level data isolation method and system based on SM3 national encryption utilizes the user's mobile phone number in combination with the password to generate a dynamic database table name through SM3 national encryption calculation; when querying data, the table name is dynamically calculated through user information to dynamically splice SQL statements, thereby avoiding the exposure of user-specific table names and achieving data isolation between users; horizontal splitting of database tables is achieved by adding suffixes, and the mapping relationship between the original database table name and the extended database table name is recorded for user query and use. This database table-level data isolation method and system based on SM3 national encryption utilizes the SM3 national encryption algorithm to encrypt database table names, which not only ensures data isolation between users and the privacy and confidentiality of user tables, but also does not rely on third-party middleware, reducing the complexity and maintenance costs of the system, and is suitable for promotion and application.

Description

Technical Field

[0001] The present invention relates to the field of cryptography and data security technology, and in particular to a database table-level data isolation method and system based on SM3 national encryption. Background Art

[0002] 1) Cryptographic hash functions

[0003] A cryptographic hash function, also known as a cryptographic hash function, is a type of hash function. It's considered a one-way function, meaning it's extremely difficult to deduce the input data from the hash function's output. Such one-way functions are known as the "workhorses of modern cryptography." The input data to a hash function is often called a message, and its output is often called a message digest or digest.

[0004] SM3 is a domestically developed commercial algorithm. In commercial cryptography, SM3 is primarily used for digital signatures and verification, message authentication code generation and verification, and random number generation. Its algorithm is public, and its security and efficiency are comparable to SHA-256.

[0005] 2) Sharding database and tables

[0006] In current real-world applications, the amount of data stored grows over time, leading to a gradual increase in the number of queries per second (QPS) during database queries. This, in turn, occupies database read and write resources for extended periods of time, contributing to slow business growth. Therefore, databases used over extended periods of time require performance optimization.

[0007] However, typical relational database tables are stored on a row-by-row basis. Even when data isolation is implemented, it's usually done solely through a specific field, such as the tenant ID. In practice, database table names are often self-explanatory, and specific user data can be quickly located after knowing the user's tenant ID. Furthermore, when data volumes are excessive, middleware is often used to shard the database and tables, making maintenance difficult. This suggests that when a single table's data volume exceeds a certain level, neither index optimization nor query optimization can resolve performance issues.

[0008] Based on the above situation, the present invention proposes a database table-level data isolation method and system based on SM3 national encryption. Summary of the Invention

[0009] In order to remedy the defects of the prior art, the present invention provides a simple and efficient database table-level data isolation method and system based on SM3 national encryption.

[0010] The present invention is achieved through the following technical solutions:

[0011] A database table-level data isolation method based on SM3 national encryption, characterized by comprising the following steps:

[0012] Step 1: Use the user's mobile phone number and password to generate a dynamic database table name through SM3 national encryption calculation;

[0013] Each user's mobile phone number and password must be different. After using the national secret SM3 encryption algorithm, user information cannot be reversed. In the background code, it can also avoid plain text splicing when operating database table names, thereby ensuring the security of the user's database table information;

[0014] Different users have different database tables based on their user information, which enables substantial data isolation between different users. This is suitable for multi-tenant systems and facilitates the implementation of operation and maintenance operations.

[0015] Step 2: When querying data, dynamically calculate the table name based on user information and dynamically splice SQL statements, thus preventing the exposure of user-specific table names. This also achieves data isolation between users, making the operation and maintenance of a single user's data fast and efficient.

[0016] Step 3: When the amount of data in the database table is too large, horizontal splitting of the database table is achieved by adding suffixes, and the mapping relationship between the original database table name and the extended database table name is recorded for user query and use.

[0017] In step 1, the user first registers an account through the system and uploads the user's basic information after registration, including but not limited to the user's mobile phone number, user registration name and encrypted registration password. After the account is successfully registered, the database table is initialized.

[0018] When developing back-end code, based on the business logic that a single mobile phone number can only identify a single user, a unified database table name is used to calculate the user's unique business database table name prefix. To distinguish specific business function tables, all required database table name suffixes are obtained based on the business codes of different services corresponding to different requests.

[0019] In step 1, when developing the code on the back end, first obtain the user's unique identifier through the unique identity token used when the user logs in, query the user's mobile phone number and encrypted password information, encrypt the user's mobile phone number again with the account password encrypted by SM3, and use the encrypted hash string as the prefix of the user database table name;

[0020] Use different business codes to identify different businesses, and set the database table name suffix to underscore + business code.

[0021] In step 2, after the system completes initialization of the database tables corresponding to all different businesses of the user, the data query passes the different database table names of different businesses as parameters to the specific SQL statement through dynamic splicing to complete the query of business data.

[0022] The connection query between multiple tables is achieved by replacing different business suffixes, so that the user's specific database table name cannot be known even from the database SQL statement file, thereby achieving encryption of the user's database table.

[0023] In step 3, when the database table is horizontally split, the steps are as follows:

[0024] Step 3.1: Set a single-table threshold for the database table. Use a scheduled task to obtain the data capacity of all original single tables. When the single-table capacity reaches the preset threshold, use a SQL script to create a new shard table based on the original database table name.

[0025] Step 3.2: Create a new database table by adding a suffix number to the original database table name as the new table name, and then maintain the corresponding record in the original database table name and extension database table name mapping information table.

[0026] In step 3, when the user adds data, the mapping information table of the original database table name and the extended database table name is traversed to query the latest sub-table, and then the data is added in the new sub-table;

[0027] When the user modifies data, first query all tables in the original database table name and extension database table name mapping information table through the data ID to find the sub-table where the data is located, and then modify the data based on the data ID;

[0028] When a user performs a data query, if it is a single-table query: the input parameters are used as query conditions, the original database table name and the extension library table name mapping information table are traversed, the total number of data entries that meet the results are queried, and the data to be returned for each sub-table is calculated based on the paging parameters. The result set is combined and returned;

[0029] If it is a multi-table join query: first perform a join query on each shard table according to the input parameters, then count the total number of query results, and then query the data combination of each shard table according to the paging parameters and return it.

[0030] When a user forgets their password and is unable to log in, which indirectly causes the corresponding database table to be unable to match the corresponding user, the data can be retrieved through the retrieval function provided by the front-end.

[0031] In step 3, the steps for retrieving data using the retrieval function are as follows:

[0032] Step 3.1: The backend verifies the user's identity using the phone number entered by the user and the corresponding phone number verification code. After the verification is passed, the backend uses the encrypted password information corresponding to the phone number stored in the original database table to calculate the original database table name prefix and the business code of all services provided by the platform to obtain all the user's original database table name suffixes, thereby calculating all the user's original database table names;

[0033] Step 3.2: Use the new password entered by the user as a parameter to calculate the unified new database table name prefix, and the business code to generate all new business database table names;

[0034] Step 3.3: Use distributed locks to restrict all business operations of the current user, and use SQL statements to traverse all original database table names to modify the table names;

[0035] Step 3.4: After the table name modification operation is completed, the distributed lock is released and the current user's add, modify, and query operations are resumed.

[0036] The system of the present invention is based on the database table-level data isolation method of SM3 national secret, which is characterized by comprising a user registration module, an initialization module, a data query module, a horizontal splitting module and a user operation module;

[0037] The user registration module is responsible for registering user accounts and uploading basic user information after registration, including but not limited to user mobile phone number, user registration name and encrypted registration password;

[0038] The initialization module is responsible for calculating the user's unique business database table name prefix based on the business logic that a single mobile phone number can only identify a single user, combined with SM3 encryption based on the unified database table name, and obtaining all required database table name suffixes based on the business codes of different services corresponding to different requests;

[0039] Generate the prefix and suffix of the user database table name;

[0040] The data query module is responsible for passing different database table names of different businesses as parameters to the SQL statement through dynamic splicing to complete the query of business data;

[0041] The horizontal splitting module is responsible for obtaining the data capacity of all original single tables through a scheduled task. When the single table capacity reaches a preset threshold, a new sub-table is created based on the original database table name through a SQL script. The new database table is created by adding a suffix number "_001"..."_00n" to the original database table name as the new table name, and the mapping relationship between the original database table name and the extension library table name is recorded.

[0042] The user operation module is responsible for implementing user data addition, data modification, data query and user data retrieval operations.

[0043] The beneficial effects of the present invention are: the database table-level data isolation method and system based on SM3 national encryption utilizes the SM3 national encryption algorithm to encrypt the database table name, which not only ensures data isolation between users and the privacy and confidentiality of user tables, but also does not rely on third-party middleware, thereby reducing the complexity and maintenance costs of the system and being suitable for promotion and application. BRIEF DESCRIPTION OF THE DRAWINGS

[0044] In order to more clearly illustrate the embodiments of the present invention or the technical solutions in the prior art, the following briefly introduces the drawings required for use in the embodiments or the description of the prior art. Obviously, the drawings described below are some embodiments of the present invention. For ordinary technicians in this field, other drawings can be obtained based on these drawings without paying any creative work.

[0045] Attachment Figure 1 This is a schematic diagram of the database table-level data isolation method based on SM3 national encryption. DETAILED DESCRIPTION

[0046] In order to enable those skilled in the art to better understand the technical solutions of the present invention, the technical solutions in the embodiments of the present invention will be clearly and completely described below in conjunction with the embodiments of the present invention. Obviously, the embodiments described are only some embodiments of the present invention, not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by ordinary technicians in this field without making creative work should fall within the scope of protection of the present invention.

[0047] Splitting large tables directly from the source, i.e. the database table, to reduce the size of a single table is undoubtedly a better solution. This method of splitting data tables can be used to solve performance issues in single-table applications.

[0048] The database table-level data isolation method based on SM3 national encryption includes the following steps:

[0049] Step 1: Use the user's mobile phone number and password to generate a dynamic database table name through SM3 national encryption calculation;

[0050] Each user's mobile phone number and password must be different. After using the national secret SM3 encryption algorithm, user information cannot be reversed. In the background code, it can also avoid plain text splicing when operating database table names, thereby ensuring the security of the user's database table information;

[0051] Different users have different database tables based on their user information, which enables substantial data isolation between different users. This is suitable for multi-tenant systems and facilitates the implementation of operations such as data migration.

[0052] Step 2: When querying data, dynamically calculate the table name based on user information and dynamically splice SQL statements to avoid exposing user-specific table names. This also isolates data between users, making the operation and maintenance of a single user's data quick and efficient.

[0053] Step 3: When the amount of data in the database table is too large, horizontal splitting of the database table is achieved by adding suffixes, and the mapping relationship between the original database table name and the extended database table name is recorded for user query and use.

[0054] In step 1, the user first registers an account through the system and uploads the user's basic information after registration, including but not limited to the user's mobile phone number, user registration name and encrypted registration password. After the account is successfully registered, the database table is initialized.

[0055] When developing back-end code, based on the business logic that a single mobile phone number can only identify a single user, a unified database table name is used to calculate the user's unique business database table name prefix. To distinguish specific business function tables, all required database table name suffixes are obtained based on the business codes of different services corresponding to different requests.

[0056] In step 1, when developing the code on the back end, first obtain the user's unique identifier through the unique identity token used when the user logs in, query the user's mobile phone number and encrypted password information, encrypt the user's mobile phone number again with the account password encrypted by SM3, and use the encrypted hash string as the prefix of the user database table name;

[0057] Use different business codes to identify different businesses, and set the database table name suffix to underscore + business code. The formula is as follows:

[0058] Table name = SM3 (mobile phone number + SM3 (user password)) + "_" + code

[0059] In step 2, after the system initializes the database tables corresponding to all different services for the user, the data query dynamically passes the different database table names for different services as parameters to the specific SQL statement to complete the business data query. Even multi-table connection queries can be easily implemented by replacing different business suffixes. This makes it impossible to explicitly know the user's specific database table names even from the database SQL statement file, thus achieving encryption of the corresponding user's database tables.

[0060] In step 3, as time goes by and the amount of data in the database table increases, the capacity of the single database table may become too large, which reduces the efficiency of the query or even makes it unusable. In this case, you can consider horizontally splitting the database table. The steps are as follows:

[0061] Step 3.1: Set a table threshold for the database table. For example, set a MySQL table limit of no more than 5 million rows. Use a scheduled task to obtain the data capacity of all original tables. When the table capacity reaches the preset threshold, use a SQL script to create a new table based on the original database table name.

[0062] Step 3.2: Create a new database table by adding a suffix number "_001"..."_00n" to the original database table name as the new table name, and then maintain the corresponding record in the original database table name and extension database table name mapping information table.

[0063] In step 3, when the user adds data, the mapping information table of the original database table name and the extended database table name is traversed to query the latest sub-table, and then the data is added in the new sub-table;

[0064] When the user modifies data, first query all tables in the original database table name and extension database table name mapping information table through the data ID to find the sub-table where the data is located, and then modify the data based on the data ID;

[0065] When a user performs a data query, if it is a single-table query: the input parameters are used as query conditions, the original database table name and the extension library table name mapping information table are traversed, the total number of data entries that meet the results are queried, and the data to be returned for each sub-table is calculated based on the paging parameters. The result set is combined and returned;

[0066] If it is a multi-table join query: first perform a join query on each shard table according to the input parameters, then count the total number of query results, and then query the data combination of each shard table according to the paging parameters and return it.

[0067] When a user forgets their password and is unable to log in, which indirectly causes the corresponding database table to be unable to match the corresponding user, the data can be retrieved through the retrieval function provided by the front-end.

[0068] In step 3, the steps for retrieving data using the retrieval function are as follows:

[0069] Step 3.1: The backend verifies the user's identity using the phone number entered by the user and the corresponding phone number verification code. After the verification is passed, the backend uses the encrypted password information corresponding to the phone number stored in the original database table to calculate the original database table name prefix and the business code of all services provided by the platform to obtain all the user's original database table name suffixes, thereby calculating all the user's original database table names;

[0070] Step 3.2: Use the new password entered by the user as a parameter to calculate the unified new database table name prefix, and the business code to generate all new business database table names;

[0071] Step 3.3: Use distributed locks to restrict all business operations of the current user. Use a SQL statement similar to "rename table" to traverse all original database table names and modify the table names.

[0072] Step 3.4: After the table name modification operation is completed, the distributed lock is released and the current user's add, modify, and query operations are resumed.

[0073] The system based on the SM3 national secret database table-level data isolation method includes a user registration module, an initialization module, a data query module, a horizontal split module and a user operation module;

[0074] The user registration module is responsible for registering user accounts and uploading basic user information after registration, including but not limited to user mobile phone number, user registration name and encrypted registration password;

[0075] The initialization module is responsible for calculating the user's unique business database table name prefix based on the business logic that a single mobile phone number can only identify a single user, combined with SM3 encryption based on the unified database table name, and obtaining all required database table name suffixes based on the business codes of different services corresponding to different requests;

[0076] Generate the prefix and suffix of the user database table name;

[0077] The data query module is responsible for passing different database table names of different businesses as parameters to the SQL statement through dynamic splicing to complete the query of business data;

[0078] The horizontal splitting module is responsible for obtaining the data capacity of all original single tables through a scheduled task. When the single table capacity reaches a preset threshold, a new sub-table is created based on the original database table name through a SQL script. The new database table is created by adding a suffix number "_001"..."_00n" to the original database table name as the new table name, and the mapping relationship between the original database table name and the extension library table name is recorded.

[0079] The user operation module is responsible for implementing user data addition, data modification, data query and user data retrieval operations.

[0080] Compared with the existing technology, the database table-level data isolation method and system based on SM3 national encryption has the following characteristics:

[0081] (1) The database table name is encrypted using the SM3 national encryption algorithm, so that the encryption level is extended to the database table level, and the privacy of user data is further enhanced.

[0082] (2) One user only corresponds to one table of data, which enables direct data isolation between different users and reduces the cost of subsequent maintenance.

[0083] For example, during data migration, all the user's data can be directly migrated based on the database table name, avoiding problems such as data loss for other users in the same database table.

[0084] The embodiment described above is only one specific implementation of the present invention. Common changes and substitutions made by those skilled in the art within the scope of the technical solution of the present invention should be included in the protection scope of the present invention.

Claims

1. A database table-level data isolation method based on SM3 national encryption, characterized by: The following steps are involved: Step 1: Use the user's mobile phone number and password to generate a dynamic database table name through SM3 national encryption calculation; Each user's mobile phone number and password must be different. After using the national secret SM3 encryption algorithm, user information cannot be reversed. In the background code, it can also avoid plain text splicing when operating database table names, thereby ensuring the security of the user's database table information; Different users have different database tables based on their user information, which enables substantial data isolation between different users. This is suitable for multi-tenant systems and facilitates the implementation of operation and maintenance operations. Step 2: When querying data, dynamically calculate the table name based on user information to dynamically splice SQL statements, thereby avoiding the exposure of user-specific table names and achieving data isolation between users; Step 3: When the database table data volume is too large, horizontally split the database table by adding a suffix and record the mapping relationship between the original database table name and the extended database table name for user query and use; When the user adds data, the mapping information table of the original database table name and the extension database table name is traversed to query the latest shard table, and then the data is added in the new shard table; When the user modifies data, first query all tables in the original database table name and extension database table name mapping information table through the data ID to find the sub-table where the data is located, and then modify the data based on the data ID; When a user performs a data query, if it is a single-table query: the input parameters are used as query conditions, the original database table name and the extension library table name mapping information table are traversed, the total number of data entries that meet the results are queried, and the data to be returned for each sub-table is calculated based on the paging parameters. The result set is combined and returned; If it is a multi-table join query: first perform a join query on each shard table according to the input parameters, then count the total number of query results, and then query the data combination of each shard table according to the paging parameters; When a user forgets their password and is unable to log in, which indirectly causes the corresponding database table to be unable to match the corresponding user, the data can be retrieved through the retrieval function provided by the front-end.

2. The database table-level data isolation method based on SM3 national encryption according to claim 1 is characterized in that: In step 1, the user first registers an account through the system and uploads the user's basic information after registration, including but not limited to the user's mobile phone number, user registration name and encrypted registration password. After the account is successfully registered, the database table is initialized; When developing code on the back end, based on the business logic that a single mobile phone number can only identify a single user, a unified database table name is used to calculate the user's unique business database table name prefix; in order to distinguish specific business function tables, all required database table name suffixes are obtained based on the business codes of different businesses corresponding to different requests.

3. The database table-level data isolation method based on SM3 national encryption according to claim 2 is characterized in that: In step 1, when developing the code on the back end, first obtain the user's unique identifier through the unique identity token used when the user logs in, query the user's mobile phone number and encrypted password information, encrypt the user's mobile phone number again with the account password encrypted by SM3, and use the encrypted hash string as the prefix of the user database table name; Use different business codes to identify different businesses, and set the database table name suffix to underscore + business code.

4. The database table-level data isolation method based on SM3 national encryption according to claim 3 is characterized by: In step 2, after the system completes the initialization of the database tables corresponding to all different businesses of the user, the data query passes the different database table names of different businesses as parameters to the specific SQL statement through dynamic splicing to complete the query of business data; The connection query between multiple tables is achieved by replacing different business suffixes, so that the user's specific database table name cannot be known even from the database SQL statement file, thereby achieving encryption of the user's database table.

5. The database table-level data isolation method based on SM3 national encryption according to claim 3 is characterized in that: In step 3, when the database table is horizontally split, the steps are as follows: Step 3.1: Set a single-table threshold for the database table. Use a scheduled task to obtain the data capacity of all original single tables. When the single-table capacity reaches the preset threshold, use a SQL script to create a new shard table based on the original database table name. Step 3.2: Create a new database table by adding a suffix number to the original database table name as the new table name, and then maintain the corresponding record in the original database table name and extension database table name mapping information table.

6. The database table-level data isolation method based on SM3 national encryption according to claim 1 is characterized in that: In step 3, the steps for retrieving data using the retrieval function are as follows: Step 3.1: The backend verifies the user's identity using the phone number entered by the user and the corresponding phone number verification code. After the verification is passed, the backend uses the encrypted password information corresponding to the phone number stored in the original database table to calculate the original database table name prefix and the business code of all services provided by the platform to obtain all the user's original database table name suffixes, thereby calculating all the user's original database table names; Step 3.2: Use the new password entered by the user as a parameter to calculate the unified new database table name prefix, and the business code to generate all new business database table names; Step 3.3: Use distributed locks to restrict all business operations of the current user, and use SQL statements to traverse all original database table names to modify the table names; Step 3.4: After the table name modification operation is completed, the distributed lock is released and the current user's add, modify, and query operations are resumed.

7. The system according to any one of claims 1 to 6, characterized in that: Includes user registration module, initialization module, data query module, horizontal split module and user operation module; The user registration module is responsible for registering user accounts and uploading basic user information after registration, including but not limited to user mobile phone number, user registration name and encrypted registration password; The initialization module is responsible for calculating the user's unique business database table name prefix based on the business logic that a single mobile phone number can only identify a single user, combined with SM3 encryption based on the unified database table name, and obtaining all required database table name suffixes based on the business codes of different services corresponding to different requests; Generate the prefix and suffix of the user database table name; The data query module is responsible for passing different database table names of different businesses as parameters to the SQL statement through dynamic splicing to complete the query of business data; The horizontal splitting module is responsible for obtaining the data capacity of all original single tables through a scheduled task. When the single table capacity reaches a preset threshold, a new sub-table is created based on the original database table name through a SQL script. A new database table is created by adding a suffix number to the original database table name as the new table name, and the mapping relationship between the original database table name and the extension library table name is recorded at the same time. The user operation module is responsible for implementing user data addition, data modification, data query and user data retrieval operations.

Images