-- SQL database encryption -----

Posted from teched INSTRUCTOR: Niu Ke

Basic concepts:

Service master key at the first layer

Backup Service master key
Backup service master key to file = 'C:/smk. Bak'
Encryption by password = 'P @ ssw0rd'
 
Restore service master key from file = 'C:/smk. Bak'
Decryption by password = 'P @ ssw0rd'
 
Layer 2 database master key
1) You must create a database master key on the database before using it.
Create master key encryption by password = 'P @ ssw0rd'
 
2) use the database master key
-If the database master key is protected by a service key, it is automatically enabled during use.
Opren master key decryption by password = 'P @ ssw0rd'
 
3) view the database master key status
Sys. tricric_keys
 
4) back up the master key of the database
Backup master key to file = 'C:/smk. Bak'
Encryption by password = 'P @ ssw0rd'
 
Restore master key from file = 'C:/SMK. Bak'
Decryption by Password = 'P @ ssw0rd'
 
 
Digital Certificate
Create a self-Signature
Create certificate cert_mycert
Encryption By Password = 'P @ ssw0rd'
With subject = 'self signed cert ',
Start_date = '2014/1/123'
Expiry_date = '2017/123'
 
 
Asymmetric Key
Create a new key pair
Create asypolicric key asy_key1
With algorithm = rsa_2048
Encryption By Password = 'P @ ssw0rd'
 
 
Symmetric Key
Create a new key pair
Create shortric key symkeymarketing3
With algorithm = aes_2048
Encryption by certificate asy_key1
 
Use symmetric keys
Must be enabled before use
Open encryption Ric SymKeyMarketing3
Decryption by certificate asy_Key1
 
Sys. open_keys
 
 
Data column Encryption
-Use symmetric keys to encrypt a large amount of column data
-Consider using certificates to protect symmetric keys with asymmetric keys
 
Prevent attacks that bypass encrypted data columns-use validators
 
Note:
The index on the encrypted column becomes invalid.
The length of the encrypted data column increases. We recommend that you use the varbinary (max) data type.
Modify existing dml statements to support encrypted data Columns
 
----- *********** Example 1 understand the database encryption architecture *****-----
 
-- ************* (1) Service master key
-- Preparations
-- Create TestDB
-- 1) Backup Service master key
Backup service master key to file = 'G:/smk. Bak'
Encryption by password = 'P @ ssw0rd'
 
-- 2) generate a new CMK
Alter service master key regenerate
 
-- 3) restore the Service master key from the backup file
Restore service master key from file = 'G:/smk. Bak'
Encryption by password = 'P @ ssw0rd'
 
-- ************** (2) Master Key of the database
-- 1) Create a database master key for the database
Create master key encryption by password = 'P @ ssw0rd'
Go
-- 2) view the database encryption status
Select [name], is_master_key_encrypted_by_server
From sys. databases where name = 'testdb'
 
-- 3) view the database master key information
Select * from sys. tricric_keys
 
-- 4) back up the master key of the database
Backup master key
To file = 'G:/testdbkey. Bak'
Encryption by password = 'P @ ssw0rd'
 
-- 5) Delete the Service master key to protect the database master key
-- The asymmetric key is successfully created. The Service master key is automatically used to decrypt and use the database master key.
Create asypolicric key asy_Testkey1 with algorithm = RSA_1024
Go
-- Delete the Service master key protection for the database master key
Alter master key
Drop Encryption By service master key
Go
 
-- View the database encryption status
Select [name], is_master_key_encrypted_by_server
From SYS. databases where name = 'testdb'
 
-- An error occurred while creating the asymmetric key because the database master key is not enabled.
Create asypolicric key asy_testkey2 with algorithm = rsa_1024
Go
-- Open the database master key
Open Master Key decryption by Password = 'P @ ssw0rd'
Select * From SYS. openkeys
Go
-- Asymmetric key created successfully
Create asypolicric key asy_testkey2 with algorithm = rsa_1024
Go
 
-- Restore the protection of the Service master key for the database master key
Alter master key
Add Encryption By service master key
Close master key
Go
 
