Voice of the DBA

Tag: administration

  • 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

     

  • Lockdown or Let Them Free

    This piece was originally published on Sept 21, 2009. It is being re-run as Steve is away on sabbatical/

    I ran into this blog post about IT v other workers. The post is in response to an article in Slate about workers being oppressed by their IT departments. They’re both relatively long reads, but the summary is that a writer at Slate thinks that the technology departments are too restrictive and unnecessarily hindering workers. The blog rebuts that point with the notion that many technology workers don’t understand the complexity of their systems and there are valid reasons for not allowing workers to have free choice in what applications they install.

    Having been in a number of technology departments, from small to large, I can say that I see both sides.  On one hand technology departments spend a lot of time and money cleaning up mistakes and problems from users. On the other hand, new applications and enhancements can often increase the efficiency or effectiveness of workers that find a new way to do their jobs.

    The problem in both cases is a few extremes are being chosen to  represent both sides. Most users don’t  require lots of attention from IT for their machines. And most IT solutions punish everyone for the problems that a few people provide.

    I think that as DBAs we sometimes start to feel this way about developers. We classify them all as problems and give them no rights, or we think that every person must have all rights to all instances. This extreme set of solutions isn’t practical, or effective, for most organizations. As much as I might seem to hate developers and make fun of them in these editorials, I recognize that there are many talented programmers and quite a few that know more about SQL Server than I do.

    The best way to handle rights and access is to selectively apply permissions to individuals, matching up their skills with their rights. If a user has problems creating indexes or adding tables, remove those rights. If they are a model DBA, then perhaps they deserve sysadmin rights. You can either loosely apply security and then tighten it up or lightly apply it and loosen it as people prove themselves.

    Let me say that I still highly recommend the use of roles for the actual implementation of permissions, but don’t view security as a set it and forget it. You should re-evaluate it periodically, and that would include the permissions you give to your co-workers.

    Steve Jones

  • Attaching All Databases with PowerShell–Refactoring out Write-Host

    Someone posted a note to me on Twitter that noted that Write-Host is not recommended for your scripts. I checked the link to an MSDN blog on Write-Host Considered Harmful, and it made some sense. Basically it says that since Write-Host always goes to the console, any output sent through Write-Host can’t be consumed in a pipeline by other PoSh commandlets or processes.

    At first I thought, what does that have to do with my script? I’m really just noting status information. However, the more I thought about it, the more I realized that it’s a minor change, and who knows? Maybe I’ll chain this in some other process, or more importantly, maybe someone else will.

    Today I popped open the script in the PowerShell ISE and did this:

    posh_a

    That’s an easy fix. Just write the output to the pipeline, and if there’s nothing consuming output, I get it on the screen.

    I also refactored a bit more. I added a “Debug x:” line to each Write-Output command, with x replaced by the appropriate debug level I’d checked for. This way I know what debugging output is being returned to the calling screen.

    I also found a few lines that were just output, using “Attaching as…” code. I replaced those with Write-Output.