Process and system for auditing database activity

• US 8,799,225 B2
• Filed: 11/05/2003
• Issued: 08/05/2014
• Est. Priority Date: 11/05/2003
• Status: Active Grant

• Current Assignee: BeyondTrust Software, Inc. (BeyondTrust Corp.)
• Original Assignee: Lumigent Technologies, Inc. (BeyondTrust Corp.)
• Inventors: Vaitzblit, Lev, Jesse, Jonathan, Orendorff, Jason, Ng, Stephen, Mazer, Murray S.
• Primary Examiner(s): Casanova, Jorge A
• Application Number:
  US10/702,127
• Publication Number:
  US 20050097149A1
• Time in Patent Office: 3,926 Days
• Field of Search: 707/102, 707/200, 707/202, 707/103.R, 707/648
• US Class Current: 707/648
• CPC Class Codes: G06F 16/2358 Change logging, detection, and notification replication G06F16/27
• 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

Described is a database audit system used to monitor, and optionally alert on database activity, providing a complete record of access to data and database structure. The data audit system may also provide an audit trail of data accesses and changes to database schema and permissions. A database audit may be performed by collecting data from database transaction logs and traces, exporting the collected data into a repository, and analyzing the data in the repository to create data audit reports and to provide data audit browsing capabilities.

Claims

1. A process of collecting database audit trail information associated with a database that maintains a database transaction log of first database activities performed on the database, said process comprising:

maintaining trace data, by a processor, regarding second database activities performed on the database, the trace data identifying session activities performed after session establishment and identifying before and after values, the second database activities resulting from commands sent to the database, the trace data identifying database activities not identifiable through the database transaction log maintained by the database;

collecting log information about the first database activities from the database transaction log to generate collected transaction log information, the collected transaction log information identifying information regarding a particular first database activity and including a first system process identifier associated with the particular first database activity;

collecting trace information about the second database activities from the trace data to generate collected trace information, the collected trace information identifying a particular session activity and including a second system process identifier associated with the particular session activity;

using the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information to generate correlated information; and

constructing the database audit trail information based on the correlated information.

2. The process of claim 1 wherein during the collecting log information, checkpoint markers are used to keep track of the information collected from the database transaction log.

3. The process of claim 1 further comprising generating an audit trail report.

4. The process of claim 1 wherein the collecting log information further comprises collecting SQL commands.

5. The process of claim 1 wherein the collecting log information further comprises collecting SELECT commands.

6. The process of claim 5 wherein the using the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information further comprises correlating System Process Identifiers (SPIDs) from SELECT commands with SPIDs gathered from other commands.

7. The process of claim 5 wherein the SELECT commands are stored in a file between collections.

8. The process of claim 5 further comprising determining data viewed by a SELECT command.

9. The process of claim 8 wherein determining data viewed by a SELECT command further comprises analyzing a WHERE clause.

10. The process of claim 8 wherein determining data viewed by a SELECT command further comprises issuing an augmented SELECT command to the database.

11. The process of claim 1 wherein the collecting log information or the collecting trace information comprises ensuring that there are no information gaps or overlaps in each log file.

12. The process of claim 11 further comprising maintaining a collection table with records about a state and status of the collected information.

13. The process of claim 12 wherein the collecting trace information comprises collecting information from the trace data on a continuous basis.

14. The process of claim 11 wherein the collecting log information or the collecting trace information is performed periodically or in response to a command.

15. The process of claim 14 wherein database checkpoint markers are used to denote boundaries during the collecting log information.

16. The process of claim 1 further comprising generating an alert in response to a command executed on the database.

17. The process of claim 16 wherein the command executed on the database is one of:

a database schema changing command and a database structure changing command.

18. The process of claim 16 wherein generating the alert further comprises generating the alert based on the collected trace information.

19. The process of claim 1 further comprising collecting audit information for a second database.

20. The process of claim 19 wherein the database and the second database are of different types.

21. The process of claim 20 further comprising capturing an audit trail of activities on both databases in a repository.

22. The process of claim 1 further comprising collecting information indicative of database structure changes.

23. The process of claim 22 further comprising collecting information indicative of system activity.

24. The process of claim 22 wherein the information indicative of database structure changes comprises data definition language commands.

25. The process of claim 1 further comprising defining logical keys for the database.

26. The process of claim 25 further comprising generating a report of data accesses based on the logical keys.

27. The process of claim 1 further comprising transferring the collected transaction log information or the collected trace information into a repository.

28. The process of claim 27 further comprising creating an audit trail of data modifications.

29. The process of claim 27 further comprising browsing a record revision history for a particular record selected by a user.

30. The process of claim 27 further comprising browsing a history of data modifications by a particular user.

31. The process of claim 27 wherein transferring the collected transaction log information or the collected trace information further comprises maintaining a collection table with records about information transfers.

32. The process of claim 27 further comprising generating a report of data accesses.

33. The process of claim 27 further comprising generating a report of data modifications.

34. The process of claim 27 further comprising archiving the collected transaction log information or the collected trace information.

35. The process of claim 27 further comprising analyzing information in the repository and reporting thereon.

36. The process of claim 35 further comprising analyzing data access patterns.

37. A system for collecting database audit trail information associated with a database that maintains a database transaction log of first database activities performed on the database, said system comprising:

a processor;

a trace collector configured to cause the processor to collect trace data regarding second database activities performed on the database, the trace data identifying session activities performed after session establishment and identifying before and after values, the second database activities resulting from commands sent to the database, the trace data identifying database activities not identifiable through the database transaction log maintained by the database;

a data collection agent configured to collect log information about the first database activities from the database transaction log to generate collected transaction log information, the collected transaction log information identifying information regarding a particular first database activity and including a first system process identifier associated with the particular first database activity, the data collection agent being further configured to collect trace information about the second database activities from the trace data to generate collected trace information, the collected trace information identifying a particular session activity and including a second system process identifier associated with the particular session activity, the data collection agent being further configured to use the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information to generate correlated information; and

a data analyzer configured to construct the database audit trail information based on the correlated information.

38. The system of claim 37 further comprising a repository agent to receive the collected log information or the collected trace information and insert it into a repository.

39. The system of claim 37 further comprising a user interface to browse a record revision history for a particular record selected by a user.

40. The system of claim 37 further comprising a user interface to browse a history of data modifications by a particular user.

41. The system of claim 37 wherein the collected trace information further comprises data about SELECT commands.

42. The system of claim 37 wherein the collected trace information further comprises information about commands relevant to session establishment.

43. A system for collecting database audit trail information associated with a database that maintains a database transaction log of first database activities performed on the database, said system comprising:

a processor;

means for maintaining trace data regarding second database activities performed on the database, the trace data identifying session activities performed after session establishment and identifying before and after values, the second database activities resulting from commands sent to the database, the trace data identifying database activities not identifiable through the database transaction log maintained by the database;

means for collecting log information about the first database activities from the database transaction log to generate collected transaction log information, the collected transaction log information identifying information regarding a particular first database activity and including a first system process identifier associated with the particular first database activity;

means for collecting trace information about the second database activities from the trace data to generate collected trace information, the collected trace information identifying a particular session activity and including a second system process identifier associated with the particular session activity;

means for using the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information to generate correlated information; and

means for constructing the database audit trail information based on the correlated information.

44. The system of claim 43 further comprising means for analyzing the collected information and reporting thereon.

45. An agent for collecting database audit trail information associated with a database that maintains a database transaction log of first database activities performed on the database, said agent comprising:

a processor;

