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


Regular Audit Analysis


Regular Audit Analysis

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: 36192 Visits: 18751
Comments posted to this topic are about the item Regular Audit Analysis

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
Bill Anton
Bill Anton
SSC-Addicted
SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)SSC-Addicted (436 reputation)

Group: General Forum Members
Points: 436 Visits: 548
My group (BI Dev/decision support) implements very basic auditing into our solutions that only gets reviewed in the event of an issue...so 100% reactionary. The main purpose of our auditing is simply to help us troubleshoot...so you could even say its more of an "event log" rather than "auditing".

Obviously it would be nice to take a proactive approach here, but our group is just too short on time. Heck - even padding time in the project plan for decent documentation is like pulling teeth with mgmt. Communicating the importance of these types of tasks to upper mgmt continues to be a challenge. Any tips?
roger.plowman
roger.plowman
SSChasing Mays
SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)SSChasing Mays (621 reputation)

Group: General Forum Members
Points: 621 Visits: 1125
Auditing data access I would think is near impossible to scale.

For example, the ideal situation would log every record's access by every individual who accesses it, presumably with a datetime stamp at the very least. Probably you'd also want the individual's location as well.

Clearly to keep this level of auditing data for any length of time would require storage many orders of magnitude larger than the database itself--especially if the number of users and/or the number of accesses was large, and you wanted to keep the data for a useful period of time, say 6 months.

Likewise, even keeping delete log data for certain tables that routinely see large numbers of deletions is problematic too.

That's why ACL-style security was invented in the first place. It eliminates much of the need for access logging. Couple that with access restrictions (perhaps a time window or even physical location) and you again reduce the need to know who accessed the data.

Even if you limit the access log to who accessed a stored procedure (which makes the problem managable) does that really buy you enough forensic info to make the game worth the candle?
djackson 22568
djackson 22568
Right there with Babe
Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)Right there with Babe (739 reputation)

Group: General Forum Members
Points: 739 Visits: 1176
Absolutely! I only manage about 35 SQL Server servers, containing far more databases, and also am the system admin for 20 application/systems. I am always looking for ways to fill my time, since I have so much left over. My boss is difficult to work with though, and won't allow me to take on more work. Why just the other day...

Who are you kidding.

(Anyone who at this point does not recognize the toungue-in-cheek paragraph above should give up and stop reading.)

Remember the post on fixing security the other day? Have you looked at the economy, unemployment, current affairs? Maybe you have noticied that companies are even worse today about laying off and then not rehiring, piling more and more work on people, and calling it efficiency.

I can think of all kinds of ways I could automate audit checking. We could email and page appropriate staff when something was going on. I can think of no time in the past few years where I had 5 minutes to actually work on anything that wasn't "approved" in budget.

Refer to the other post for a discussion on how serious business is about actually securing anything.

Dave
SanDroid
SanDroid
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1414 Visits: 1046
This montoring and analysis at my last three employers is automated by a CA Enterprise monitoring product.
The analysis output is useless, but it satisfies the regulations and Audit requirements for our company.

That is why I think we have such a huge issue. Regulations where created and audits put in place to find and mitigate security risks. Unfortunately the tools approved for use by the auditors are not at a level of safitication to actualy do this.
There are tools out there that can do these security audits at a level that could be considered functional, but I have yet to find one that is fiscaly responsible and on our auditors list of approved products.
JChrisCompton
JChrisCompton
Old Hand
Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)Old Hand (380 reputation)

Group: General Forum Members
Points: 380 Visits: 283
wta306 (6/24/2011)
[We implement] very basic auditing into our solutions that only gets reviewed in the event of an issue...so 100% reactionary.


My group is similar.
Note there are other groups here (security and platform people) watching various stuff and sometimes that causes a proactive review. Also we have an IPS which is proactive - but you can't credit my group with that.

That's the 'general' answer - there are applications that get much more attention.

