Sql server Technical Article

Database Security

Security at the server level is probably the greatest concern for system administrators, but the database is where all the action is in a production environment. For the most part, a database administrator can let the database developer worry about the details in the database, as long as the developer works within the constraints of the environment. SQL Server 2005 provides plenty of new features for securing the database.

Data encryption

SQL Server 2000 and earlier versions did not have built-in support for encrypting the data stored in a database. Why would you need to encrypt data that is stored in a well-secured database on a secure server nestled safely behind state-of-the-art firewalls? Because of an important, age-old security principal called defense in depth. Defense in depth means layering defenses so that even if attackers successfully pierce your outermost defenses they still have to get through layer after layer of defense to get to the prize. In a database, it means that if an attacker gets through the firewall and through Windows security on the server to the database, she still has to do some nasty brute force hacking to decrypt your data. And in these days of legislated data and privacy protection, data needs to have strong protection.

SQL Server 2005 has rich support for various types of data encryption using symmetric and asymmetric keys, and digital certificates. Best of all, it takes care of managing the keys for you, since key management is by far the hardest part of encryption. Keeping secrets secret is never easy.

Data encryption is largely an application development topic, so this paper does not go into detail in this area. But as an administrator, you'll probably need to manage at least the upper level of keys in the hierarchy shown in Figure 10. Database administrators need to understand the service master key at the server level and the database master key at the database level. Each key lower in the hierarchy is protected by its immediate parent, which is in turn protected by its parent, on up the tree. The one exception is where a password is used to protect a symmetric key or certificate, which is how SQL Server lets users manage their own keys and take responsibility for keeping the key secret.

Figure 10   Encryption key hierarchy in SQL Server 2005

Note   Microsoft recommends against using certificates or asymmetric keys for encrypting data directly. Asymmetric key encryption is orders of times slower and the amount of data that can be protected by this mechanism is limited, depending on the key modulus. Certificates and asymmetric keys can be protected by a password instead of by the database master key.

The service master key is the one key that rules them all—all the keys and certificates in SQL Server. It is a symmetric key that is created automatically when you install SQL Server. It is obviously a critical secret because if it is compromised an attacker can eventually decipher every key in the server that is managed by SQL Server. It is protected by the Data Protection API (DPAPI) in Windows.

SQL Server manages the service master key for you, although you can perform maintenance tasks on it to dump it to a file, regenerate it, and restore it from a file. But most of the time you won't need or want to make any of these changes to the key. It is strongly recommended that administrators back up their service master keys in case of key corruption.

Within the scope of a database, the database master key is the root encryption object for all keys, certificates, and data in the database. Each database can have a single master key; you'll get an error if you try to create a second key. You must create a database master key before using it by using the CREATE MASTER KEY Transact-SQL statement with a user-supplied password:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EOhnDGS6!7JKv'

The key is encrypted with a triple DES key derived from the password as well as the service master key. The first copy is stored in the database while the second is stored in the master database. Having the database master key protected by the service master key makes it possible for SQL Server to automatically decrypt the database master key when required. The end application or user does not need to open the master key explicitly using the password and is a major benefit of having the keys protected in the hierarchy.

Detaching a database with an existing master key and moving it to another server can be an issue. The problem is that the new server's service master key is different from that of the old server. As a result, the server cannot automatically decrypt the database master key. This can be circumvented by opening the database master key with the password with which it is encrypted and using the ALTER MASTER KEY statement to encrypt it by the new service master key. Otherwise, the database master key will always have to be explicitly opened before use.

Once the database master key exists, developers can use it to create any of three types of keys, depending on the type of encryption required:

• 
  Asymmetric keys, used for public key cryptography with a public and private key pair
  
• 
  Symmetric keys, used for shared secrets where the same key both encrypts and decrypts data
  
• 
  Certificates, essentially wrappers for a public key
  

With all the encryption options and its deep integration into the server and database, encryption is now a viable way to add a final layer of defense to your data. But use the tool judiciously because encryption adds a lot of processing overhead to your server.

Code module signing

One of the nice benefits of having encryption within SQL Server is that it provides the ability to digitally sign code modules (stored procedures, functions, triggers, and event notifications) with certificates. This provides much more granular control over access to database tables and other objects. Like encrypting data, you sign the code with the private key contained within the certificate. The result is that the tables used in the signed code module are accessible only through the code and not allowed outside of the code module. In other words, access to the tables are only provisioned to the certificates that have been used to sign the module.

The effect can be the same with a stored procedure. For example, if it has an unbroken ownership chain, you carefully control which users get EXECUTE permission on the procedure, and you deny direct access to the underlying tables. But this doesn’t help in situations such as when the procedure has a broken ownership chain or executes dynamic SQL, requiring that the user executing the procedure have permissions to the underlying tables. Another way to achieve the same effect is to use EXECUTE AS, but this changes the security context under which the procedure executes. This may not be desirable, for example, if you need to record in the table the user who actually caused the procedure to run (short of requiring a user name as a parameter to the procedure).

Signing code modules has the additional benefit of protecting against unauthorized changes to the code module. Like other documents that are digitally signed, the certificate is invalidated when the code changes. The code doesn’t execute under the context of the certificate, so any objects that have their access provisioned to the certificate will not be accessible.

To do this, you create a certificate, associate it with a new user, and sign the procedure with the certificate. Grant this user whatever permissions are necessary to execute the stored procedure. In essence, this user is added to the security context of the stored procedure as a secondary identity. Then grant execute permissions to whatever users or roles need to execute the procedure. The following code shows these steps. Assume that you want to sign the mySchema.GetSecretStuff procedure, and that all of the referenced objects already exist in the database:

CREATE CERTIFICATE certCodeSigning

ENCRYPTION BY PASSWORD = 'cJI%V4!axnJXfLC'

WITH SUBJECT = 'Code signing certificate'

GO

-- Sign the stored procedure

ADD SIGNATURE TO mySchema.GetSecretStuff BY CERTIFICATE certCodeSigning

WITH PASSWORD = 'cJI%V4!axnJXfLC'

GO

-- Map a user to the certificate

CREATE USER certUser FOR CERTIFICATE certCodeSigning

GO

--Assign SELECT permissions to new certUser

GRANT SELECT ON SocialSecurity TO certUser

GO

-- Grant execute permission to the user who will run the code

GRANT EXECUTE ON mySchema.GetSecretStuff TO ProcedureUser

GO

Now only users explicitly granted EXECUTE permission on the stored procedure are able to access the table’s data.

DDL triggers

DDL triggers are an interesting addition to database security. Unlike DML triggers that execute Transact-SQL code when data in a table changes, a DDL trigger fires when the structure of the table changes. This is a great way to track and audit structural changes to a database schema.

The syntax for these triggers is similar to that of DML triggers. DDL triggers are AFTER triggers that fire in response to DDL language events; they do not fire in response to system-stored procedures that perform DDL-like operations. They are fully transactional, and so you can ROLLBACK a DDL change. You can run either Transact-SQL or CLR code in a DDL trigger. DDL triggers also support the EXECUTE AS clause similar to other modules.

The information about the trigger event is materialized as untyped XML. It is available though a new, XML-emitting built-in function called EVENTDATA(). You can use XQuery expressions to parse the EVENTDATA() XML in order to discover event attributes like schema name, target object name, user name, as well as the entire Transact-SQL DDL statement that caused the trigger to fire in the first place. You will find examples in SQL Server Books Online (http://msdn2.microsoft.com/en-us/library/ms130214.aspx).

Database-level DDL triggers fire on DDL language events at the database level and below. Examples are CREATE_TABLE, ALTER_USER, and so on. Server-level DDL triggers fire on DDL language events at the server level, for example CREATE_DATABASE, ALTER_LOGIN, etc. As an administrative convenience, you can use event groups like DDL_TABLE_EVENTS as a shorthand to refer to all CREATE_TABLE, ALTER_TABLE, and DROP_TABLE events. The various DDL event groups and event types, and their associated XML EVENTDATA(), are documented in SQL Server Books Online.

Unlike DML trigger names, which are schema-scoped, DDL trigger names are database scoped or server-scoped.

Use this new catalog view to discover trigger metadata for DML triggers and database-level DDL triggers:

SELECT * FROM sys.triggers ;

GO

If the parent_class_desc column has a value of 'DATABASE' then it is a DDL trigger and the name is scoped by the database itself. The body of a Transact-SQL trigger is found in the sys.sql_modules catalog view, and you can JOIN it to sys.triggers on the object_id column. The metadata about a CLR trigger is found in the sys.assembly_modules catalog view, and again, you can JOIN to sys.triggers on the object_id column.

Use this catalog view to discover metadata for server-scoped DDL triggers:

SELECT * FROM sys.server_triggers ;

GO

The body of a Transact-SQL server-level trigger is found in the sys.server_sql_modules catalog view, and you can JOIN it to sys.server_triggers on the object_id column. The metadata about a CLR server-level trigger is found in the sys.server_assembly_modules catalog view, and again, you can JOIN to sys.server_triggers on the object_id column.

You can use DDL triggers to capture and audit DDL activity in a database. Create an audit table with an untyped XML column. Create an EXECUTE AS SELF DDL trigger for the DDL events or event groups you are interested in. The body of the DDL trigger can simply INSERT the EVENTDATA() XML into the audit table.

Another interesting use of DDL triggers is to fire on the CREATE_USER event and then add code to automate permissions management. For example, suppose you want all database users to get a GRANT EXECUTE on procedures P1, P2, and P3. The DDL trigger can extract the user name from the EVENTDATA() XML, dynamically formulate a statement like 'GRANT EXECUTE ON P1 TO someuser', and then EXEC() it.

Conclusion

SQL Server 2005 provides rich security features to protect data and network resources. It is much easier to install securely, since all but the most essential features are either not installed by default or disabled if they are installed. SQL Server provides plenty of tools to configure the server, particularly the SQL Server Surface Area Configuration Tool. Its authentication features are stronger because they is more closely integrated with Windows authentication and protect against weak or ancient passwords. Granting and controlling what a user can do when authenticated is far more flexible with granular permissions, SQL Agent proxies, and execution context. Even metadata is more secure, since the system metadata views return information only about objects that the user has permission to use in some way. At the database level, encryption provides a final layer of defense while the separation of users and schemas makes managing users easier.

For more information:

http://www.microsoft.com/technet/prodtechnol/sql/2005/library/security.mspx

Did this paper help you? Please give us your feedback. On a scale of 1 (poor) to 5 (excellent), how would you rate this paper?!href(mailto: sqlfback@microsoft.com?subject=Feedback: SQL Server 2005 Security: Strong Security That’s Easy to Configure and Manage)