SQL University Hoops Practice

OK folks, sorry about having to cancel practice last week but we didn’t want to take any chances with the current swine flu pandemic that is sure to be made into a TV movie to be shown during May sweeps next year. I know that many of you had a chance to practice on your own at http://test.sqlserverbeta.com, and I think that is wonderful. For those that have not yet had the chance I want to remind you that when I tell you something is “optional” what that means is “you should be doing it anyway without me having to remind you”.

Your lessons this week have been on SQL security, or what I like to call defense. And your defense needs improvement. Trust me when I tell you that no one in this locker room is on their way to being named defensive player of the year. Although Jeremiah (blog | twitter) was pretty good at defending himself when we accused him of replacing our bacon with tofu at dinner the other night, what we really need is some defense out on the floor.

So let’s get started, two lines at half court. Lay ups. <whistle blows>

Layups

Learn what authentication means. Understand that it is the first thing that happens when a user tries to connect to an instance, they need to be authenticated. Just like you need to be able to do lay ups with both your right and left hands, you need to understand the two types of authentication your SQL instance will allow. Know the difference between those two, as it will save you a lot of headaches later on. Lots of people assume one but end up asking for the other because they have no idea about the difference. That’s where you come in: we need you to understand the difference, but also to be the difference.

You can be the difference with good defense. In this case, with a good security policy.

Shooting

<shouting> JUMPSHOTS! FIRST FROM THE WING, THEN ELBOWS, THEN BASELINE JUMPERS. GAME SHOTS AT GAME SPEEDS! DON’T YOU DARE WALK THROUGH THIS!

Now that you know how you can be authenticated, we can look at what authorization means. I always like to keep this simple, so just think of authorization as permissions. Like a military document marked TOP SECRET and someone says “you’re not authorized to look at that”. Same thing here. Once you are authenticated the database engine then goes about figuring out if you are authorized to do things.

Practice reviewing the fixed server roles and the database roles. Then, look at some of the various GRANTS allowed on different objects inside a database. When you are done, go back and do it again. Because I said so. And because your defense the other night was awful, you could use some extra work. Then I can find you some extra time on the floor.

Dribbling

Everyone on the baseline, let’s get some dribbling in real quick. Go through the Chill Drill four times, two times from each side, finish strong with a lay up.

Go to your SQL instance and add in some logins. Yes, some of each. And configure permissions for them. Use the GUI to do this but then click that little ‘script’ button and examine the T-SQL that is output. Get used to the commands that are being used behind the scenes for login creation and permissions.

Fast Break

Let’s put it together now. Start reviewing the logins on your servers. Do you know if they are still valid? Did you know you can check on which ones are no longer valid by running the sp_validatelogins system stored procedure? Go ahead, load that into a new query window against multiple instances and see if you get some results.

Next, start poking around the sys.syslogins view. Get a feel for what details are being returned. Look for some patterns in the result set. After that, head over to the sys.sysusers view. Same thing, review the output and get used to the information being presented.

Then, when you are done with that, take a look at sp_helprotect. Go ahead, run it, and check out the output. See if you can make sense of what it is trying to tell you.

Sprints

Everyone on the line. I am going to put 29 seconds on the clock. You must finish running the lines in 29 seconds. If not, then we will run it again.

Configure SQL Profiler to capture login details. What? You don’t know how? No problem, I am sure you can figure it out. If not, I can find someone that else that will. Oh, you would rather use SQL Server 2008 Audit? That’s fine with me. Go ahead. And don’t forget that you can also use Policy Based Management to audit login information. As long as you understand that there are a variety of ways for you to examine the login activity on your server.

Press Break

Get some water and be back in two minutes.

OK, we need to work on our press break. We struggled with Sybase ASE Junior College and their 1-3-1 full court back into a 1-3-1 zone. And we shouldn’t struggle with that at all. We should know to spread ourselves in a 2-1-2, throw over the top when necessary, get the ball over half-court, and run ‘corner’ to slice through the zone for some easy looks. But, since you weren’t able to do it the other night we will have to go over it today, because DB2 U had some scouts there the other night and I know they will do the same thing to us this weekend. So let’s get ready.

Start collecting your syslogins and sysusers information daily. It is crucial to do so, otherwise you will not have a way to prove that permissions have not changed on your end from the previous day. I have lost count of the number of times I have heard “it worked fine yesterday”. If we did not collect those details then we would not have a way to verify if something changed on our end or on the users end.

OK, here is the starting five for our match with DB2 U:

• KBK (PG)
• Steve Jones (SF)
• Chip Andrews (SF)
• David Litchfield (SF)
• Richard Waymire (PF)

There is no practice next week, as I will be on a recruiting trip in the Seattle area. I will give you the “option” of using http://test.sqlserverbeta.com to continue practicing on your own. Right now let’s work on some OOB (out-of-bounds) plays, this time from half-court. You do this one right and we’ll get a lay up or baseline jumper every time.