-- ***** (3) certificate
-- 1) create a self-signed certificate
Create certificate cert_Testcert
Encryption by password = 'P @ ssw0rd'
With subject = 'testcert1 ',
Start_date = '2014/1/123 ',
Expiry_date = '2017/123'
Go
Select * from sys. certificates
 
 
-- 2) import a certificate from a file
Create certificate cert_TestCert2
From file = 'G:/MSCert. cer'
Go
 
-- 3) backup and export certificates and keys
Backup certificate cert_Testcert
To file = 'G:/Testcert. cer'
With private key
(Decryption by password = 'P @ ssw0rd ',
File = 'G:/testcert_pvt', -- Private Key
Encryption by password = 'P @ ssw0rd ')
Go
 
-- 4) use certificates to encrypt and decrypt data
-- Encryption: use the public key of the certificate
Declare @ cleartext varbinary (200)
Declare @ cipher varbinary (200)
Set @ cleartext = convert (varbinary (200), 'test text string ')
Set @ cipher = EncryptByCert (Cert_ID ('cert _ testcert'), @ cleartext)
Select @ cipher
 
-- Decryption: Use the private key of the certificate
Select convert (varchar (200), DecryptByCert (Cert_ID ('cert _ testcert'), @ cipher, n'p @ ssw0rd ') as [cleartext]
 
 
-- 5) Delete the certificate Private Key
Alter certificate cert_TestCert
Remove private key
Go
 
-- Encryption successful
Declare @ cleartext varbinary (200)
Declare @ cipher varbinary (200)
Set @ cleartext = convert (varbinary (200), 'test text string ')
Set @ cipher = EncryptByCert (Cert_ID ('cert _ testcert'), @ cleartext)
Select @ cipher
 
-- Decryption failed: the private key was deleted.
Select convert (varchar (200), DecryptByCert (Cert_ID ('cert _ testcert'), @ cipher, n'p @ ssw0rd ') as [cleartext]
 
 
-- *** (4) asymmetric key
-1-use sn.exeto generate asymmetric keys. After vs2005th is installed, sn.exe is installed and executed in command line mode.
Sn-k g:/asy_Test.key
 
-- 2) create an asymmetric key from a file
Create asypolicric key asm_Test
From file = 'G:/asy_Test.key'
Encryption by password = 'P @ ssw0rd'
Go
 
Select * from sys. asypolicric_keys
 
 
 
-- *********** Example 2 use encryption to protect column data
----- ***** (1) Preparations
-- 1) Create an example table
Create table empsalary
(EmpID int,
Title nvarchar (50 ),
Salary varbinary (500)
)
Go
 
-- 2) create a database master key
Create master key encryption by password = 'P @ ssw0rd'
Go
 
-- 3) create a symmetric key for encryption
Create foreign Ric key sym_Salary
With algorithm = AES_192
Encryption by password = 'P @ ssw0rd'
Go
 
Select * from sys. Metrics ric_keys where [name] = 'sym _ Salary'
 
------ ***** (2) encrypt column data
-- 1) enable symmetric keys
Open policric key sym_Salary
Decryption by password = 'P @ ssw0rd'
Go
Select * from sys. openkeys
 
-- 2) insert data into the table and encrypt the salary Column
Insert into empsalary values (1, 'ceo ', EncryptByKey (KEY_GUID ('sym _ Salary'), '123 '))
Insert into empsalary values (2, 'manager', EncryptByKey (KEY_GUID ('sym _ Salary '), '123 '))
Insert into empsalary values (3, 'db admin', EncryptByKey (KEY_GUID ('sym _ Salary '), '123 '))
 
-- 3) disable the opened symmetric key
Close your Ric key sym_Salary
Go
Select * from sys. openkeys
 
 
-- 4) View table data
Select * from empsalary
 
-- (3) decrypt and access encrypted data Columns
-- 1) enable symmetric keys
Open encryption Ric key sym_Salary decryption by password = 'P @ ssw0rd'
Go
-- 2) use symmetric keys to decrypt and access encrypted Columns
Select empid, title, cast (DecryptByKey (Salary) as varchar (20) as salary from empsalary
 
-- 3) Disable symmetric keys
Close your Ric key sym_Salary
Go
 
