Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Working Your SOX Off


Working Your SOX Off

Author
Message
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12575 Visits: 8914
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/btarvin/3178.asp

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Guus Kramer
Guus Kramer
SSC-Enthusiastic
SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)SSC-Enthusiastic (153 reputation)

Group: General Forum Members
Points: 153 Visits: 12
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
Ramseier Andreas
Ramseier Andreas
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 90
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 Smile



Andy
alogin
alogin
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 7
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
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12575 Visits: 8914
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Liliya Huff
Liliya Huff
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 63
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.)
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12575 Visits: 8914
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. @=)

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Liliya Huff
Liliya Huff
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 63
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 Smile. interpretation, interpretation, interpretation Smile
Brandie Tarvin
Brandie Tarvin
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12575 Visits: 8914
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.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Liliya Huff
Liliya Huff
SSC Journeyman
SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)SSC Journeyman (97 reputation)

Group: General Forum Members
Points: 97 Visits: 63
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 Smile. 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 troubleWink

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 Smile



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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search