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


Protect and Monitor


Protect and Monitor

Author
Message
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36107 Visits: 18741
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
My Blog: www.voiceofthedba.com
James Stover
James Stover
SSC Veteran
SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)SSC Veteran (279 reputation)

Group: General Forum Members
Points: 279 Visits: 862
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

SuperDBA-207096
SuperDBA-207096
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1475 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.
bob.willsie
bob.willsie
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
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.
dma-669038
dma-669038
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 1035
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.
jay-h
jay-h
SSC Eights!
SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)SSC Eights! (923 reputation)

Group: General Forum Members
Points: 923 Visits: 2222
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 --
Steve Jones
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36107 Visits: 18741
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
My Blog: www.voiceofthedba.com
Matt Miller (#4)
Matt Miller (#4)
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7641 Visits: 18073
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?
ta.bu.shi.da.yu
ta.bu.shi.da.yu
SSC Veteran
SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)SSC Veteran (251 reputation)

Group: General Forum Members
Points: 251 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
bitbucket-25253
bitbucket-25253
SSCertifiable
SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)SSCertifiable (5.7K reputation)

Group: General Forum Members
Points: 5689 Visits: 25280
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
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