use master
CREATE DATABASE db_Demo
go
USE db_Demo
go
CREATE TABLE t_Customer(
SSN nvarchar(20) PRIMARY KEY,
name nvarchar(200)
NOT NULL,
Status int NOT NULL,
ExtraData nvarchar(100) )
go
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Yukon900'
go
CREATE CERTIFICATE cert_ProtectEncryptionKeys WITH SUBJECT = 'Data encryption key protection'
go
CREATE SYMMETRIC KEY key_Encryption WITH ALGORITHM =
ENCRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys
go
CREATE CERTIFICATE cert_ProtectIndexingKeys WITH SUBJECT = 'Data indexing key protection'
go
CREATE SYMMETRIC KEY key_Indexing WITH ALGORITHM =AES_256
ENCRYPTION BY CERTIFICATE cert_ProtectIndexingKeys
go
CREATE TABLE t_MacIndexKeys( table_id int PRIMARY KEY, Mac_key varbinary(100) not null )
go
CREATE FUNCTION MAC( @Message nvarchar(4000), @Table_id int )
RETURNS varbinary(24)
WITH EXECUTE AS 'dbo'
AS
BEGIN
declare @RetVal varbinary(24)
declare @Key varbinary(100)
SET @RetVal = null
SET @key = null
SELECT @Key = DecryptByKeyAutoCert( cert_id('cert_ProtectIndexingKeys'), null, Mac_key) FROM t_MacIndexKeys WHERE table_id = @Table_id
if( @Key is not null )
SELECT @RetVal = HashBytes( N'SHA1', convert(varbinary(8000), @Message) + @Key )
RETURN @RetVal
END
go
CREATE PROC AddMacForTable @Table_id int
WITH EXECUTE AS 'dbo'
AS
declare @Key varbinary(100)
declare @KeyGuid uniqueidentifier
SET @KeyGuid = key_guid('key_Indexing')
OPEN SYMMETRIC KEY key_Indexing DECRYPTION BY CERTIFICATE cert_ProtectIndexingKeys
SELECT @Key = HashBytes( N'SHA1', ENCRYPTBYKEY( @KeyGuid, convert(varbinary(100), newid())) )
SET @KEY = ENCRYPTBYKEY( @KeyGuid, @Key )
CLOSE SYMMETRIC KEY key_Indexing
if @Key is null
BEGIN
RAISERROR( 'Failed to create new key.', 16, 1)
END
INSERT INTO t_MacIndexKeys VALUES( @Table_id, @Key )
go
CREATE TABLE dbo.t_CustomerRaw(
SSN_index varbinary(20) PRIMARY KEY,
SSN_cipher nvarchar(60) NOT NULL,
Name_cipher nvarchar(300),
Status int,
ExtraData nvarchar(100) )
go
declare @objid int
SET @objid = object_id('t_CustomerRaw')
EXEC AddMacForTable @objid
go
CREATE TRIGGER trig_ProtectSSN on t_CustomerRaw
INSTEAD OF INSERT
AS
declare @Index varbinary(24)
declare @KeyGuid uniqueidentifier
declare @Cipher nvarchar(60)
if( select count(*) from inserted where SSN_cipher is null ) > 0
RAISERROR( 'Cannot store null as protected data. ', 16, 1)
ELSE
BEGIN
SET @KeyGuid = key_guid('key_Encryption')
SELECT @Index = dbo.MAC( SSN_cipher,
object_id('t_CustomerRaw') ) from inserted
if( @Index is null
OR @KeyGuid is null
OR encryptbykey( key_guid('key_Encryption'), 0x00)
is null )
BEGIN
RAISERROR( 'Cannot Insert protected data. Either the encryption or indexing keys are not available or the indexing key is not valid for MAC generation.', 16, 1)
END
ELSE
INSERT INTO dbo.t_CustomerRaw select
@Index,
encryptbykey( key_guid('key_Encryption'), SSN_cipher, 1, @Index ),
encryptbykey( key_guid('key_Encryption'), Name_cipher, 1, @Index ),
Status, Extradata
from inserted
END
go
CREATE TRIGGER trig_ProtectSSNUpdate on t_CustomerRaw
INSTEAD OF UPDATE
AS
if( COLUMNS_UPDATED() & 3 ) > 0
raiserror( 'Cannot update protected columns. Drop the row and create a new one with the updated information.', 16, 1 )
ELSE
BEGIN
SET NOCOUNT ON
if( COLUMNS_UPDATED() & 4 ) > 0
BEGIN
if( encryptbykey( key_guid('key_Encryption'), 0x00) is null )
BEGIN
RAISERROR( 'Cannot Insert protected data. The encryption or indexing keys are not available.', 16, 1)
END
ELSE
UPDATE t_CustomerRaw SET Name_cipher = encryptbykey( key_guid('key_Encryption'), ins.Name_cipher, 1, ins.SSN_index )
FROM inserted ins, t_CustomerRaw orig
WHERE ins.SSN_index = orig.SSN_index
END
UPDATE t_CustomerRaw
SET Status = ins.Status, ExtraData = ins.ExtraData
FROM inserted ins, t_CustomerRaw orig
WHERE ins.SSN_index = orig.SSN_index
END
go
OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys
go
SET NOCOUNT ON
DECLARE @SSN nvarchar(15)
DECLARE @Name nvarchar(200)
DECLARE @Status int
DECLARE @ExtraData nvarchar(100)
DECLARE curs_Customer CURSOR FOR SELECT SSN, Name, Status, ExtraData FROM t_Customer
OPEN curs_Customer
FETCH NEXT FROM curs_Customer INTO @SSN, @Name, @Status, @ExtraData
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO t_CustomerRaw VALUES( null, @SSN, @Name, @Status, @ExtraData )
FETCH NEXT FROM curs_Customer INTO @SSN, @Name, @Status, @ExtraData
END
CLOSE curs_Customer
DEALLOCATE curs_Customer
go
select * from t_CustomerRaw
SELECT
convert( nvarchar(15), DecryptbyKeyAutoCert(
cert_id('cert_ProtectEncryptionKeys'), null,
SSN_cipher, 1, SSN_index)) as SSN,
convert( nvarchar(200), DecryptbyKeyAutoCert(
cert_id('cert_ProtectEncryptionKeys'), null,
Name_cipher, 1, SSN_index)) as Name,
Status, ExtraData FROM dbo.t_CustomerRaw ORDER BY SSN
go
CLOSE SYMMETRIC KEY key_Encryption
go
select * from t_CustomerRaw
DROP TABLE t_Customer
go
CREATE VIEW t_Customer
WITH SCHEMABINDING
AS
SELECT
SSN_index,
convert( nvarchar(15), DecryptbyKeyAutoCert(
cert_id('cert_ProtectEncryptionKeys'), null,
SSN_cipher, 1, SSN_index)) as SSN,
convert( nvarchar(200), DecryptbyKeyAutoCert(
cert_id('cert_ProtectEncryptionKeys'), null,
Name_cipher, 1, SSN_index)) as Name,
Status, ExtraData FROM dbo.t_CustomerRaw
go
CREATE TRIGGER trig_CustomerDataEx_ins on t_Customer
INSTEAD OF INSERT
AS
SET NOCOUNT ON
INSERT INTO dbo.t_CustomerRaw select null, SSN, Name, Status, Extradata from inserted
go
CREATE TRIGGER trig_CustomerDataEx_upg on t_Customer
INSTEAD OF UPDATE
AS
SET NOCOUNT ON
UPDATE dbo.t_CustomerRaw SET
Name_cipher = ins.Name,
Status = ins.Status,
Extradata = ins.Extradata
FROM inserted
left outer join t_CustomerRaw raw ON raw.SSN_index = dbo.MAC( ins.SSN, object_id('t_CustomerRaw') )
WHERE raw.SSN_index = dbo.MAC( ins.SSN, object_id('t_CustomerRaw') )
go
OPEN SYMMETRIC KEY key_Encryption DECRYPTION BY CERTIFICATE cert_ProtectEncryptionKeys
go
INSERT INTO v_CustomerDataEx VALUES ( null, N'555-55-5555', N'User 5555',1, N'Extra Data ' )
go
UPDATE v_CustomerDataEx SET
SSN_index = null,
SSN = N'555-55-5555',
Name = N'New User 5555',
Status = 0,
ExtraData = N'Extra Data2'
WHERE SSN_index = dbo.MAC( N'555-55-5555', object_id('t_CustomerRaw') )
go
SELECT * FROM v_CustomerDataEx WHERE SSN = N'555-55-5555'
go
DELETE v_CustomerDataEx WHERE SSN = N'555-55-5555'
go
SELECT * FROM v_CustomerDataEx WHERE SSN = N'555-55-5555'
go
CLOSE SYMMETRIC KEY key_Encryption
go
use db_Demo
SELECT * FROM v_CustomerDataEx
go
-------------------------------------------------------
-----------------------------------------------------
use db_demo
select * from v_CustomerDataEx
but when i alter the code in my database having data more then 1400000 rows each and every query slow down so i need to do performance turning. for what i m looking into some alternative for big database
i know that encrypting entire database will slow down but it is the requirement and delay can't be accepted above 10 sec but now i m having above 2 min