Shulou(Shulou.com)05/31 Report--

This article mainly explains "what is the use of asymmetric key encryption in SQLServer2008", interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn "what is the use of asymmetric key encryption in SQLServer2008"?

What is the use of asymmetric key encryption in SQLServer2008

Let's look at a set of examples:

Example 1. Create an asymmetric key

To create an asymmetric key, use the following command:

CREATEASYMMETRICKEY creates an asymmetric key. (http://msdn.microsoft.com/en-us/library/ms174430.aspx)

Viewplaincopytoclipboardprint

Example 2. View the asymmetric keys in the current database

Use the catalog view sys.asymmetric_keys (http://msdn.microsoft.com/en-us/library/ms188399.aspx)) to view.

Viewplaincopytoclipboardprint

Example 3. Modify the private key password of an asymmetric key

You can use ALTERASYMMETRICKEY (http://technet.microsoft.com/en-us/library/ms189440.aspx)) with ENCRYPTIONBYPASSWORD and DECRYPTIONBYPASSWORD options to change the password of the private key.

Viewplaincopytoclipboardprint

-- modify the private key password

Name of the key to be modified by ALTERASYMMETRICKEYasymDemoKey--

WITHPRIVATEKEY-- private key

(ENCRYPTIONBYPASSWORD='newpasswordE4D352F280E0',-- specifies a new password

DECRYPTIONBYPASSWORDING TestASYM123 password')-- the old password is used to decrypt

-- modify the private key password

Name of the key to be modified by ALTERASYMMETRICKEYasymDemoKey--

WITHPRIVATEKEY-- private key

(ENCRYPTIONBYPASSWORD='newpasswordE4D352F280E0',-- specifies a new password

DECRYPTIONBYPASSWORDING TestASYM123 password')-- the old password is used to decrypt

Example 4. Use asymmetric keys to encrypt and decrypt data.

Because both public key and key are required, it is a very secure way to use asymmetric keys to encrypt data when maintaining confidential data. But more resources will be consumed when used for large datasets at the same time.

It is not recommended to use asymmetric keys to encrypt data, but it is still an option. Once an asymmetric key is added to the database, it can be used to encrypt and decrypt data.

The following two sql functions are used:

EncryptByAsymKey encrypts data. (http://technet.microsoft.com/en-us/library/ms186950.aspx)

DecryptByAsymKey decrypts the data. (http://msdn.microsoft.com/en-us/library/ms189507.aspx)

Note that when encrypted through a certificate, DecryptByAsymKey returns encrypted data of type varbinary.

Here is an example:

Viewplaincopytoclipboardprint

UseDB_Encrypt_Demo

Go

-- create data that needs to be encrypted

CreateTableBankUser

(PKIDintprimarykeyidentity (10001pc1)

, UserNovarbinary (1000) null

, CurStatesmallintdefault (0) notnull

)

Go

InsertintoBankUser

(UserNo,CurState)

VALUES (EncryptByAsymKey (AsymKey_ID ('asymDemoKey'),' 137492837583249ABR'), 1)

Insert a record with the field UserNo storing the encrypted number value

Go

SelectPKID,Curstate,cast (UserNoasnvarchar (1000)) asUserNo

FromBankUser

WherePKID=10001

UseDB_Encrypt_Demo

Go

-- create data that needs to be encrypted

CreateTableBankUser

(PKIDintprimarykeyidentity (10001pc1)

, UserNovarbinary (1000) null

, CurStatesmallintdefault (0) notnull

)

Go

InsertintoBankUser

(UserNo,CurState)

VALUES (EncryptByAsymKey (AsymKey_ID ('asymDemoKey'),' 137492837583249ABR'), 1)

Insert a record with the field UserNo storing the encrypted number value

Go

SelectPKID,Curstate,cast (UserNoasnvarchar (1000)) asUserNo

FromBankUser

WherePKID=10001

What is the use of asymmetric key encryption in SQLServer2008

The clear result is:

View unencrypted data:

Viewplaincopytoclipboardprint

SELECTPKID,Curstate

Cast

(DecryptByAsymKey (AsymKey_ID ('asymDemoKey'), UserNo,N'newpasswordE4D352F280E0')

Asvarchar (1000)) asUserNo-- requires the original private key

FromBankUserwherePKID=10001

SELECTPKID,Curstate

Cast

(DecryptByAsymKey (AsymKey_ID ('asymDemoKey'), UserNo,N'newpasswordE4D352F280E0')

Asvarchar (1000)) asUserNo-- requires the original private key

FromBankUserwherePKID=10001

Example 5. Delete unless a symmetric key

Command: DROPASYMMETRICKEY deletes the specified asymmetric key (http://msdn.microsoft.com/en-us/library/ms188389.aspx)

Example:

Viewplaincopytoclipboardprint

DROPASYMMETRICKEYasymDemoKey

DROPASYMMETRICKEYasymDemoKey

At this point, I believe you have a deeper understanding of "what is the use of asymmetric key encryption in SQLServer2008?" you might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.