Database security used to be a simple matter of access control. Break your users into task groups, then assign each group the permissions its members require. How times have changed! No SQL Server professional today would ever think of releasing a database without first checking it for security vulnerabilities such as weak passwords or porous firewalls. But how do you know whether you've verified every essential security setting? Forget to lock down your installation folders or disable your server's Guest account, and just like that, you're toast!
The answer is to develop an organized security-testing plan that checks various configuration settings that your company requires and generates reports that log the test results. I'll explain the elements of a security-testing scheme, then provide T-SQL code examples that you can use to automate various parts of your configuration-testing and test-reporting process. Let's get started.
Automating Testing
Configuration checks are the nuts and bolts of database-security testing and are an essential part of protecting your databases against outside intruders or tampering from within your organization. A configuration check essentially involves verifying that a particular security option—such as enabling a firewall or a specific port configuration or deleting nonessential network libraries—has been correctly set. Configuration checking also includes backup and file-protection policies, authentication modes, Guest accounts, and Public role permissions. Most DBAs do security checks manually, but by scripting the steps involved, you can easily automate many routine security-configuration checks.
For example, to find out whether your database server is running the most up-to-date service pack, you could run the T-SQL code that Listing 1 shows (insert your own version number). (Some code in the listings wraps to multiple lines because of space constraints.) Or, to determine whether an unnecessary service has been disabled,you could run the code in Listing 2, which checks whether the distributed transaction coordinator (DTC) service (i.e., MSDTC) is running.
Although scripts that automate security tests are seldom more complicated than the previous code examples, the examples aren't fully automated tests. A fully automated security test includes these elements:
- a clearly defined test plan for each security check, which includes an expected outcome (e.g., any SQL Server version equal to or greater than 8.00.2039), so that you can determine whether a configuration option is correctly set, and preconditions that the test requires (e.g., the server is running, the database supports all the settings being tested, the test has the necessary permissions on the test script and test targets)
- test reports documenting the results of each check
- a separate bug report that's produced when a configuration setting violates a security policy
Don't waste time testing a configuration setting if your organization has no security requirement or policy regarding that configuration option. Furthermore, if you believe that a particular security setting must be tested, although it's undocumented, you should first log a bug against your company's security plan or business rules (or both) ASAP. If the setting is an essential security checkpoint, you should inform your business analyst or security planner of this so that he or she can revise your company's security policy accordingly.
Logging Test Reports
As part of your configuration-testing procedure, you should log a test report whenever you check a security setting. At a minimum, every test report should include a unique ID, the date the security check occurred, the tester's name, the test's expected and actual outcomes, and the test result (pass or fail). (I'll discuss how to determine an expected outcome in the next section.)
To generate a test report, you can create a table that stores the results of a configuration test, such as the T-SQL code in Listing 3. The two test reports that Table 1 shows contain sample test results similar to the results you'd see after running the previous configuration-testing scripts. (Later, I'll provide specific coding techniques for moving the test results into a table as well as automating other parts of the testing process that I discuss here.)
Logging test results saves time, promotes consistency, and reduces test duplication. Developers can use these results to reproduce apparent bugs. Testers can use them to verify any bug fixes that are made. Logging your results also discourages unplanned and undocumented tests. As I'll show you shortly, you can also automate test reporting just as you can automate the security checks.
You should also log a bug report whenever you discover a security violation or vulnerability. The bug report should contain the same information as the test report, along with a bug identifier, the date the report was logged, a description of the security violation or vulnerability, and a status flag (e.g., Testing, Debugging, Awaiting Clarification, Fixed) that indicates where the bug stands in your development workflow. Some companies also require the identifier of the parent requirement or security policy.
To log bug reports, you should first create another table for the bug-report data, by using a statement similar to that in Listing 4. To see what a bug report might look like, let's consider another example: A security check detects a server running under the LocalSystem account (a definite "no-no" in almost any production environment), as the code in Listing 5 does. The bug report for this security check might look like the one that Table 2 shows. Notice how the Current Status column has been set to Debugging to signify that testing has stopped until the problem is fixed.Once the bug has been fixed,the administrator or developer resets the status either to Testing (i.e., a defective setting—Local-System in this case—is found and fixed) or to Closed (i.e., the issue is otherwise resolved).
Prev. page
[1]
2
3
next page 
