August 9, 2007 at 12:10 pm
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/btarvin/3178.asp
September 30, 2007 at 11:53 pm
Hi,
Make lists, check and double check them (weekly!!)
Create reports and document them.
Create some more reports and alter the ones you made earlier (a company is always in motion).
Set Security, document it and check it again and again and again and report the status over and over again.
I just wonder how is doing the operational DBA work?
Did you hire a secretary or another DBA to do the work for you????
I also work as a DBA (1200 DB's/52 servers) for a company who has to deal with the SOX-act but I like to keep focused on my job and not on the SOX-administration with comes with it. Creating lists, checking reports and overviews doesn't build our product.......
GKramer
The Nehterlands
October 1, 2007 at 1:04 am
Hi
Actually if you had a Administrative DB. You could fill up with information most automatically (Security, Access, Permission). and a few information manually. I would be nice to have such a DB. This DB could also have reports so if an auditor comes in you only have to push a button and all the documentation (several thousand pages) prints and the auditor will be overwhelmed with tones of pages 🙂
Andy
October 1, 2007 at 3:00 am
Hi
Thanks for the brief overview. I've also been through my first year in a SOX-IT environment, and have three observations:
1. Your A/S/P breakdown is a useful way of looking at things - along with what is the approved access-permissions, and how to make sure any changes are also approved (including monitoring and correction/prevention).
2. Don't know about documenting reports - haven't been asked to do so, though I agree it would be good practice to get reports properly documentated and automated where possible (i.e. so no one can 'fiddle' them).
3. We also work on the principle that anything that could change data should be documented and approved (which probably is a broader view to your reports documentation). So we have monitoring and change management procedures around Applications, Databases, and (to be added) 'Key Control' reports.
Thanks
Andrew
October 3, 2007 at 4:03 am
To be honest, we don't have an Administrative DBA. We just have a small team of 3 DBAs who do it all, Operational, Development and Administration. Yes, a lot of small things had to temporarily fall by the wayside, but that's what project planning is for. It helped us schedule things so we could do the important operational tasks and still get our SOX compliance done. Also, we had business analysts and a project manager to fall back on for a lot of our reports documentation.
Once you get into the grove of documenting reports, it's not as hard as you think. Adopting the attitude that documentation gets written up *before* reports can be designed does help somewhat (plus prevents some of the inevitable "scope creep").
But if you don't do it because you're too busy, you will get bitten by the SOX bug. It's not just companies that can be fined, if I understand the law. Managers can get get hit with pretty hefty fines for violating SOX, plus they can lose their jobs. If you don't have time for documenting the reports and don't have access to a BA, ask your manager to hire a temporary BA for the project. Mentioning the monetary implications of not completing SOX should help shake loose some funds for project.
October 3, 2007 at 7:23 am
actually auditors ask for the content of domain groups in sql for financial databases. keep it in mind and make yourself nice useful script that will pull groups you have in AD. fed it something like so if your domain is for example yourdc1.yourdc2.yourdc3.com
"SELECT * FROM 'LDAP://dc=yourdc1,dc=yourdc2,dc=yourdc3,dc=Com' WHERE objectCategory='group'"
"SELECT * FROM 'LDAP://dc=yourdc1,dc=yourdc2,dc=yourdc3,dc=Com' WHERE objectCategory='user'"
pulling AD from sql itself is not exactly a good business and not applicable fr all the systems. I do not like exposing sql in AD at all for security reasons.
dump the output into the format you happen to like and load into your administrative database and merge that content with the security extracts from production system catalogs (ssis/dts, c# whatever you like to use)
you may find many interesting things.
and you may also consider sql server lockdown project according to what do you find (buildin admins for example bring plenty users that you may not really like in there... etc.)
October 3, 2007 at 7:37 am
Liliya,
That actually might be one of those little things that differs from auditor to auditor.
None of the local department ITS support groups in my workplace have actually been asked for Active Directory or Windows Groups lists. Usually, the auditors go to the ITS group that manages Active Directory. I don't know if these people are a part of our Server Admin team or have their own "AD Team" collection, but in my experience, they are the ones the auditors contact when wanting a list of all the users associated with specific Windows Groups.
If the auditors ask for this information again, you might want to point out that you aren't in charge of AD and that using SQL Server to pull that information is not the best method of getting it. Telling them that you might not get all the logins for each Group by pulling it through SQL Server might help get them off your back. And this is absolute truth if the SQL Statements are written wrong. @=)
October 3, 2007 at 11:20 am
Brandie Tarvin (10/3/2007)
Liliya,That actually might be one of those little things that differs from auditor to auditor.
...
there is a legitimate request: provide all the individuals/services/applications that have access to a particular data set. that is when AD groups get unrolled. how to represent the answer to the auditors - a different story :). interpretation, interpretation, interpretation 🙂
October 4, 2007 at 5:25 am
Ah, yes, interpretation is key with SOX, especially as the law itself is so vague.
This is one of the reasons I recommend checking with other departments within your company or the auditors before you go full on into your SOX implementation. Because the things the auditors require will be different depending on who your auditor is and what they think is important.
Another key note that I probably should have mentioned in my article. If your auditor requires something for SOX and you do it, you should be fairly safe from other auditor (and federal court) opinions if you can show proof that you did as the original auditor asked.
As long, of course, as you're actually still obeying the SOX law to the best of your abilities. If you've blantantly gone off the track though, not even saying "Well the auditor said..." will save you.
October 4, 2007 at 8:27 am
Sure can do so. It all depends on what a company can get away with.
If the database is ms sql it is possible to fails on SOX as long as the auditor knows what they are asking for and what the answer supposed to be. Many of them actually do not :). Is it a good thing or a bad thing - depends on who is talking.
And anyways as long as it's shown that the progress is being made (how fast - nobody asks, can put budget depending things etc... many ways to delay the fix and put it on more comfortable schedule) etc all comes to negotiations what is called what.
Beware of a DBA as your auditor. that is a trouble;)
I mean it is very easy to make a legitimate case, ask several simple questions like so:
let's imagine there is a database 'finance'.
If I would audit I'd ask for
- list members of sysadmin, dbo, data reader, data writer, ddl admin, security admin on the server and database level to begin with.
- list all the users and non system groups in 'finance', if there are AD groups, include all the members and through which groups do they become the members of the groups in question with SID's
- list of privileges for each user and each group in finance
- list of privileges of public group
- if there are database logins and it is sql server 2000 I'd ask to provide network trace of the login operation (and see if there is an ssl certificate in place) and ask how many ws's that access 'finance' database with the accounts listed as database logins do not have the certificates. if they chhose to encrypt all traffic - their business
- for each user listed there would be a question - how often do they change passwords
- how many users know database login password for each of the logins
- if there is discovered something like buildinadmins with sa rights , then questions like how exactly system administration job requires access to 'finance' database will be asked
- can ask to show a confirmation that only modifications that are made to the finance database are coming from particular legitimate application and how exactly any other modifications from something like query analyzer are rejected/controlled etc...
- and so on we go... limit is - your imagination, no limits that is 🙂
it is not about that ms sql is bad or impossible to lock down by any means, any commercial database has problems or can be set up the way to cause problems. it is all about who finds it first and what to do next
October 4, 2007 at 8:34 am
Brandie Tarvin (10/3/2007)
Liliya,None of the local department ITS support groups in my workplace have actually been asked for Active Directory or Windows Groups lists. Usually, the auditors go to the ITS group that manages Active Directory. I don't know if these people are a part of our Server Admin team or have their own "AD Team" collection, but in my experience, they are the ones the auditors contact when wanting a list of all the users associated with specific Windows Groups.
forgotten to comment on this part.
depends how many years did your company go through and how exactly the auditors can match database logins and groups no matter when are they coming from with the job responsibilities of particular individuals. On 3-rd year and so on you may expect a bit smarter auditors. Does not mean you can not let the auditors to discover the security problems already known or new ones... (make it a little bit difficult to put all the ends together and give the info from different departments is one of the ways to do it, buys you some time because it takes time to analyze the data)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply