Method of and system for searching unstructured data stored in a database

• US 7,650,512 B2
• Filed: 12/08/2003
• Issued: 01/19/2010
• Est. Priority Date: 11/18/2003
• Status: Active Grant

• Current Assignee: Oracle International Corporation (Oracle Corporation)
• Original Assignee: Oracle International Corporation (Oracle Corporation)
• Inventors: Karimisetty, Srikanth, Singh, Charanjeet, Puri, Srinivasulu, Durgada, Savita, Akella, Ravindra
• Primary Examiner(s): Vu; Kimyen
• Assistant Examiner(s): PATEL, NIRAV B
• Application Number:
  US10/731,673
• Publication Number:
  US 20050108212A1
• Time in Patent Office: 2,234 Days
• Field of Search: 713176-180, 713/156, 713/193, 713/189, 726/1, 726/2, 726 26- 30, 707 1- 10, 707100-102, 707/200, 709/201, 709/229, 709/246, 705/50, 705/1
• US Class Current: 713/193
• CPC Class Codes: G06F 16/24573 using data annotations, e.g. user-defined metadata
• CPC Class Codes: G06F 21/6227 where protection concerns the structure of data, e.g. records, types, queries
• CPC Class Codes: G06F 2221/2101 Auditing as a secondary aspect

Abstract

A method of and system for searching unstructured data stored in a database. In one embodiment the method comprises storing a plurality of electronic records in a common repository of electronic records in the database that provides an audit trail that cannot be altered or disabled by users of the system where each electronic record comprises unstructured data stored in a character large-object (CLOB) format in a column of a table of the database; creating a security protocol that protects the electronic records against unauthorized access; and creating a query designed to identify electronic records in the database that meet criteria designated in the query. The method further comprises modifying the query in accordance with the security protocol to create a modified query prior to executing the query and running the modified query against the unstructured data. In one particular implementation, the unstructured data comprises a well-formed XML document stored within a column of a table stored in the database.

Claims

1. A computer-implemented method of searching unstructured data stored in a database, the method comprising:

receiving, at a first set of one or more computer systems, information indicative of a set of one or more application events that, upon occurrence, cause the first set of one or more computer systems to intercept database transactions instantiated by database applications with a database management system of the database and to generate electronic records from data in underlying database tables associated with execution of operations by the database applications identified in the database transactions;

storing, using the first set of one or more computer systems, a plurality of electronic records created based on mappings between underlying database tables associated with execution of operations by the database applications and the plurality of electronic records in response to occurrence of the set of one or more application events in a common repository of electronic records in the database that provides an audit trail that cannot be altered or disabled by users associated with the database, wherein each electronic record comprises unstructured data stored in a character large-object (CLOB) format in a column of a table of the database;

forwarding, to a client computer, information configured for generating a first graphical user interface, the first graphical user interface configured to enable users of the first graphical user interface to identify one or more references to sections of unstructured data within the plurality of electronic records stored in the database as elements of security rules;

receiving, at a second set of one or more computer systems, information from a user via the first graphical user interface identifying a reference to a section of unstructured data within an electronic record stored in the database as an element of one or more security rules;

generating, using the second set of one or more computer systems, the one or more security rules in response to the information from the user identifying the reference to a section of unstructured data within the electronic record as an element of the one or more security rules;

creating, using the second set of one or more computer systems, a security protocol that protects the plurality of electronic records stored in the database against unauthorized access based on the one or more security rules;

receiving, at a third set of one or more computer systems, a query designed to identify a set of electronic records stored in the database that meet criteria designated in the query;

prior to executing the query at the database management system, modifying the query using the third set of one or more computer systems in accordance with the security protocol to create a modified query that includes the reference to a section of unstructured data within the electronic document identified by the user as an element of the one or more security rules; and

running, using the database management system, the modified query against the unstructured data of the plurality of electronic records stored in the database.

2. The method of claim 1 further comprising:

forwarding, to the client computer, information configured for displaying a second graphical user interface configured to enable users of the second graphical user interface to identify one or more references to sections of unstructured data within the plurality of electronic records stored in the database as elements of an intermediate index that indirectly indexes into one or more of the sections of unstructured data within the plurality of electronic records using a plurality of database tables;

