SQL Server 2005 Encryption System (II)

server| Encryption | system

SQL Server 2005 Encryption System (II)
Immediately on the previous, and then give a paragraph with the key to encrypt data example, this code is relatively simple, rhubarb not like the ravages of Dayu said I irrigation!!!

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

--Create an experimental database
Use master
IF EXISTS (SELECT [name] from sys.databases WHERE [name] = ' Sales ')
DROP DATABASE Sales
CREATE DATABASE Sales
IF EXISTS (SELECT principal_id from sys.server_principals WHERE [name] = ' Ryan ' and [type] = ' S ')
DROP LOGIN Ryan
CREATE LOGIN ryan with PASSWORD = ' P@ssw0rd '
IF EXISTS (SELECT principal_id from sys.server_principals WHERE [name] = ' Teddy ' and [type] = ' S ')
DROP LOGIN Teddy
CREATE LOGIN Teddy with PASSWORD = ' P@ssw0rd '
Go

--Create user Ryan and create DATABASE master key
Use Sales
IF EXISTS (SELECT * from sys.database_principals WHERE [name] = ' Ryan ' and [type] = ' S ')
DROP User Ryan
CREATE USER Ryan for LOGIN ryan with default_schema = dbo
Go
IF EXISTS (SELECT * from sys.database_principals WHERE [name] = ' Teddy ' and [type] = ' S ')
DROP User Teddy
CREATE USER Teddy for LOGIN teddy with default_schema = dbo
Go
CREATE MASTER KEY Encryption by PASSWORD = ' P@ssw0rd '
Go
--Encrypts the database master key with the service master key,
--Delete here because it discovers default when database master key is created and encrypts with service master key
--The database master key encrypted with service master key is called automatic Key management
--You can use the following query statement to enable automatic key management of the database master key
SELECT [name], is_master_key_encrypted_by_server from sys.databases WHERE [name] = ' Sales '
--Use the following statement to enable automatic management of the database master key
--alter master key ADD encryption by SERVICE master key
Go

--Create a certificate for Ryan
IF EXISTS (SELECT [name] from sys.certificates WHERE [name] = ' Cert_ryan ')
DROP Certificate Cert_ryan
CREATE Certificate Cert_ryan AUTHORIZATION RYAN
--encryption by PASSWORD = ' P@ssw0rd '
--It is recommended that you do not use a password because, after testing, a certificate with a password is protected with a password,
Instead of the database master key, you can use the following statement to test the encryption method of the certificate
--select [name], Pvt_key_encryption_type_desc from Sys.certificates
--where [Name] = ' cert_db '
With SUBJECT = ' Certificate for Database ',
start_date = ' 01/01/2006 ',
expiry_date = ' 12/31/2015 '
Go
--Create a certificate for Teddy
IF EXISTS (SELECT [name] from sys.certificates WHERE [name] = ' Cert_teddy ')
DROP Certificate Cert_teddy
CREATE Certificate Cert_teddy AUTHORIZATION TEDDY
--encryption by PASSWORD = ' P@ssw0rd '
--It is recommended that you do not use a password because, after testing, a certificate with a password is protected with a password,
Instead of the database master key, you can use the following statement to test the encryption method of the certificate
--select [name], Pvt_key_encryption_type_desc from Sys.certificates
--where [Name] = ' cert_db '
With SUBJECT = ' Certificate for Database ',
start_date = ' 01/01/2006 ',
expiry_date = ' 12/31/2015 '
Go

SELECT * from Sys.certificates

--for Ryan and Teddy. Create a symmetric password that leverages certificate protection, respectively
CREATE symmetric KEY Key_sym_ryan AUTHORIZATION RYAN
with algorithm = Triple_des
Encryption by Certificate Cert_ryan
Go
CREATE symmetric KEY Key_sym_teddy AUTHORIZATION TEDDY
with algorithm = Triple_des
Encryption by Certificate Cert_teddy
Go

--Create a test table
IF EXISTS (SELECT [name] from sys.tables WHERE [name] = ' encryption ')
DROP TABLE Encryption
CREATE TABLE dbo.encryption
(
PT nchar (Ten),--plain Text
ET varbinary (128),--encrypted Text
)
Go
GRANT SELECT, INSERT on encryption to Ryan
GRANT SELECT, INSERT on encryption to Teddy

--Complete the preparation and start testing the encryption
EXECUTE as LOGIN = ' Ryan '
OPEN symmetric KEY Key_sym_ryan decryption by certificate Cert_ryan
INSERT into encryption
VALUES (n ' Ryan ', EncryptByKey (Key_GUID (' Key_sym_ryan '), n ' Ryan '))
Close all symmetric KEYS
REVERT

EXECUTE as LOGIN = ' Teddy '
OPEN symmetric KEY Key_sym_teddy decryption by certificate Cert_teddy
INSERT into encryption
VALUES (n ' TEDDY ', EncryptByKey (Key_GUID (' Key_sym_teddy '), n ' TEDDY '))
Close all symmetric KEYS
REVERT

--test data has been encrypted
SELECT * from encryption

--Decrypting data
EXECUTE as LOGIN = ' Ryan '
OPEN symmetric KEY Key_sym_ryan decryption by certificate Cert_ryan
SELECT PT, CONVERT (Nchar,decryptbykey (ET)) as ET from encryption
Close all symmetric KEYS
REVERT
EXECUTE as LOGIN = ' Teddy '
OPEN symmetric KEY Key_sym_teddy decryption by certificate Cert_teddy
SELECT PT, CONVERT (Nchar,decryptbykey (ET)) as ET from encryption
Close all symmetric KEYS
REVERT