A user authentication method for an Excel data acquisition function from outside

Abstract

The present application relates to the technical field of database security, and in particular to a user identity authentication method for an Excel function of externally obtaining data, which utilizes the feature of a database that only authorizes a specified stored procedure to run for a database user without authorizing the access permission of a related table object, hides the real database table object, and enables a user to obtain data only after running the stored procedure after identity verification by a short message random code. The method avoids the security risks brought by directly reading table object data from a relational database. The database user cannot directly read the data of the table object, so even if the user directly logs in to the database through a database client, the user cannot obtain unauthorized data without random number authentication, greatly reducing the risk of data leakage and misuse.

Description

Technical Field

[0001] This invention relates to the field of database security technology, specifically to a user authentication method for Excel's function of retrieving data from external sources. Background Technology

[0002] Excel documents can easily and quickly retrieve large amounts of data from relational databases (including but not limited to MSSQL Server databases) using the "Get Data from External Sources" function (called "Import Data" in WPS Spreadsheets), making it convenient for users to further analyze and statistically process the data.

[0003] This function presents data security issues. For example, in most cases, the database password is stored in the document and does not require user input, meaning that data can be accessed simply by obtaining the Excel file. Even when a database account password is required, it is easily disseminated through interpersonal communication in a real-world environment, making the password public. This necessitates frequent password changes by the database administrator and notification of new passwords to authorized users, leading to complex password management. Furthermore, document users' IP addresses may not be static, making it impossible to simply restrict client IP access to control the scope of Excel document usage. Users can even view the Excel database connection string and directly log in to the database through the database client to obtain all data, including data associated with other Excel documents. These situations result in data being disseminated beyond authorized users, significantly impacting data security. Therefore, the user identity SMS authentication method of this invention can effectively solve these data security problems. Summary of the Invention

[0004] The purpose of this invention is to provide a user authentication method for Excel's function of retrieving data from external sources, solving a series of security problems arising from Excel documents directly accessing data from relational databases. This invention is specifically designed for relational databases (where database users can be added and their access to database objects can be restricted).

[0005] To achieve the above-mentioned technical objectives and effects, the present invention is implemented through the following technical solution:

[0006] User authentication methods for Excel's function to retrieve data from external sources.

[0007] First, the authorization requirements for database objects are: database users cannot directly read data from table objects; they can only return data through stored procedures.

[0008] Database users cannot directly read the table records that store the random code and the sending time; they can only authenticate through authentication functions.

[0009] Data access specifically includes the following steps:

[0010] S1: The Excel document requests a random code from the server using the document ID and mobile phone number;

[0011] S2: The server sends a random code to the authorized mobile phone number;

[0012] S3: The server records the random code and the sending time, and writes it to the log;

[0013] S4: The server returns an authorization ID. If the ID > 0, it means the random sending was successful and the next step can be continued; otherwise, the operation ends. If the authorization ID is 0, it means the verification failed. If the authorization ID is -1, it means this report has been deactivated. If the authorization ID is -2, it means the mobile number has expired. If the authorization ID is -3, it means the mobile number has not been registered.

[0014] S5: After receiving the random code, enter the random code into the Excel document within the specified time and click the button to request data;

[0015] S6: The Excel document requests data from the server and uploads the authorization ID and random code at the same time;

[0016] S7: The stored procedure calls the authentication function, passing the document ID, authorization ID, and random code as parameters to obtain the permission identifier. The stored procedure code writer interprets the permission identifier and executes SQL to obtain data from the table object.

[0017] S8: SQL returns data;

[0018] S9: The stored procedure returns data to an Excel document, which then displays the data.

[0019] Furthermore, the present invention also includes a database table object:

[0020] Report type: Unique identification of Excel documents: Document ID

[0021] Report Account: This refers to the Excel document number and its corresponding user account. One Excel document number authorizes one account, corresponding to one record. The unique ID in this table is the "Authorization ID".

[0022] Report Log: Used to record historical data such as account, Excel document number, and random code sending time.

[0023] Furthermore, the logical steps of the database stored procedure for "Get Random Code" are as follows:

