May 15, 2014 at 5:26 am
I apologize if this is not the correct place to post my question on SOX compliance....Did not know where else to post it...
I understand what Sox is and compliance regarding who has access to the production environment, what they can do there and auditing it.
But I would like to know, what about the QA or Dev environment. These 2 environment of ours are mostly a copy of the production environment - data and all, with the only difference the data would be out dated - usually a month or so.
Each month we will take a backup or our live DBs and restore it onto our QA and Dev environments for proper testing etc.
What does Sox say about this? We can't apply the same restrictions on QA and Dev as we do for production - for obvious reasons. Our developers require full access to create and modify their stored procs etc.
May 16, 2014 at 2:00 am
From what I remember of SOX, the answer depends partly on the type of data in your database (and mostly on who is auditing your SOX compliance). If your development team can see data such as the value of a customer's portfolio in a financial system then, however out of date that information is, you have a problem. If they can only see products a customer has ordered, there is less likely to be a problem.
If you do need to obfuscate the data, consider running a process after the restore that does something like;
Update CustomerTable
Set CustomerName = 'Name of customer ' + CustomerCode
on the important data. The auditors should be happy with that.
May 16, 2014 at 8:26 am
My job requires SOX compliancy as well. Regardless, it is always a good practice to remove all PI data. At a minimum I would clean Last Names, Addresses, SSNs, Driver info, birthdates etc. in your DEV and QA environments.
May 16, 2014 at 10:29 am
I don't know about SOX, but for PCI compliance, we also wiped sensitive data from dev/test after restoring from Prod.
July 21, 2014 at 3:14 pm
3 questions about a public university IT environment.
1) Do we 'have' to be SOX compliant? Most of what I read talks about corporations and businesses. What about a U? My guess is the answer might be 'no' but it is a good idea?
2) The topic of 'should our IT Developers' have db_owner role or even be the DBO have come up countless times, but is actually against any state or federal compliance laws to have db_owners for Production data, however non-sensitive, when they are not DBA?
3) When asked as an an available service offering to host Databases from NON-IT areas, should any of the existing DBO or db_owner roles continue as they won't be DBA either?
July 21, 2014 at 3:26 pm
ranmazure (7/21/2014)
3 questions about a public university IT environment.1) Do we 'have' to be SOX compliant? Most of what I read talks about corporations and businesses. What about a U? My guess is the answer might be 'no' but it is a good idea?
2) The topic of 'should our IT Developers' have db_owner role or even be the DBO have come up countless times, but is actually against any state or federal compliance laws to have db_owners for Production data, however non-sensitive, when they are not DBA?
3) When asked as an an available service offering to host Databases from NON-IT areas, should any of the existing DBO or db_owner roles continue as they won't be DBA either?
This thread is a bit old, but you're asking questions that are particular. What you have to understand is the general purpose of SOX.
Noone is allowed to modify data or processes in production without having it audited. That is the key purpose of SOX, they must have a paper trail for determine how, what, and why a particular piece of data gets altered. Everything else is fluffy details.
Noone on this forum can answer if YOU have to be SOX compliant. Ask a lawer. A University usually has multiples of them on staff. Pin one down at their desk. Use duct tape if necessary.
For devs in production: If they ever adjust anything outside of audit controls, and you need compliance, you're hosed. Devs and DBAs are usually not split heavily because of IT, it's usually because a business user 'just needs this one thing fixed'. It's too easy to skip your change controls in a busy environment. Forced 'pass over' of processing helps to keep this in check.
For #3, it depends on the audit controls around the process being pulled into your jurisdiction. These are not decisions IT alone should EVER MAKE. If you need SOX compliance, if you need to work with it, you need lawyers. You need buy in from C-level to enforce workflow and change controls. This is not something a small isolated group in IT should be trying to decipher without help.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 22, 2014 at 7:24 am
Thanks for the response Kraig.
I'll see what I can find out from TPTP here at our Univ regarding Sox Compliance. Additionally, are there any other compliance and controls standards out there they we may have to be aware of? I've seen things like ITIL, COBIT, COSO, PCI, etc.
You are spot on with regards to your business user needs something done asap example!
I agree on point #3 that we should not be the ones coming up with this, actually, it is just me pounding the drum. Everyone else in on the decision thinks everyone that has access to 'their' DB, should also have Write access in Prod on the Primary Node of the Cluster. If something goes 'wrong', they, the non-DBA will supposedly be responsible. Hmmm, sounds great in theory.
I'm reading SQL Server 2012 Securty Best Practices - Operational and Administrative Tasks. I"ve also found a video about Security, Compliance, and SQL Server by Andy Warren and David Maman. Do you have any other guidelines to look at and/or compliancy 'laws' that we best not cross?
Thanks again.
July 22, 2014 at 7:40 am
Just wanted to add that Andy Warren is excellent. I've listened to him speak at our SQL Saturday in Orlando. Just keep in mind he deals mostly with PCI rather than SOX.
I agree, the above posts are right on about every organization being different. Before moving into the insurance industry, I worked for a university in California for 8 years and a community college in Florida for 3. Both were State schools and had to be compliant with a different set of laws set up by each state. UCLA had to adhere to some additional Federal regulations since they accepted grants. While this is definitely a question for the lawyers, I would hope your supervisor would have some guidance for you as well.
July 22, 2014 at 7:45 am
I'd add one thing to Craig's post; SOX isn't just about data - you need the paper trail for code changes too (table/view definitions, stored procedures, functions, triggers, etc.).
Chris
July 22, 2014 at 9:58 am
Chris, that is the kind of info I'm looking for. SOX seems to be primarily centered around all things financial.
The paper trail for code changes, etc, can you point me to a specific portion of a white paper or any article that explains that need regardless if financial or not. We have a couple 100 databases, next to none are financial in nature.
Thanks for all the replies.
I've learned we are using EthicsPoint. As part of the usage and justification, we have the following within one of our websites: "The National Association of College and University Business Officers (NACUBO) provided guidance in its Advisory Report 2003-3, The Sarbanes-Oxley Act of 2002: Recommendations for Higher Education, by describing this reporting mechanism as a best practice for higher education. Publicly traded companies are required by law (Sarbanes-Oxley Act of 2002) to have an anonymous reporting system to address accounting and auditing misconduct. In addition, colleges and universities that receive federal awards may possibly be required to follow Public Company Accounting Oversight Board (PCAOB) regulations at some point in the future."
Just an overwhelming amount of items to read at this point. I need to talk to our CFO and Internal Audit Director. My supervisor is in favor of having the hosted databases include/retain db_owner rights for the group requesting the hosting. Therefore, they can Write, remove, etc. The justification is that without that ability, these satellite entities would not want to relinquish the kind of current ownership that they are accustomed to. I need to be able to say "That's fine, but if we do that, we are not compliant with ????". Right now I can't say that or point to anything that says that. And there very well may not be anything here that would make that scenario non-compliant with non-financial data. Frowned up, sure, by me only apparently. Sigh.
Again, thanks for reading and giving advice!
July 23, 2014 at 1:43 am
Sorry, I can't point you in the direction of any specific SOX documentation; the work I did was over 8 years ago at a different employer. We didn't go through the documentation ourselves, the company employed a large number of (expensive) consultants to come in and produce a set of procedures we would need to follow. As far as I'm aware, the legislation doesn't prescribe specific procedures - it just says you must have a record of who did what and under whose authority was it done? That's why I said in an earlier post that the nature of your processes will depend largely on who audits you for SOX compliance; you have to satisfy them that you are doing the job correctly.
On your point about dbo access, I would say that you will probably need to remove it. Your new process (once you get one) will almost certainly require that any change needing dbo access must be done under specific authority from one of a small number of signatories.
As others have pointed out, you need a lot of backing from your management and advice from the people in your company who should be 'expert' in this area.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply