USE Hashgroups
GO
/* Create the keys and certificates required to encrypt SSn numbers.
Values for passwords here are for demo purposes only.
If you use this code modify the password and names according to your requirements.
To see the various changes it is recommended to have set statsistics time on as well as 'show actual execution plan'
*/

SELECT COUNT(1) FROM dbo.cust
go
select TOP 10  custID ,
        ssn FROM dbo.cust;
go


-- create basic keys & certificates to be able to use encryption!
CREATE MASTER KEY ENCRYPTION 
BY PASSWORD = 'P(-"2w2+]?*5/3+@06.15*)<!_|0&ou6.]|2-_4cX@#]7h6Iv'
GO

CREATE CERTIFICATE SSN_Encrypt WITH SUBJECT = 'HGSSNENCRYPT'
GO

CREATE SYMMETRIC KEY HGSSNKEY WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE SSN_Encrypt
GO



-- Demo #1

--Demo of how hashing is determinate and encryption is non-determinite
OPEN SYMMETRIC KEY HGssnkey
DECRYPTION BY CERTIFICATE SSN_Encrypt
go

SELECT TOP 1
        custid ,
        ssn ,
        HASHBYTES('SHA2_256', ssn) AS ssn_hashed ,
        ENCRYPTBYKEY(KEY_GUID('HGSSNKEY'), SSN) AS ssn_encrypted
FROM    dbo.cust
go 3

CLOSE SYMMETRIC KEY hgssnkey;
go

-- Note how the hashed value is the same everytime, but the encrypted value starts the same, then changes every time the value is encrypted.



--hash the demo values (with a salt value) and store in new table
declare @salt nvarchar(20)

select @salt = N'R@nd0mS!a6lTValue'

INSERT dbo.SSN_hash
(custid, ssn_hash)
SELECT custid, Hashbytes('SHA2_256', ssn + @salt)
FROM cust

select TOP 10  custID ,
        ssn_hash FROM ssn_hash


-- Encrypt the demo values and store in new table

OPEN SYMMETRIC KEY HGssnkey
DECRYPTION BY CERTIFICATE SSN_Encrypt
go
insert dbo.ssn_encr (custid, ssn_encr)
SELECT custid, ENCRYPTBYKEY(KEY_GUID('HGSSNKEY'),SSN)
FROM dbo.cust
go

CLOSE SYMMETRIC KEY HGssnkey;
go

-- Sample of the encrypted values
SELECT TOP 10
        custID ,
        ssn_encr 
FROM    dbo.ssn_encr





-- demo 1a 
---- Check the values are correct
--OPEN SYMMETRIC KEY hgssnkey
--DECRYPTION BY CERTIFICATE SSN_Encrypt
--go

/* Get a random record (no id's other than ssn users in table */
--SELECT TOP 1 
--        cust.custid ,
--        cust.ssn ,
--        ssnh.ssn_hash ,
--        ssne.ssn_encr,
--        CONVERT(VARCHAR, DECRYPTBYKEY(ssne.ssn_encr)) AS decrypted_ssn ,
--        ssne.ssn_hg
--FROM    dbo.cust cust 
--INNER JOIN dbo.ssn_hash ssnh ON cust.custid = ssnh.custid
--INNER JOIN dbo.ssn_encr ssne ON cust.custid = ssne.custid
 
--  WHERE (ABS(CAST(
--  (BINARY_CHECKSUM(*) *
--  RAND()) as int)) % 500000) < 10

--  CLOSE SYMMETRIC KEY HGssnkey;
--go



-- what happens if you don't have the key open, or don't have permission to use it?
-- user requires 'control' permission on the symmetric key to be able to encrypt/decrypt values with it
-- make sure key isn't left open by other process above: 
CLOSE SYMMETRIC KEY HGssnkey;
go

-- query showing that without the appropriate access to the encryption key you will only receive a null value back.
SELECT 
        custID ,
        ssn_encr ,
        CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr)) AS decrypted_ssn 
FROM    dbo.SSN_encr
 
  WHERE    custID = 445353

-- decyrpt the value with the key
OPEN SYMMETRIC KEY HGssnkey
DECRYPTION BY CERTIFICATE SSN_Encrypt
go

SELECT 
        custID ,
        ssn_encr ,
        CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr)) AS decrypted_ssn 
FROM    dbo.SSN_encr
 
  WHERE    custID = 445353

  CLOSE SYMMETRIC KEY HGssnkey;
go


