Audit Trail Anti-Patterns
source link: https://completedeveloperpodcast.com/audit-trail-anti-patterns/?utm_campaign=audit-trail-anti-patterns
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
Audit Trail Anti-Patterns
Podcast: Play in new window | Download (49.3MB) | Embed
Subscribe: Apple Podcasts | Spotify | Email | RSS | More
While the typical user of your application probably won’t be interested in your audit trails, that doesn’t mean that you can get by without them. Whether it is due to regulatory compliance issues, security policies, or simply because you need to troubleshoot something in production, you’ll have to deal with setting up and managing application audit trails at some point. Audit trails suffer from many of the same problems that logging does; while a simple implementation can be set up easily by a junior developer, you’ll often find that these implementations do not work well over time, at scale, or for making quick diagnostic decisions under pressure in production.
When you start talking to people about audit trails, you’ll also find that there are some persistent misunderstandings that are common. When we discuss audit trails, we really need to spend some time at the beginning breaking down what we are auditing, how we are auditing it, and what we are looking for (and how we expect to find it). Most of the time, when a business leader starts talking about audit trails, they have no idea about what they really want. “Audit trails” have been a corporate buzzword for years.
“An audit trail (also called audit log) is a security-relevant chronological record, set of records, and/or destination and source of records that provide documentary evidence of the sequence of activities that have affected at any time a specific operation, procedure, event, or device. Audit records typically result from activities such as financial transactions, scientific research and health care data transactions, or communications by individual people, systems, accounts, or other entities.” ~ Wikipedia
Basically, the idea here is to have a way of listing out the sequence of events (and other relevant information, such as who caused the event) that resulted in a system state. This information needs to be comprehensive, comprehensable, and secured in a way that prevents tampering. It’s essentially evidence and is often a critical feature of applications that touch healthcare information, online transactions, personal information, or financial data. Audit trails are also part of an organization’s security and compliance requirements. They are often required both by governmental regulation (such as HIPAA) and by industry standards (such as PCI). There are usually data retention requirements here as well. Audit trails are also useful for catching security issues, for providing evidence in courtroom proceedings involving computer systems, and can be very helpful when debugging complex and rare system issues.
Audit trails help you track when things happen in a large application or system of applications. You’re able to follow the path that a user or tester took when they found the issue. The concept is not only useful for computing but can also be applied in your own life. Tracking is often the first step in making changes, for example if you want to lose weight you could start by tracking the foods you eat, when you eat them, and your mood or state when eating. That will give you a baseline to start making changes. You can apply this same concept to any area of your life where you want to make a change. Need to save up for a new car or PS5? Start tracking your finances to see where and why you are spending your money. Need to stop smoking, or chewing, start tracking when you smoke, what’s going on around the event, and what your mood is before and after. The idea is that you can apply this concept to your own life to help identify ways to make positive changes.
Episode Breakdown
Anti-pattern #1: Audit trails controlled at the application level by only tracking property changes.
This is an antipattern because your data is likely in a database that is controlled from outside the application, or could be. Code in applications also tends to be a bit more volatile than the underlying database structure, at least once the app is stable, creating more opportunities for problems. It’s also more difficult when doing this to make sure that audit trail records are always written and always written in the same transaction as any changes the application creates. A DBA (or more correctly, anyone with access to the database) can circumvent audit trails that are only managed by the app, whether on purpose or on accident. You’ll further want to make sure that this data is write-once. This same concept is why accountants use adjusting entries rather than simply “adjusting” an existing entry.
Anti-pattern #2: No logging of DDL changes.
Assuming you are storing your audit trails in a database, one common pattern is to do so in the application database itself, at least in the early phases. You might then use a trigger to record auditing changes (there are multiple problems with this.) If you aren’t logging changes to table structure (Data definition language), then someone can remove the trigger, make some changes, and then put the trigger back in a way that is hard to detect. The people with the greatest motivation to tamper with audit trails are people in the company. Automated database migrations might also occur. In some cases, this could mean that an unscrupulous (or sloppy) developer managed to get a script through your PR procedures (you have those, right?) and get deployed. You’ll want to be able to track that down, if so. Don’t store your DDL logs somewhere that typical users can reach. Remember that these can be tampered with as well.
Anti-pattern #3: No user identity in the audit trail.
You need to know WHO did something. This is the simple part, but can often be overlooked when something like a web application is making changes to data on behalf of a user. If the user information isn’t passed through, you might have more difficulty telling the difference between a user’s own actions and security violation that allowed one person to touch another person’s data. Identity is also complicated. There is the user account that is used to log into the database, and there will be a user identity within the app. However, you might have other features (such as support being able to impersonate users) that further complicate things. This, by the way, is one of the many reasons that audit trails that are simply a copy of all the fields in a particular table are often not sufficient. This also becomes entertaining when deferred actions are undertaken by the app on behalf of a user, or when there is some multi-user action undertaken.
Anti-pattern #4: Difficult to search / noisy audit trails.
A lot of audit trails are bolted onto applications after they applications are built. This often means that more data is stored in the audit trail than is required, and it can also mean that the structure of the data makes actual use of the audit trail more difficult. Adding to the fun, the entries into the audit trail should be taking place at the same time that other data is being changed, or you have a risk that an entry will be skipped. This means that you can’t overload your audit trail table with indexes or you’ll kill write performance and slow your app down. Audit trails that are improperly indexed based upon how they are expected to be searched, will be slow and use a lot of system resources. If the reason for an audit is particularly severe, you might have to run it in business hours on production. Noisy audit trails that have too much data can also be a problem in terms of storage. It takes a lot of time and disk space to back them up (or restore them) and there is a maintenance overhead. You haven’t lived until you’ve taken down a production database by adding an index to an audit table with 100s of millions of rows.
Anti-pattern #5: Timing and Date issues.
Your audit trails have to also indicate WHEN something happened, as that is used for determining the sequence of events that occurred. While this doesn’t sound like something you’d screw up, dates and times can be supremely annoying. While a timestamp can help you a lot, be aware that if the time is coming from some other system, that it may be using a different timezone (or even calendar). Whatever you do, don’t store the date and time as a string. Keep the time is also tricky when a long-running or deferrable task occurs. Do you record the time of the change as when it happened, or when it started? What about failures and retries. This can be a real issue if something happens hours after someone BELIEVES they did it, as this can quickly grow into a false positive for a security issue.
Anti-pattern #6: Log flooding / storage problems
You’ll also want to make sure that your audit trails can’t be flooded with junk. Many changes are superfluous and you may not want to log every change to every object in your system (it’s not worth doing). Depending on how you create your audit trail entities, you could end up creating a bunch of them when you don’t need them. For instance, if you have an object with properties that logs its changes every time a property setter is invoked (even if there was not an actual change), a poorly written function in your application can flood the audit trails with records that show no change. Also consider what happens if your audit trail data is on different storage than your main application. This could mean that a failure in audit trail storage breaks your application. The alternative is worse.
Anti-pattern #7: Degraded performance during ops.
This brings up a problem mentioned earlier. As your application ages, your audit trails can dwarf your main application tables in size. This means things like indexing, backups, etc. are slower. While our previous discussion touched on insert performance and indexing, realize that your audit trail itself will get slower to work with over time if not properly managed, and will degrade at a rate that exceeds the rate of your application (unless all the relevant parts are write once…). This also makes backups and restores slower, which can mean more downtime, even if your database is in the cloud. Users will not be able to use your application while the database is being restored, unless your database engine supports this. This can also mean that things like continuous deployments are harder. For instance, if you are migrating your schema while deploying, a change that requires a lot of work on the audit trail table could cause the migration to time out. If nothing else, the wait time will make your devops process slower and lower team throughput.
Antipattern #8: Not accessible to non-tech-savvy people.
While your audit trail should be accessible to tech savvy people, especially if you are using it for diagnostic purposes, a lot of people building up audit trails don’t spend enough time thinking about how non-technical people might need to use it. For instance, if a support person (or their manager) needs to verify what happened on an account, you don’t want them asking development every time they need information. This will slow development down and create a point of friction between support and development. Further, if some degree of forensic examination is required, either involving the police, or senior level members of the company, then you as a developer really want to make sure they can get that stuff without your involvement. Remember that these are situations where people tend to be upset anyway. It’s better to avoid inserting yourself into those kinds of situations. Bear in mind also that if you don’t make this accessible to non-tech people, that you could more easily be the one in court explaining all the intricacies of your audit trail to a bunch of lawyers.
Tricks of the Trade
Audit trails help you track when things happen in a large application or system of applications. You’re able to follow the path that a user or tester took when they found the issue. The concept is not only useful for computing but can also be applied in your own life. Tracking is often the first step in making changes, for example if you want to lose weight you could start by tracking the foods you eat, when you eat them, and your mood or state when eating. That will give you a baseline to start making changes. You can apply this same concept to any area of your life where you want to make a change. Need to save up for a new car or PS5? Start tracking your finances to see where and why you are spending your money. Need to stop smoking, or chewing, start tracking when you smoke, what’s going on around the event, and what your mood is before and after. The idea is that you can apply this concept to your own life to help identify ways to make positive changes.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK