SQL SERVER2005 Encrypted decryption data

server| Encryption | decryption | data

Describes the data encryption capabilities and configuration of SQL Server 2005 and how it enables the protection of sensitive data.

Demo scripts are provided for your reference:

--------------------------------------------------------------------------------

/*[Course] Protect sensitive data with database encryption Demo 1 understand that the SQL2005 encryption hierarchy [Procedure] process is divided into 4 parts */--================== (I) Service master key =====================--1.) Backup Service master key to file backup Services Master to File = ' C:\DBFile\SMK.bak ' encryption by PASSWORD = ' P@ssw0rd '--2.) Generate a new service Master key alter service master key regenerate; Go--3.) Restore Service master key from backup file Restore Services Master key from file = ' C:\DBFile\SMK.bak ' decryption by PASSWORD = ' P@ssw0rd '--============== = = = (II) database master key =====================--1.) Create a database master key for the Northwind database use Northwind gocreate MASTER key encryption by PASSWORD = ' P@ssw0rd ' go--2.) View database encryption status Select [name], is_master_key_encrypted_by_server     from sys.databases WHERE name = ' Northwind '; Go--3.) View the database master key information use Northwindselect * from sys.symmetric_keysgo--4.) Backing up the database master key use Northwindgobackup master key     to FILE = ' C:\DBFile\DMK.bak '     Encryption by PASSWORD = ' p@ssw0rd!@ ' go--5.) To remove a service master key for the protection of the database master key--     create an asymmetric key successfully, automatically decrypt using the service master key and use the database master key to create asymmetric key asy_testkey1 with ALGOrithm = rsa_1024 go--     Delete Service master key pair database Master key protection alter master key     DROP Encryption by SERVICE MASTER keygo--      View the encrypted state of the database select [name], is_master_key_encrypted_ by_server     from sys.databases WHERE name = ' Northwind ';--     failed to create an asymmetric key, The database master key is not open create asymmetric key asy_testkey2 with algorithm = rsa_1024 go--     Open Database master key not open master K EY decryption by PASSWORD = ' P@ssw0rd ' SELECT * from sys.openkeys--     Create an asymmetric key successfully created asymmetric key Asy_testkey2 with algorithm = rsa_1024 go--     recovery service master key pair database Master key protection alter master key      ADD encryption by SERVICE Master Keyclose Master key--================== (III) certificate =====================--1.) Let SQL2005 create a self-signed certificate use Northwindgocreate certificate cert_testcert1     encryption by PASSWORD = ' P@ssw0rd '     with SUBJECT = ' TestCert1 ',   &NBSp start_date = ' 1/31/2006 ',    expiry_date = ' 1/31/2008 ' goselect * from Sys.certificates--2.) Import certificates from files use northwindgocreate certificate cert_testcert2    from file = ' C:\DBFile\MSCert.cer ' goselect * From Sys.certificates--3.) Backing up the export certificate and private key backup certificate cert_testcert1     to FILE = ' C:\DBFile\TestCert1.cer '       with PRIVATE key         (decryption by PASSWORD = ' P@ssw0rd ', & nbsp;         FILE = ' c:\DBFile\TestCert1_pvt ',           encryption by PASSWORD = ' pa$w0rd ')--4.) Encrypt and decrypt data using certificates DECLARE @cleartext varbinary DECLARE @cipher varbinary SET @cleartext = CONVERT (varbinary (200), ' Test text string ') SET @cipher = EncryptByCert (cert_id (' Cert_testcert1 '), @cleartext) SELECT @cipherSELECT CONVERT ( varchar, DecryptByCert (cert_id (' Cert_testcert1 '), @cipher, N ' P@ssw0rd ')) as [cleartext]--5.) Delete certificate PrivateKey Alter certificate cert_testcert1    REMOVE PRIVATE keygo--    Encryption succeeded, decryption failed declare @ cleartext varbinary DECLARE @cipher varbinary Set @cleartext = CONVERT (varbinary, ' Test text string ') set @ cipher = EncryptByCert (cert_id (' Cert_testcert1 '), @cleartext) SELECT @cipherSELECT CONVERT (varchar (200), DecryptByCert (cert_id (' Cert_testcert1 '), @cipher, N ' P@ssw0rd ')) as [cleartext]--================== (IV) asymmetric key ======= ==============--1.) Use Sn.ext to generate a non-pair key file--     sn-k C:\DBFile\asy_Test.key--2.) Create an asymmetric key from a file use Northwindgocreate asymmetric key asy_test      from file = ' C:\DBFile\asy_ Test.key '       encryption by PASSWORD = ' P@ssw0rd ' goselect * from Sys.asymmetric_keys

