ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 学习简记:SQL Server Encryption and Symmetric Key Encryption

学习简记:SQL Server Encryption and Symmetric Key Encryption

原创 Linux操作系统 作者:kirinri 时间:2009-01-12 12:47:00 0 删除 编辑

Introduction to SQL Server Encryption and Symmetric Key Encryption Tutorial

Introduction

SQL SERVER 2005 provides encryption as a new feature to protect data against the attacks of hackers. Hackers may be able to get hold of the database or tables, but they wouldn't understand the data or be able to use it. It is very important to encrypt crucial security related data when stored in the database, as well while transmitting across a network between the client and the server.

There are three levels of encryption hierarchy. These levels provide different mechanisms for securing data across networks and local servers. Different level of hierarchies allows multiple instances of services (e.g. SQL Server Services) to run on one physical server.

  • Windows Level - Highest Level - Uses Windows DP API for encryption
  • SQL Server Level - Moderate Level - Uses Services Master Key for encryption
  • Database Level - Lower Level - Uses Database Master Key for encryption

There are two different kind of keys used in encryption.

  • Symmetric Key - Symmetric cryptography system in which the sender and receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement and the sender and receiver either can encrypt or decrypt the messages.
  • Asymmetric Key - Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys - a public key and a private key - to encrypt and decrypt the message. This is relatively complex system and the sender can use its key to encrypt the message, but he can't decrypt it. The receiver can use its key to decrypt the message but he can't encrypt it. Due to its complexity, this is a resource intensive process.

Certificates are used as well for encrypting data. A public key certificate is a digitally-signed statement that binds the value of a public key to the identity of the person, device, or service that holds the corresponding private key. A Certification Authority (CA) issues and signs certifications.

Please create a sample database that we will use for testing Encryption. There are two different kinds of encryption available in SQL Server:

  • Database Level - This will secure all the data in database. However, every time data is written or read from database, the whole database has to be decrypted. This is a very resource intensive process and not a practical solution.
  • Column (or Row) Level - This level of encryption is the preferred method of encryption. Only columns containing important data should be encrypted; this will result in less CPU load than the whole database level encryption. If a column is used as primary key, or used in comparison clauses (WHERE clauses, JOIN conditions) the database will have to decrypt the whole column to do operations involving those columns.

Let's go over a simple example that demonstrates the encryption and decryption process done with Symmetric Key and Triple DES encryption algorithm.

 

 

1. Create DB and Table and TableData

 

CREATE TABLE TestTable (FirstCol INT, SecondCol VARCHAR(50))   

GO  

INSERT INTO TestTable (FirstCol, SecondCol)   

SELECT 1,'First' 

UNION ALL 

SELECT 2,'Second' 

UNION ALL 

SELECT 3,'Third' 

UNION ALL 

SELECT 4,'Fourth' 

UNION ALL 

SELECT 5,'Fifth' 

GO  

 

SELECT *  

FROM TestTable  

GO 

 

 

 

 

2. Create Master key & Certificate & Symmetric & add Column

 

 

Every database can have one master key. The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys present in the database. It uses Triple DES algorithm along with user provided password to encrypt the keys.

Certificates are used to protect encryption keys, which are used to encrypt data in the database. SQL Server 2005 has the ability to generate self-signed X.509 certificates.

The symmetric key can be encrypted by using any of the certificate, password, and symmetric key, asymmetric key options. We can use many different algorithms for encrypting key. Supported algorithms are DES, TRIPLE_DES, RC2, RC4, RC4_128, DESX, AES_128, AES_192, and AES_256.

-- Create Database Master Key     

CREATE MASTER KEY ENCRYPTION BY 

PASSWORD = 'SQLAuthority' 

GO 

-- Create Encryption Certificate

CREATE CERTIFICATE EncryptTestCert  

    WITH SUBJECT = 'SQLAuthority' 

GO 

-- Create Symmetric Key  

CREATE SYMMETRIC KEY TestTableKey  

    WITH ALGORITHM = TRIPLE_DES  

    ENCRYPTION BY CERTIFICATE EncryptTestCert  

GO 

-- Encrypt Data using Key and Certificate  

-- Add Columns which will hold the encrypted data in binary  

ALTER TABLE dbo.TestTable  

ADD EncryptSecondCol VARBINARY(256)   

GO 

 

SELECT *  

FROM TestTable  

GO 

 

3. Update encrypted data created by certificate and key

 

Before using the key, it needs to be decrypted by the same method with which it was encrypted. In our example we had used a certificate for encrypting the key. Due to the same reason, we are using the same certificate for opening the key and make it available for use. After it is open and available to use, we can use the encryptkey function and store the encrypted values in the database, in the EncryptSecondCol column.

 

-- Update binary column with encrypted data created by certificate and key  

OPEN SYMMETRIC KEY TestTableKey  

DECRYPTION BY CERTIFICATE EncryptTestCert  

UPDATE TestTable  

SET EncryptSecondCol = ENCRYPTBYKEY(KEY_GUID('TestTableKey'),SecondCol)   

GO

-- Check the content of the TestTable  

 

SELECT *  

FROM TestTable  

GO 

We can run a SELECT query on our database and verify that our data in the table is protected, and hackers will have no understanding of it if they manage to reach the data.

 

4. Decrypt data of the SecondCol

Authorized user can use the decryptbykey function to retrieve the original data from the encrypted column. If Symmetric key is not open for decryption, it has to be decrypted using same certificate which was used to encrypt it. One thing to keep in mind here is that the original column and the decrypted column should have the same data types. If they are of different data types, incorrect values could be reproduced. In our case, we have used a VARCHAR data type for SecondCol and EncryptSecondCol.

 

 

-- Decrypt the data of the SecondCol 

OPEN SYMMETRIC KEY TestTableKey   

DECRYPTION BY CERTIFICATE EncryptTestCert  

SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(EncryptSecondCol)) AS DecryptSecondCol, *  

FROM TestTable  

GO

 

5. Clean up ALL

 

If you drop the database after all the processing is complete, you do not have to worry about cleaning up the database. However, in real world on production servers, the database is not dropped. It is a good practice for developers to close the key after using it. If keys and certificates are used only once or their usage is over, they can be dropped as well. Dropping a database will drop everything it contains - table, keys, certificates, all the data etc.

 

-- Clean up database  

CLOSE SYMMETRIC KEY TestTableKey  

GO  

DROP SYMMETRIC KEY TestTableKey  

GO  

DROP CERTIFICATE EncryptTestCert  

GO  

DROP MASTER KEY 

GO  

 

-- DROP DATABASE SQL_ENCRYPTION   

GO 

 

Summary

Encryption is a very important security feature of SQL Server 2005. Long keys as well as asymmetric keys create strong encryption and stronger encryption uses lots of CPU to encrypt data. Stronger encryption is slower to process. When there is lots of data to encrypt, it is suggested to encrypt it using a symmetric key. The same symmetric key can be encrypted further with an asymmetric key for further protection and adds the advantage of a stronger encryption. It is also advisable to compress data before encryption, as encrypted data can't be compressed.

 

参照:http://dotnetslackers.com/articles/sql/IntroductionToSQLServerEncryptionAndSymmetricKeyEncryptionTutorial.aspx

image002.jpg

image004.jpg

image006.jpg

image008.jpg

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12476590/viewspace-536413/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 09年刚刚开始
请登录后发表评论 登录
全部评论

注册时间:2007-12-21

  • 博文量
    257
  • 访问量
    502584