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»»

Regular Audit Analysis Expand / Collapse
Author
Message
Posted Thursday, June 23, 2011 9:11 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 10:50 AM
Points: 31,284, Visits: 15,749
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
Post #1130917
Posted Friday, June 24, 2011 4:48 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, September 8, 2014 5:25 PM
Points: 416, Visits: 534
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?
Post #1131062
Posted Friday, June 24, 2011 7:08 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, December 2, 2013 6:30 AM
Points: 346, Visits: 691
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?
Post #1131120
Posted Friday, June 24, 2011 8:09 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, November 10, 2014 7:42 AM
Points: 492, Visits: 814
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
Post #1131168
Posted Friday, June 24, 2011 10:16 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 31, 2013 8:01 AM
Points: 1,228, Visits: 1,046
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.
Post #1131292
Posted Friday, June 24, 2011 10:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 12:18 PM
Points: 350, Visits: 259
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.
Post #1131303
Posted Friday, June 24, 2011 1:15 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Sunday, November 23, 2014 2:48 PM
Points: 1,754, Visits: 4,966
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'.
Post #1131415
Posted Friday, June 24, 2011 1:24 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 9:51 AM
Points: 5,446, Visits: 7,616
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
Post #1131417
Posted Friday, June 24, 2011 4:07 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 2:03 PM
Points: 1,334, Visits: 3,069
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. ..."
Post #1131496
Posted Saturday, June 25, 2011 4:59 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Friday, August 22, 2014 7:44 AM
Points: 606, Visits: 312
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.



Post #1131611
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse