Two phase intermediate query security using access control

• US 7,171,413 B2
• Filed: 08/29/2003
• Issued: 01/30/2007
• Est. Priority Date: 08/29/2003
• Status: Expired due to Fees

• Current Assignee: International Business Machines Corporation
• Original Assignee: International Business Machines Corporation
• Inventors: Bondar, Vitaliy, Bhaghavan, Rupa, Richardt, Randal J., Puz, Nicholas K.
• Primary Examiner(s): Ali; Mohammad
• Assistant Examiner(s): Ahn; Sangwoo
• Application Number:
  US10/651,892
• Publication Number:
  US 20050050046A1
• Time in Patent Office: 1,250 Days
• Field of Search: 707 1- 10, 707100-1041
• US Class Current: 1/1
• CPC Class Codes: G06F 16/2452 Query translation
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: Y10S 707/99939 Privileged access

Abstract

A method, system and article of manufacture for two phase intermediate query security using access control. A networked client-server computer system having a plurality of users of the client-server system and including software performing database queries via a DBMS for users of the system implements the method. The method includes receiving a query string from one of the users by the client system, the query string including references to database objects. The received query string is transformed by the client system to an intermediate query string, and a first phase query security is performed by the client system including identifying the referenced database objects and inserting a security marker into the intermediate query string for each respective identified database object, and sending the intermediate query string to the server system. Access control checks are performed by the server system on the inserted security markers in the intermediate query string, and the inserted security markers are replaced with corresponding DBMS code to enforce access control.

Claims

1. In a networked client-server computer system having a plurality of users of the client-server system and including software performing database queries via a database management system (DBMS) for users of the system, a method of two-phase query security, the method comprising:

receiving by the client system a query string from one of the plurality of users, the query string including references to database objects;

transforming the received query string by the client system to an intermediate query string;

performing a first phase query security by the client system including;

identifying the referenced database objects; and

inserting a security marker into the intermediate query string for each respective identified database object, thereby forming respective pairs of query parts and marker parts;

transferring the intermediate query string, including the query parts and the marker parts, to the server system;

performing access control checks in a second phase query security by the server system on the inserted security markers in the intermediate query string; and

replacing each of the inserted security markers in the second phase query security by the server system with a corresponding security check string to enforce access control.

2. The method as set forth in claim 1, wherein the transforming includes transforming the received query string by the client system to said intermediate query string as a structured query language (SQL) string.

3. The method as set forth in claim 2, wherein the transforming includes:

transforming the query string into a parse tree;

transforming the parse tree into an SQL tree; and

transforming the SQL tree into an SQL string.

4. The method as set forth in claim 3, wherein the replacing includes replacing the inserted security markers with DBMS code.

5. The method as set forth in claim 4, wherein the DBMS code includes at least one of SQL joins and SQL conditions.

6. A client-server computer system having a plurality of users of the system comprising a networked client-server computer system having a plurality of users of the network system and including software performing database queries via a database management system (DBMS) for users of the system, including:

a client system for receiving user queries;

a server system for resolving the user queries;

a communication network connecting the client system and the server system;

client-side software running on the client system, including;

means for transforming a received query into an intermediate query string;

means for identifying database objects included in the received query;

means for inserting a security marker into the intermediate query string for each identified database object, thereby forming respective pairs of query parts and marker parts;

means for sending the intermediate query string, including the query parts and the marker parts, to the server system; and

means for receiving a resolved query from the server system;

server-side software running on the server system, including;

means for performing access control checks on each of the inserted security markers in a received query string;

means for replacing each of the inserted security markers in the received intermediate query string with respective security check strings for enforcing access control;

means for submitting the received intermediate query string to a DBMS;

means for receiving a query response from the DBMS; and

means for returning the query response to the client system;

a database connected to the server system; and

a DBMS running on the server system for accessing the database and resolving received query strings and returning a resolved query response.

7. The client-server computer system as set forth in claim 6, further including:

means for transforming the received query into a parse tree;

means for transforming the parse tree into a structured query language (SQL) tree; and

means for transforming the SQL tree into an SQL string, wherein the SQL string is the intermediate query string.

8. The client-server computer system as set forth in claim 7, wherein the security check strings include DBMS code.

9. The client-server computer system as set forth in claim 8, wherein the DBMS code includes at least one of SQL joins and SQL conditions.

10. The client-server computer system as set forth in claim 9, wherein the database is one of a relational database, an object-relational database and a data repository.

11. An article of computer-readable media having contents that cause a client-server computer system having a plurality of users of the network, and including software running on the client-server computer system, performing database queries via a database management system (DBMS) for users of the system to perform the computer-implemented steps of:

receiving by the client system a query string from one of the plurality of users, the query string including references to database objects;

transforming the received query string by the client system to an intermediate query string;

performing a first phase query security by the client system including;

identifying the referenced database objects; and

inserting a security marker into the intermediate query string for each respective identified database object, thereby forming respective pairs of query parts and marker parts;

transferring the intermediate query string, including the query parts and the marker parts, to the server system;

performing access control checks in a second phase query security by the server system on the inserted security markers in the intermediate query string; and

replacing each of the inserted security markers in the second phase query security by the server system with a corresponding security check string to enforce access control.

12. The article of computer-readable media as set forth in claim 11, wherein the transforming includes transforming the received query string by the client system to said intermediate query string as a structured query language (SQL) string.

13. The article of computer-readable media as set forth in claim 12, wherein the transforming includes:

transforming the query string into a parse tree;

transforming the parse tree into an SQL tree; and

transforming the SQL tree into an SQL string.

14. The article of computer-readable media as set forth in claim 13, wherein the security check strings include DBMS code.

15. The article of computer-readable media as set forth in claim 14, wherein the DBMS code includes at least one of SQL joins and SQL conditions.