Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Working Your SOX Off Expand / Collapse
Author
Message
Posted Thursday, August 9, 2007 12:10 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/btarvin/3178.asp

Brandie Tarvin, MCITP Database Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #389413
Posted Sunday, September 30, 2007 11:53 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 11, 2009 7:15 AM
Points: 129, 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
Post #404849
Posted Monday, October 1, 2007 1:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 7, 2008 11:26 PM
Points: 8, 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 :)

Andy
Post #404870
Posted Monday, October 1, 2007 3:00 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 18, 2008 6:52 AM
Points: 3, 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
Post #404903
Posted Wednesday, October 3, 2007 4:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #406092
Posted Wednesday, October 3, 2007 7:23 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 14, 2009 5:17 PM
Points: 85, 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.)
Post #406190
Posted Wednesday, October 3, 2007 7:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #406204
Posted Wednesday, October 3, 2007 11:20 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 14, 2009 5:17 PM
Points: 85, 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 :). interpretation, interpretation, interpretation :)
Post #406334
Posted Thursday, October 4, 2007 5:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:38 AM
Points: 7,211, Visits: 6,353
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 Administrator

Webpage: http://www.BrandieTarvin.net
LiveJournal Blog: http://brandietarvin.livejournal.com/
On LinkedIn!, Google+, and Twitter.

Freelance Writer: Shadowrun
Latchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.
Post #406684
Posted Thursday, October 4, 2007 8:27 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, March 14, 2009 5:17 PM
Points: 85, 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 :). 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


Post #406818
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse