Protect Your SQL Objects from Unwanted Changes

An increasing number of organizations are adopting relational database management systems (RDBMSs), which is leading to more stringent security procedures and role definitions. For example, some organizations are using access control models based on schema, principals, and securables. Microsoft added several msdb roles that facilitate more security during SQL Server Integration Services (SSIS) package deployment and execution, such as db_ssisadmin, db_ssisltduser, and db_ssisoperator.

As RDBMSs become more complex, a deployment might include several changes to SQL objects, so the entire operation might need to be atomic—that is, either all the changes are deployed or no changes are deployed. To manage such a deployment, you can use a deployment kit, which is a collection of scripts. It might even be necessary to include a rollback script in the kit to regress changes if a problem occurs.

There are several factors that can contribute to an incorrect deployment of SQL object changes. Some organizations have taken measures to prevent incorrect deployments. Using multiple profiles is one such measure. However, these measures don’t address the source of the problem, thereby limiting their effectiveness. For example, here’s what I’ve observed while working in the field:

• If an organization’s servers are separated into different groups (e.g., development, test, preproduction, and production), someone will, at the very least, need to change the server’s name in the kit’s scripts so that the deployment is made on the correct server, leaving room for human error. If they include the name of the wrong server, it’s unlikely that an automated process will detect the error and the SQL objects will be changed on the wrong server.
• If organizations separate the roles of database development and database administration, the DBAs might not have detailed knowledge of the SQL objects being changed. Thus, the DBAs might not be able to determine the validity of the changes.
• Some organizations combine the roles of database development and database administration. In such organizations, if the DBA realizes that there has been an incorrect deployment and he or she fixes it before anyone else realizes it, the incorrect deployment and the impact caused by that incident might remain hidden. Thus, the risks are higher and hidden because knowledge of the incident remains with one person.
• Some organizations follow the practice of having a different user profile or ID for each job function a person fulfills. For example, a person might have one profile for database development activities and another profile for database administration activities. This practice doesn’t prevent incorrect deployment of SQL object changes because the database administration profile might have permissions for more than one server or database.
• Software development life cycles (SDLCs) usually necessitate the use of a version control system. Suppose that a version control system generates a report that lists the objects changed under a release. To generate this report, the version control system will typically compare the latest schema in the version control store against the current schema in the SQL Server database. The report won’t be able to determine which SQL object changes were due to development and which changes were due to a deployment kit. Thus, an incorrect deployment will have less chance of getting corrected.

Using Data Definition Language (DDL) triggers at the database level is a good start for preventing unwanted changes to SQL objects in RDBMSs. One way to implement these triggers is to create a SQL object audit table in each database, then set up a two-step authorization process.

The first step occurs before a deployment. A change control manager authorizes the changes that are appropriate for the planned deployment and inserts information about those changes in the audit table. The change control manager is the only one permitted to enter information into the audit table. In a small organization, the change control manager should be someone other than a DBA or database developer so that human errors are eliminated. For example, it could be a project manager or business analyst.

The second step occurs during the deployment. Database triggers check the audit table to make sure that the attempted SQL object changes are authorized. Authorized changes are deployed; unauthorized changes are rolled back. After the authorized changes are deployed, the audit table is updated with the date and time the changes occurred. Other information can also be captured and stored in the audit table, such as user ID of the person who deployed the code or the object’s comment section.

Besides eliminating human error, this system has several other benefits:

• The audit table can serve as a SQL object change log.
• Unintentional (malicious or otherwise) changes are prevented.
• A gap report can be generated to detect incomplete deployments (e.g., a change was entered by the change control manager but the change was never deployed).
• A reconciliation report can be generated to determine what was released for production and what actually was deployed.

To demonstrate how this system works, I created five scripts that you can run. You should run these scripts in a nonproduction system running SQL Server 2005 or later. Here are the steps:

1.  Run S1_CreateSqlMagSecuredDeplyDb.sql. This script creates a database named sqlMagSecuredDeploy, then creates two tables in it. The deploymentObject table is a repository of SQL objects. The deploymentObjectAudit table is the database’s SQL object audit table. It contains five columns:

• deploymentObjectId, which specifies the ID of the table to be changed.
• versionTag, which can be used to record comments or version numbers.
• approvedFor, which specifies the type of change that’s approved. The possible values are A (add), M (modify), D (delete), and I (invalidated entry—delete isn’t possible).
• approvedDate, which specifies the date and time the change was approved.
• deployedDate, which specifies the date and time the change was deployed. 

2. Run S2_TriggForTableCreate.sql. This script creates a database trigger for a CREATE TABLE event.

3. Run S3_TriggForTableDrop.sql. This script creates a database trigger for a DROP TABLE event.

4. Run S4_SetUpTestData.sql. This script populates the deploymentObject table with SQL objects. It also inserts sample data into the audit table. Specifically, it approves the creation and the deletion of a table named employee.

5. Run S5_TestForSecuredDeployment.sql. This script tests whether the audit table is working correctly. The script begins by creating then removing temporary tables to demonstrate that local and global temporary tables won’t trigger an event because they’re created in the temp database. Next, the script attempts to create two tables (itShouldFail and employee), but only one table will be created (employee) because the creation of the other table (itShouldFail) wasn’t authorized in the audit table. Similarly, the script attempts to delete two tables (test and employee), but only one table (employee) will be deleted. At several points in this script, the audit table is displayed so you can see the changes made to it.