Voice of the DBA

Author: way0utwest

  • A Matter of Life or Death

    In this book a data breach, identity theft, causes a death

    It sounds like something out of a fictional story, but could there be a data breach that might result in a person’s death? I read a book recently called Medical error in which a patient dies because of an allergy. The physician checked their records and noted the patient had been prescribed the drug earlier that year at the hospital, administered it, and the patient reacted and died. It turns out the patient was a victim of identity theft and the thief was the one that was seen at the hospital. The thief  didn’t have an allergy, but the victim did and died.

    According to this story, there hasn’t been a case of a data breach resulting in someone’s death, but the recent attacks against law enforcement agencies could change that. Informants and undercover detectives depend on their anonymity. A release of details from active cases could result in a death since the people that would be most interested in the information might be willing to kill those named.

    We seem to have no shortage of people that hack and attack systems for fun, often without much forethought about the potential effects of their exploits. As criminals get more sophisticated, I would expect more attacks to occur against law enforcement agencies. We’ve already had attacks that cross national borders, potentially sponsored by governments and I suspect we will see even more in the future.

    The data professionals working in the industries where life and death are dependent on technology should be worried. In law enforcement and medical fields, data professionals should be constantly educating themselves about security and working to regularly ensure their systems are protected, and audited . Auditing may even be more important in order to respond to breaches quickly.  It is asking a lot, but the potential results from a security lapse could haunt you for a long time if you haven’t done your best.

    Steve Jones

    The Voice of the DBA Podcasts

  • Create a Login – Basic Skill #3

    This post is part of a series based on my presentation The Top Ten Skills You Need for SQL Server. This post is part of Skill #3 – Setup Security.

    I wrote about the basic security model for SQL Server, recently and wanted to now expand on the practical aspects of how you setup security. Let’s start with logins and creating them.

    In the current versions of SQL Server, which includes SQL Server 2008 R2 and below, a login is the way in which you connect to an instance of SQL Server. Things may change in SQL Server 11 with contained databases, but I think this will still apply in many situations.

    Logins are defined at the instance level, and in Management Studio, you can right click the Logins folder and select New Login to create one.

    newlogin

    This will create a dialog like this one:

    newlogin2

    There are lots of choices here, but really there are only a few decisions that we make for most logins. You might use the other options, but these are the basics for 80% of the cases, following the 80/20 rule of this series.

    The first thing is the login name. This can be a user or group in Active Directory, and as you can see in the next image, if you don’t know the exact name, there is a search button. This is the standard AD search dialog.

    newlogin3

    There is a radio button below the name, which defaults to Windows Authentication. That’s for AD accounts, and is the recommended default. You can also choose SQL authentication, which means that a password is required and the administrator must set it.

    newlogin4

    If you choose SQL authentication, and are on Windows 2003 or later (Vista/XP as well) that allow you to set password policy. As a note, leave these checked unless you have a great reason not to. For most logins you should not know the user’s password and it ought to conform to the policies.

    The rest of this page is advanced stuff that isn’t often needed. The only thing that you should look over is the default database. For normal users, make sure this is a database the person will have access to. For administrators, leave it at master.

    newlogin5

    Next we look at the server roles page:

    newlogin6

    These are roles, or groups, with permissions for the instance. They don’t necessarily give a person access to a database, but many of them could allow someone to gain access, so for most users, leave this blank. For administrators, give them just what they need.

    The user mapping is next, and this is where you can have the dialog create a user in the database and grant access.

    newlogin7

    Most users will need access to a specific database, usually the one you chose as their default database. If you select a database checkbox, the user will be created with the same name as the login by default. Leave this alone, it’s a good policy.

    newlogin8

    Once you select the user, you also can add a database role at the bottom. Everyone is a member of public, and you should have a database role you’ve created for permissions that you can assign to them. I dislike giving regular users any of the fixed database roles like db_datareader. They are too global in permissions and have caused me confusion later on.

    Create your own role and assign permissions.

    Next is the explicit securables tab. Don’t use this unless you know what it means. I never use it, and most of the time you shouldn’t. Leave it along until you learn why, and more importantly, why not to use it.

    newlogin9

    That last tab isn’t one you normally need, but you might come here if a user has locked themselves out.

    newlogin10

    Leave these defaults alone unless you need to disable the user or prevent them from accessing this database for some reason. You can switch the radio buttons. If the user is locked out, the bottom checkbox will be selected and you can uncheck it.

    That’s the basics of creating a user in SSMS. For the most part, stick with defaults and keep your security simple, and restrictive. Don’t grant more rights than you need to.

  • The Apprentice

    Vader's Apprentice: Hope tech work isn't this hard.

    It used to be that most serious developers went to college and studied CS. Those people that wanted to make a living programming computers became grounded in theory, struggled through assembler class, and understood the classic waterfall development technique. They could write bubble sorts and quick sorts, and could create and destroy pointers in thin air. At least in theory.

    As computer science curriculums evolved, they became more practical. Students no longer had to learn C or emacs (a shame, IMHO), but learned Java and .NET, working in nicely crafted IDEs like the ones they would use in the corporate world. Practical programming classes allowed students to build more rich and interesting applications, instead of just building virtual linked lists that  weren’t visualized outside of one’s imagination.

    At the same time, I’ve found that a larger and larger percentage of developers haven’t gone to college for a CS degree. Some have gotten other degrees, some just learned to program outside of school, and in both cases, they aren’t necessarily any better or worse than those developers with CS degrees. That leads me to this week’s question:

    Should we move to vocational developer training?

    We’ve asked the question before about whether a degree is required for a technology career, and I think most people agree it’s nice, but not necessary. However I’m wondering today if you think we ought to build better training for those people who are interested in computers, but don’t want to go to college (or can’t afford to). Should there be vocational schools that teach programming, maybe including apprenticeships with experienced professionals in the field.

    I think there is a place for some formal training in development, preferably including some C programming, but I’m not sure that I think a four year college degree is needed. I’d like to see a more practical, more affordable way for people to get bootstrapped into technology and begin a career, preferably with some apprenticeships that might build practical experience and good habits among younger coders and DBAs.

    Steve Jones

    The Voice of the DBA Podcasts

  • Prepping for Certification

    Having prepped for, taken, and then failed the MCM exam this year, perhaps I’m not the best person to give you advice for certification preparation. However the MCM is a hard test, I did better than I expected, and I was close. Lots of people haven’t passed it on their first try, including a couple people that work with SQL Server every day to build solutions that must work in the real world. A few of them passed on their second try, though I’m not sure if I’ll take it again anytime soon.

    Recently I ran across this preparation post from Susan Ibach on MSDN. I think it’s good advice and if you follow it, you’ll be prepared for the exam. At least you’ll have a good idea on which general areas the exams focus on since there are %s given for each area. I’ve shown part of the SQL Server exam 70-450:

    cert1

    Note that this section, which includes security for the instance, database, schema, and encryption, is 15%. Since the exam is supposed to be around 50-60 questions, this means that you should get about 10 questions on security. You might get 8, you might get 12, but I wouldn’t expect to get 20.

    I know most of you would like a step by step list of things to do for the exam. However these exams are going to test a wide variety of skills, and since 50 questions isn’t a lot, the exam can’t specify tightly which questions will be asked.

    Sidebar: Personally I’d like to see more specific exams, perhaps as specific as security, replication, SSIS, etc., but lots of people don’t want to certify in all those areas. Plus it changes the profits for MS since they have more exams to administer. We’ll see if this changes in the future.

    As a result you need to study a wide variety of materials. For example, for the security section above, you ought to tackle this in 5 sessions. Build yourself a short list of skills to have in each area. I’ll do section one for you. Here is what I’d go read about in BOL and practice in SSMS.

    If you can explain each of these things to someone else, and perform the skills, you should be fine. I would recommend you blog or write about these, because that helps you to learn and remember this stuff. If you blog about them, ask a friend to look at your blog and see if you have correctly described things.

    Once you think you’re OK with all the sections, not perfect and a guru, but you understand these areas, take the exam. If you want extra practice, get a MeasureUp or other practice test.

    There’s no guarantee and you shouldn’t expect one. Go through the material, explain it back to yourself or someone else and you should be able to pass the test.