[0024] Step 1: Locate the authorization record using the document ID and phone number. Ensure the document is not deactivated and the phone number is valid. Do not send a random code to the same authorized phone number again within 10 seconds using the same document ID. Proceed to the next step if successful; otherwise, proceed to Step 4.

[0025] Step 2: If an authorization record is found, generate a random code and send it to the mobile phone number. Save the random code and the current time.

[0026] Step 3: Write log records to the report log table.

[0027] Step 4: If the random code is sent successfully, the authorization ID (>0) will be returned; if the document is deactivated, -1 will be returned; if the mobile number is invalid, -2 will be returned; if the mobile number is not registered, -3 will be returned; otherwise, 0 will be returned.

[0028] Furthermore, the logic steps of the function [Authentication] are as follows:

[0029] Step 1: Locate the authorization record by inputting the authorization ID, check if the document ID and random code match the authorization, whether it has expired, and whether the mobile phone number is still valid.

[0030] Step 2: If the verification passes, return the permission description string from the authorization record; otherwise, return an empty string.

[0031] Furthermore, database stored procedures (or table-valued functions) [document data retrieval]:

[0032] The SQL from the original Excel data source is encapsulated within this stored procedure, ensuring that each Excel document corresponds to at least one stored procedure for retrieving data. In addition to the original query condition parameters, the stored procedure adds two parameters: an authorization ID and a random code.

[0033] The stored procedure calls the `Authentication` function, passing in the document ID, authorization ID, and random code to obtain a permission description string. The SQL statement retrieving data then uses this permission description string to perform permission checks. If the function returns an empty string, the result set should contain 0 rows.

[0034] You can also control the column set of the returned dataset through the permission description string, that is, different permission values ​​correspond to different returned column sets.

[0035] Furthermore, the Excel document button macro code for "Get Random Code":

[0036] Step 1: Check if the authorization ID already exists: If the authorization ID > 0 and the timeout has not occurred, do not send a random code again, and notify the user that the random code has been sent. Otherwise, assign the authorization ID to 0 and proceed to the next step.

[0037] Step 2: Create a new ADODB.Command object and call the stored procedure "Get Random Code". The parameters are document ID (a constant for this document) and mobile phone number (entered by the user).

[0038] Step 3: Assign the stored procedure return value to the authorization ID. If authorization ID > 0, it indicates that the random code was sent successfully, and the user is notified that the SMS was sent successfully; the current local time is saved as the SMS sending time. If authorization ID = 0, the user is notified that authentication failed; if authorization ID = -1, the user is notified that this document has been disabled; if authorization ID = -2, the user is notified that the mobile phone number is invalid; if authorization ID = -3, the user is not registered.

[0039] Furthermore, the macro code for the "Get Data" button in the Excel document:

[0040] Step 1: Check if the locally stored authorization ID is greater than 0 and if it has timed out. If the check is successful, proceed to the next step. Otherwise, prompt the user to obtain a random code first, and end this process.

[0041] Step 2: Call the stored procedure using the Excel data source object. The parameters include the query criteria, the locally stored authorization ID, and the random code received on the user's mobile phone. If server-side verification fails, the returned result will contain 0 rows.

[0042] Step 3: Assign the authorization ID a value of 0.

[0043] Furthermore, database user authorization used in the Excel document:

[0044] Grant execute permission to the stored procedure "Get Random Code".

[0045] Grant execute permissions to the stored procedure "Document Retrieval".

[0046] You cannot grant database users access permissions to related table objects.

[0047] The beneficial effects of this invention are:

[0048] 1. Enhanced Data Security: This invention avoids the security risks associated with directly reading table object data from a relational database by restricting database users to returning data only through stored procedures. Database users cannot directly read table object data; therefore, even if a user logs into the database directly through a database client, they cannot obtain unauthorized data without a random number authentication, significantly reducing the risk of data leakage and misuse.

