Audit trails in DBMS-Mysql Tutorial-php.cn

DBMS 中的审计跟踪

Introduction

The audit trail, also known as the transaction log, is the record in a database management system (DBMS) of all changes made to the database. It is used to track and monitor database activity, identify and resolve problems, and ensure data integrity and security. In this article, we will explore the purpose and benefits of audit trails in a DBMS, as well as how they work, and provide practical and SQL code examples to illustrate their implementation and use.

What is an audit trail in a DBMS?

The audit trail is a chronological record of all database transactions, including inserts, updates, and deletes. It captures the old and new values of modified data, as well as metadata such as the user or application responsible for the change, the date and time of the change, and the type of change (for example, insert, update, delete).

Audit trails can be used to track and monitor database activity, identify and resolve problems, and ensure data integrity and security. For example, if a user accidentally deletes important data from a database, an audit trail can be used to identify the responsible user and recover the deleted data. Likewise, if data is corrupted or modified in an unauthorized manner, an audit trail can help identify the cause and take corrective action.

Types of audit trails

In a database management system (DBMS), an audit trail is a record of changes made to the database. There are several types of audit trails available for tracking changes in a DBMS. The three main types of audit trails are internal, external, and IRS (U.S. Internal Revenue Service) audit trails.

Internal Audit Trails − These audit trails are used by organizations to track changes made to their own databases. They are often used to ensure data integrity, detect and correct errors, and meet regulatory requirements.

Example − A company might use an internal audit trail to track changes made to its financial records or customer database.

External audit trails − These audit trails are used by external organizations or auditors to review the data in a database. They are often used to verify the accuracy and reliability of the data for regulatory or compliance purposes.

Example− An external auditor may use an external audit trail to review a company's financial records to ensure compliance with accounting standards.

IRS Audit Trails - These audit trails are used by the Internal Revenue Service (IRS) to track changes to tax records. They are used to ensure the accuracy and completeness of tax information and to detect and prevent tax fraud.

Example− The IRS may use IRS audits to track changes to an individual's tax records, such as changes to income or deductions.

Other important audit trail types and their examples are described below.

Log-based audit trails − These audit trails use a log file to record changes made to the database. The log file contains information about each change, such as the time the change was made , the user who made the change, and the type of change (e.g., insert, update, delete).

Example - In a financial database, log-based audit trails can be used to track changes in account balances or transactions.

Trigger-based audit trails − These audit trails use triggers, which are special types of database objects that are activated when a specific event occurs (e.g., a row is inserted or updated). Triggers can be used to record changes made to the database in an audit table.

Example - In a medical database, trigger-based audit trails can be used to track changes to patient records, such as changes to medication lists or vital signs.

Version-based audit trails − These audit trails use version control to track changes to the database. Whenever a change is made to a row in the database, a new version of the row is created with the updated data. Older versions of the row are retained, allowing you to view the history of changes made to the row.

Example - In a project management database, you can use version-based audit trails to track changes to project tasks, such as changes to deadlines or completion status.

Shadow Tables - These tables are used to store copies of rows when they are updated in the main table. A shadow table contains old and new versions of a row, allowing you to view the history of changes made to the row.

Example − In a customer relationship management (CRM) database, a shadow table might be used to track changes to customer profiles, such as changes to contact information or purchasing history.

SQL example

The following is an example of an audit trail using SQL to track changes made to a table named "employees" -

CREATE TABLE employees_audit (
   employee_id INTEGER,
   action VARCHAR(255),
   change_time TIMESTAMP,
   old_data JSON,
   new_data JSON
);

CREATE TRIGGER audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
   IF (TG_OP = 'DELETE') THEN
      INSERT INTO employees_audit (employee_id, action, change_time, old_data)
      VALUES (OLD.id, 'DELETE', NOW(), OLD.*);
   ELSEIF (TG_OP = 'UPDATE') THEN
      INSERT INTO employees_audit (employee_id, action, change_time, old_data, new_data)
      VALUES (OLD.id, 'UPDATE', NOW(), OLD.*, NEW.*);
   ELSE
      INSERT INTO employees_audit (employee_id, action, change_time, new_data)
      VALUES (NEW.id, 'INSERT', NOW(), NEW.*);
   END IF;
END;

Copy after login

This SQL code creates an audit table named "employees_audit" and a trigger named "audit_employee_changes". The trigger will be activated when a row is inserted, updated, or deleted in the "employees" table.

When the trigger is activated, it inserts a new row into the "employees_audit" table with information about the change that was made. The "action" column specifies the type of change (INSERT, UPDATE, or DELETE), the "change_time" column records the time the change was made, and the "old_data" and "new_data" columns contain the data before and after the change, respectively.

For example, if a row is updated in the "employees" table, the trigger will insert a new row into the "employees_audit" table with the action "UPDATE", the current time, the old data from the row before the update, and the new data from the row after the update. This allows you to track changes made to the "employees" table over time.

数据库管理系统中审计跟踪的好处

There are several benefits to implementing an audit trail in a DBMS −

Data integrity and security − Audit trails help to ensure the integrity and security of data by tracking and monitoring all database activity. This can help to prevent unauthorized access, modification, or deletion of data, as well as detect and correct errors or corruption.
合规性 − 许多行业和组织对数据管理有严格的法规和合规要求，例如欧盟的个人数据保护通用数据保护条例（GDPR）或美国的医疗数据保险可移植性与责任法案（HIPAA）。审计追踪可以帮助组织满足这些要求，通过提供所有数据库活动的记录，并确保数据以安全和符合规定的方式处理。
故障排除和问题解决 − 审计跟踪可以用于识别和解决数据库的问题，如错误，损坏或未经授权的访问。它们还可以通过提供导致问题的更改记录来帮助解决问题，从而可以采取纠正措施。
审计和法证分析 − 审计轨迹可用于审计和法证分析，以调查潜在的安全漏洞或欺诈活动。它们提供了所有数据库活动的详细记录，可用于识别和跟踪可疑活动。