/*
[Course] protects sensitive data with database encryption

DEMO 2
Encrypt column data using a key

Process
The process is divided into 4 parts

*/

--================== (I) Prepare =====================
--1.) To create a sample table
Use Northwind
IF EXIST dbo. Empsalary DROP TABLE dbo. Empsalary;

CREATE TABLE dbo. Empsalary (
EmpID int,
Title nvarchar (50),
Salary varbinary (500)
)
Go

--2.) Create DATABASE Master Key
CREATE MASTER KEY Encryption by PASSWORD = ' P@ssw0rd '
Go

--3.)

--4.) Create a symmetric key for encryption
CREATE symmetric KEY Sym_salary
with algorithm = aes_192
Encryption by PASSWORD = ' P@ssw0rd ';

SELECT * from Sys.symmetric_keys WHERE [name] = ' Sym_salary '

--================== (II) encrypted column data =====================

--1.) Open Symmetric key
OPEN symmetric KEY Sym_salary
Decryption by PASSWORD = ' P@ssw0rd '

SELECT * FROM Sys.openkeys--view open symmetric key

--2.) Inserts data into a table and encrypts the data in the salary column
INSERT into Empsalary VALUES (1, ' CEO ', EncryptByKey (Key_GUID (' sym_salary '), ' 20000 ')
INSERT into Empsalary VALUES (2, ' Manager ', EncryptByKey (Key_GUID (' sym_salary '), ' 10000 '))
INSERT into Empsalary VALUES (3, ' DB Admin ', EncryptByKey (Key_GUID (' sym_salary '), ' 5000 ')

--3.) Turn off the open symmetric key
Close symmetric KEY sym_salary

SELECT * FROM Sys.openkeys--view open symmetric key

--4.) View data stored in a table
SELECT * from Empsalary

--================== (III) decrypts and accesses the encrypted data column =====================
--1.) Open Symmetric key
OPEN symmetric KEY sym_salary decryption by PASSWORD = ' P@ssw0rd '

--2.) Decrypting and accessing the encrypted data column with a symmetric key
SELECT EmpID, Title, CAST (DecryptByKey (Salary) as VARCHAR) as Salary from Empsalary

--3.) Turn off symmetric keys
Close symmetric KEY sym_salary

--================== (III) attacks that bypass encrypted data =====================
--1.) An attacker replaces data in a row with encrypted data from another data row
SELECT * from Empsalary
UPDATE empsalary SET Salary =
(SELECT Salary from empsalary WHERE EmpID = 1)
WHERE EmpID = 3

--2.) View the data that was decrypted after the attack
OPEN symmetric KEY sym_salary decryption by PASSWORD = ' P@ssw0rd '
SELECT EmpID, Title, CAST (DecryptByKey (Salary) as VARCHAR) as Salary from Empsalary
Close symmetric KEY sym_salary

--================== (IV) uses validators to prevent attacks that bypass encrypted data =====================
--1.) Delete previously added rows of data
DELETE from Empsalary

--2.) Inserts data into the table and encrypts the data in the salary column using a validator, the fourth parameter is the encryption factor
OPEN symmetric KEY sym_salary decryption by PASSWORD = ' P@ssw0rd '
INSERT into Empsalary VALUES (1, ' CEO ', EncryptByKey (Key_GUID (' sym_salary '), ' 20000 ', 1, ' 1 '))
INSERT into Empsalary VALUES (2, ' Manager ', EncryptByKey (Key_GUID (' sym_salary '), ' 10000 ', 1, ' 2 '))
INSERT into Empsalary VALUES (3, ' DB Admin ', EncryptByKey (Key_GUID (' sym_salary '), ' 5000 ', 1, ' 3 '))
Close symmetric KEY sym_salary

