June 23, 2011 at 9:11 pm
Comments posted to this topic are about the item Regular Audit Analysis
June 24, 2011 at 4:48 am
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?
Data Enthusiast | @SQLbyoBI | www.opifexsolutions.com
June 24, 2011 at 7:08 am
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?
June 24, 2011 at 8:09 am
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
June 24, 2011 at 10:16 am
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.
June 24, 2011 at 10:27 am
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.
June 24, 2011 at 1:15 pm
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'.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
June 24, 2011 at 1:24 pm
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...
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[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
June 24, 2011 at 4:07 pm
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. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
June 25, 2011 at 4:59 am
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.
June 25, 2011 at 11:19 am
netmikem (6/25/2011)
.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.
True story Mike, SQL Audit does not currently have the NOT LIKE filter capability that a SQL Profiler Trace does. But I can usually get close to what I need through a SQL trace (through code) if I need to do something like this and call the START/STOP trace through a job. Or, if this is just not good enough for you, then just audit for each specific login and use a DDL Trigger to update the Audit Specification to include new logins as they are created dynamically. That will get you close to what you are after. Unfortunately, as robust as SQL Server has gotten over the years, it still can't do everything. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply