Set up sample encryption database
USE
-- Set up three logins
CREATE
LOGIN User1 WITH password = '3f@$fWDY3QvP&K0'
GO
CREATE
GO
USE
CREATE
CREATE
TABLE Customer (
CustId int,
Name nvarchar(30),
City varchar(20),
CreditCardType varbinary(1000),
CreditCardNumber varbinary(1000),
Notes varbinary(4000))
GO
-- Grant access to user
GRANT
-- **************************************************************************
-- DATABASE MASTER KEY
-- **************************************************************************
USE
-- Databases do not have a master key by default, so you must create it
-- before you can use it:
CREATE
MASTER KEY ENCRYPTION BY PASSWORD = 'gK#3hbQKDFQY0oF'
-- Create an asymmetric key to protect the new symmetric key
CREATE
AUTHORIZATION User1
WITH ALGORITHM = RSA_2048
-- **************************************************************************
-- SYMMETRIC KEYS
-- **************************************************************************
CREATE
WITH ALGORITHM = RC4
ENCRYPTION BY ASYMMETRIC KEY User1AsymmetricKey
-- List the symmetric keys in the database
SELECT
-- Insert data, using various asymmetric keys. If the symmetric key isn't
-- open, no error and no data inserted.
* FROM sys.symmetric_keys
OPEN
DECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey
INSERT
INTO Customer VALUES (1, 'Sally Roe', 'Chatinika',
EncryptByKey(Key_GUID('User1SymmetricKey'), 'Visa'),
EncryptByKey(Key_GUID('User1SymmetricKey'), '1234-5678-9009-8765'),
EncryptByKey(Key_GUID('User1SymmetricKey'),
'One of our best customers. Treat like royalty.'))
CLOSE
-- View the new data entered using symmetric keys.
SELECT
-- View the data. Note that you don't have to specify which symmectric key
-- to use to decrypt data, even though multiple symmetric keys can be open.
OPEN
DECRYPTION BY ASYMMETRIC KEY User1AsymmetricKey
SELECT
CustID, Name, City,
CONVERT(VARCHAR, DecryptByKey(CreditCardType)) AS CardType,
CONVERT(VARCHAR, DecryptByKey(CreditCardNumber)) AS CardNumber,
CONVERT(VARCHAR, DecryptByKey(Notes)) AS Notes
FROM
CLOSE
-- **************************************************************************
-- Encryption Catalog Views
-- **************************************************************************
-- Existing keys
SELECT
* FROM sys.certificates
SELECT
* FROM sys.asymmetric_keys
SELECT
* FROM sys.symmetric_keys
SELECT
* FROM sys.database_principals
SELECT
* FROM sys.key_encryptions
SELECT
-- Information about keys
-- "Returns a row for each symmetric key encryption specified using the
-- ENCRYPTION BY clause of the CREATE SYMMETRIC KEY statement."
* FROM sys.crypt_properties
SELECT
-- "Returns one row for each cryptographic property associated with a securable"
-- None in this database
* FROM sys.key_encryptions
SELECT
-- **************************************************************************
-- Clean Up Samples
-- **************************************************************************
* FROM sys.crypt_properties
USE
GO
DROP
DROP
LOGIN User1 DATABASE EncryptionDB master SYMMETRIC KEY User1SymmetricKey Customer
SYMMETRIC KEY User1SymmetricKey * FROM Customer SYMMETRIC KEY User1SymmetricKey
SYMMETRIC KEY User1SymmetricKey SYMMETRIC KEY User1SymmetricKey ASYMMETRIC KEY User1AsymmetricKey EncryptionDB SELECT,INSERT on Customer to User1
USER User1 FOR LOGIN User1 EncryptionDB DATABASE EncryptionDB master
Tuesday, June 27, 2006 4:53 PM