[0049] 2. Authentication Function: This invention adds a user authentication function to the Excel document. By sending a random code to the user's mobile phone number and requiring the user to enter the correct random code into the Excel document within a specified time, it ensures that only verified users can access the data. Users must add their mobile phone number to a whitelist beforehand and obtain authorization to receive the random code, which effectively restricts data access permissions and improves data security.

[0050] 3. Record Management and Monitoring: This invention records historical data on accounts, Excel document numbers, and random code sending times through report logs, enabling management and access control of user mobile phone numbers. Administrators can use this record to activate, disable, and deactivate accounts that have not been used for a long time, ensuring that only authorized users can access the data. Simultaneously, the log records also provide monitoring and auditing functions for data access, allowing administrators to track and monitor data usage and promptly detect abnormal operations.

[0051] 4. Flexible Access Control: By using permission description strings in the stored procedure, this invention achieves flexible control over data access. Data access can be judged and restricted based on the permission description string within the stored procedure. Different permission values ​​can correspond to different data sets and return column sets, thereby achieving precise control over data display and meeting the diverse data needs of different users.

[0052] 5. User-friendliness: This invention provides a user-friendly operation method by adding button macro code to Excel documents. Users can obtain random codes and data by clicking the buttons, making the operation simple and convenient. Users do not need complex database operation knowledge; they can complete the data acquisition simply by following the instructions, greatly improving the user experience.

[0053] In summary, this invention effectively improves the security and controllability of Excel's function of obtaining data from external sources through authentication, access control, and log monitoring, avoiding the risks of data leakage and abuse, while also improving the user experience.

[0054] Of course, any product implementing this invention does not necessarily need to achieve all of the advantages described above at the same time. Attached Figure Description

[0055] To more clearly illustrate the technical solutions of the embodiments of the present invention, the accompanying drawings used in the description of the embodiments will be briefly introduced below. Obviously, the drawings described below are only some embodiments of the present invention. For those skilled in the art, other drawings can be obtained based on these drawings without creative effort.

[0056] Figure 1 This is a schematic diagram of the data access steps described in an embodiment of the present invention. Detailed Implementation

[0057] The technical solutions of the embodiments of the present invention will be clearly and completely described below with reference to the accompanying drawings. Obviously, the described embodiments are only some embodiments of the present invention, and not all embodiments. Based on the embodiments of the present invention, all other embodiments obtained by those skilled in the art without creative effort are within the scope of protection of the present invention.

[0058] Example 1

[0059] like Figure 1 As shown

[0060] The user authentication method for Excel's external data retrieval function described in this embodiment includes a database. The database object authorization requirements include: database users cannot directly read data from table objects, but can only return data through stored procedures; database users cannot directly read table records that store random codes and sending times, but can only authenticate through authentication functions.

[0061] Specifically, the following steps are included:

[0062] S1: The Excel document requests a random code from the server using the document ID and mobile phone number;

[0063] S2: The server sends a random code to the authorized mobile phone number;

[0064] S3: The server records the random code and the sending time, and writes it to the log;

[0065] S4: The server returns an authorization ID. If the ID > 0, it means the random sending was successful and the next step can be continued; otherwise, the operation ends. If the authorization ID is 0, it means the verification failed. If the authorization ID is -1, it means this report has been deactivated. If the authorization ID is -2, it means the mobile number has expired. If the authorization ID is -3, it means the mobile number has not been registered.

[0066] S5: After receiving the random code, enter the random code into the Excel document within the specified time and click the button to request data;

[0067] S6: The Excel document requests data from the server and uploads the authorization ID and random code at the same time;

[0068] S7: The stored procedure calls the authentication function, passing the document ID, authorization ID, and random code as parameters to obtain the permission identifier. The stored procedure code writer interprets the permission identifier and executes SQL to obtain data from the table object.

[0069] S8: SQL returns data;

[0070] S9: The stored procedure returns data to an Excel document, which then displays the data.

[0071] In this embodiment, the database table object includes:

[0072] Report type: Uniquely number an Excel document: Document ID;

[0073] Report account: This refers to the Excel document number and its corresponding user account; one Excel document number authorizes one account, which corresponds to one record; the unique ID in this table is the "authorization ID";

