Systems and methods for generating and implementing database audit functionality across multiple platforms

• US 10,360,203 B2
• Filed: 03/31/2014
• Issued: 07/23/2019
• Est. Priority Date: 03/31/2014
• Status: Active Grant

• Current Assignee: Mckesson Specialty Care Distribution Corporation
• Original Assignee: Mckesson Specialty Care Distribution Corporation
• Inventors: Bhatnagar, Ajay, Jiwani, Asif, Stickler, Alan
• Primary Examiner(s): Saeed, Usmaan
• Assistant Examiner(s): Weinrich, Brian E.
• Application Number:
  US14/230,233
• Publication Number:
  US 20150278320A1
• Time in Patent Office: 1,940 Days
• Field of Search: 707610
• CPC Class Codes: G06F 16/21 Design, administration or maintenance of databases
• CPC Class Codes: G06F 16/2228 Indexing structures
• CPC Class Codes: G06F 16/23 Updating
• CPC Class Codes: G06F 16/27 Replication, distribution or synchronisation of data between databases or within a distributed database system; Distributed database system architectures therefor

Abstract

Systems and methods are provided for generating and implementing data auditing functionality supporting multiple database platforms. A database management computer system can engage a shadow audit module to automate and optimize the implementation of data auditing functionality for multiple database platforms that may be utilized by an enterprise application to record data interactions (e.g., insertions, updates, deletions, etc). The shadow audit module may run generated audit SQL code to create an audit trigger in the selected main data tables. Each audit trigger may be a procedure and/or a flag that is stored in the main data table to run or transmit a signal indicating a change may have occurred on the record indicating the required replication of the modification in the shadow audit data table.

Claims

1. A computer-implemented method to automate and optimize implementation of data auditing functionality for multiple database platforms, comprising:

accessing, by one or more computers comprising one or more processors, a shadow audit configuration file comprising a list of one or more first data tables in a first database to be audited and a list of one or more second data tables in a second database to be audited, wherein the first database is a first database platform and the second database is a second database platform;

generating, by the one or more computers comprising one or more processors, a first audit file comprising first audit code;

running, by the one or more computers, the generated first audit code against a first data table in the one or more first data tables in the first database to create a first shadow audit data table;

running, by the one or more computers, the generated first audit code to create one or more first audit triggers;

inserting, by the one or more computers, the one or more first audit triggers in the first data table, wherein at least one of the one or more first audit triggers is configured to run when a data modification is made to the first data table;

replicating, by the one or more computers, the data modification in the first shadow audit data table;

generating, by the one or more computers, a second audit file comprising second audit code;

running, by the one or more computers, the generated second audit code against a second data table in the one or more second data tables in the second database to create a second shadow audit data table;

running, by the one or more computers, the generated second audit code to create one or more second audit triggers that are inserted in the second data table and wherein at least one of the one or more audit triggers is configured to run when a data modification is made to the second data table; and

replicating, by the one or more computers, the data modification in the second shadow audit data table when the data modification is made to the second data table and an audit trigger of the one or more second audit triggers has run.

2. The computer-implemented method of claim 1 further comprising:

reporting, by the one or more computers, one or more data modifications that were made to the first data table.

3. The computer-implemented method of claim 1, wherein the first shadow audit data table comprises at least data of the first data table and metadata corresponding to the data modification to the first data table.

4. The computer-implemented method of claim 3, wherein the data modification to the first data table comprises at least one of a deletion, an insertion, or an update, and wherein the metadata further comprises information for the user making the data modification to the first data table, or a date or time the data modification to the first data table was made.

5. The computer-implemented method of claim 1, wherein generating the first audit file is engaged by an authenticated user.

6. The computer-implemented method of claim 1, wherein the first data table contains a latest version of a data record.

7. The computer-implemented method of claim 1, wherein the first shadow audit data table includes at least a history of all data modifications that have been made to the first data table.

8. The method of claim 1, wherein the first audit code comprises structured query language (SQL) code.

9. The method of claim 1, wherein the one or more first audit triggers comprise one or more flags.

10. The method of claim 1, wherein the one or more first audit triggers comprise one or more procedures.

11. A system to automate and optimize implementation of data auditing functionality for multiple database platforms comprising:

at least one memory storing computer-executable instructions; and

at least one processor configured to access the at least one memory and execute the computer-executable instructions to;

access a shadow audit configuration file comprising a list of one or more first data tables in a first database to be audited and a list of one or more second data tables in a second database to be audited, wherein the first database is a first database platform and the second database is a second database platform;

run a shadow audit module against the first database corresponding to an application to determine a first data table in the one or more first data tables in the first database that needs to be audited;

generate first audit code;

run the generated first audit code against the first data table in the first database to create a first shadow audit data table;

run the generated first audit code to create one or more first audit triggers;

insert the one or more first audit triggers in the first data table, wherein at least one of the one or more first audit triggers is configured to run when a data modification is made to the first data table;

replicate the data modification in the first shadow audit data table,

generate second audit code;

run the generated second audit code against a second data table in the one or more second data tables in the second database to create a second shadow audit data table;

run the generated second audit code to create one or more second audit triggers;

insert the one or more second audit triggers in the second data table;

wherein at least one of the one or more audit triggers is configured to run when a data modification is made to the second data table; and

replicate the data modification in the second shadow audit data table when the data modification is made to the second data table and an audit trigger of the one or more second audit triggers has run.

12. The system of claim 11 wherein the at least one or more processors configured are further configured to execute the computer-executable instructions to:

report one or more data modifications that were made to the first data table.

13. The system of claim 11, wherein the first shadow audit data table comprises at least data of the first data table and metadata corresponding to the data modification to the first data table.

14. The system of claim 13, wherein the data modification to the first data table comprises at least one of a deletion, an insertion, or an update, and wherein the metadata further comprises information for the user making the data modification to the first data table, or a date or time the data modification to the first data table was made.

15. The system of claim 11, wherein the shadow audit module is engaged by an authenticated user.

16. The system of claim 11, wherein the first data table contains a latest version of a data record.

17. The system of claim 11, wherein the first shadow audit data table includes at least a history of all data modifications that have been made to the first data table.

18. The system of claim 11, wherein the first audit code comprises structured query language (SQL) code.

19. The system of claim 11, wherein the one or more first audit triggers comprise one or more flags.

20. The system of claim 11, wherein the one or more first audit triggers comprise one or more procedures.