Voice of the DBA

Tag: security

  • SQL Slammer

    Seven years ago I was in the mountains of Colorado, taking advantage of the a school break to get away for a long weekend. There wasn’t good cell phone coverage up there, and I was surprised as we drove back Sunday evening with a voice mail indicator on my phone. I dialed in and found a message from work to call ASAP. SQL Slammer had struck.

    I wasn’t expecting to get back to work so soon, but as soon as I got kids out of the car and into bed, I headed into work to deal with the issues. A crisis team was assembled, with the other production DBA already having spent much of Sunday at work. We worked late into the night, patching servers and trying to eradicate the worm.

    However we had literally thousands of infected MSDE instances that had been installed with custom paths and the patch Microsoft had released would not fix them. We had an engineer fly out from Seattle and help work through the issues, finding workarounds and understanding our environment. A combination of creativity and scripting allowed us to finally develop a solution late Monday night that we deployed into the wee hours of Tuesday morning.

    That was seven years ago and it was a memorable time for me. I had thought that the Slammer worm was dead, but apparently that’s not the case. Traces are still detected at times and this month the SANS Institute is trying to work with ISPs and companies to eradicate the worm once and for all. They are asking system administrators to spread the word and help them try and remove SQL Slammer as a threat. They are not confident that they’ll succeed, but I think it’s worth a try.

    I’m sure many of you encountered the Slammer worm if you were working with SQL Server at that time. Any great memories? Any stories to share? Let us know in the discussion below and if you can help eradicate the worm, make sure you pass the word to the administrators of SQL Server 2000 instances.

  • Yikes!

    I don’t love traveling, but after reading this, I am not sure I want to fly anymore. Perhaps I should take fewer trips next year, or stick to those events that I can drive to?

    The link is about a plane that supposedly had malware introduced into the airline computer network from a USB drive. The plane subsequently crashed and although it’s debatable whether or not the software was the cause, it’s an issue that needs to be examined.

    We use more and more computer systems in our lives, especially in our transportation. Planes, trains, and automobiles, all are becoming more and more computerizes, which I like, but the linkages between all their computer systems is something that concerns me.

    It seems that every few months we are constantly finding new ways in which computer systems can be hacked, altered, or changed, despite tremendous efforts on the part of software vendors. I’m not sure that we’ll ever completely secure our systems, which means that the more interconnections, and more access points we have, the more likely that someone will be able to introduce some type of malware into the system.

    Which could have extremely serious consequences for our safety.

    I don’t like the idea of a USB port in my car having any linkage to the mechanical controls, and I certainly don’t think that this should exist in airplanes. We ought to have a good separation of systems so that a failure or security breech in one, can’t affect any others.

    Steve Jones

  • Data Portability

    It seems that there is a never ending list of formats that data professionals need to deal with. As I read through posts and articles talking about Integration Services, I’m struck by how many unique solutions people come up with to move data in and out of Excel. They know how to handle multiple worksheets, headers, practically anything that people put into Excel. If there’s a pattern in there, chances are some SSIS worker has a solution.

    This week I saw that Facebook had announced a data portability initiative, giving users the ability to download all their Facebook data into one file. The file can include images, movies, posts, events, etc. and gives you a way to not only back up your data, but perhaps use it for other applications. I’m sure it’s only a matter of time before we have an SSIS package that can load up your information into a SQL Server database. I suspect someone will do it just for the challenge, and not because they have a practical use for it.

    Facebook seems to have been under attack constantly from groups that are concerned about the privacy policies and features of a company that has so much data about individuals. However this new feature seems to show that the company isn’t looking to lock you and your data into their own system. You can now backup your data and shut down your account if you with to move on to another service.

    Recognizing that data is important to users and allowing some level of portability is a feature that I wish more companies would embrace. I’d love to be able to easily move my medical records, my financial investment history, or even my credit data to my own systems as a backup. Or even as a way to document the current state and perhaps ensure that my data is correct.

    Steve Jones

  • Rights for one table

    I ran across a thread that was asking how to grant rights to a person for one table only, and not other tables.

    My response is simple:

    CREATE ROLE MySingleTableRole
    GO
    GRANT SELECT ON     dbo.MyCustomers TO MySingleTableRole
    GO
    EXEC     sp_addrolemember 'MySingleTableRole', 'Steve'

    That’s it. By default users do not have rights to any tables even if they have rights to the database. If you have a user that needs rights to one table, just grant them rights to that table.

    If the user is a member of a group that has rights to other tables, you should probably remove them from the other group/role. Then build another group for them, or for the other users. If this is the case, then your group is incorrectly being used as you have people in the group needing different permissions.