Voice of the DBA

Tag: administration

  • 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. feed

    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.

  • Monitoring for Non Existent Events

    I was catching up on work recently, reading the third installment of The 5 Worst Days in a DBA’s life, starring The DBA Team. Someone had asked me if I enjoyed having Paul Randal (b | t) of SQLskills join them team. The piece had been edited and published while I was gone, and I hadn’t had a chance to immerse myself in the adventure. I was anxious too read how Paul helped save the day.

    It was a fun read, but one quote in the piece struck me. “A job that runs long or doesn’t run at all can sting just as bad as one that fails.” That’s a quote from my character showcasing a situation that few people actually think about. However jobs that don’t run or don’t finish are situations that DBAs should be monitoring for.

    So many of us adopt a set-it-and-forget-it mentality with our jobs. We assume that things will work, or fail, as we set them up. However it’s easy to forget that there are other states we might find ourselves or our systems in that can cause issues.

    Monitoring is critical to any well run system, but monitoring needs to be set up well. If we require that certain jobs run, we need to not only check for success or failure, but if the job has actually run and completed. It’s easy to accidentally disable the wrong job and not notice. It’s also entirely possible that a job gets stuck and doesn’t complete.

    If you’re not watching for those other states, you might find yourself in a situation where you don’t have backups and your job is on the line. However you probably won’t have The DBA Team to call on.

    Steve Jones

    The Voice of the DBA Podcast

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

    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.

  • Frustration with Bad Design

    This is not how you handle things. When you find security issues, and they don’t get fixed, it’s one thing to attempt to prove a point with a PEN test. It’s quite another to publicly expose information. You might find yourself in trouble, and you should be in trouble. This is a violation of the professional responsibility you undertake when working for someone.

    This is the type of frustration that occurs in many IT workers. I’ve seen more than a few people working in technology that are sure they know how to properly configure and manage an application. They know how to set up security, and they become upset with a company that doesn’t do a good job of running internal systems. They know that the architecture chosen for their application will fail when a load is applied.

    There are some smart people in IT, but sometimes they think they’re smarter than they are. Bad design, bad decisions, mistakes, even poor security practices will occur. However it’s usually not your company, and it’s not your place to prove that there is a flaw in a system. It’s especially true that it’s not your place to prove things without having been given permission to do so. Proving a point on your own is something children do, not professionals.

    When you find problems in your organization, it is your responsibility to report them. I hope you think it’s your job as a professional to do the best job you can, following the best practices as we know them. It’s also your decision to choose to leave a job if you can’t go along with, or abide by, the decisions made by your management.

    If your company has chosen poorly in their technology decisions, I understand your frustration. I’ve often shared it, but I’d advise you to do what I’ve done. State your objections and either support the chosen path or find another job.

    Steve Jones

    The Voice of the DBA Podcast

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

    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.

  • Administering Securely

    This piece was originally published on Jul 8, 2009. It is being re-run as Steve is away on sabbatical.

    The other day I noticed a post where someone was asking a question that I’ve seen asked often. I still haven’t seen a great solution, and I expect this question will continue to be asked for some time to come. The poster asked how to securely set up a database to prevent administrators from accessing the data.

    Prior to SQL Server 2005 this was pretty much impossible. The sysadmin group was considered to be like a god inside the SQL Server and could access most anything. With 2005 there are more restrictions you can place on the administrator, and with the addition of encryption capabilities, you can prevent casual access to certain data.

    However it’s not easy, and most of the time the system administrator still has access to data for tuning, troubleshooting, disaster recovery, etc. That makes sense sometimes, and I feel that you really have to trust your administrator with a lot of responsibility and discretion. Sometimes, however, it’s just not appropriate for the person that runs the server to see other data. Salary information, among other data, sometimes just isn’t the business of the administrator.

    I don’t know how you handle this. Does the administrator just get access to the database as a container, able to attach it, back it up, restore it, and perform basic functions? Are they limited to setting security for users, but unable to access the objects themselves? I’m not even sure that’s a valid way to handle things since the administrator can always set up a dummy account for themselves, or change someone’s password and access data.

    To me the best solution for secure administrator access is to have a second person audit all actions performed by a sysadmin. Kind of a default trace for sysadmins that is always running, and is not accessible to the administrator. Even then, it’s probably only something that works in larger companies where you have enough people to dedicate to the task of reviewing things. Maybe policy based management (PBM) will help here at some point, limiting the access to data by administrators in an easy to understand manner that a manager of some sort can understand and audit.

    There’s no good solution now other than to trust your administrators to responsibly manage data, and that means hiring responsible people for the job.

    Steve Jones