How do you use the audit trail pattern to track changes in your database?

由人工智能和领英社区提供技术支持

If you want to keep track of who, when, and what changed in your database, you need to use the audit trail pattern. This is a database schema design pattern that records every modification to your data in a separate table or collection. In this article, you will learn how to use the audit trail pattern to enhance your database security, accountability, and analysis.

此文章中的业界达人

由社区从 9 条内容中精选。了解更多

1 Why use the audit trail pattern?

The audit trail pattern has many benefits for your database design. First, it helps you prevent and detect unauthorized or malicious changes to your data. You can monitor the actions of your users and applications, and verify their identity and permissions. Second, it helps you comply with regulatory and legal requirements, such as GDPR or HIPAA, that mandate data protection and retention. You can prove the integrity and history of your data, and handle data breaches or audits more easily. Third, it helps you analyze and improve your database performance and functionality. You can track the trends and patterns of your data changes, and optimize your queries and indexes.

添加您的观点

Greg Anderson

Leader in Data Architecture & Strategy, Analytics, Governance, and AI
举报内容
Using the audit trail pattern to track changes in your database is crucial for maintaining a historical record of data modifications, including inserts, updates, and deletions. This approach enhances data transparency and accountability by providing a clear, timestamped log of who made changes, what changes were made, and when they occurred. It's invaluable for compliance with regulatory requirements, as it supports data integrity and security audits. Additionally, audit trails facilitate error tracking and correction by allowing the rollback of changes to previous states, thereby ensuring data accuracy and reliability.

已翻译

赞

2 How to implement the audit trail pattern?

The audit trail pattern can be implemented in different ways, depending on your database type, structure, and needs. However, the general idea is to create a separate table or collection that stores the metadata of every data change, such as the timestamp, user, operation, source, and old and new values. For example, if you have a relational database, you can use triggers or stored procedures to insert a row into the audit table every time a row is inserted, updated, or deleted in another table. If you have a document database, you can use hooks or middleware to append a document to the audit collection every time a document is created, modified, or removed in another collection.

添加您的观点

Sal Hobbi

Senior Information Technology (IT) Leader/Trainer – Driving positive results through software engineering
(已编辑)
举报内容
Furthermore, the audit trail database should be a separate database. And it should not share production-related data. In other words, it is isolated so it has less of performance issue against the production database. Audit trail databases should only support write and read operations. Update and deletes are not allowed to maintain integrity.

已翻译

赞
Jason Sacks

Senior Full Stack Engineer
举报内容
Having the database design include triggers, and stored procedures is a fine solution if the organization chooses a database centric manner of approach. However there are some issue with having this functionality be part of the database. 1) Dependency on a database for tracking the changes in triggers and stored procedures or a baked in solution creates a strong coupling with that database. 2) In today's landscape there are generally multiple database of different types from different vendors. Additionally data may be saved on external services. A solution to decouple this approach and have a centralized log for changes can occur through change data capture, queue, ESB/resolver event driven design. Then all data can be tracked.

已翻译

赞
Charles Drake

Software Developer internetapplications.ca
举报内容
MS SQL Server has built in transaction logs: https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16

已翻译

赞

3 What are the challenges of the audit trail pattern?

The audit trail pattern is not without its challenges. One of the main challenges is the storage and performance overhead. Since the audit table or collection grows with every data change, it can consume a lot of disk space and memory, and slow down your database operations. To mitigate this, you can use compression, partitioning, archiving, or purging techniques to reduce the size and impact of the audit data. Another challenge is the consistency and reliability of the audit data. Since the audit data is generated by triggers, hooks, or other mechanisms, it can be affected by errors, failures, or tampering. To prevent this, you can use encryption, checksums, or digital signatures to secure and validate the audit data.

添加您的观点

Kiran V.

Azure Application Engineer
举报内容
SQL Server has built in history tables which record every transaction ever made, including minute changes. This not only helps in audit trail, but also helps in getting the state at a certain point in time.

已翻译

赞
Kiran V.

Azure Application Engineer
举报内容
If the audit trail DB is on a different server, it does help in performance of the main production database. However, the performance may not be exactly great as anticipated, due to the other factors involved, one of which is the number of audit trail transactions we are making, the size of audit database, infrastructure, etc.

已翻译

赞

4 How to query the audit trail data?

The audit trail data can be queried like any other data in your database, using SQL or other query languages. However, you need to be careful and efficient when querying the audit data, as it can be large and complex. You can use filters, joins, aggregations, or other functions to narrow down and analyze the audit data. For example, you can query the audit table or collection to find out who changed a specific record, when and how often a record was changed, what fields were changed and how, what sources triggered the changes, and so on.

添加您的观点

5 How to test and monitor the audit trail pattern?

The audit trail pattern requires regular testing and monitoring to ensure its accuracy and functionality. You can use unit tests, integration tests, or regression tests to check if the audit data is generated correctly and consistently for every data change. You can also use logging, alerting, or reporting tools to monitor the audit data for any anomalies or issues. For example, you can log the errors or failures that occur during the audit data generation, alert the administrators or users if there is a suspicious or unauthorized change, or report the statistics or trends of the audit data.

添加您的观点

6 Here’s what else to consider

This is a space to share examples, stories, or insights that don’t fit into any of the previous sections. What else would you like to add?

添加您的观点

Martin Hills

Entrepreneur in technology and software in highly regulated industries and services. ES: Emprendedor en tecnología y software en industrias y servicios altamente regulados.
举报内容
I think that using the database to perform this task has major drawbacks. What if there is a deadlock? Would it not be better to design your software to log all of this data for offline analysis usimg a tool like splunk? Databases to audit themselves is quite an old approach. Also, what if the database is offline. The only way to retrieve the audit which may then be unreliable anyway is lost.

已翻译

赞
Kiran V.

Azure Application Engineer
举报内容
Irrespective of the type of application, updating the database to latest version, using tools to make the audit trail will be better in terms of performance. The built in features would help with audit trail in most cases. If we still need an elaborate trail, I would personally think that instead of using triggers, we need to redesign. Having an api that handles audit alone, without effecting the prod database , probably using a nosql version if db, would be much better than following traditional relational model for audit.

已翻译

赞
Stojan Kne?evi?

Software architect. Software consultant. Game developer. Starcraft Terran.
举报内容
We are currently developing app and one of the features is to show client changes on records during time, i.e. if someone change some general information about employee when, who and why this changes were made. We are combining audit trail with history of changes feature. Our plan (we already implementing it his way) is to audit all changes on fly with triggers and procedures and put data in another table and probably in another database.

已翻译

赞

Database Design

+ 关注

给文章评分

我们借助人工智能创建了此文章。您认为这篇文章怎么样？

很棒不太好

举报此文章

查看全部

How do you use the audit trail pattern to track changes in your database?

1

2

3

4

5

6

1 Why use the audit trail pattern?

2 How to implement the audit trail pattern?

3 What are the challenges of the audit trail pattern?

4 How to query the audit trail data?

5 How to test and monitor the audit trail pattern?

6 Here’s what else to consider

Database Design

给文章评分

感谢您的反馈

更多Database Design相关文章

更多相关阅读内容