Voice of the DBA

Tag: auditing

  • T-SQL Tuesday #46–The Rube Goldberg Machine

    tsqltuesdayIt’s T-SQL Tuesday time again. This is the monthly blog party where we all write on a topic. This month the topic is the Rube Goldberg Machine, as it might relate to SQL Server. You can read the invitation linked above from Rick Krueger and write your own post if you choose.

    Just be sure you post it on Tuesday, Sept 6, 2013.

    Catching the Cheaters

    A long, long time ago, in a county just to the west of here, I worked for a small company as a DBA. I had to support our production operations, as well as a team of 10-12 developers that were building our main company application. Since we were an ecommerce type company, this was an important application for the company.

    We were working in an high incremental way, probably similar to an Agile methodology, and deploying changes every week. However when I started the deployments weren’t smooth. Our developers were handling them, often bumbling them a bit, but able to fix things in an hour since they had done the development.

    Not a good separation of duties and it was becoming more of an issue as our boss wanted minutes of downtime, preferably single digits. I tracked down the main cause as a haphazard development style where changes were made to databases, instances, and IIS without much tracking going on. The IIS changes were easy: the developers had their rights removed from production and even test machines for configuration changes. However the database required a bit more work.

    As this was SQL Server 2000, we had limited ability to audit and manage rights. We also required our developers to have rights to the development machines in order to create test databases and work on importing data. We also wanted to ensure that developers moved quickly, but I needed a way to keep control of the instance.

    The first contraption I used was a simple one. I loaded the output of sp_configure into a table on the instance. I had created an administrative database that I used to track backups, and I added this information in there. I then created a job that would run sp_configure every day, compare the values to my table, and then alert me to changes. It would also update the table with the new, current values.

    This allowed me to catch various changes that developers were making when they tinkered with the server to “make it run faster”. I didn’t prevent changes, and if their changes worked, we’d deploy them to test (and eventually production), but this allowed us to document them and be aware.

    This worked well enough that I build a few more mousetraps. Catching schema changes is hard, as there isn’t good auditing in SQL Server 2000. However there is a “version” number for each object that is incremented when it’s changed. I built a similar audit/job system for our sysobjects table, but ran this every hour, catching changes. When I was alerted, which was more days than not, I’d email the team, track down the person changing things, and make a note for our current branch of work.

    This worked great in that development wasn’t slowed, but I was able to account for all development changes and slot them into the current, or future, development deployments. In a few months we reduced our deployment time from about an hour every Wed night to less than 5 minutes.

    Sometimes the mousetraps actually help the mice work better.

  • T-SQL Tuesday #45–Follow the Yellow Brick Road

    tsqltuesdayIt’s T-SQL Tuesday time again, and this month Mickey Stuewe hosts the party. It’s an interesting topic, and one that I think is important, and will be more important, as we capture, store, analyze, and depend on more and more data.

    The topic this month is auditing, and you can read the invitation on Mickey’s blog. I think it’s amazing no one has asked us to write on this in the past, and I expect we’ll see some interesting stories that people will share.

    This is a monthly blog party and all you have to do to participate is write a post on the 2nd Tuesday of the month. The topic comes out about a week before, and you can schedule something to go out on the appropriate day. Be sure you leave a comment on the host blog or a pingback.

    Auditing

    I’ve never been bound by regulatory requirements for strict auditing. When I worked in industries that had bounds, they weren’t really updated for computer systems, and lots of our practices slipped by because no one thought to look. A few times that was scary.

    However I’ve implemented auditing in various systems, in various ways. The most common way to do this in the past was with triggers, though I had a situation where I had to build a unique solution that would capture changes being made to systems.

    I worked for a small company one time and we had a few critical systems our clients regularly accessed remotely for various functions. This was in the era of client server systems, and we delivered software to our clients that connected to our servers. We had lots of buggy software, and when we released new features, I’d cringe as I knew we would have calls and complaints for a few weeks as we patched things.

    I ran a team of 3 DBAs, and we had 5 or 6 developers working for another manager, including a few remote developers. We had agreed to limit changes to known times, and document the packages being deployed so that we were aware of changes and could better troubleshoot issues. Our environment was complex enough with multiple processes communicating with each other and SQL Server without adding the issues of unknown changes. We communicated things to our development staff, and though they grumbled everyone agreed.

    One day I came in to find that a process that had worked the day before had failed. We were struggling to get it working, and eventually discovered that a schema had been changed. This was in response to another enhancement, but it caused an import process to change. We hadn’t scheduled a change, which must have occurred overnight. No one admitted to making a change, and after a long day, I had to drop it.

    But not completely.

    I went back and set up a server side trace to run and capture information for object changes. I let this run constantly, rolling over the files periodically. It was a bit of a load on our system, but I felt it would be short term and it was important enough to understand what was happening.

    A few days went back and we were having an issue with another part of the system. Clients were screaming at my boss, who was coming to the development area to complain to DBAs and developers alike. As we tried to reproduce the problem on test systems, suddenly it started working.

    Relieved, but concerned, I decided to check my trace. Sure enough, I found a change to an object logged, by none other than that craft “sa”. Digging in further, we discovered that a piece of middleware had a timestamp of a few minutes before the system started working.

    It can be hard to track down who is using an “sa” account, which is why it is not recommended as an account to use by individuals. Cross referencing some information from the network team, we discovered our remote developers were slinging code changes on the system whenever they felt like it, using sa and “Administration” on Windows.

    They patched systems at times, but often these were patches to fix issues in poorly written software they’d deployed in the first place. They were also patching patches when thing didn’t work. Whether this was in reducing the time of problems or not was difficult to determine, but it certainly meant a lot of wasted time when we tried to troubleshoot issues with no idea what had changed. We also found lots of their changes weren’t being added to version control.

    My boss agreed this was an issue, and it allowed me to change all system administrator passwords, as well as revoke all rights to production systems from developers. Eventually we achieved more stability in our systems than the company had ever found. I’d attribute this to preventing cowboy coding changes to systems, which seem to cause as many issues as they fix.

    Auditing is something I’ve depended on, especially for myself. As I’ve worked with more and more systems concurrently, I find that it’s easy to forget about changes made in a hurry, or forget which change occurred on which systems. Logging and auditing allows me to retrace my own steps, as well as those of others.

  • Honeywords in SQL Server

    honeypotAs we become more and more security conscious, it becomes more important not only to configure systems for better security, but also to add more monitoring and auditing to detect when problems occur. We know that at some point someone will attempt to hack our systems. Many of us have auditing set up to detect failed logins, but is that good enough?

    If a hacker manages to gain access to your password hashes, and it’s not a stretch these days to think that they might, wouldn’t you like to know if they manage to find the plain text that corresponds to the hash? There’s an idea that systems could be written to store multiple passwords for user accounts, but only one of which is valid. A separate system detects attempts to log in with the false passwords and alerts administrators to a hacking attempt.

    This is an interesting idea, and while it won’t solve all our problems, it will solve some. If a brute force attack occurs on an account, and multiple passwords are being tried, all of which are known to be false (the honeyword passwords), administrators can be notified, and warnings passed on to users. It doesn’t help if the hacker chooses the correct password to enter first, but with enough honeywords, you reduce the chances that they will.

    I don’t know that I’d like to see this for SQL Server, but I certainly would like to see additional security features. Two factor authentication would be nice, perhaps even some sort of  approval process enabled that required multiple approvals for some changes. The latter would help us prevent the cowboy DBA from making changes without anyone else being aware of them.

    Detection of breaches, using something like honeywords, provide another layer of security. They don’t prevent hacks, but they can help us deal with them.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.

  • Double Compliance

    hipaa_compliant_seal    vs          pci-seal

    I haven’t ever been bound by HIPAA or PCI regulations with the data I’ve managed. Those laws hadn’t been enacted when I worked in those industries, and so I’ve never had to go through an audit. I have been through ISO 9000 and SOX audits, and I found those audits to be both a pain and also a good idea. Various inconsistencies and exceptions in our policies were found, often strengthening our security or bringing more consistency (and stability) to our organization. Those two audits were also very close in scope and requirements. If we could pass one, we typically could pass the other.

    However PCI and HIPAA are not the same, and you shouldn’t expect that passing one would mean you could pass the other. For most of us, we wouldn’t be bound by both of these, since they are applied to the financial and medical fields respectively. However as we look to move forward and use new partners in business, including cloud services, we should be aware that just because a company has one certification doesn’t mean they have the other. If your business partner is PCI complaint in some way, I wouldn’t assume that this means they are in any way HIPAA compliant, and vice versa.

    Should we have standards for data protection that matter to a variety of industries? I’m not sure we should, despite the hassles that may mean for those of us bound by these regulations. Each industry and type of business has it’s own requirements, some of which are not applicable to other fields. Trying to build one standard for privacy, security, or any other requirement is likely to mean a watered-down, ill-fitting regulation that doesn’t protect any data well. Instead we should have specific requirements we need to meet to provide security (or any other need), without specifics on the technology or implementation used.

    Most of you probably don’t like the idea of any regulation, and I’d like to agree with you. However I’ve seen too many people ignore good practices, engage in morally debatable activities, and in general treat other people, and data, in a way they wouldn’t want to be treated themselves. A little regulation, that limits abuses and gross malpractices is a good thing. Too much regulation, specifying details that are often obsolete before they can be enforced, is a bad idea.

    Steve Jones

    The Voice of the DBA Podcasts

    We publish three versions of the podcast each day for you to enjoy.