Protect and Monitor

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQLServerCentral.com » Editorials » Protect and Monitor

Protect and Monitor

Rate Topic

Display Mode

Topic Options

Author

Message

Steve Jones - SSC Editor

Posted Wednesday, May 06, 2009 9:00 PM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

Comments posted to this topic are about the item Protect and Monitor

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #711735

James Stover

Posted Thursday, May 07, 2009 12:14 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 10:37 AM
Points: 263, Visits: 859

Hmmm, I'm thinking more Service Broker. Or maybe even a streaming database like Streambase. But I suppose you could get SSIS to do it. Maybe an SSIS package called via service broker. Any thoughts there?

You can store all events for later analysis but for a real-time application you want solid rules-based alerting. "Let me know when something isn't right. Otherwise, don't bother me."

Technicalities aside, the age-old question comes up: Who audits the auditor? At some point you just have to trust that your DBA isn't out to screw you over.

James Stover, McDBA

Post #711782

SuperDBA-207096

Posted Thursday, May 07, 2009 4:38 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443, Visits: 711

interesting article.

I worked on a banking app (25-30 concurrent users) that audited all application accesses via application code. they needed an audit trail but in the 5 years I worked w/ the app, they never looked at the audit data.

As far as DBA access goes, none of that was audited.

Many financial and pharma apps have similar requirements - need to be able to prove who saw what if anyone ever asks.

Post #711873

bob.willsie

Posted Thursday, May 07, 2009 6:52 AM

Valued Member

Group: General Forum Members
Last Login: Friday, October 02, 2009 6:43 AM
Points: 57, Visits: 151

I think there has to be some consideration of the value of what is being audited. For instance, they have set our ERP system up to log audit data for all columns in our po-lines table whenever any column is changed.

That means 87 audit records anytime any column relating to a purchase order line is changed. In reality, we only need to audit about 8-12 of the columns.

So, about 90% of our audit data on these transactions is non-value logging.

I have also seen requests for extraneous logging on data that was pretty much self logging. For instance, one manager wanted an audit log entry that indicated what user created a record, and the date and time the record was created, even though that data was stored in columns in the original data records.

Post #711978

dma-669038

Posted Thursday, May 07, 2009 6:56 AM

Old Hand

Group: General Forum Members
Last Login: Monday, November 12, 2012 6:46 AM
Points: 312, Visits: 1,024

Where I work we use Guardiam on SOX audited applications. It does a pretty neat job although it is an expensive tool. I wonder how many people realized the value of the default trace on SQL 2005 and the report that shows recent DDL changes? As a DBA that is so easy and invaluable to do a random audit. We audit logins otherwise and have a pretty tight process for getting access the database server.

Post #711984

jay-h

Posted Thursday, May 07, 2009 7:45 AM

Say Hey Kid

Group: General Forum Members
Last Login: 2 days ago @ 8:55 AM
Points: 685, Visits: 1,707

monitor access in real time? A DBA cannot be expected to be the corporate traffic cop.

In a large organization you can have hundreds or more legitimate users at any moment, most of whose names you don't know. Since their access is controlled by AD grouping, which in turn is controlled by their managers and HR, I have no idea what a DBA is supposed to be doing in 'real time' here.

...

-- FORTRAN manual for Xerox Computers --

Post #712030

Steve Jones - SSC Editor

Posted Thursday, May 07, 2009 8:20 AM

SSC-Dedicated

Group: Administrators
Last Login: Yesterday @ 6:14 PM
Points: 31,421, Visits: 13,734

Honestly, I don't think this is a DBA's job. I was curious if anyone would bring it up, but there should be someone doing compliance, that looks over the DBA.

That being said, the DBA needs to be able to set this up. Service Broker is a good idea. Pipe events to it, write them off somewhere.

Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help

Post #712061

Matt Miller (#4)

Posted Thursday, May 07, 2009 9:26 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 4:20 PM
Points: 6,998, Visits: 13,947

Actually - this sounds suspiciously like some of the stuff the new DMW could do. (where DMW = 2008's version of SqlH2).

Granted - both versions the Data Management warehouse are there to track performance, but they can hook onto a series of events and then respond in some way.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Post #712124

ta.bu.shi.da.yu

Posted Thursday, May 07, 2009 9:39 AM

SSC Veteran

Group: General Forum Members
Last Login: Sunday, July 03, 2011 7:09 AM
Points: 258, Visits: 494

Steve, I looked at her qualifications and they seem to be: marketing, managing at various IT companies, and an MBA. This would explain the extremely generic and wish-washy advise provided.

Random Technical Stuff

Post #712141

bitbucket-25253

Posted Thursday, May 07, 2009 2:23 PM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 5,102, Visits: 20,204

Steve a rather timely editorial ... listening to CNN broadcast this morning. Salient points --
1. FAA (Federal Airtraffic Authority) had someone hack into one of their networks and compromised over 18,000 passwords and login names...

2. State of Virginia reported that the database that tracks the usage of restricted drugs had been hijacked .. copied by a hacker... the state so far has refused to divulge what data (name, address, doctor's name for example) is contained in the database.. This highjacker offered to return the copy of the db for a ransom over 1,000,000 USD.
Reference http://hamptonroads.com/pilotonline/

So security is a REAL and EVERY DAY problem and the quicker it is recognized by DBA's and management the better off we all will be.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read
Before posting a performance problem please read

Post #712424

« Prev Topic | Next Topic »

Permissions