Secure data access using SQL query rewrites

A computer program product for optimizing a query requiring a join operation in a secure data access environment 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 receive a query statement requiring a join operation of the first table and the second table, the query statement including authentication credentials;
• in response to determining that the authentication credentials allow only a masked access security view to the sensitive attribute:
• 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;
• program instructions to generate an optimized query statement by rewriting the query statement using an inverse function of the reversible function to simplify the query statement;
• and
• program instructions to perform the join operation between the first view and the second view by executing the optimized query statement.

A computer system for optimizing a query requiring a join operation in a secure data access environment 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 receive a query statement requiring a join operation of the first table and the second table, the query statement including authentication credentials;
• in response to determining that the authentication credentials allow only a masked access security view to the sensitive attribute:
• 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;
• program instructions to generate an optimized query statement by rewriting the query statement using an inverse function of the reversible function to simplify the query statement;
• and
• program instructions to perform the join operation between the first view and the second view by executing the optimized query statement.