XP_CRYPT GUI. Code generation tool.
Quick Introduction
XP_CRYPT giu is a tool to simplify the creation of UDFs, triggers and views
for encryption with XP_CRYPT 4.0 and higher.
How to encrypt
XP_CRYPT supports 5 encryption algorithms: RSA (asymmetric algorithm), AES,
Triple DES, DESX and RC4 (symmetric algorithms). You can choose an algorithm
depending on your needs. However please note that RSA is a relatively slow
encryption comparing to symmetric algorithms.
To start protecting your data login to the database. We take database
"pubs" which is installed together with SQL Server and contains some
demo data about books and authors.
After successful login, you see a window with existing tables and a list of
encryption keys. Now it is empty.
Now, you need to choose which algorithm you want to use and create a key of
this type. Lets choose RC4 and RSA.
Choose "Add New Key" or press Ctrl-N, the following dialog will
appear:
Consider "Password Alias" as a name of the key. Actually this is a
name of variable that will hold a password in your SQL session.
The key is created, now, you must enter the password. The password will be
used to encrypt existing data in tables. Click on "Enter Password" and
enter "test" as password.
Green "A" sign means "Activated". This means you can
start using this key now.
We will encrypt first and last names of the authors. Those fields are
in "authors" table. Expand the tree and choose field "au_lname'.
Check "Encrypt Field" checkbox and choose encryption key in the
combobox.
"Choose Encryption" drop down menu shows keys created in "Keys
and Passwords" window. Currently there is only our RC4 key. Choose it and
press "Save" on the toolbar (Ctrl-S)
At this stage XP_CRYPT starts creating a schema needed for encryption. You
can see the log of the operation if you click on "Tables" on the left
tree.
XP_CRYPT added a field called "enc_au_lname" and filled it with
encrypted data from the field au_lname. We can see it if we select
some data.
select enc_au_lname from authors
enc_au_lname
------------------------------
ydSrcsidbufzu9XZ
ydSrcsidbvfpt8TS
ydSrccidbvP6oNLTBg==
ydSrcMidbv+8nsTdGhA=
ydSrf8idbuPvoMDVDwGw
XP_CRYPT created a VIEW which shows decrypted result. After your table is
encrypted, you should use this view to select and insert data. It will be
automatically encrypted. This VIEW has the same name as the table +
"View" at the end. You can rename it after it has been created.
Lets select from this view:
SELECT au_lname from authorsView
au_lname
----------------------------------------
NULL
NULL
NULL
Now, there are NULLs instead of the real data, because your SQL session does
not have the password. The password is set with XP_CRYPT function called
xp_crypt_set_var. We need to set the password "test" for a variable
"pubs.pass1". Actually this is the same password we entered at the step 3.
exec master..xp_crypt_set_var 'pubs.pass1','test'
select au_lname from authorsview
Output:
au_lname
----------------------------------------
White
Green
Carson
....
Your application needs to set the password only once. It will be kept in the
session. Try to select again without calling xp_crypt_set_var. It will work
until you close Query Analyzer or a timeout expires. Other users connected to
SQL Server cannot see this data. The password is set only for your session.
Now, lets create RSA key and encrypt the last name field.
Choose Asymmetric encryption , algorithm RSA, key size 1024, and
password alias "pass2". After you press ok, you will be asked for a
password, this password will be used to store the key is the table. Do not
forget this password. Lets enter rsatest as a password. Of course in real
life you should choose better passwords.
We see that the key is already activated and ready to use. If you want to look how
the key looks like, make a double click, you will see the public and the
private key. The public key is used for encryption and is stored unencrypted, the
private key should be kept securely and encrypted with Triple DES. Both keys are
in PEM format. If necessary, you can use them in another applications. In real
life you have to copy those keys elsewhere to avoid the loss.
Select fields you wan to encrypt. In our case it is au_fname and phone.
Choose RSA key for both fields and press Ctrl-S or choose "Save"
in menu.
Everything what is needed is created automatically, existing data is also encrypted.
NOTE: You may see that original table authors still have au_lname, au_fname
and phone fields which contain unencrypted information. XP_CRYPT GUI intentionally
DOES NOT DELETE those fields from the table. You must do it yourself
after you are sure that data is safe. We recommend you to backup the database
before encryption.
Test if everything works:
exec master..xp_crypt_set_var 'pubs.pass1','test'
exec master..xp_crypt_set_var 'pubs.pass2','rsatest'
select au_lname,au_fname,phone from authorsview
Output:
au_lname au_fname phone
---------------------------------------- -------------------- ------------
White Johnson 408 496-7223
Green Marjorie 415 986-7020
Carson Cheryl 415 548-7723
After you make sure that everything works you can delete the original fields
from source table. In our case au_lname, au_fname and phone
in table authors are not needed. Because they are stored in encrypted
fields. What you see in "select au_lname,au_fname,phone from authorsview"
is fields created by the view on-fly as a result of decryption.
System tables
During the work, XP_CRYPT gui creates 3 tables for every database which is
used to keep information about encrypted fields, triggers, views and so on:
xpcrypt_ENCRYPTED_FIELDS
xpcrypt_GENERATED_KEYS
xpcrypt_TABLES
You do not need to create those tables, they will be created automatically
after you login to the database from application.
Attention! Table xpcrypt_TABLES stores RSA and DSA keys, after you setup
encryption you must backup this table to avoid the loss of keys. If you lose RSA
or DSA keys, you will not be able to decrypt or validate your data. This tables
does not store passwords. Do not edit those tables manually. Keys are stored in tables which are created automatically for every database.
|