[0074] Report Log: Used to record historical data such as account, Excel document number, and random code sending time.

[0075] In this embodiment, the stored procedure (or table-valued function) of the database includes: encapsulating the SQL of the original Excel data source into this stored procedure, with each Excel document corresponding to at least one stored procedure for retrieving data; in addition to the parameters of the original query conditions, the stored procedure adds two parameters: authorization ID and random code;

[0076] The stored procedure calls an authentication function, passing in the document ID, authorization ID, and random code to obtain a permission description string; the permission description string is then used to determine permissions in the SQL statement that retrieves the data; if the function returns an empty string, the returned result set should contain 0 records.

[0077] Example 2

[0078] The user authentication method for Excel's external data retrieval function described in this embodiment also includes:

[0079] The logical steps of the database stored procedure

Get Random Code

[0080] Step 1: Locate the authorization record using the document ID and phone number. Ensure the document is not deactivated and the phone number is valid. Do not send a random code to the same authorized phone number again within 10 seconds using the same document ID. Proceed to the next step if successful; otherwise, proceed to Step 4.

[0081] Step 2: If an authorization record is found, generate a random code and send it to the mobile phone number. Save the random code and the current time.

[0082] Step 3: Write log records to the report log table.

[0083] Step 4: If the random code is sent successfully, the authorization ID (>0) will be returned; if the document is deactivated, -1 will be returned; if the mobile number is invalid, -2 will be returned; if the mobile number is not registered, -3 will be returned; otherwise, 0 will be returned.

[0084] In this embodiment, the logic steps of the function [Authentication] are as follows:

[0085] Step 1: Locate the authorization record by inputting the authorization ID, check if the document ID and random code match the authorization, whether it has expired, and whether the mobile phone number is still valid.

[0086] Step 2: If the verification passes, return the permission description string from the authorization record; otherwise, return an empty string.

[0087] In this embodiment, the database stored procedure (or table-valued function) [document data retrieval]:

[0088] The SQL from the original Excel data source is encapsulated within this stored procedure, ensuring that each Excel document corresponds to at least one stored procedure for retrieving data. In addition to the original query condition parameters, the stored procedure adds two parameters: an authorization ID and a random code.

[0089] The stored procedure calls the `Authentication` function, passing in the document ID, authorization ID, and random code to obtain a permission description string. The SQL statement retrieving data then uses this permission description string to perform permission checks. If the function returns an empty string, the result set should contain 0 rows.

[0090] You can also control the column set of the returned dataset through the permission description string, that is, different permission values ​​correspond to different returned column sets.

[0091] In this embodiment, the macro code for the Excel document button "Get Random Code" is as follows:

[0092] Step 1: Check if the authorization ID already exists: If the authorization ID > 0 and the timeout has not occurred, do not send a random code again, and notify the user that the random code has been sent. Otherwise, assign the authorization ID to 0 and proceed to the next step.

[0093] Step 2: Create a new ADODB.Command object and call the stored procedure "Get Random Code". The parameters are document ID (a constant for this document) and mobile phone number (entered by the user).

[0094] Step 3: Assign the stored procedure return value to the authorization ID. If authorization ID > 0, it indicates that the random code was sent successfully, and the user is notified that the SMS was sent successfully; the current local time is saved as the SMS sending time. If authorization ID = 0, the user is notified that authentication failed; if authorization ID = -1, the user is notified that this document has been disabled; if authorization ID = -2, the user is notified that the mobile phone number is invalid; if authorization ID = -3, the user is not registered.

[0095] In this embodiment, the macro code for the Excel document button "Get Data" is as follows:

[0096] Step 1: Check if the locally stored authorization ID is greater than 0 and if it has timed out. If the check is successful, proceed to the next step. Otherwise, prompt the user to obtain a random code first, and end this process.

[0097] Step 2: Call the stored procedure using the Excel data source object. The parameters include the query criteria, the locally stored authorization ID, and the random code received on the user's mobile phone. If server-side verification fails, the returned result will contain 0 rows.

