Tag: auditing

Quick Schema Auditing
I was working on a demo recently and needed to show that a little monitoring can help you catch schema changes. At first I looked at SQL Audit and DDL Triggers, but then I ran across a short custom metric on the SQL Monitor Metrics site that my company, Red Gate Software, put up to help people share their custom monitoring metrics and alerts.

The metric is called Schema Modified, and it uses a really simple query. This is all is does:
```
SELECT DATEDIFF(ss, '1970', MAX([modify_date]))
 FROM [sys].[objects];
```
It calls this query every minute for each database on which you have it enabled. This gives you a count of the number of seconds between 1970 and the latest schema modification in your database.

Now that’s not terribly useful, but if you look for changes in this metric, then it becomes interesting. For example, in one of my tests, I got this value

1413904788

If I continued to run the query, the same value was returned if nothing changed in the database. However once I added a new object, then the value changed to

1413905295

That’s an increase, and my alerting was looking for changes in the value, so when this new count of seconds appeared, an alert was raised.

Using the Information

What good does it do you to know that something changed? Admittedly, this may or may not be useful. This doesn’t tell you what changed, and certainly help you determine who changed things.

However, in more than a few of my development jobs, we knew people would change things. That wasn’t the issue. Really we wanted to know that something changed, and if so, we would investigate further. Often we could easily determine who made the change, based on what it was.

This is really a trigger more for something like production, where I don’t expect changes, except when I deploy things. Any other change is cause for concern, and I might have alerts set to ping people when there’s a change. If we’re making the change, then we ignore the alert, because we’re aware of it.

If we aren’t deploying changes, then we start investigating immediately.
October 29, 2014
Disconnecting Auditing

We know security is becoming more and more of a topic for IT professionals. As we realy more heavily on our computer systems, we have to be sure that the information contained in them is secure. We know that we can’t necessarily anticipate and protect the applications from every attack, but we can usually detect and respond to incidents. To do that, we need good auditing of all the events that occur.

The problem, in my mind, is that our auditing efforts and implementations are too tightly tied to the administration of our systems. The auditing features must be configured by administrators, who are also often tasked with the review of the auditing data and logs. This is a fundamental problem as it’s entirely possible that an administrator or privileged user might be just the person that will violate security practices. With their rights inside of the computer system, it’s likely that the same person perpetrating the malicious activity would be able to easily cover up or remove any evidence of the incident.

I think that auditing is fundamentally implemented poorly. Auditing features in software, including SQL Server, should be separated out from administration, perhaps even configured and enabled by a separate user or account than the person who administers the system. I would anticipate that a person in the finance or accounting departments at most companies might be responsible for managing the audit data. Even if they were unsure of the meaning of the data, having control over the information would prevent problems with the auditing data being compromised. I could even see auditing services being offered by third parties that interpret or review the data for companies without a dedicated security department.

I doubt we’ll see a disconnect anytime soon, but I do think that the value of auditing is drastically reduced when we don’t have a strong separation of rights, responsibility, and capabilities between auditing and administration.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

August 18, 2014
Auditing Matters

There’s been some reporting about Yahoo Mail getting hacked and potential issues recently. I ran across this piece that talks about what happened and it’s an interesting read, but there’s one quote that stuck with me: “Yahoo reported on its Tumblr that it had detected “a coordinated effort”—basically, an attack—by somebody trying to gain access to user accounts.”

It’s not the notification or the proactive resetting of accounts, but the detection that resonates with me. We’ll never stop all hack attacks. We’ll never plug all the holes in software. We’ll never anticipate the ways in which our systems might be compromised, but we can detect issues. I think more often than not we can deal with any hacks or attacksif we are aware they took place.

We have some login auditing in SQL Server, an auditing and eventing framework, and the ability to capture and store this data. However we don’t have good proactive tools to help us detect issues. I’d like to see enhanced tooling to allow us to review log data, write alerts that look for patterns, and more. While much of this can be built by DBAs, it requires use to develop and maintain software, and even then it’s easy to miss potential attack vectors if you don’t constantly supplement your knowledge and enhance your tools.

Given what we have to work with, I’d encourage you to learn a bit about the different frameworks and gain some basic skills with the tools. I’d also encourage you to think about writing queries to look for potential hacking issues, like updating all of your lookup values to the same string, or embedding script tags in your data. I’d encourage you to write or speak about what you learn, and how you use the information. If you’d like to write software to make the task easier, that would be great.

I’d also encourage you to befriend your network or security administrators and teach them how to query their own lo data. Lots of their tools collect data, but provide poor query tools for the information. Perhaps you can even build them a data warehouse that allows them to tighten security by examining their own data.

Steve Jones

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and Mevio .

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.

February 24, 2014

The Future of Auditing

I was reading Captain’s Share the other day and enjoying a quiet afternoon at home. It’s a science fiction book about one man’s journey in the future as a captain of a space freighter. It’s an interesting series from Nathan Lowell that I’ve enjoyed and recommended to other science fiction fans. In the book, there’s a scene where the main character is leaving his old ship as first mate and moving to a new ship as the captain. However he notes that the formal process is to deactivate his records on the old ship and ensuring they will be read only forever. The book notes they can’t be deleted because they are a part of the ship’s records, log entries, etc.

That seems to be a far cry from the way auditing takes place in current computer systems. Auditing of systems is under the control of the sysadmins (who are sometimes hackers) and can be altered, changed, etc. We, as software designers, haven’t done a good job of ensuring the integrity and longevity of log records. In some sense, it seems to be a fundamental flaw in OS and software design to not have separated out the auditing and recording of actions from the administration and rights of the rest of the system.

I’d hope that we would recognize that auditing actions and preserving this data is something that ought to be tightly linked to, but separate from, the rest of system operation. I’d like to think that fundamental changes and actions taken on the system should be written separately to an area that is easily marked as readable by non-sysadmins that are designated to review the information. I know we have the challenges of managing the space and the problems of spurious actions being generated to fill (or rollover) logs, but I’d think after 50+ years of computing we would have considered some sort of event log that isn’t under the control of the people whose actions it is recording.

SQL Server has improved its auditing features and capabilities, but far too much is still linked invariably to the sysadmin, often the same person the auditing should be watching. This is certainly one area that I hope matures in future versions as the need grows to track and review actions taken by privileged accounts.

Steve Jones

Video and Audio versions

Today’s podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music. Support this great duo at www.everydayjones.com.