Welcome back to another week of SQL University. This week’s lesson is on security, you know, that thing that most people never think about until it is too late? Or put another way, that thing that people never want you to mention. Ever.
Well, that’s too bad because this time we are going to mention it. So sit back and take good notes. There is a test on this later and you’ll need to pass that test before I will let you unlock the door to get out of the room. Good luck!
tempdb
With regards to security and SQL Server the first item you need to understand is what authentication means. Authentication is the first thing that happens when a user tries to connect to an instance, they will need to be authenticated. You also need to understand the two types of authentication your SQL instance will allow (Windows login and SQL login). 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: you need to understand the difference and you will also need to know how to explain the difference.
msdb
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 by reviewing the fixed server roles and the database roles. Then, look at some of the various GRANTS allowed on different objects inside a database (you can use SSMS for this, just examine the properties of the object and the effective permissions and keep drilling through). When you are done, go back and do it again. Because I said so.
model
Next 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.
Now go one step further and start scripting out the permissions for database roles and fixed server roles. Examine those scripts and immerse yourself in the information. If you don’t understand any of the commands you are seeing then open up BOL and review what the command means.
master
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.
resourcedb
Configure SQL Profiler to capture login details. What? You don’t know how? No problem, I am sure you can figure it out. 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.
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.
Here are more people to follow for additional security related information:
2 thoughts on “SQL University – Security Week”