USE master
GO
CREATE DATABASE CellLevelEncryptionDemo
GO
Use CellLevelEncryptionDemo
GO
--create a database master key (DMK) for the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'F#4*KN^7$';
GO
--create a certificate for use as the database encryption key (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE CertificateforColumns
WITH SUBJECT = 'Certificate for Column Level encryption';
GO
--Create a symmetric key and encrypt it by using above created certificate
CREATE SYMMETRIC KEY EncryptColumn
WITH ALGORITHM = AES_256 --Supported encryption algorithms are AES with 128-bit, 192?bit, or 256?bit keys or 3 Key Triple DES
ENCRYPTION BY CERTIFICATE CertificateforColumns;
GO
--To decrypt the encrypted data you need to use another inbuilt function called DecryptByKey
--When you try encrypting or decrypting data without opening the symmetric key, the command will not fail but also, it will not work; for example the command given below will not fail as symmetric is not open but it will return NULLs instead of data:
--It’s very important and essential to take a backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys\certificates there.
--Please note, enabling encryption at cell level has overhead and performance penalties (as it is a resource intensive operation and salts the data differently, which causes a table scan) as discussed above and hence it’s recommended to first evaluate the need and
--then plan for its implementation.
-- drop table Customer
--go
CREATE TABLE dbo.Customer
(
CustomerID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
CreditCardNumber Varchar(128) NULL
)
GO
INSERT INTO dbo.Customer(FirstName, LastName, CreditCardNumber)
VALUES
('Steve', 'Savage','1111-1111-1111-1111'),
('Ranjit', 'Srivastava','2222-2222-2222-2222'),
('Akram', 'Haque', '3333-3333-3333-3333')
GO
SELECT * from Customer
go
-----------FROM HERE Actually Steps Start
ALTER TABLE Customer
ADD DecCreditCardNumber VARBINARY(256)
GO
UPDATE Customer
SET DecCreditCardNumber = ENCRYPTBYKEY(KEY_GUID('EncryptColumn'),CreditCardNumber)
GO
--CHECK DATA
SELECT * from Customer
SELECT CustomerID,FirstName,LastName,CreditCardNumber,DecCreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(DecCreditCardNumber)) AS DecryptSecondCol
from Customer
CLOSE SYMMETRIC KEY EncryptColumn;
GO
--CHeck any Indexes, FK, Constraints
--Backups
--Backup Database or table
Alter table Customer
drop Column CreditCardNumber
Alter table Customer
--sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
sp_RENAME 'Customer.DecCreditCardNumber' , 'CreditCardNumber', 'COLUMN'
--CHeck any Indexes, FK, Constraints
SELECT * from Customer
go
SELECT CustomerID,FirstName,LastName,CreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer
--if you're using cell encryption then you need to call the encryptby or decryptby methods for each insert\select. The app code would need to reflect this
--Insert method: Joe need to change
Declare @CreditCardNumber Varchar(128)
SET @CreditCardNumber ='123456789087'
INSERT INTO dbo.Customer(FirstName, LastName, CreditCardNumber)
VALUES
('Kari', 'Singh', EncryptByKey(Key_GUID('EncryptColumn'),@CreditCardNumber ))
OPEN SYMMETRIC KEY EncryptColumn DECRYPTION
BY CERTIFICATE CertificateforColumns
SELECT CustomerID,FirstName,LastName, CreditCardNumber,CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer
SELECT *
from Customer
CLOSE SYMMETRIC KEY EncryptColumn;
GO
GO
CREATE DATABASE CellLevelEncryptionDemo
GO
Use CellLevelEncryptionDemo
GO
--create a database master key (DMK) for the master database
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'F#4*KN^7$';
GO
--create a certificate for use as the database encryption key (DEK) protector and is protected by the DMK.
CREATE CERTIFICATE CertificateforColumns
WITH SUBJECT = 'Certificate for Column Level encryption';
GO
--Create a symmetric key and encrypt it by using above created certificate
CREATE SYMMETRIC KEY EncryptColumn
WITH ALGORITHM = AES_256 --Supported encryption algorithms are AES with 128-bit, 192?bit, or 256?bit keys or 3 Key Triple DES
ENCRYPTION BY CERTIFICATE CertificateforColumns;
GO
--To decrypt the encrypted data you need to use another inbuilt function called DecryptByKey
--When you try encrypting or decrypting data without opening the symmetric key, the command will not fail but also, it will not work; for example the command given below will not fail as symmetric is not open but it will return NULLs instead of data:
--It’s very important and essential to take a backup of the keys and certificates in order to restore or attach the encrypted database on another SQL Server instance after restoring these keys\certificates there.
--Please note, enabling encryption at cell level has overhead and performance penalties (as it is a resource intensive operation and salts the data differently, which causes a table scan) as discussed above and hence it’s recommended to first evaluate the need and
--then plan for its implementation.
-- drop table Customer
--go
CREATE TABLE dbo.Customer
(
CustomerID INT IDENTITY PRIMARY KEY,
FirstName VARCHAR(100),
LastName VARCHAR(100),
CreditCardNumber Varchar(128) NULL
)
GO
INSERT INTO dbo.Customer(FirstName, LastName, CreditCardNumber)
VALUES
('Steve', 'Savage','1111-1111-1111-1111'),
('Ranjit', 'Srivastava','2222-2222-2222-2222'),
('Akram', 'Haque', '3333-3333-3333-3333')
GO
SELECT * from Customer
go
-----------FROM HERE Actually Steps Start
ALTER TABLE Customer
ADD DecCreditCardNumber VARBINARY(256)
GO
UPDATE Customer
SET DecCreditCardNumber = ENCRYPTBYKEY(KEY_GUID('EncryptColumn'),CreditCardNumber)
GO
--CHECK DATA
SELECT * from Customer
SELECT CustomerID,FirstName,LastName,CreditCardNumber,DecCreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(DecCreditCardNumber)) AS DecryptSecondCol
from Customer
CLOSE SYMMETRIC KEY EncryptColumn;
GO
--CHeck any Indexes, FK, Constraints
--Backups
--Backup Database or table
Alter table Customer
drop Column CreditCardNumber
Alter table Customer
--sp_rename 'table_name.old_column_name', 'new_column_name', 'COLUMN';
sp_RENAME 'Customer.DecCreditCardNumber' , 'CreditCardNumber', 'COLUMN'
--CHeck any Indexes, FK, Constraints
SELECT * from Customer
go
SELECT CustomerID,FirstName,LastName,CreditCardNumber, CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer
--if you're using cell encryption then you need to call the encryptby or decryptby methods for each insert\select. The app code would need to reflect this
--Insert method: Joe need to change
Declare @CreditCardNumber Varchar(128)
SET @CreditCardNumber ='123456789087'
INSERT INTO dbo.Customer(FirstName, LastName, CreditCardNumber)
VALUES
('Kari', 'Singh', EncryptByKey(Key_GUID('EncryptColumn'),@CreditCardNumber ))
OPEN SYMMETRIC KEY EncryptColumn DECRYPTION
BY CERTIFICATE CertificateforColumns
SELECT CustomerID,FirstName,LastName, CreditCardNumber,CONVERT(VARCHAR(50),DECRYPTBYKEY(CreditCardNumber)) AS DecryptSecondCol
from Customer
SELECT *
from Customer
CLOSE SYMMETRIC KEY EncryptColumn;
GO
No comments:
Post a Comment