
CREATE LOGIN User1 WITH PASSWORD = 'StrongPW1'
CREATE LOGIN User2 WITH PASSWORD = 'StrongPW1'
CREATE LOGIN Admin1 WITH PASSWORD = 'StrongPW1'
GO

USE pubs
GO

-- need master key for this
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPW1'
GO

CREATE USER User1 FOR LOGIN User1
CREATE USER User2 FOR LOGIN User2
CREATE USER Admin1 FOR LOGIN Admin1
sp_addrolemember 'db_owner', 'Admin1'
GO

-- Generate a certificate for user1 and user2
CREATE CERTIFICATE user1cert 
   AUTHORIZATION User1 WITH SUBJECT = 'Cert for User1'
CREATE CERTIFICATE user2cert 
   AUTHORIZATION User2 WITH SUBJECT = 'Cert for User2'
GO
-- symmetric keys to be used for encryption
-- they are faster than certificates
CREATE SYMMETRIC KEY Key1 AUTHORIZATION User1 
  WITH ALGORITHM = TRIPLE_DES  ENCRYPTION BY CERTIFICATE user1cert
CREATE SYMMETRIC KEY Key2 AUTHORIZATION User2 
  WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE user2cert
GO

-- The primary key and name data is public
-- The secret_data column is encrypted

CREATE TABLE dbo.secret_table (
  id INT PRIMARY KEY IDENTITY,
  first_name VARCHAR(20),
  last_name VARCHAR(50),
  secret_data VARBINARY(8000)
)
GO
GRANT INSERT, SELECT ON dbo.secret_table TO User1, User2
GO

-- listing 6-4
-- sample after calculateCipherLen

-- insert rows into secret_table, using encryption
EXECUTE AS USER='user1'
OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert;
INSERT dbo.secret_table VALUES('Joe', 'User',
  EncryptByKey(Key_GUID('Key1'),'some secret number1'))
CLOSE SYMMETRIC KEY Key1
GO
REVERT
GO

EXECUTE AS USER='user2'
OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert;
INSERT dbo.secret_table VALUES('Jill', 'Smith',
  EncryptByKey(Key_GUID('Key2'),'some other secret number2'))
CLOSE SYMMETRIC KEY Key2
GO
REVERT
GO

-- listing 6-5

EXECUTE AS USER='user1'
OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert
-- you get the encrypted value
SELECT id, first_name, last_name, secret_data
  FROM secret_table
-- you either get the unencrypted value or NULL
SELECT id, first_name, last_name, 
       cast(decryptByKey(secret_data) AS VARCHAR(256))
  FROM secret_table
CLOSE SYMMETRIC KEY Key1
GO
REVERT
GO

EXECUTE AS USER='user2'
OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert
-- you get the encrypted value
SELECT id, first_name, last_name, secret_data
  FROM secret_table
-- you either get the unencrypted value or NULL
SELECT id, first_name, last_name, 
       cast(decryptByKey(secret_data) AS VARCHAR(256))
  FROM secret_table
CLOSE SYMMETRIC KEY Key2
GO
REVERT
GO

EXECUTE AS USER='admin1'
OPEN SYMMETRIC KEY Key1 DECRYPTION BY CERTIFICATE user1cert
OPEN SYMMETRIC KEY Key2 DECRYPTION BY CERTIFICATE user2cert
-- cyphertext
SELECT id, first_name, last_name, secret_data
  FROM secret_table
-- sees both rows decrypted
SELECT id, first_name, last_name, 
       cast(decryptByKey(secret_data) AS VARCHAR(256))
  FROM secret_table
CLOSE ALL SYMMETRIC KEYS
GO
REVERT
GO
