Determining criticality of a SQL statement

• US 9,703,854 B2
• Filed: 08/06/2014
• Issued: 07/11/2017
• Est. Priority Date: 09/26/2013
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Yang, Xin Ying, Feng, Hao H H., Li, Shuo B J., Sun, Sheng Yan
• Primary Examiner(s): HO, BINH VAN
• Application Number:
  US14/453,201
• Publication Number:
  US 20150088913A1
• Time in Patent Office: 1,070 Days
• Field of Search: 707751
• CPC Class Codes: G06F 16/284 Relational databases

Abstract

The present invention discloses a mechanism for determining criticality of a SQL statement. A plurality of elements in the SQL statement is extracted. A score of the SQL statement is calculated based on a correlation relation among respective elements in the plurality of elements and base scores of the respective elements. The criticality of the SQL statement is determined based on the score of the SQL statement. In this way, a database administrator only needs to define a small number of rules according his own needs. In the meantime, the mechanism automatically analyzes a received simple or complex SQL statement according to the small number of rules defined by the user to determine its criticality, without missing any critical SQL statement, and thereby protect security of data in the database.

Claims

1. A method, in a data processing system comprising a processor and a memory coupled to the processor, for determining criticality of a Structured Query Language (SQL) statement, comprising:

extracting, by an extracting apparatus in a criticality determining device executed by the processor, a plurality of elements in the SQL statement;

calculating, by a calculating apparatus in the criticality determining device executed by the processor, a score of the SQL statement based on a correlation relation among respective elements in the plurality of elements and base scores of the respective elements, wherein calculating the score of the SQL statement based on a correlation relation among respective elements in the plurality of elements and base scores of the respective elements includes;

determining, by the calculating apparatus in the criticality determining device executed by the processor, the score of the SQL statement as a first value in response to that the correlation relation does not exist between any two of the plurality of elements, the first value being a maximum one of the respective base scores of the plurality of elements; and

determining, by the calculating apparatus in the criticality determining device executed by the processor, the score of the SQL statement as a second value in response to that the correlation relation exists between at least two of the plurality of elements, the second value being greater than the maximum one of the respective base scores of the plurality of elements; and

determining, by a determining apparatus in the criticality determining device executed by the processor, the criticality of the SQL statement based on the score of the SQL statement.

2. The method of claim 1, wherein the plurality of elements include at least one of a table and a field in a database which the SQL statement is directed at, and an action to be executed by the SQL statement.

3. The method of claim 1, wherein the base scores of the respective elements are obtained by referencing a base score table, the base score table including base scores of respective tables stored in the database, base scores of fields of the respective tables, and base scores of actions allowed to be executed on the database.

4. The method of claim 3, wherein the base scores of the respective tables and the base scores of the fields of the respective tables are determined according to a user-specified rule and a correlation relation among the respective tables in the database, the correlation relation referring to a correlation relation formed by correlating two tables via a foreign key, or a correlation relation formed by two tables which appear in a same historical SQL statement executed on the database and are correlated via at least one field.

5. The method of claim 4, wherein a table or a field included in the user-specified rule, and a table in the database, which has the correlation relation with the table, and a field thereof have a first base score, and a remaining table in the database and a field thereof have a second base score which is smaller than the first base score.

6. The method of claim 3, wherein the base score table further includes a weighting factor applied to a base score of a field based on a position of the field in the SQL statement.

7. The method of claim 6, wherein the weighting factor applied to the base score of the field is increased in response to the field being located in a conditional region of the SQL statement.

8. The method according to claim 1, wherein determining the criticality of the SQL statement based on the score of the SQL statement includes:

comparing, by the determining apparatus in the criticality determining device executed by the processor, the score of the SQL statement with a threshold value; and

determining, by the determining apparatus in the criticality determining device executed by the processor, the SQL statement to be critical in response to the score of the SQL statement being greater than the threshold value.

9. A device for determining criticality of a Structured Query Language (SQL) statement, comprising:

a processor; and

a memory coupled to the processor, wherein the memory comprises instructions which, when executed by the processor, cause the processor to;

extract a plurality of elements in the SQL statement;

calculate a score of the SQL statement based on a correlation relation among respective elements in the plurality of elements and base scores of the respective elements, wherein the instructions to calculate the score of the SQL statement based on a correlation relation among respective elements in the plurality of elements and base scores of the respective elements further causes the processor to;

determine the score of the SQL statement as a first value in response to that the correlation relation does not exist between any two of the plurality of elements, the first value being a maximum one of the respective base scores of the plurality of elements; and

determine the score of the SQL statement as a second value in response to that the correlation relation exists between at least two of the plurality of elements, the second value being greater than the maximum one of the respective base scores of the plurality of elements; and

determine the criticality of the SQL statement based on the score of the SQL statement.

10. The device of claim 9, wherein the plurality of elements include at least one of a table and a field in a database which the SQL statement is directed at, and an action to be executed by the SQL statement.

11. The device of claim 9, wherein the calculating apparatus obtains the base scores of the respective elements by referencing a base score table, the base score table including base scores of respective tables stored in the database, base scores of fields of the respective tables, and base scores of actions allowed to be executed on the database.

12. The device of claim 11, wherein the base scores of the respective tables and the base scores of the fields of the respective tables are determined according to a user-specified rule and a correlation relation among the respective tables in the database, the correlation relation referring to a correlation relation formed by correlating two tables via a foreign key, or a correlation relation formed by two tables which appear in a same historical SQL statement executed on the database and are correlated via at least one field.

13. The device of claim 12, wherein a table or a field included in the user-specified rule, and a table in the database, which has the correlation relation with the table, and a field thereof have a first base score, and a remaining table in the database and a field thereof have a second base score which is smaller than the first base score.

14. The device of claim 11, wherein the base score table further includes a weighting factor applied to a base score of a field based on a position of the field in the SQL statement.

15. The device of claim 14, wherein the weighting factor applied to the base score of the field is increased in response to the field being located in a conditional region of the SQL statement.

16. The device according to claim 9, wherein the instructions to determine the criticality of the SQL statement based on the score of the SQL statement further causes the processor to:

compare the score of the SQL statement with a threshold value; and

determine the SQL statement to be critical in response to the score of the SQL statement being greater than the threshold value.

17. A computer program product comprising a non-transitory computer readable storage medium having a computer readable program stored therein, wherein the computer readable program, when executed on a computing device, causes the computing device to:

extract a plurality of elements in a Structured Query Language (SQL) statement;

calculate a score of the SQL statement based on a correlation relation among respective elements in the plurality of elements and base scores of the respective elements, wherein the computer readable program to calculate the score of the SQL statement based on a correlation relation among respective elements in the plurality of elements and base scores of the respective elements further causes the computing device to;

determine the score of the SQL statement as a first value in response to that the correlation relation does not exist between any two of the plurality of elements, the first value being a maximum one of the respective base scores of the plurality of elements; and

determine the score of the SQL statement as a second value in response to that the correlation relation exists between at least two of the plurality of elements, the second value being greater than the maximum one of the respective base scores of the plurality of elements; and

determine the criticality of the SQL statement based on the score of the SQL statement.

18. The computer program product of claim 17, wherein the plurality of elements include at least one of a table and a field in a database which the SQL statement is directed at, and an action to be executed by the SQL statement.