MOVED TO SQL 2016 Administration section
Kindest Regards,Just say No to Facebook!
My opinion (I work at a company that is SOX compliant), if you do your best to follow SOX compliance as best as you understand it and you pass the audits, you are fine. My advice below is based on my experience working with auditors at the database level.
With data integrity, from the DBA standpoint, you just want to make sure that none of your processes (ETL and reporting mostly) are changing data in a way that could mean it is no longer accurate. The data integrity falls on the DBA (or ETL/Reporting team) to ensure that data is valid. As you have little to no control over how the vendor software puts data in the database, you are not responsible for that data integrity.
On top of that, end users are notoriously bad for putting inaccurate data into the system. It is very easy to make a typo and your $50.00 bill becomes a $5000.00 bill because you missed hitting the . key. As a DBA, you have no way to automate or validate that the data the end users enter is accurate, but you are responsible for presenting that data back to the user. If you have database corruption (for example), it might be that the end user entered 50.00, but due to some bad settings and not running proper checks, you end up with bad data in there such as $5000 instead of $50. That would be another case where the data integrity falls on the DBA.
The data integrity applies to all software that is putting in or pulling out data. For example, if your company operates in USD but you have a report in SSRS showing it in CAD funds, the math for that would need to be proven to the auditor to be accurate. SSRS is a 3rd party tool, your financial system is very likely a 3rd party tool, so it does not apply to only custom in-house applications.
That being said, it might not even be the DBA or reporting team who is generating the financial data reports. It could be that the SSRS report is 100% accurate, but the finance team exports that to Excel and then runs some macros on it to make the data work for what they need (adjusting values to account for taxes for example) and those calculations the auditors would want to look at as well.
Basically, the rules apply to things you can control. If you cannot control it, you are not responsible for it. Permissions are something that get audited for us every year. Every year they want to look at who is in the sysadmin role, who is in the datawriter roles and who has permissions to update, insert or delete data and how those permissions are managed.
I work at a Canadian company and the rules may be different based on where you live. If you have an auditor you can reach out to, they can probably provide you the best advice.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Thanks for taking the time to reply. I tried to move the post to another location b/c I thought no one would see it here since the SOX section is under the older SQL Server 7/2000 section and b/c I did not realize until after the post that the parts about data integrity in that red-gate article are so interpretative and not literal. I was expecting those sections of the act to be more specific then they are.
In our case we have lost a lot of control over the database since we moved to our software vendors cloud based solution. The accounting software is known for data integrity issues from over use of allowed NULL values to sibling FK (foreign keys in 2 different table that are supposed to point back to the same Primary key value in a 3rd table) that don't match. We've manually fixed these in the past but I was hoping that the move to what the vendor calls SOX compliance would include changes to the DB and or the software to prevent these kinds of problems but the reply I'm getting is that SOX is just about access in their eyes. It very well could be that they are just assuming this because its the only part of SOX they have had to deal with.
As long as when the shift hits the fan I'm not in a position of being held responsible for when this happens because the vendors software program has allowed it I'm OK. I'd prefer it not happen period but we are limited in what we can do now that we no longer host the DB locally.
Kindest Regards,Just say No to Facebook!
My apologies. I started to reply yesterday and got sidetracked.
Here's my view. I'm not a lawyer, auditor, or regulatory reviewer. Your job is to be compliant with SOX auditing. From an auditor. You should work with them to explain what you do, show how you know this happens, and adjust your work based on their findings. SOX compliance (or GDPR, PCI, HIPAA, etc.) isn't about every pedantic thing a technical person can think of to change/lose/steal/etc. data. It's about adhering to the regulation in a way that the auditor agrees with. Like a CPA with taxes, they are more on the hook than you are if there is a government review.
When I went through SOX audits, they were like ISO audits. It wasn't that we had to have perfect data or security or anything. We had to know what we did, prove we knew it, and have records. This is about knowing how your systems work, how you modify/change/etc. them, and showing you have records of those efforts.
As noted above, you are responsible for showing how things work. If user A mis-enters data, you need to show that you return that data to User B correctly, as per your system. If you aggregate data, and you can't use SUM correctly, that's not your fault, but the fact that you show where the formula was written, tested, deployed, signed off on is enough (usually). What can't happen is changes going outside of your (meaning org's) prototcols that are audited and recorded.
If your vendor doesn't process data correctly, that's on them. What you are responsible for dealing with are your issues. You have security in place to know who put data in, who got it out, and what they did. If it's manipulated by IT systems, that's on you. If a user does this in Excel on their laptop, that's on them. They should have records showing which versions of the XLS with the formulas were used for reporting. Same for your systems. What was the code used to process data at that time. This usually means records of deployment, of the actual code, who made the decision.
This whole thing wasn't about reporting bad information. We know that happens. This is about people being able to willfully misreport data outside of auditor knowledge. Enron got execs thrown in jail, and auditors that conspired with them. Anderson auditing was broken up after this.
Know what you do with data, and prove it. Those are the real guidelines.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
This was removed by the editor as SPAM
Viewing 5 posts - 1 through 4 (of 4 total)