We have internal audit team, which works with E&Y and SOX on Monthly, Quarterly and Yearly basis. They would come up with different kind of questions each time when its time for monthly/quarterly/yearly audit needs to be done. This would involve my DBA's to spend almost a day and half (depending upon requirements) to provide them the information (i.e. Screenshot, Query, Result-set) they are looking for.
Here are few examples of what they ask to see:
- Who has DBO/SA access?
- Who can modify particular object?
- Who got access to modify MonthEnd process since last time we did security check?
We have asked our internal audit group to provide us list of things (i.e. most commonly asked question from E&Y and SOX), so this way, we can build them a Security Dashboard, which internal audit group can utilize to see, who has access to what and so on without requiring DBA help. But, each time we asked for requirements from them, we get little run around or just won't respond with requirements. I know there are 3rd Party solution out there like Idera, Apexsql…but we don't have luxury to spend money on 3rd party tools at the moment.
My questions are:
-
Does anybody have build their Own Solution to provide Security information to internal audit via Dashboard/Report/Some Automated ways so they can export out result-set or take a screenshot from the dashboard or something like that?
-
Is there a way to get requirements which are approved by E&Y and SOX compliance for SQL database?
We are all Microsoft shop and have various SQL Server i.e. 2000 (please don't ask why still SQL 2000) to 2016.
**Note: If you think this is not the correct place to post this question or is missing anything or wanted me to correct anything, please let me know.
Thank you,
HP
Best Answer
In my experience with audits, some questions repeat every year, and some are tweaked repeatedly, either as the company moves from being less compliant to more compliant (requiring more detail), or as the rules change over time.
Also, some requests require human verification (for example, if you're asked to note all accounts with elevated privileges, and why those accounts have those privileges, then simply check for anything new added or old removed, and then handing them the same thing you gave last year (modulo those changes), doesn't really do the trick - someone actually needs to look at the reasons given, and be sure they still apply. Because, if next year someone responds "Gee, that shouldn't be on there still, we stopped using that software 5 years ago," lots of people will not be happy.
Finally, some things (like screen shots showing the "sa" login is disabled, if it is) aren't easily automated (as far as I know), or require enough effort to automate that it would take years for it to balance out.
For many of the rest, the auditing team wouldn't necessarily want there to be an automated where miscellaneous people can run (for instance) a query to see what logins have sysadmin access. Among other things, if such a tool is available, it's exploitable. They'd probably rather have a sysadmin user run these things as needed, for that reason.
However, that doesn't mean the sysadmin user can't save the script they ran last year, and use it again next year. Upgrades and such can make this less useful (if a command you were using was removed, or if new options make your script obsolete), but a starting point is always good.
When I was responsible for this sort of thing, I had a few scripts saved off that I'd run each year to provide as much of what was wanted as I could. In fact, some of the data was part of jobs that triggered things (like reviewing privileged logins/users at least 4 times a year, or whatever). Early on I would tweak them regularly to make sure I was getting everything needed, but after two or three iterations they generally stayed the same.
Unfortunately, as such code belongs to my former employer, I can't really pass any of it on to anyone else. However, once you have what you need for this year, make sure it's saved, and appropriately documented (as in, what was used to fulfill what requests), and known to all DBAs in the department; put it in your knowledgebase, or wherever your people look to find how to do something they haven't done before. Also keep the requests and responses, so you can refer back to them (as the auditors change, sometimes the terminology they use when making a request also changes - being able to say, "Here's what I think we gave the auditors last year in response to a similar request - is that what you're looking for?" is very helpful.
If your audit team is good about maintaining an email-distribution list, then automated jobs are much more useful; if not, then your job output is likely to be sent to one guy who's not doing audits any more, and 5 addresses that no longer exist.
The best requirements from your auditors (internal or external) tends to be "whatever worked last year". In fact, if you can say "here's what we gave them last year", and it's not good enough this year, it tends to put the onus on the auditors to give you an updated script that covers what else they need. After all, who's to say you know how to get it, or you'll get it right (sometimes, the "DBA" is the one guy who could actually write a
SELECTquery if he had to, maybe even without a query building tool).If you're too swamped to do something when they ask, don't be afraid to tell them. Maybe they don't have to have it for a month, and are fine with you not running it for three weeks. They are being paid by your company, and generally don't want to interfere more than they have to.
That said, I always try to be as helpful as possible with the auditors. Yes, they're interfering with your normal work, but they're just doing their job, and it's necessary for your company to continue. And, if you're always honest and up-front with them, they may be far more understanding if they do see something amiss than if you've seemed adversarial in general.