a collector configured to cause the processor to collect log information about the first database activities from a database transaction log maintained by the database to generate collected transaction log information, the collected transaction log information identifying information regarding a particular first database activity and including a first system process identifier associated with the particular first database activity, the collector being further configured to collect trace information from trace data regarding second database activities performed on the database to generate collected trace information, the trace data identifying session activities performed after session establishment and identifying before and after values, the second database activities resulting from commands sent to the database, the trace data identifying database activities not identifiable through the database transaction log maintained by the database, the collected trace information identifying a particular session activity and including a second system process identifier associated with the particular session activity, and the collector being further configured use the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information to generate correlated information; and

a communicator configured to transfer the correlated information to a repository configured to provide the correlated information during construction of the database audit trail information.

46. The agent of claim 45, wherein the collector is further configured to send alerts in response to predetermined events.

47. The agent of claim 45, wherein the communicator is further configured to ensure reliable information transfer.

48. An agent for collecting database audit trail information associated with a database that maintains a database transaction log of first database activities performed on the database, said agent comprising:

a processor;

means for collecting log information about the first database activities from a database transaction log maintained by the database to generate collected transaction log information, the collected transaction log information identifying information regarding a particular first database activity and including a first system process identifier associated with the particular first database activity;

means for collecting trace information regarding second database activities performed on the database from trace data, the trace data identifying session activities performed after session establishment and identifying before and after values, the second database activities resulting from commands sent to the database, the trace data identifying database activities not identifiable through the database transaction log maintained by the database, the collected trace information identifying a particular session activity and including a second system process identifier associated with the particular session activity;

means for using the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information to generate correlated information; and

means for transferring the correlated information into a repository configured to provide the correlated information during construction of the database audit trail information.

49. The agent of claim 48 further comprising means for sending alerts in response to predetermined events.

50. The agent of claim 48, the means for transferring the correlated information further comprising means for ensuring a reliable transfer.

51. A method of collecting information by a data collection agent from a database that maintains a database transaction log, said method comprising:

collecting, by a processor, log information regarding first database activities performed on the database from the database transaction log maintained by the database to generate collected transaction log information, the collected transaction log information identifying information regarding a particular first database activity and including a first system process identifier associated with the particular first database activity;

collecting trace information from trace data regarding second database activities performed on the database to generate collected trace information, the trace data identifying session activities performed after session establishment and identifying before and after values, the second database activities resulting from commands sent to the database, the trace data identifying database activities not identifiable through the database transaction log maintained by the database, the collected trace information identifying a particular session activity and including a second system process identifier associated with the particular session activity;

using the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information to generate correlated information; and

transferring the correlated information into a repository.

52. The method of claim 51 further comprising sending alerts in response to predetermined events.

53. A system for auditing multiple databases that maintain respective database transaction logs, said system comprising:

a processor;

a trace collector, respective to each database, configured to cause the processor to collect trace data regarding first database activities performed on a database respective to the trace collector, the trace data identifying session activities performed after session establishment and identifying before and after values, the first database activities resulting from commands sent to the database respective to the trace collector, the trace data identifying database activities not identifiable through the database transaction log maintained by the database respective to the trace collector;

a data collection agent, respective to each database, configured to collect log information about second database activities from a database transaction log maintained by the database respective to the data collection agent to generate collected transaction log information, the collected transaction log information identifying information regarding a particular first database activity and including a first system process identifier associated with the particular first database activity, the data collection agent further being configured to collect trace information about the second database activities from the trace data to generate collected trace information, the collected trace information identifying a particular session activity and including a second system process identifier associated with the particular session activity, and the data collection agent further being configured to use the first system process identifier and the second system process identifier to correlate the collected transaction log information with the collected trace information to generate correlated information for the database respective to the data collection agent; and

a repository configured to receive the correlated information from each respective data collection agent.

54. The system of claim 53 wherein at least two databases of the multiple databases are of different types.

55. The system of claim 53 further comprising a repository agent to receive the correlated information from the data collection agents.

56. The system of claim 53 further comprising a data analyzer to create database audit trail information based on the correlated information.

57. The system of claim 56 wherein the database audit trail information for the multiple databases is correlated into a coherent data set.