receiving, at the second set of one or more computer systems, information from a user via the second graphical user interface identifying one or more references to sections of unstructured data of the electronic record stored in the database as indexed elements of the intermediate index; and

generating, using the second set of one or more computer systems, the one or more security rules based on the indexed elements of the intermediate index.

3. The method of claim 1 wherein access to electronic records in the common repository is automatically granted unless the security protocol restricts such access;

and

wherein the security protocol comprises a plurality of security rules that restrict access to the electronic records within the database based on content of one or more sections of unstructured data within the electronic records whose corresponding references are identified as elements of the plurality of security rules.

4. The method of claim 1 wherein access to electronic records in the common repository is automatically denied unless the security protocol grants such access;

and

wherein the security protocol comprises a plurality of security rules that grant access to the electronic record within the database based on content of one or more sections of unstructured data within the electronic records whose corresponding references are identified as elements of the plurality of security rules.

5. The method of claim 1 wherein the plurality of electronic records are generated from multiple data sources prior to committing a database transaction to the database.

6. The method of claim 5 wherein one or more fields of an electronic record in the plurality of electronic records are filled with XML data based on the mappings including a predefined mapping of the fields to the multiple data sources.

7. The method of claim 1 wherein the unstructured data comprises well-formed XML documents stored within the column of the table stored in the database.

8. The method of claim 1 further comprising allowing a user to enable and disable the security protocol.

9. A computer system for searching unstructured data stored in a database, the computer system comprising:

a processor;

a database; and

a computer-readable memory coupled to the processor, the computer-readable memory configured to store a computer program;

wherein the processor is operative with the computer program to;

receive information indicative of a set of one or more application events that, upon occurrence, cause the processor to intercept database transactions instantiated by database applications with a database management system of the database and to generate electronic records from data in underlying database tables associated with execution of operations by the database applications identified in the database transactions;

store a plurality of electronic records created by the processor based on mappings between underlying database tables associated with execution of operations by the database applications and the plurality of electronic records in response to occurrence of the set of one or more application events in a common repository of electronic records in the database that provides an audit trail that cannot be altered or disabled by users associated with the database, wherein each electronic record comprises unstructured data stored in a character large-object (CLOB) format in a column of a table of the database;

forward, to a client computer, information configured to generate a first graphical user interface configured to enable a user if the first graphical user interface to identify one or more reference to sections of unstructured data within the plurality of electronic records stored in the database as elements of security rules;

receive information from a user via the first graphical user interface identifying a reference to a section of unstructured data within an electronic record as an element of one or more security rules;

generate one or more security rules in response to the information from the user identifying the reference to a section of unstructured data within the electronic record as an element of the one or more security rules;

create a security protocol that protects the plurality of electronic records stored in the database against unauthorized access to the unstructured data within each electronic record based on the one or more security rules;

receive a query designed to identify a set of electronic records stored in the database that meet criteria designated in the query;

modify the query in accordance with the security protocol to create a modified query prior to the database management system executing the query that includes the reference to a section of unstructured data within the electronic document identified by the user as an element of the one or more security rules; and

cause the database management system to run the modified query against the unstructured data of the plurality of electronic records stored in the database.

10. The computer system of claim 9 wherein the processor is further operative with the computer program to:

forward, to the client computer, information configured for displaying a second graphical user interface configured to enable a user of the second graphical user interface to identify one or more references to sections of unstructured data within the plurality of electronic records stored in the database as elements of an intermediate index that indirectly indexes into one or more of the sections of unstructured data within the plurality of electronic records;

receive information from a user via the second graphical user interface identifying one or more references to sections of unstructured data of the electronic record stored in the database as indexed elements of the intermediate index; and

generate the one or more security rules based on the indexed elements of the intermediate index.

11. The computer system of claim 9 wherein the processor is further operative with the computer program to:

automatically grant access to electronic records in the database unless the security protocol restricts such access; and

wherein the security protocol comprises a plurality of security rules that restrict access to the electronic records within the database based on content of one or more sections of unstructured data within the electronic records whose corresponding references are identified as elements of the plurality of security rules.

