Geeks With Blogs
Geekette Mai Blog

 

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 Posted on Tuesday, June 27, 2006 4:53 PM | Back to top


Comments on this post: Encryption.sql in SQL Server 2005

# re: Encryption.sql in SQL Server 2005
Requesting Gravatar...
These a very good script...thanks
Left by Stu Grossman on Jun 29, 2006 6:47 PM

# re: Encryption.sql in SQL Server 2005
Requesting Gravatar...
These a very good script...thanks
Left by Stu Grossman on Jun 29, 2006 6:48 PM

# re: Encryption.sql in SQL Server 2005
Requesting Gravatar...
Thank you for new tip...
Left by Linda Green on Jun 29, 2006 6:49 PM

# re: Encryption.sql in SQL Server 2005
Requesting Gravatar...
Hey Geekette,

How did you come up with the name Geekette? It sound ver Geeky. Anyhow, I tested this and it work.
Left by John Nguyen on Jul 06, 2006 3:58 PM

Your comment:
 (will show your gravatar)


Copyright © Mai Nguyen | Powered by: GeeksWithBlogs.net