Mostly our 'general' way of doing business is driven by the customer's budget.
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4623 Visits: 9574
Some types of events warrant not just logging or auditing, but sending an immediate notification to the DBA's attention. For example there is no good reason for the following errors to occur on a production server. If they occur out of the blue, then it indicates somebody is poking around for some reason, wether it be a hacker exploiting a login account or another DBA fumbling with a SSMS query window.

Msg 916: The server principal 'xxx' is not able to access the database.
Msg 208: Invalid object name 'xxx'.
Msg 911: Could not locate entry in sysdatabases for database 'xxx'.



"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Evil Kraig F
Evil Kraig F
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: 5703 Visits: 7660
All the data in the world doesn't have any value if it's not used. In a security context audit data isn't all that useful if it's only examined when an incident is discovered.


And yet we don't have time to build automatic reviewers, maintain them for new ideas, instances, etc, and then spend the time to deal with the billion false hits we'll get to find the one true one. It ends up as a complete time sink and even a good DBA eventually gets tired of hearing the boy cry wolf.

You could have entire teams of people doing nothing but building and examing auditable information for pattern detection. Then reviewing again for the items they hadn't though of yet. And digging into every message not only for what it does mean but what it might mean, and combinations that indicate an attack vs. simple business.

Time and money are against us. Audits are there to explain what happened. The realistic chances of catching it in motion are nil if they've gotten through the firewalls...


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
TravisDBA
TravisDBA
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: 1472 Visits: 3069
roger.plowman (6/24/2011)
Auditing data access I would think is near impossible to scale.

For example, the ideal situation would log every record's access by every individual who accesses it, presumably with a datetime stamp at the very least. Probably you'd also want the individual's location as well.

Clearly to keep this level of auditing data for any length of time would require storage many orders of magnitude larger than the database itself--especially if the number of users and/or the number of accesses was large, and you wanted to keep the data for a useful period of time, say 6 months.

Likewise, even keeping delete log data for certain tables that routinely see large numbers of deletions is problematic too.

That's why ACL-style security was invented in the first place. It eliminates much of the need for access logging. Couple that with access restrictions (perhaps a time window or even physical location) and you again reduce the need to know who accessed the data.

Even if you limit the access log to who accessed a stored procedure (which makes the problem managable) does that really buy you enough forensic info to make the game worth the candle?



My thoughts exactly Roger! Concentrate on security first, and then your need to audit diminishes. Now that said, the need to audit data periodically based on specific need or particular circumstance never totally goes away, but it does diminish considerably if you address granular security properly first. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
netmikem
netmikem
SSChasing Mays
SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)SSChasing Mays (634 reputation)

Group: General Forum Members
Points: 634 Visits: 360
We've recently implemented auditing in our shop. In terms of head count, server count and application count, I suspect we are smaller than most. However, we're a financial firm and there is a lot of money involved with our day-to-day activities. We're also trying to obtain certain audit designations so we're in the midst of a long term audit plan.

Our approach was to first clean up database security, then identify those objects deemed sensitive and start auditing operations involving them. In some cases, we decided not to audit operations on these objects performed by authorized users. Instead, we only audit operations on these objects performed by those who would not normally use them (e.g. IT having to fix a trade gone awry, a rare event).

We set up a simple SQL job to run daily and e-mail the audit activity from the last 7 days to a few of us. If we see anything unusual, we follow up with the individual for an explanation and keep their response around. We produce a summary report on a monthly basis. It is somewhat painful and tedious but the auditors love it. As mentioned in this thread, this approach may not scale well. I suppose the point here is to consider limiting what you audit to key objects and atypical users so that you don't have to sift through reams of useless audit events.

One issue I would like to see addressed in SQL Server audit is to be able to specify a Windows group or user NOT to audit, meaning that every other user would be audited. If this was available, we could eliminate needless audit events for authorized users but be assured events by anybody else would be audited.

Also, our database is replicated for disaster recovery purposes so there are a lot of replication related commands that contribute to the audited events. This is another reason why we'd like to be able identify users and/or groups we don't want to audit.



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