[0098] Step 3: Assign the authorization ID a value of 0.

[0099] In this embodiment, the database user authorization used in the Excel document is as follows:

[0100] Grant execute permission to the stored procedure "Get Random Code".

[0101] Grant execute permissions to the stored procedure "Document Retrieval".

[0102] You cannot grant database users access permissions to related table objects.

[0103] Example 3

[0104] This invention utilizes the characteristic of databases that only authorize database users to run specified stored procedures (or table-valued functions, the same below) without authorizing access permissions to related table objects, thus hiding the real database table objects. Users can only obtain data by running the stored procedure after verifying their identity with a random SMS code.

[0105] User authentication is achieved by receiving a random verification code via the user's mobile phone number, with a limited validity period for the verification code. The user's mobile phone number (or email address, hereinafter the same) must be added to a whitelist beforehand; otherwise, they cannot receive the random SMS code. Only after successful verification can the user access authorized Excel document data. Other unauthorized Excel documents will remain inaccessible. Even if a user logs into the database directly through a database client, they cannot access unauthorized data because they cannot directly access table objects and have not passed the random number authentication. Furthermore, this invention also manages and controls access permissions for user mobile phone numbers for each document, including account activation, disabling, and disabling accounts that have not been used for a long time. Access is logged, and for the same Excel document, different permissions can be differentiated for different users.

[0106] The preferred embodiments of the present invention disclosed above are merely illustrative of the invention. These preferred embodiments do not exhaustively describe all details, nor do they limit the invention to the specific implementations described. Clearly, many modifications and variations can be made based on the content of this specification. This specification selects and specifically describes these embodiments to better explain the principles and practical applications of the invention, thereby enabling those skilled in the art to better understand and utilize the invention. The invention is limited only by the claims and their full scope and equivalents.

Claims

1. A user authentication method for Excel's function of retrieving data from external sources, comprising a database, characterized in that, Database object authorization requirements include: database users cannot directly read data from table objects, but can only return data through stored procedures; database users cannot directly read table records that store random codes and sending times, but can only authenticate through authentication functions. Specifically, the following steps are included: S1: The Excel document requests a random code from the server using the document ID and mobile phone number; S2: The server sends a random code to the authorized mobile phone number; S3: The server records the random code and the sending time, and writes it to the log; S4: The server returns an authorization ID. If the ID > 0, it means the random sending was successful and the next step can be continued; otherwise, the operation ends. If the authorization ID is 0, it means the verification failed. If the authorization ID is -1, it means this report has been deactivated. If the authorization ID is -2, it means the mobile number has expired. If the authorization ID is -3, it means the mobile number has not been registered. S5: After receiving the random code, enter the random code into the Excel document within the specified time and click the button to request data; S6: The Excel document requests data from the server and uploads the authorization ID and random code at the same time; S7: The stored procedure calls the authentication function, passing the document ID, authorization ID, and random code as parameters to obtain the permission identifier. The stored procedure code writer interprets the permission identifier and executes SQL to obtain data from the table object. S8: SQL returns data; S9: The stored procedure returns data to an Excel document, which then displays the data.

2. The user authentication method for Excel's external data acquisition function as described in claim 1, characterized in that: Database table objects include: Report type: Uniquely number an Excel document: Document ID; Report account: This refers to the Excel document number and its corresponding user account; one Excel document number authorizes one account, which corresponds to one record; the unique ID in this table is the "authorization ID"; Report Log: Used to record historical data such as account, Excel document number, and random code sending time.

3. The user authentication method for Excel's external data acquisition function as described in claim 1, characterized in that: The stored procedures of the database include: encapsulating the SQL of the original Excel data source into this stored procedure, with each Excel document corresponding to at least one stored procedure for retrieving data; in addition to the original query condition parameters, the stored procedure adds two parameters: authorization ID and random code; The stored procedure calls an authentication function, passing in the document ID, authorization ID, and random code to obtain a permission description string; the permission description string is then used to determine permissions in the SQL statement that retrieves the data; if the function returns an empty string, the returned result set should contain 0 records.

Images