Certificates and asymmetric keys encrypt data using the internal public key at the database level, and data is decrypted using a database-level internal private key. Symmetric keys are relatively simple, and they contain a key for both encryption and decryption. In this case, encrypting the data with a symmetric key is faster and more appropriate for large data. Although complexity is a factor to consider using it, it is still a good choice for encrypting data.
Let's look at a set of examples:
Example one, creating a symmetric key
The characteristic of a symmetric key is that it must be opened first before it is used in a database session to encrypt and decrypt data.
Create a symmetric key using the following command: Create symmetric key creates a symmetric key. (http://msdn.microsoft.com/en-us/library/ms188357.aspx)
Copy Code code as follows:
Use Db_encrypt_demo
Go
--Create an asymmetric key to encrypt the symmetric key
CREATE Asymmetric KEY Symdemokey--Name
with algorithm = rsa_512--Encryption algorithm
Encryption by PASSWORD = ' testsym456! ' --Password
--Create a symmetric key
CREATE symmetric KEY Sym_demo
with algorithm = Triple_des
Encryption by asymmetric KEY Symdemokey
Example two, view the symmetric key in the current database
Use catalog view Sys.symmetric_keys (http://msdn.microsoft.com/en-us/library/ms189446.aspx) to view.
Copy Code code as follows:
--View the asymmetric key in the current database
Use Db_encrypt_demo
Go
SELECT name, Algorithm_desc from Sys.symmetric_keys
return----Results
/*
Name Algorithm_desc
Sym_demo Triple_des
*/
Example three, modifying the encryption method of an asymmetric key
You can use the ALTER symmetric key (http://technet.microsoft.com/en-us/library/ms189440.aspx) command to modify the way the symmetric key is encrypted. However, you must use the open symmetric KEY (http://msdn.microsoft.com/en-us/library/ms190499.aspx) command to open it before execution.
Copy Code code as follows:
Use Db_encrypt_demo
Go
--First open the symmetric key with the private key password
OPEN symmetric KEY Sym_demo
Decryption by asymmetric KEY Symdemokey
with PASSWORD = ' testsym456! '
--After opening, add password encryption first, replace the original key
ALTER symmetric KEY Sym_demo
ADD encryption by PASSWORD = ' newnew!456 '
--Delete asymmetric key encryption again
ALTER symmetric KEY Sym_demo
DROP encryption by asymmetric KEY Symdemokey
--Close the symmetric key when the operation is complete
Close symmetric KEY Sym_demo
example Four, the data is encrypted and decrypted using a symmetric key.
1. In order to encrypt data using a symmetric key, you must first open it and then use the function EncryptByKey to encrypt the data. (http://msdn.microsoft.com/zh-cn/library/ms174361.aspx)
2. Use DecryptByKey to decrypt data encrypted using symmetric keys. Note that DecryptByKey does not need to use symmetric key GUIDs, unlike EncryptByKey. Therefore, in order to decrypt, the correct symmetric key session must be opened or null will be displayed.
Here is an example:
Copy Code code as follows:
/****************************3w@live.cn***********************/
Use Db_encrypt_demo
Go
--Create a test data table for symmetric encryption
CREATE TABLE dbo. Pwdquestion
(CustomerID int not NULL PRIMARY KEY,
Passwordhintquestion nvarchar () not NULL,
Passwordhintanswer varbinary () not NULL)
Go
--Inserting encrypted data
OPEN symmetric KEY Sym_demo
Decryption by PASSWORD = ' newnew!456 '
INSERT dbo. Pwdquestion
(CustomerID, Passwordhintquestion, Passwordhintanswer)
VALUES
(12, ' The name of the hospital you were born in? ',
EncryptByKey (Key_GUID (' Sym_demo '), ' Hangzhou City One '))
Close symmetric KEY Sym_demo
To view unencrypted data:
Copy Code code as follows:
--Decrypting data
OPEN symmetric KEY Sym_demo
Decryption by PASSWORD = ' newnew!456 '
SELECT Customerid,passwordhintquestion,
CAST (DecryptByKey (passwordhintanswer) as varchar) passwordhintanswer
FROM dbo. Pwdquestion
WHERE CustomerID = 12
--Remember to close!!! after opening 3w@live.cn
Close symmetric KEY Sym_demo
Copy Code code as follows:
--Do not open direct read
SELECT Customerid,passwordhintquestion,
CAST (DecryptByKey (passwordhintanswer) as varchar) passwordhintanswer
FROM dbo. Pwdquestion
WHERE CustomerID = 12
At this point, seems to have done, don't, do not be happy too early!
Here's the problem, if a malicious user does not know the true value of the Passwordhintanswer column of customerid=13, but knows the true value of the Passwordhintanswer column of customerid=14, it is entirely possible to replace it with malicious passwordhintanswer columns and bypassing encryption!! 3w@live.cn at this point, we simply even CustomerID column as a validation column also encrypted, Ehin!
Note: encrypted validation columns can also be passed in as parameters by another related table's columns.
Look at a complete example:
Copy Code code as follows:
TRUNCATE TABLE dbo. Pwdquestion
Go
--Add two unencrypted rows
INSERT dbo. Pwdquestion
(CustomerID, Passwordhintquestion, Passwordhintanswer)
Select 13, ' The name of the hospital you were born in? ', cast (' Zhejiang Women's Hospital ' as varbinary)
UNION ALL
Select 14, ' The name of the hospital you were born in? ', cast (' Zhejiang Women's second house ' as varbinary)
--Open the symmetric key and encrypt it together with the CustomerID column
OPEN symmetric KEY Sym_demo
Decryption by PASSWORD = ' newnew!456 '
UPDATE dbo. Pwdquestion
SET Passwordhintanswer =
EncryptByKey (Key_GUID (' Sym_demo '),
passwordhintanswer,1,--1 means using validator values
CAST (CustomerID as varbinary))
WHERE CustomerID in (13,14)
--Remember to close!!! after opening 3w@live.cn
Close symmetric KEY Sym_demo
--You must look at the original data this way
OPEN symmetric KEY Sym_demo
Decryption by PASSWORD = ' newnew!456 '
SELECT Customerid,passwordhintquestion,
CAST (DecryptByKey (passwordhintanswer, 1,--1 indicates the use of validator values
CAST (CustomerID as varbinary)) varchar passwordhintanswer
FROM dbo. Pwdquestion
WHERE CustomerID = 13
--Remember to close!!! after opening 3w@live.cn
Close symmetric KEY Sym_demo
Malicious substitution start:
Copy Code code as follows:
/**********************************************************
--We use the Passwordhintanswer column value of CustomerID = 13 just now
--Replaces the Passwordhintanswer column value of CustomerID = 14,
--then read the real value using the method just read 14
**********************************************************/
UPDATE dbo. Pwdquestion Set passwordhintanswer=
(select Passwordhintanswer from dbo.) pwdquestion where CustomerID = 14)
where CustomerID = 13
At this point, we'll look at:
Copy Code code as follows:
OPEN symmetric KEY Sym_demo
Decryption by PASSWORD = ' newnew!456 '
SELECT Customerid,passwordhintquestion,
CAST (DecryptByKey (passwordhintanswer, 1,--1 indicates the use of validator values
CAST (CustomerID as varbinary)) Passwordhintanswer,
Passwordhintanswer as Binaryvalue
FROM dbo. Pwdquestion
WHERE CustomerID in (13,14)
--Remember to close!!! after opening 3w@live.cn
Close symmetric KEY Sym_demo
Rongle a lang! It's cool! Although the same binary data was replicated, the results of the reading made the attackers disappointed!
Example five, deleting a symmetric key
Command: Drop symmetric key Deletes the specified symmetric key (http://technet.microsoft.com/en-us/library/ms182698.aspx)
Example:
DROP symmetric KEY Symdemokey
Note: Drop fails if the encryption key is turned on without closing.
Summary:
1. This article mainly introduces the creation, deletion, view and use of symmetric key to modify the encryption method, data encryption and decryption.
2. The characteristic of a symmetric key is that it must be opened first before it is used in a database session to encrypt and decrypt data.
3, the symmetric key can be used for large data encryption.
The following is a major introduction to Certificate encryption (certificate encryption)