--3.) Decrypting and accessing the data columns that were encrypted
OPEN symmetric KEY sym_salary decryption by PASSWORD = ' P@ssw0rd '
SELECT EmpID, Title, cast (DecryptByKey (Salary, 1, cast (EmpID as VARCHAR (3))) as VARCHAR) as Salary from Empsalary
Close symmetric KEY sym_salary

--4.) Attackers use the same method to tamper with data
SELECT * from Empsalary
UPDATE empsalary SET Salary =
(SELECT Salary from empsalary WHERE EmpID = 1)
WHERE EmpID = 3

--5.) The encrypted data column becomes invalid after being tampered with.
OPEN symmetric KEY sym_salary decryption by PASSWORD = ' P@ssw0rd '
SELECT EmpID, Title, cast (DecryptByKey (Salary, 1, cast (EmpID as VARCHAR (3))) as VARCHAR) as Salary from Empsalary
Close symmetric KEY sym_salary

/*
[Course] protects sensitive data with database encryption

DEMO 3
Signing a stored procedure with a certificate

Process
The process is divided into 2 parts

*/

--================== (I) sample preparation =====================
--1.) Create DATABASE Master Key
Use Northwind
Go
CREATE MASTER KEY Encryption by PASSWORD = ' P@ssw0rd '

--2.) Create the certificate required to sign the stored procedure
CREATE Certificate Cert_products
With SUBJECT = ' products Sign ',
start_date = ' 2006/1/1 ',
expiry_date = ' 2008/1/1 '

--3.) Create a Spdeveloper login account and user that creates a stored procedure to access the Products table
CREATE LOGIN [Spdeveloper] with Password=n ' P@ssw0rd ', Default_database=[northwind]
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 a stored procedure as Spdeveloper products.usp_products
EXECUTE as USER = ' Spdeveloper '
Go
CREATE PROCEDURE products.usp_products
As
SELECT Top 5 * FROM dbo. Products
Go

REVERT
SELECT USER

--4.) Create an ordinary user Jerry
CREATE LOGIN Jerry with Password=n ' P@ssw0rd ', Default_database=[northwind]
CREATE USER Jerry for LOGIN Jerry

--================== (II) signing stored procedures using certificates =====================
--1.) Grant user permission to execute stored procedures
GRANT EXECUTE on Products.usp_products to Jerry

--2.) Executing the stored procedure as Jerry failed because the whole chain was broken
EXECUTE as USER = ' Jerry '
SELECT USER
Go

EXECUTE products.usp_products
Go

REVERT

--3.) Use a certificate to create a user Productsreader in the current database
--and give the user permission to read the Products table
CREATE USER productsreader for certificate cert_products
Go
GRANT SELECT on the products to Productsreader

--4.) Signing the current stored procedure with a certificate
ADD SIGNATURE to products.usp_products by certificate Cert_products

--4.) Re-executing the stored procedure as Jerry, successful,
--Because the stored procedure executes in the Productsreader permission context
EXECUTE as USER = ' Jerry '
SELECT USER
Go
EXECUTE products.usp_products

Lecturer: Ox can

Time: August 9, 2006 10:00--11:30
Products: SQL Server
Technology level: 200

We welcome the active participation in the discussion

Post-class questions and Answers

1. In SQL Server 2005, the master key of the database can be used directly for encryption protection: (AB)

A. The private key of the certificate

B. The private key of an asymmetric key

C. The public key of an asymmetric key

D. Service Master Key

2. When encryption is used to protect a large number of sensitive data in a database, the best approach is to take account of both performance and data security: (C)

A. Encrypt all sensitive data using a certificate and secure the private key of the certificate with a symmetric key

B. Encrypt all sensitive data using the public key of an asymmetric key and secure the private key of the key pair with symmetric key encryption

C. Encrypt all sensitive data using a symmetric key and secure the symmetric key with certificate encryption

D. Encrypt all sensitive data using the private key of an asymmetric key and protect the public key of the key pair with a certificate encryption

3. The purpose of using certificates to sign stored procedures in SQL Server 2005 is: (D)

A. Ensure that only users who have the corresponding private key for this certificate can execute the stored procedure

B. Encrypt stored procedures to prevent others from viewing T-SQL statements in stored procedures

C. Encrypt stored procedure execution returned data result set

D. Let the stored procedure execute with the permissions of the database user corresponding to the certificate