Source: http://www.google.com/patents/US20050050046?dq=6,163,776
Timestamp: 2015-04-18 04:08:06
Document Index: 676213114

Matched Legal Cases: ['arts 34', 'art 40', 'art 34', 'art 40', 'art 40', 'art 36', 'art 42', 'art 40', 'art 38', 'art 44', 'arts 34', 'art 40', 'art 42', 'art 44', 'art 44', 'arts 34', 'art 40']

Patent US20050050046 - Two phase intermediate query security using access control - Google PatentsSearch Images Maps Play YouTube News Gmail Drive More »Sign inAdvanced Patent SearchPatentsA 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...http://www.google.com/patents/US20050050046?utm_source=gb-gplus-sharePatent US20050050046 - Two phase intermediate query security using access controlAdvanced Patent SearchPublication numberUS20050050046 A1Publication typeApplicationApplication numberUS 10/651,892Publication dateMar 3, 2005Filing dateAug 29, 2003Priority dateAug 29, 2003Also published asUS7171413Publication number10651892, 651892, US 2005/0050046 A1, US 2005/050046 A1, US 20050050046 A1, US 20050050046A1, US 2005050046 A1, US 2005050046A1, US-A1-20050050046, US-A1-2005050046, US2005/0050046A1, US2005/050046A1, US20050050046 A1, US20050050046A1, US2005050046 A1, US2005050046A1InventorsNicholas Puz, Randal Richardt, Rupa Bhaghavan, Vitaliy BondarOriginal AssigneeInternational Business Machines CorporationExport CitationBiBTeX, EndNote, RefManReferenced by (18), Classifications (10), Legal Events (4) External Links: USPTO, USPTO Assignment, EspacenetTwo phase intermediate query security using access control
The procedures presented herein are not inherently related to any particular computer or other apparatus. In particular, various general purpose machines may be used with programs in accordance with the teachings herein, or it may prove more convenient to construct more specialized apparatus to perform the required method steps. However, one of ordinary skill in the art will recognize that there exists a variety of platforms and languages for creating software for performing the procedures outlined herein. One of ordinary skill in the art also recognizes that the choice of the exact platform and language is often dictated by the specifics of the actual system constructed, such that what may work for one type of general purpose computer may not be efficient on another type of general purpose computer. One of ordinary skill in the art to which this invention belongs will have a solid understanding of content management systems, database management systems, and methods of securely controlling access to items managed by the content management system. It being recognized that such practitioners do not require specific details of the software, but rather find data structure descriptions and process descriptions more desirable (due to the variety of suitable hardware and software platforms), such specifics are not discussed to avoid obscuring the invention. The invention is described in terms of a content management system generating SQL queries, however, this is done only to facilitate an understanding of the invention. One of ordinary skill in the art will readily recognize that the scope of the invention includes any client-server system providing users access to database objects, entities or tables. Further, the term database as used herein is not limited in scope to any particular type of database. The database may be, for example, any form of data repository, relational database or object-relational database. FIG. 1 is a block diagram of a network-connected client-server system 10 in accordance with a preferred embodiment of the present invention. The system shown in FIG. 1 is particularly suited to delivery of content over a network or the Internet. A content management system 12, 14 includes server-side software 12 running on a server computer system 16 and client-side software 14 running on a client computer system 18. The client and server systems communicate via a network 20. Only one client system 16 is shown in the figure. It is to be appreciated, however, that there may be many client systems connected to the server system 16 by the network 20 or other networks. Each client system may further have a plurality of users although features of the present application are described with respect to a single user. It is also to be appreciated that the server system 16 may also be a distributed system or include a plurality of systems. The server computer system 16 also includes a database management system (DBMS) 22 for accessing records and tables stored on a database 24. A user at a user interface 26 connected to the client computer system 18 provides queries to the client-side CMS 14. In prior art systems, the prior-art client-side software 14 is responsible for receiving database query requests from the user and transforming those requests into relational database commands, SQL for example. The client-side software 14 would also have the responsibility for ensuring the user had proper access permission for each database object to be traversed during the query. This prior-art scheme imposes the aforementioned overhead in submitting SQL joins and access control commands for all of the database objects, including those that are only involved in a referential manner but not returned as part of the reply to the query. In embodiments of the present application, the access control overhead is divided between the client-side software 14 and the server-side software 12 in an efficient and overhead-reducing manner. FIG. 2 illustrates concepts of the invention by means of an exemplary query 30 submitted by a user, the query involving a database schema 32 referencing a Journal table and an Employee table stored on the database 24 of FIG. 1. The client-side software 14 parses the query 30, converts it to a parse tree (not shown), and generates an SQL string. The parse tree is analyzed and, for each database object involved in the query, an appropriate security marker is inserted into the SQL string. Thus, the SQL string is divided into multiple query parts 34, 36, 38, each query part including an SQL part 40, 42, 44 and a respective security marker 46, 48, 50. In the example shown in the figure, the first query part 34 includes a first SQL part 40 and a first security marker 46. The first security marker 46 contains �Employee�3� as a result of the first SQL part 40 accessing the Employee data table referenced in the schema 32. The second query part 36 includes a second SQL part 42 and a second security marker 48. The second security marker 48 contains �Journal�1� as a result of the first SQL part 40 accessing the aforementioned Journal data table. The third query part 38 includes a third SQL part 44 which, as shown, includes only parentheses, and a null (non-existent) third security marker 50 indicating that no access checks are required for the third query part. In the illustrated embodiment, the final query part does not require access security checks since the final SQL part consists only of parentheses optionally inserted by the client-side software 14. It is to be appreciated that user queries are normally much longer than the exemplary query 30 and the resulting query will include more than 3 query parts. The exemplary client-side query parts 34, 36, 38 are sent to the server 16 for processing by the server-side software 12. The server-side software 12 receives the SQL query parts from the client system 18 as a single string. The server-side software 12 then performs access control list (ACL) checks on the security markers 46 in the received string and generates a final SQL string 60 as shown in FIG. 3. The final SQL string 60 is a single string, however, it has logical or identifiable parts corresponding to the received query parts. For example, the final SQL string 60 includes the first SQL part 40 followed by a first SQL security check 62 that corresponds to the first security marker 46. The server-side software 12 has, however, transformed the first security marker 46 to a respective SQL security check 62, in place of the first security marker. In like manner, the final SQL string 60 includes the second SQL part 42 and a respective second SQL security check 64 derived from the second security marker 48. The final SQL string 60 includes the third SQL part 44. However, because there was no security marker 50 in the illustrated embodiment, there is no SQL security code generated for the third SQL part 44. It is also to be appreciated that, in some circumstances, no SQL security code will be generated for query parts other than the last, depending on the database objects accessed by the query. It is to be appreciated that, although the security markers are inserted by client-side software, the security rules pertaining to the database objects are known only on the server, the client is normally unaware of the security rules. With reference now to FIG. 4, a flowchart illustrates the preferred method 70 for two-phase intermediate query security using access control and SQL. A client system receives a database query from a user (72). A client-side CMS running on the client system parses the query and creates a parse tree (74). The client-side CMS next converts the parse tree to an SQL tree (76) and then converts the SQL tree to an SQL string (78) for further processing. At this point, the SQL string represents a valid SQL query suitable for submission to a database server system, however, the SQL does not contain any security code. Therefore, the client-side CMS analyzes the SQL string and inserts the aforementioned security markers into the SQL string at appropriate positions (80). Exemplary markers can be the names of database tables referenced by the query such as the previously described Employee and Journal database tables. The database tables do not necessarily provide query results but may be referenced in the query as a source of information to satisfy conditional relationships expressed in the query. At this point, the query parts 34, 36, 38, each query part including the respective SQL part 40, 42, 44 and any respective inserted security marker 46, 48, are sent to a server system (82) for a second phase of query processing. A server-side CMS running on the server system receives each query part and processes the respective inserted security markers, if any. For each security marker received, ACL checks are performed and each security marker is replaced with respective SQL joins and conditions to form a final SQL string for submission to a DBMS (84). The final SQL string includes the client SQL (SQL parts) and the server-generated SQL, forming a single, final SQL string. ACL checks are well known to those of normal skill in the art. The final SQL string is submitted by the server-side CMS to the DBMS for execution of the final SQL query, and the results of the query are returned by the server-side CMS to the client-side CMS (86). Details of the SQL query submission to the DBMS are not provided herein since the Structured Query Language and methods of submitting SQL queries to a variety of database management systems are well known in the art. By distributing the above-described tasks of generating the main query 74-80 and generating the access control checks 84 between the client and the server respectively, the query engine achieves a very extensible and efficient solution to a rather complicated problem. To summarize and rephrase aspects of the present application for clarity, the client sends to the server a sequence of query parts, each of which consists of an SQL portion (what the user is looking for) and a marker portion (what should be protected by access control). The server then determines the required SQL joins and conditions that should be inserted in place of the markers in the main user query in order to enforce the appropriate access control rules. Concepts of the present application facilitate independent optimizations and changes to the client-side and the server-side software. For instance, the client analyzes the parse tree and determines whether access control checks are redundant, and does not create markers to do ACL checks in such situations. This is advantageous since the server only deals with SQL fragments and markers, and the server-side code does not necessarily have to be changed to account for any changes made by the client regarding where markers are placed in the SQL string, as long as the information contained in a marker remains consistent. It is also advantageous that the server generates the SQL code necessary to perform the security checks for each marker since the security model may be changed at the server without impacting the client. If the relations that contain security information change, or the security rules themselves change, the server can change the SQL joins created to perform the access control checks, but the client does not need to be changed. The server-side software can also be changed to make the ACL joins more efficient without impacting the client. This allows the security model to be extended by the server without impacting the application program interface (API) used by the client and server software. Further, concepts of the present application shift some of the query processing load to the client system, providing needed relief to the server system. It is natural for the markers to be set by the client-side software because it has all the necessary information at its disposal during the SQL translation phase, such as, for example, any table alias used in the generated SQL, information about the underlying database table (such as whether it has certain columns necessary to do the access control checks) and knowledge of the tables being accessed, what is being projected, and the predicates involved. However, since the client-side software does not know the server's table structure or the specifics of the necessary SQL security joins and checks, the server is advantageously delegated the job of interpreting the markers and translating them into SQL. On the other hand, if the security markers were to be set by server (based on the SQL portions of the query parts sent to it by the client), the server would need to re-parse the SQL string, then rebuild a tree representing the query in order to determine where to do the access control checks. This would result in slow server performance and duplicated work on the client and server systems. Thus, it is advantageous to perform the initial query-to-SQL translation on the client system. Such division of labor is a major advantage of the preferred embodiment. Still further, concepts of the present application provide a one-pass query to the referenced database. After the client-side software generates an SQL statement for the user query, the server-side software adds all of the relevant security checks. The resulting SQL statement is then executed against the database. By putting both the user constraints and the security access control checks in the same query, it is ensured that only a single SQL query is executed regardless of the complexity of the user query, the security checks, and the number of items that are intermediates in the query. The resultant reduction in overhead is proportional to the number of different database objects traversed in the query. For example, A=>B=>C only requires one check, rather than three, to see if user has permission to access items A, B and C. This also advantageously allows the DBMS to apply optimization to the SQL statement that contains both the main query and the ACL checks. The invention has been described with reference to the preferred embodiments. Modifications and alterations will occur to others upon a reading and understanding of the specification. It is our intention to include all such modifications and alterations insofar as they come within the scope of the appended claims, or the equivalents thereof. Referenced byCiting PatentFiling datePublication dateApplicantTitleUS7281003Sep 15, 2004Oct 9, 2007Oracle International CorporationDatabase fine-grained access controlUS7310647Jan 23, 2004Dec 18, 2007Oracle International CorporationColumn masking of tablesUS7457832 *Aug 31, 2004Nov 25, 2008Microsoft CorporationVerifying dynamically generated operations on a data storeUS7702642 *Dec 7, 2005Apr 20, 2010Precise Software Solutions, Inc.Method, system and computer-readable code for instrumenting code for logging database-related stringsUS7831621Sep 27, 2007Nov 9, 2010Crossroads Systems, Inc.System and method for summarizing and reporting impact of database statementsUS7873660Feb 27, 2003Jan 18, 2011Oracle International CorporationEnforcing data privacy aggregationsUS7962513Oct 30, 2006Jun 14, 2011Crossroads Systems, Inc.System and method for defining and implementing policies in a database systemUS8069153 *Oct 23, 2006Nov 29, 2011Salesforce.Com, Inc.Systems and methods for securing customer data in a multi-tenant environmentUS8078595Oct 9, 2007Dec 13, 2011Oracle International CorporationSecure normal formsUS8095531Oct 2, 2007Jan 10, 2012Salesforce.Com, Inc.Methods and systems for controlling access to custom objects in a databaseUS8239396Mar 20, 2009Aug 7, 2012Oracle International CorporationView mechanism for data security, privacy and utilizationUS8438276Dec 2, 2010May 7, 2013Precise Software Solutions, Inc.Method of monitoring network and application performance by analyzing web clients and web serversUS8543566 *Nov 10, 2006Sep 24, 2013Salesforce.Com, Inc.System and methods of improving a multi-tenant database query using contextual knowledge about non-homogeneously distributed tenant dataUS8560568 *Jul 2, 2012Oct 15, 2013Zeewise, Inc.Remote data collection systems and methods using read only data extraction and dynamic data handlingUS8788527Aug 15, 2011Jul 22, 2014Precise Software Solutions, Inc.Object-level database performance managementUS8825702 *Feb 24, 2004Sep 2, 2014Oracle International CorporationSending control information with database statementUS20130232468 *Mar 27, 2013Sep 5, 2013Apple Inc.Systems, methods, and computer-readable media for fertilizing machine-executable codeWO2009051488A1 *Oct 8, 2008Apr 23, 2009Fast Search & Transfer AsA method for restricting access to search results and a search engine supporting the method* Cited by examinerClassifications U.S. Classification1/1, 707/999.009International ClassificationG06F21/00, G06F7/00, G06F17/30Cooperative ClassificationY10S707/99939, G06F17/30427, G06F21/6227European ClassificationG06F21/62B1, G06F17/30S4P2Legal EventsDateCodeEventDescriptionMar 22, 2011FPExpired due to failure to pay maintenance feeEffective date: 20110130Jan 30, 2011LAPSLapse for failure to pay maintenance feesSep 6, 2010REMIMaintenance fee reminder mailedAug 29, 2003ASAssignmentOwner name: INTERNATIONAL BUSINESS MACHINES CORPORATION, NEW YFree format text: ASSIGNMENT OF ASSIGNORS INTEREST;ASSIGNORS:PUZ, NICHOLAS K.;RICHARDT, RANDAL J.;BHAGHAVAN, RUPA;ANDOTHERS;REEL/FRAME:014471/0189Effective date: 20030829RotateOriginal ImageGoogle Home - Sitemap - USPTO Bulk Downloads - Privacy Policy - Terms of Service - About Google Patents - Send FeedbackData provided by IFI CLAIMS Patent Services