SECURE DATA ACCESS USING SQL QUERY REWRITES

• US 20140380051A1
• Filed: 06/21/2013
• Published: 12/25/2014
• Est. Priority Date: 06/21/2013
• Status: Active Grant

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Branish, Edward G. II, Chinnam, Veerabhadra R., Hughes, George R. Jr., Sun, James C.
• Granted Patent: US 9,069,987 B2
• Time in Patent Office: Days
• US Class Current: 713/169
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: H04L 63/0428 wherein the data content is protected, e.g. by encrypting or encapsulating the payload

Abstract

A mechanism is provided for secure data access in a data processing system. A database having two tables is provided. A subset of the tables'"'"' primary key attributes is considered sensitive. A first user is authorized to access the primary key'"'"'s sensitive attribute in an unmasked format, while a second user is authorized to access same data in a masked format. Two security views are generated granting the second user access to the primary key'"'"'s sensitive attribute values of both tables in the masked format. The masked format value is generated from an unmasked format value using a reversible function. A join operation between the two security views is performed by optimizing a query statement corresponding to the join operation.

Claims

1. A method for secure data access in a data processing system, the method comprising:

providing a relational database having a first table and a second table, wherein a sensitive attribute serves as subset of a primary key for the first and second tables;

generating a first security view, the first security view granting a user access to the sensitive attribute values of the first table in a masked format, wherein the masked format value is generated from an unmasked format value using a reversible function;

generating a second security view, the second security view granting the user access to the sensitive attribute values of the second table in a masked format, wherein the masked format value is generated from an unmasked format using a reversible function; and

performing a join operation between the first view and the second view by optimizing a first query statement corresponding to the join operation.

2. The method of claim 1, wherein optimizing the first query statement comprises rewriting the first query statement using inherent properties of the reversible function and using an inverse function of the reversible function to simplify the first query statement.

3. The method of claim 1, wherein the sensitive attribute comprises a partitioning key attribute in a parallel data processing system.

4. The method of claim 1, wherein performing the join operation comprises performing an optimized join operation.

5. The method of claim 4, wherein performing the optimized join operation comprises:

receiving the first query statement from the relational database, the first query statement comprising the join operation between the first view and the second view;

generating a second query statement by expanding the first view in accordance with a first view definition and by expanding the second view in accordance with a second view definition;

generating a third query statement, wherein the third query statement comprises an optimized version of the first query statement; and

executing the third query statement in the relational database.

6. The method of claim 1, further comprising generating a first index for accessing the sensitive attribute within the first table and generating a second index for accessing the sensitive attribute within the second table.

7. The method of claim 2, wherein the reversible function comprises an encryption transformation and wherein the inverse function comprises a decryption transformation.

8. A computer program product for secure data access in a data processing system, the computer program product comprising one or more computer-readable tangible storage devices and a plurality of program instructions stored on one or more computer-readable tangible storage devices, the plurality of program instructions comprising:

program instructions to provide a relational database having a first table and a second table, wherein a sensitive attribute serves as subset of a primary key for the first and second tables;

program instructions to generate a first security view, wherein the first security view grants a user access to the sensitive attribute values of the first table in a masked format and wherein the masked format value is generated from an unmasked format value using a reversible function;

program instructions to generate a second security view, wherein the second security view grants the user access to the sensitive attribute values of the second table in a masked format and wherein the masked format value is generated from an unmasked format using a reversible function; and

program instructions to perform a join operation between the first view and the second view by optimizing a first query statement corresponding to the join operation.

9. The computer program product of claim 8, wherein the program instructions to optimize the first query statement comprise program instructions to rewrite the first query statement using inherent properties of the reversible function and using an inverse function of the reversible function to simplify the first query statement.

10. The computer program product of claim 8, wherein the sensitive attribute comprises a partitioning key attribute in a parallel data processing system.

11. The computer program product of claim 8, wherein the program instructions to perform the join operation comprise program instructions to perform an optimized join operation.

12. The computer program product of claim 11, wherein the program instructions to perform the optimized join operation comprise:

program instructions to receive the first query statement from the relational database, the first query statement comprising the join operation between the first view and the second view;

program instructions to generate a second query statement by expanding the first view in accordance with a first view definition and by expanding the second view in accordance with a second view definition;

program instructions to generate a third query statement, wherein the third query statement comprises an optimized version of the first query statement; and

program instructions to execute the third query statement in the relational database.

13. The computer program product of claim 8, further comprising program instructions to generate a first index for accessing the sensitive attribute within the first table and to generate a second index for accessing the sensitive attribute within the second table.

14. The computer program product of claim 9, wherein the reversible function comprises an encryption transformation and wherein the inverse function comprises a decryption transformation.

15. A computer system for secure data access in a data processing system, the computer system comprising one or more processors, one or more computer-readable tangible storage devices, and a plurality of program instructions stored on one or more storage devices for execution by one or more processors, the plurality of program instructions comprising:

program instructions to provide a relational database having a first table and a second table, wherein a sensitive attribute serves as subset of a primary key for the first and second tables;

program instructions to generate a first security view, wherein the first security view grants a user access to the sensitive attribute values of the first table in a masked format and wherein the masked format value is generated from an unmasked format value using a reversible function;

program instructions to generate a second security view, wherein the second security view grants the user access to the sensitive attribute values of the second table in a masked format and wherein the masked format value is generated from an unmasked format using a reversible function; and

program instructions to perform a join operation between the first view and the second view by optimizing a query statement corresponding to the join operation.

16. The computer system of claim 15, wherein the program instructions to optimize the first query statement comprise program instructions to rewrite the first query statement using inherent properties of the reversible function and using an inverse function of the reversible function to simplify the first query statement.

17. The computer system of claim 15, wherein the sensitive attribute comprises a partitioning key attribute in a parallel data processing system.

18. The computer system of claim 15, wherein the program instructions to perform the join operation comprise program instructions to perform an optimized join operation.

19. The computer system of claim 18, wherein the program instructions to perform the optimized join operation comprise:

program instructions to receive the first query statement from the relational database, the first query statement comprising the join operation between the first view and the second view;

program instructions to generate a second query statement by expanding the first view in accordance with a first view definition and by expanding the second view in accordance with a second view definition;

program instructions to generate a third query statement, wherein the third query statement comprises an optimized version of the first query statement; and

program instructions to execute the third query statement in the relational database.

20. The computer system of claim 15, further comprising program instructions to generate a first index for accessing the sensitive attribute within the first table and to generate a second index for accessing the sensitive attribute within the second table.