Guest Blog Post: Be the Next contestants tackle SQL Server Security – but do they pass the test?
Guest Blog Authors:
Buck Woody, Senior Technical Professional, Windows Azure and Cloud Computing at Microsoft
boB Taylor, Principal Content Publishing Manager MCSM at Microsoft
When we put our heads together to figure out the challenges for the reality show Be the Next Microsoft Employee, we had a few requirements that we used as a filter: 1) real-world, 2) top-of-mind, and 3) a must-know for any member of the USIT team.
It was clear ‘security’ was going to need to be its own challenge, given its increasing importance.
We thought long and hard about what the challenge should include—there’s no shortage of security challenges to tackle. In the end, we had them focus specifically on least privilege, among other things.
The nitty gritty
This week’s challenge asked contestants to use the AdventureWorks 2005 database they installed on their laptops at the start of the show to assign three new (fictional) hires various levels of access. It seemed simple enough, but there was a lot more to it than met the eye. (Read a complete overview of the challenge for the various twists and turns we served up.) Specifically, the contestants were asked to support specific types of activities such as updating a certain record. While some of the contestants were able to apply the correct UPDATE permissions, they failed to allow SELECT permissions – which were required in order to find the specific record that had to be updated. For example, the challenge asked the contestants to allow Dave Salesperson to update the address of a person when provided with an AddressID. To do so requires T-SQL of the following form:
UPDATE Person.Address
SET AddressLine1 = ‘123 Here Street’
WHERE AddressID = 20731;
If the user only had UPDATE permissions, this would fail with SELECT permission issue as seen in this error message:
Msg 15517, Level 16, State 1, Line 103
Cannot execute as the database principal because the principal “DaveSalesperson2” does not exist, this type of principal cannot be impersonated, or you do not have permission.
The contestants were also judged on several ‘extra credit’ activities such as setting a default database and default schema for their logins and users. The ultimate icing on the cake, however, would have been if one of them had used flexible database roles, allowing permissions to be granted to groups of users instead of on an individual basis.
Real-world results?
This week’s challenge caught more than one of our contestants off-guard, as evident in the presentation of their solutions to the judges’ panel. In the end, one of them emerged as the clear winner, but no one was able to completely solve the problem in the 100 minutes allowed. (In fact, when we ran the script to test their solutions on their laptops, every contestant failed to pass it!)
Did we meet the real-world filter with this challenge? You bet. This week’s challenge highlights how important it is for database administrators to unearth the real challenge underlying a “presenting problem,” to use existing documentation and support rather than re-inventing the wheel, and to go after the big things first. Have you had to employ those same tactics in your work as a DBA?
Watch Episode 2 to see what steps the contestants missed and why we chose our winner even though he/she failed to pass the script.
Wanna play?
Have your own idea for a great security challenge to test the real-world skills of DBAs? Submit it to our Play @ Home challenge this week. If your proposed security challenge is selected, you’ll win a free eBook from Microsoft Press and have a shot at the grand prize – an HP Folio 13 Ultrabook.
Next week, we’ll see if we can activate the top-of-mind and must-know filters a bit more!