-- (4) attacks that bypass encrypted data
-- 1) attackers can use encrypted data from other data rows to replace data from one row.
Update empsalary
Set salary = (select salary from empsalary where empid = 1)
Where empid = 3
 
-- 2) view the decrypted data after the attack
Open encryption Ric key sym_Salary decryption by password = 'P @ ssw0rd'
Select empid, title, cast (DecryptByKey (Salary) as varchar (20) as salary from empsalary
Close your Ric key sym_Salary
 
 
-- (5) use the validators to prevent attacks that bypass encrypted data
-- 1) Delete the added data row
Delete empsalary
 
-- 2) insert data to the table and encrypt the data in the salary column using the validators
Open encryption Ric key sym_Salary decryption by password = 'P @ ssw0rd'
Insert into empsalary values (1, 'ceo ', EncryptByKey (KEY_GUID ('sym _ Salary'), '123', 1, '1 '))
Insert into empsalary values (2, 'manager', encryptbykey (key_guid ('sym _ salary '), '123', 1, '2 '))
Insert into empsalary values (3, 'db admin', encryptbykey (key_guid ('sym _ salary '), '123', 1, '3 '))
 
-- 3) decrypt and access encrypted data
Select empid, title, cast (decryptbykey (salary, 1, cast (empid as varchar (3) as varchar (20) as salary from empsalary
 
-- 4) use the same method to tamper with data
Update empsalary
Set salary = (select salary from empsalary where empid = 1)
Where empid = 3
 
-- 5) the tampered encrypted data column becomes invalid
Select empid, title, cast (decryptbykey (salary, 1, cast (empid as varchar (3) as varchar (20) as salary from empsalary
 
 
 
-- ************ Example 3 use a digital certificate to sign the Stored Procedure
 
-- ***** (1) Preparation
-- 1) Create a database master key
Create master key encryption by password = 'P @ ssw0rd'
 
-- 2) Create the certificate required to sign the Stored Procedure
Create certificate cert_Product
With subject = 'products sign ',
Start_date = '2014/1/123 ',
Expiry_date = '2017/123'
Go
 
-- 3) create a SPDeveloper Logon account and user. This user creates and accesses the stored procedure of the Products table.
Create login [SPDeveloper] with password = 'P @ ssw0rd ', default_database = [TestDB]
Go
Create user [SPDeveloper] for login SPDeveloper with default_schema = [SPDeveloper]
Go
Create schema products authorization SPDeveloper
Go
Exec sp_addrolemember @ rolename = 'db _ owner', @ membername = 'spdeveloper'
 
-- 4) Create the Stored Procedure products. usp_Products as SPDeveloper
Execute as user = 'spdeveloper'
Go
Create procedure products. usp_products
As
Select * From DBO. Products
Go
 
Revert
Select User
 
-- 5) create a common user, Jerry
Create login [Jerry] with Password = 'P @ ssw0rd ', default_database = [testdb]
Go
Create user [Jerry] For Login Jerry
Go
 
-- ******* (2) use the certificate to sign the Stored Procedure
-- 1) grant the user Jerry the permission to execute the Stored Procedure
Grant execute on products. usp_products to Jerry
 
-- 2) failed to execute the stored procedure as Jerry because the ownership chain is broken
Execute as user = 'Jerry'
Select User
Go
 
Execute products. usp_products
Go
Revert
 
-- 3) use the certificate to create the user ProductsReader in the current database and grant the user the permission to read the products table.
Create user ProductsReader for certificate cert_Products
Go
Grant select on products To ProductsReader
 
-- 4) use the certificate to sign the current Stored Procedure
Add signature to products. usp_Products by certificate cert_Products
 
-- 5) re-execute the stored procedure as jerry.
-- Because the stored procedure is executed in the context of ProductsReader permission
Execute as user = 'Jerry'
Select user
Go
 
Execute products. usp_Products
Go
Revert

This article from the CSDN blog, reproduced please indicate the source: http://blog.csdn.net/cuoguo1111/archive/2006/11/29/1419515.aspx