Systems and methods for secure access of data

• US 9,002,868 B2
• Filed: 02/12/2013
• Issued: 04/07/2015
• Est. Priority Date: 07/12/2010
• Status: Active Grant

• Current Assignee: SAP SE
• Original Assignee: SAP SE
• Inventors: Zurek, Thomas
• Primary Examiner(s): Beausoliel, Robert W.
• Assistant Examiner(s): Liu, Hexing
• Application Number:
  US13/765,407
• Publication Number:
  US 20130151560A1
• Time in Patent Office: 784 Days
• Field of Search: None
• US Class Current: 707/759
• CPC Class Codes: G06F 16/2455 Query execution
• CPC Class Codes: G06F 16/2471 Distributed queries

Abstract

In one embodiment the present invention includes a computer-implemented method comprising receiving a first query in a local computer system to access data stored on a remote computer system the local computer system. Data elements of the remote data are associated with data elements of the local data. The query is transformed into multiple queries. A second query is sent to the remote computer system to retrieve remote data. A first data result is received and stored on the local computer system and incorporated into a third query. The third query is executed against the local data and the first data result to produce a result. Sensitive data may be stored locally in a dimension table of a star schema and non-sensitive data may be stored remotely in a fact table and another dimension table of the star schema, for example.

Claims

1. A computer-implemented method comprising:

receiving a query at a first computer system;

using a portion of the received query to access non-sensitive data stored at a second computer system, remotely separated from the first computer system, which relates to a response to the received query;

generating the response to the received query using the non-sensitive data accessed from the second computer system and using another portion of the received query to access sensitive data that is stored locally at the first computer system; and

sending the response as a reply to the received query,

wherein data stored in the first computer system and data stored in the second computer system collectively define a star schema, wherein one or more dimension tables of the star schema comprise data stored in the first computer system and a fact table of the star schema comprise data stored in the second computer system; and

wherein the fact table includes a first column of surrogate values, wherein the first column of the fact table is associated with a first column in a first dimension table, wherein the first column in the first dimension table includes said surrogate values, the first dimension table comprising a second column comprising actual values corresponding to the surrogate values;

wherein the received query is an SQL query specifying at least a first column in a first table stored in the first computer system and at least a second column in a second table stored on the second computer system, wherein accessing the non-sensitive data includes generating a derived SQL query specifying the one or more columns in the second table and one or more third columns in the first table.

2. The method of claim 1 wherein accessing the non-sensitive data includes generating a first derived query from the received query and executing the first derived query against database tables stored at the second computer system.

3. The method of claim 2 wherein generating the response further includes generating a second derived query from the received query and executing the second derived query against at least some of the non-sensitive data accessed from the second computer system and the sensitive data that is stored locally at the first computer system.

4. The method of claim 1 wherein the sensitive data is only stored locally at the first computer system.

5. A non-transitory computer readable storage medium having stored thereon a computer executable program, which, when executed by a processor at a first computer system, causes the processor to perform a method comprising:

receiving a query at the first computer system;

using a portion of the received query to access non-sensitive data stored at a second computer system, remotely separated from the first computer system, which relates to a response to the received query;

generating the response to the received query using the non-sensitive data accessed from the second computer system and using another portion of the received query to access sensitive data that is stored locally at the first computer system; and

sending the response as a reply to the received query,

wherein data stored in the first computer system and data stored in the second computer system collectively define a star schema, wherein one or more dimension tables of the star schema comprise data stored in the first computer system and a fact table of the star schema comprise data stored in the second computer system, and wherein the fact table includes a first column of surrogate values, wherein the first column of the fact table is associated with a first column in a first dimension table,

wherein the first column in the first dimension table includes said surrogate values, the first dimension table comprising a second column comprising actual values corresponding to the surrogate values;

wherein the received query is an SQL query specifying at least a first column in a first table stored in the first computer system and at least a second column in a second table stored on the second computer system, wherein accessing the non-sensitive data includes generating a derived SQL query specifying the one or more columns in the second table and one or more third columns in the first table.

6. The non-transitory computer readable storage medium of claim 5 wherein accessing the non-sensitive data includes generating a first derived query from the received query and executing the first derived query against database tables stored at the second computer system.

7. The non-transitory computer readable storage medium of claim 6 wherein generating the response further includes generating a second derived query from the received query and executing the second derived query against at least some of the non-sensitive data accessed from the second computer system and the sensitive data that is stored locally at the first computer system.

8. The non-transitory computer readable storage medium of claim 5 wherein the sensitive data is only stored locally at the first computer system.

9. A computer system comprising:

one or more local computers, each local computer including a processor and a memory, the one or more local computers configured to;

receive a query at the first computer system;

use a portion of the received query to access non-sensitive data stored at a second computer system, remotely separated from the first computer system, which relates to a response to the received query;

generate the response to the received query using the non-sensitive data accessed from the second computer system and using another portion of the received query to access sensitive data that is stored locally at the first computer system; and

send the response as a reply to the received query,

wherein data stored in the first computer system and data stored in the second computer system collectively define a star schema, wherein one or more dimension tables of the star schema comprise data stored in the first computer system and a fact table of the star schema comprise data stored in the second computer system, and

wherein the fact table includes a first column of surrogate values, wherein the first column of the fact table is associated with a first column in a first dimension table, wherein the first column in the first dimension table includes said surrogate values, the first dimension table comprising a second column comprising actual values corresponding to the surrogate values;

wherein the received query is an SQL query specifying at least a first column in a first table stored in the first computer system and at least a second column in a second table stored on the second computer system, wherein accessing the non-sensitive data includes generating a derived SQL query specifying the one or more columns in the second table and one or more third columns in the first table.

10. The computer system of claim 9 wherein the non-sensitive data is accessed by generating a first derived query from the received query and executing the first derived query against database tables stored at the second computer system.

11. The computer system of claim 10 wherein the response is generated by further generating a second derived query from the received query and executing the second derived query against at least some of the non-sensitive data accessed from the second computer system and the sensitive data that is stored locally at the first computer system.