Choose a Test Benchmark
To determine a test's expected outcome, you need a benchmark (also called an oracle). A security configuration test can include various benchmarks—for example, business rules or requirements such as "SQL Server must be installed on a standalone server," design rules such as "Every input procedure must contain a data-validation routine,"vendor specifications such as Microsoft's Security Best Practices (SBP) Checklist, and industry best practices such as the US Department of Defense's C2 security standards.
Here I use Microsoft's SBP Checklist as a benchmark for our security checks. If your organization doesn't yet have its own database-security plan or security best practices, I recommend you use the SBC Checklist as a guide for developing them. You can always add new or revise existing security policies as your needs change. (You can find the checklist in SQL Server 2000 SP3 Security Features and Best Practices at http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sp3sec00.mspx.) Table 3 contains an abbreviated list of SBPs in the checklist.
More Automation Techniques
I've shown you sample code that automates discrete areas of security testing: performing a configuration test and creating tables to contain test and bug reports.You can write code to automate other test activities, such as the reporting and bug-logging steps of the process.
How might you automate test-report creation? Earlier, you saw sample code that creates a report table, tabConfigSettings. Now you need code that moves the test results into tabConfigSettings.To do so, you can use a simple INSERT statement like this, which places the results of the service-pack test done earlier into tabConfigSettings:
INSERT tabConfigSettings
(ConfigItem, ExpectedSetting)
VALUES
('Version#', '8.00.2039')
(The Version# value you use depends on your particular configuration.)
Even better, instead of creating separate INSERT statements to move each set of test results into the table, you can use a stored procedure, such as the one that Listing 6 shows, to automate this task for all the test results.
Next, you'll need a way to automatically log test results. To do so, you can use an UPDATE statement, similar to the one that Web Listing 1 (http://www.sqlmag.com, InstantDoc ID 50027) shows. Since every security setting repeats the same basic logic as this UPDATE statement, you can simply wrap the statement inside another stored procedure, as the upUpdateTestCase procedure in Web Listing 2 shows. upUpdateTestCase takes three input values: a configuration item, a test result, and the item's actual setting.
You can automate bug reports by using coding techniques similar to those I just explained.The basic method for doing so is to use an INSERT statement like that in Web Listing 3, which assigns each bug report a unique TestID and specifies the configuration setting being tested and other related information. Alternatively, you could write a separate procedure that inserts a report after a test is run, depending, of course, on the test result—since bugs are logged only when a configuration setting fails the test. But what if someone wants to log a bug report manually or by using a different testing tool? To accommodate such cases, you can simply create a trigger on the tabConfigSettings table and let it automatically log bug reports. As the code in Web Listing 4 shows, trigger trgLogBug is fired whenever the tabConfigSettings table is updated. trgLogBug first checks whether the testresult column (which is found in the inserted temp table that the trigger automatically creates) is set to Fail. If it isn't (i.e., the test "passed"), the trigger aborts and no bug report is logged. If testresult is set to Fail, trgLogBug inserts a new bug report in the tabBugReports table, taking its values from the updated tabConfigSettings table.
Putting It All Together
You now have the basic pieces for an automated security test-and-report system. There's only one problem, though. Although the individual steps are automated, the process itself still requires manual intervention.You can fix that by writing code that fires each step automatically. To do so, first fold each step into another stored procedure, as Listing 7 shows. Procedure upVerifySecurityConfiguration inserts test cases into tabConfigSettings, then verifies that each security setting is correct. Notice that upVerifySecurityConfiguration contains our previous two subprocedures, upInsertTestCase and upUpdateTestCase.
To extend upVerifySecurityConfiguration, you simply include additional test cases with their corresponding automation scripts. Web Table 1 contains additional automated security verification scripts you can use. As with our initial three test cases, each of these scripts targets a security setting from Microsoft's SBP Checklist.Web Table 1 also shows each test's goal and the expected setting for SBP-compliant servers and databases. Simply include the scripts you need in your automated security-checking process.
Your final step is to write code that automates the test-reporting and bug-logging processes. The tabConfigSettings table and the trgLogBug trigger provide the necessary reporting infrastructure. To keep everyone up to date on your latest test results and security bugs, you can simply publish those tables by using SQL Server's sp_makewebtask Web reporting tool, as the code in Web Listing 5 shows.
Web Table 2 shows what the tabConfigSettings table might look like after you've run all 15 configuration tests against SQL Server's Northwind database (using the defaults on a standard SQL Server 2000 install on a Toshiba laptop runningWindows XP). As you can see, nine of the 15 tests uncovered security settings that violate Microsoft's own SBPs.
Prev. page
1
[2]
3
next page 