--CREATE CLUSTERED INDEX ix_ssn ON cust (ssn);
--go
--CREATE UNIQUE CLUSTERED INDEX ix_hash ON dbo.ssn_hash (ssn_hash);
--go


-- use random value from above below
-- show plan ctrl-m
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
SET STATISTICS TIME ON;


DECLARE @ssn VARCHAR(9)
SET @ssn = '033578781'


SELECT  custID ,
          ssn  FROM cust 
		  WHERE ssn = @ssn
go 


DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;



-- check performance on having encrypted the data
OPEN SYMMETRIC KEY HGssnkey
DECRYPTION BY CERTIFICATE SSN_Encrypt
go

DECLARE @ssn VARCHAR(9)
SET @ssn = '033578781'


/* Get a random record based on SSN coming in and being decrypted for comparison */
SELECT 
        custID ,
        ssn_encr ,
        CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr)) AS decrypted_ssn 
FROM    dbo.SSN_encr
 
  WHERE      CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr))  = @ssn


CLOSE SYMMETRIC KEY HGssnkey;
go





-- Demo# 2
--function code
USE [Hashgroups]
GO

/****** Object:  UserDefinedFunction [dbo].[ufn_GroupData]    Script Date: 2/25/2013 6:53:20 PM ******/
Create FUNCTION [dbo].[ufn_GroupData]   
    (  
     @String VARCHAR(MAX),  
     @Divisor INT  
    )   
    RETURNS INT  
    AS 
    /* based on value being hashed, generates a number within the divisor range to be stored and indexed rather than using an actual hashed value. */
    BEGIN  
      DECLARE @Result INT;  
      SET @Result = HASHBYTES('SHA1', @String);  
	  IF (@Divisor > 0)  
        SET @Result =  @Result % @Divisor;  
        
      RETURN @Result;  
      
    END  

GO

-- show what the parts do
      DECLARE @Result INT;  
      SET @Result = HASHBYTES('SHA1', 'thisismyhashinginput-withsalt');  
      SELECT @Result AS result_value
	  SET @Result =  @Result % 50;  
	  SELECT @Result AS result_group_number




-- apply the hashgrouping - aprox 
UPDATE dbo.SSN_encr
set ssn_hg = dbo.ufn_GroupData(ssn,10000)
FROM ssn_encr INNER JOIN cust ON cust.custid = ssn_encr.custID;
go

SELECT  TOP 1000 * FROM dbo.ssn_encr

-- what the distribution looks like
SELECT ssn_hg, COUNT(1) AS group_members FROM dbo.ssn_encr
GROUP by ssn_hg
ORDER BY ssn_hg


-- only a few indexes for now
--CREATE clustered INDEX ix_hg ON ssn_encr (ssn_hg);
--go

-- use random value below
-- turn on execution plan!
SET STATISTICS TIME ON;

DBCC freeproccache;
DBCC DROPCLEANBUFFERS;


DECLARE @ssn VARCHAR(9)
SET @ssn = '033578781'
SELECT  custID ,
          ssn 
		  FROM cust WHERE ssn =@ssn
go 







DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;





-- check performance on having encrypted the data
OPEN SYMMETRIC KEY HGssnkey
DECRYPTION BY CERTIFICATE SSN_Encrypt
go

DECLARE @ssn VARCHAR(9)
SET @ssn = '033578781'



/* Get a random record based on SSN coming in and being decrypted for comparison */
SELECT 
        custid ,
        ssn_encr ,
        CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr)) AS decrypted_ssn ,
        ssn_hg
FROM    dbo.SSN_encr
 
  WHERE      CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr))  = @ssn


  CLOSE SYMMETRIC KEY HGssnkey;
go



  -- drop any optimizations
DBCC FREEPROCCACHE;
DBCC DROPCLEANBUFFERS;
 go





 -- add the Hash group lookup to the select 

OPEN SYMMETRIC KEY HGssnkey
DECRYPTION BY CERTIFICATE SSN_Encrypt
go

DECLARE @ssn VARCHAR(9)
SET @ssn = '033578781'

  SELECT 
        custid ,
        ssn_encr ,
        CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr)) AS decrypted_ssn ,
        ssn_hg
FROM    dbo.SSN_encr
 
  WHERE      CONVERT(VARCHAR, DECRYPTBYKEY(ssn_encr))  = @ssn
  AND dbo.ufn_GroupData(@ssn,10000) = SSN_HG

  
  CLOSE SYMMETRIC KEY HGssnkey;
go