12. The computer system of claim 9 wherein the processor is further operative with the computer program to:

automatically deny access to electronic records in the database unless the security protocol grants such access; and

wherein the security protocol comprises a plurality of security rules that grant access to the electronic records within the database based on content of one or more sections of unstructured data within the electronic records whose corresponding references are identified as elements of the plurality of security rules.

13. The computer system of claim 9 wherein the plurality of electronic records are generated from multiple data sources prior to committing a database transaction to the database.

14. The computer system of claim 13 wherein one or more fields of an electronic record in the plurality of electronic records are filled with XML data based on the mappings including a predefined mapping of the fields to the multiple data sources.

15. The computer system of claim 9 wherein the unstructured data comprises well-formed XML documents stored within the column of the table stored in the database.

16. A computer program product having a computer-readable memory medium storing a set of code modules which when executed by a processor of a computer system cause the processor to search unstructured data stored in a database, the computer program product comprising:

code for receiving information indicative of a set of one or more application events that, upon occurrence, causes database transactions instantiated by database applications with a database management system of the database to be intercepted and electronic records to be generated from data in underlying database tables associated with execution of operations by the database applications identified in the database transactions;

code for storing a plurality of electronic records created based on mappings between underlying database tables associated with execution of operations by the database applications and the plurality of electronic records in response to occurrence of the set of one or more application events in a common repository of electronic records in the database that provides an audit trail that cannot be altered or disabled by users associated with the database, wherein each electronic record comprises unstructured data stored in a character large-object (GLOB) format in a column of a table of the database;

code for generating a first graphical user interface and displaying the first graphical user interface on a display device, the first graphical user interface configured to enable a user to identify one or more reference to sections of unstructured data within the plurality of electronic records stored in the database as elements of security rules;

code for receiving information from a user via the first graphical user interface identifying a reference to a section of unstructured data within an electronic record as an element of one or more security rules;

code for generating the one or more security rules in response to the information from the user identifying the reference to a section of unstructured data within the electronic record as an element of the one or more security rules;

code for creating a security protocol that protects the plurality of electronic records stored in the database against unauthorized access based on the one or more security rules;

code for creating a query designed to identify a set of electronic records stored in the database that meet criteria designated in the query;

code for modifying the query in accordance with the security protocol to create a modified query prior to executing the query, the modified query including the section of unstructured data within the electronic document identified by the user as an element of the one or more security rules; and

code for running the modified query against the unstructured data of the plurality of electronic records stored in the database.

17. The computer program product of claim 16 further comprising:

code for generating a second graphical user interface and displaying the second graphical user interface on the display device, the second graphical user interface configured to enable a user to identify one or more references to sections of unstructured data within the plurality of electronic records stored in the database as elements of an intermediate index that indirectly indexes into one or more of the sections of unstructured data within the plurality of electronic records;

code for receiving information from a user via the second graphical user interface identifying one or more references to sections of unstructured data of the electronic record as indexed elements of the intermediate index; and

code for generating the one or more security rules based on the indexed elements of the intermediate index.

18. The computer program product of claim 16 further comprising:

code for automatically granting access to electronic records in the database unless the security protocol restricts such access;

wherein the security protocol comprises a plurality of security rules that restrict access to the electronic records within the database based on content of one or more sections of unstructured data within the electronic records whose corresponding references are identified as elements of the plurality of security rules.

19. The computer program product of claim 16 further comprising:

code for automatically denying access to electronic records in the database unless the security protocol grants such access;

wherein the security protocol comprises a plurality of security rules that grant access to the electronic records within the database based on content of one or more sections of unstructured data within the electronic records whose corresponding references are identified as elements of the plurality of security rules.

20. The computer program product of claim 16 wherein the plurality of electronic records are generated from multiple data sources prior to committing a database transaction to the database.

21. The computer program product of claim 16 wherein one or more fields of an electronic record in the plurality of electronic records are filled with XML data based on the mappings including a predefined mapping of the fields to multiple data sources.

22. The computer program product of claim 16 wherein the unstructured data comprises well-formed XML documents stored within the column of the table stored in the database.