Step by step transparent table encryption with XP_CRYPT 3.5
using VIEWs and INSTEAD OF triggers.
Part 1
Introduction
This tutorial demonstrates how to encrypt
important data stored in your MSSQL Server database with strong,
hacker-proof RSA encryption. All code showed in examples is written
with native MSSQL language - T-SQL. Here you can find all ready-to-use
scripts, and you can start working with them immediately. It shows you
how to work with new XP_CRYPT in the most effective way and how to use
it in triggers and for searching encrypted values.
If you need a free copy of XP_CRYPT tool for MS SQL Server, you can
get it here
The last version of the XP_CRYPT introduces new features, such as
bulk encryption and password caching. This tutorial teaches how to get
the maximum performance from using it with INSTEAD OF triggers and
user defined functions.
Bulk encryption is the way of encrypting multiple variables (even
with different types) into one encrypted block. By using this
method, you achieve an increase in the speed of encryption performance
and greatly reduce space required for the encrypted values. RSA
encryption adds some random data (padding) to every variable to make
bruteforce decryption harder. Besides, by the nature of RSA encryption
and base64 encoding the minimum length of the output value will be
more then the length of the used key in bytes. For example if you use
512 bit key the minimum output will be longer then 512 / 8 = 64 chars.
With bulk encryption you can encrypt many values at once so the
overhead will be much smaller.
Another newly introduced feature is password caching. This gives
you an ability to work with encrypted tables as if they were not
encrypted. Your SQL queries do not need to be modified! You should
only specify the password once at the beginning of the session and
that's all. You can write decrypting stored procedures and triggers
without providing the correct password! Earlier, the only choice was
'WITH ENCRYPTION' tag on such triggers or procedures, but this is not
convenient because this code becomes non-editable.
Let's consider a real life example - protecting table that stores
some valuable information.
This tutor
assumes that you are using a free version of XP_CRYPT that's why the
number is small and values of the variables are short. The full
version does not have such size limitations
Creating key pair
Creating key pair is simple and fast:
DECLARE @PrivateKey VARCHAR (10)
DECLARE @PublicKey VARCHAR (10)
EXEC master..xp_rsa_generate_couple '256', @PrivateKey OUTPUT, @PublicKey OUTPUT
EXEC master..xp_rsa_save_key @PublicKey , 'c:\xp_crypt\demo\publickey.pem'
EXEC master..xp_rsa_save_key @PrivateKey , 'c:\xp_crypt\demo\privatekey.pem', 'xp_crypt'
EXEC master..xp_rsa_free_key @PublicKey
EXEC master..xp_rsa_free_key @PrivateKey
GO
A pair of 256 bits RSA keys is created and saved
into two separate files. After saving, it was unloaded from memory.
Places, where you need encryption only, you can have just a public
key. For decryption, you must have a private key and a password for
loading it.
Creating source table
We will use a sample table called users. It has the
following fields:
user_id INT
username VARCHAR(5)
secretvalue SMALLINT
Here is an SQL script to create such table
CREATE TABLE [dbo].[users] (
[user_id] [INT] IDENTITY (1, 1) NOT NULL ,
[username] [VARCHAR] (5) NOT NULL ,
[secretvalue] [SMALLINT] NOT NULL
) ON [PRIMARY]
GO
Let's populate the table with some data
INSERT INTO users(username,secretvalue) VALUES ('james',1234)
INSERT INTO users(username,secretvalue) VALUES ('lucas',1976)
INSERT INTO users(username,secretvalue) VALUES ('anna',1979)
Suppose now, that in real life the table we have has much more
fields associated with a user and the table is quite big. So, you
choose not to encrypt each value separately, but encrypt them all at
once.
Suppose also that in future your system will have to search users
by their names or emails or by any other field. In our simple example
it will be a username
field.
The problem with a search is that SQL server would like to have
data decrypted when it searches for clear text data. It seems that the
only way to do it is to decrypt data and compare it with a search
value. Now what if we have a table with million records? The search on
this table with this method will be incredibly slow. Luckily, there is
a better approach - using hashes. We must store the hash of the clear
text values, as they make an index on this hash. Later, we will use
this field as a search criterion.
Creating table for encryption
Now, assuming all of the above, let's create a
table that holds encrypted information.
- We will keep user_id
unencrypted,
- username and secretvalue
fields must be encrypted.
- Searching field username
must be possible
- Insertion and select statements must be as simple as possible
Here is a table we created that meets all of the above
requirements:
CREATE TABLE [dbo].[users_enc] (
[user_id] [INT] IDENTITY (1, 1) NOT NULL ,
[encryptedvars] [VARCHAR] (60) NOT NULL ,
[username_srch] [CHAR] (7) NOT NULL
) ON [PRIMARY]
GO
To simplify the work with the encrypted tables in
future we must do some additional work now. The best way would be to
add view here and define INSTEAD
OF INSERT trigger.
Creating view
For now we created a simple view without decryption (we will improve
it later).
CREATE VIEW dbo.users_enc_view
AS
SELECT user_id, username ,secretvalue FROM dbo.users
GO
Creating encryption trigger
This view currently shows decrypted values.
Currently we do not have decryption functions here but we need to
insert values with the correct size and type so that SQL Server can
handle them correctly. Now, let's add INSTEAD
OF INSERT trigger so that all values that we insert
to this view are automatically encrypted with our public key.
CREATE TRIGGER encrypt_fields ON [dbo].[users_enc_view]
INSTEAD OF INSERT
AS
INSERT users_enc(encryptedvars,username_srch)
SELECT dbo.encrypt_pair(username,secretvalue),dbo.make_short_hash(username) FROM INSERTED
GO
This trigger modifies the data passed by
inserting command; also it inserts modified values into users_enc
table. It can handle single and multiply inserts
Now, the encrypt_pair
UDF used in the last trigger will take a pair of values VARCHAR and
SMALLINT and output VARCHAR(60) as encrypted string. Change path to
the one you generated previously. Note, that the first char of the
path is < symbol - do
not remove it. It gives a command to the encryption function that it
must load the key from the file itself. .
CREATE FUNCTION encrypt_pair (@username VARCHAR(5) , @secretvalue SMALLINT )
RETURNS VARCHAR (60) AS
BEGIN
DECLARE @encryptedvars VARCHAR(60)
EXEC master..xp_rsa_pub_enc @username,@secretvalue,'<c:\xp_crypt\demo\publickey.pem',
@encryptedvars OUTPUT
RETURN @encryptedvars
END
Another UDF that we've used in encrypted_fields
is make_short_hash. We will use this
function to produce hashes for search on encrypted values. This
function returns a part of hash to make bruteforcing harder. More
about search you will find in the end of tutorial.
CREATE FUNCTION make_short_hash (@name VARCHAR(100))
RETURNS CHAR(2) AS
BEGIN
DECLARE @hash VARCHAR (32)
EXEC master..xp_md5 @name,@hash OUTPUT
RETURN substring (@hash,1,2)
END
Testing encryption
From now on, users_enc_view can
handle inserts, encrypt them and store them in the table users_enc.
Here we copy and encrypt on-the-fly all old table users into users_enc_view.
Remember that the view virtually represents the actual storage place -
table users_enc
INSERT INTO users_enc_view([user_id],username,secretvalue) SELECT * FROM users
Or you can try to fill out the table manually:
INSERT INTO users_enc_view(user_id,username,secretvalue) VALUES (-1,'james',12345)
INSERT INTO users_enc_view(user_id,username,secretvalue) VALUES (-1,'lucas',1976)
INSERT INTO users_enc_view(user_id,username,secretvalue) VALUES (-1,'anna',1979)
Note that we still have to mention some unused
fields like user_id
even if they are not used inside the trigger. Let's
take a look what we have in the encrypted table now
user_id |
encryptedvars |
username_srch |
1 |
DiHCcdpiVPhvOFOE8sQNquF8OhrKB8L6x5DZwWKieV0= |
b4 |
2 |
DfTpwx686yndRNUh6wFDBNH7NO+Tx9jNEFVPSYBoGqw= |
dc |
3 |
lyTjLNXaNFzy+7x8p1p4OJLM+jq9xtYjF7dvrgk7jxs= |
a7 |
In the future, we will select decrypted information from the same
view we insert data from. Currently, this view does not have a
decryption trigger.
You will have different encryptedvars
values, but username_srch
field should be the same. We intentionally do not use
the whole hash value in the trigger because this would be a possible
security leak. Someone could use the hash value for bruteforcing. When
we cut off a part of hash, we increase the number of possible
collisions but also it increases the number of fake
"successful" combinations. You should experimentally set
this length according to the size of your table. The larger table you
have and the quicker and sharper search you need, the longer hash you
should use.
Part 2
Creating decryption functions
Now let's modify the view to decrypt data from
this table. Remember, in a view you can not use a complicated login
like the ones in user defined functions or procedures. Only select a
valid statement. We have 2 different types of encrypted variables -
SMALLINT and VARCHAR. First, we need 2 different UDFs, the first one
decrypts the string and returns a first variable and the second one
returns the second variable.
CREATE FUNCTION username_decrypt (@encryptedvars VARCHAR(60))
RETURNS SMALLINT AS
BEGIN
DECLARE @result INT
DECLARE @k VARCHAR (10)
DECLARE @username VARCHAR(5)
DECLARE @secretvalue SMALLINT
EXEC @result =master.. xp_rsa_priv_dec @encryptedvars, '<c:\xp_crypt\demo\privatekey.pem',
@username OUTPUT, @secretvalue OUTPUT,'xp_crypt'
IF @result = 0
BEGIN
RETURN @username
END
RETURN NULL
END
And the second function
CREATE FUNCTION secretvalue_decrypt (@encryptedvars VARCHAR(60))
RETURNS SMALLINT AS
BEGIN
DECLARE @result INT
DECLARE @k VARCHAR (10)
DECLARE @username VARCHAR(5)
DECLARE @secretvalue SMALLINT
EXEC @result =master.. xp_rsa_priv_dec @encryptedvars, '<c:\xp_crypt\demo\privatekey.pem',
@username OUTPUT, @secretvalue OUTPUT,'xp_crypt'
IF @result = 0
BEGIN
RETURN @secretvalue
END
RETURN NULL
END
It is not really effective to perform decryption
for each variable, we can decrypt everything at once, but if we want
to have it with a view, we have to do it in this way. Alternatively,
if having decryption in view is not important for you, you can put
decryption in the stored procedures and use cursors
Modifying the view
Now, we modify the view and change the old
selected unencrypted values with our new functions. You can modify
view from enterprise manager by clicking on view users_enc_view and
choosing 'Design view' menu. Replace old statement with the new one:
SELECT user_id, dbo.username_decrypt(encryptedvars) AS username,
dbo.secretvalue_decrypt(encryptedvars) AS secretvalue FROM dbo.users_enc
Now, select from this view
SELECT * FROM users_enc_view
If you did everything correctly you should have the output:
user_id |
username |
secretvalue |
1 |
james |
12345 |
2 |
lucas |
1976 |
3 |
anna |
1979 |
Works great!
Applying password caching
A small note about security: if you attentively look into those
two decryption functions, you will notice that the password is
stored in a clear text there. There are two possible solutions to
fix this: the first one is to use WITH
ENCRYPTION statement with CREATE FUNCTION to
create an encrypted function. The text of this function will be
encrypted. However, there are some tools in the Internet that can
decrypt such functions quickly. Another problem with this way is
that once having encrypted your data you cannot edit the text of
this function. You must always keep the text somewhere else, which
is not really convenient. Another solution for the problem of the
opened password with XP_CRYPT is using a new feature called
"password caching". The idea is the following, in publicly
visible functions you use password '?'. If the session option 'pswd_cache'
is set to 'yes', XP_CRYPT will search for the password for this
source in the current session. Passwords are cached during loading
and saving keys to string or files. By default, this feature is set
to 'no'.
Let's demonstrate how it works. Edit the above
functions in your SQL manager so that instead of the password 'xp_crypt'
they use password '?'. Try to select it from the view again. You
will get NULLs in all encrypted values. Now we need to put password
into hash; simply load and free the privatekey:
DECLARE @privatekey VARCHAR (10)
EXEC master..xp_crypt_set_option 'pswd_cache','yes'
EXEC master..xp_rsa_load_key '<c:\xp_crypt\demo\privatekey.pem', @privatekey OUTPUT, 'xp_crypt'
EXEC master..xp_rsa_free_key @privatekey OUTPUT
You have preloaded the password now. Try to
select it from view, you should see decrypted values. The
session has a default idle timeout of 5 minutes. You can override this
value with xp_crypt_set_option function. If the time expires, all keys are unloaded
from memory and password cache is cleaned. If you explicitly close the
session with xp_crypt_close_session
- decryption becomes impossible again.
How to search
As you see, encryption and decryption works, what about search ? It
would be nice to have something like
SELECT * FROM users_enc_view where username = 'user'
But unfortunately MSSQL Server does not provide INSTEAD OF SELECT
trigger, so the maximum comfort you can achieve with
SELECT * FROM users_enc_view where username_srch = make_short_hash ('user')
We only miss the make_short_hash UDF. Let's
create it. We made MD5 hash and used only first 2 chars like we did in
encrypt_fields trigger.
Try it now :
SELECT * FROM users_enc_view where username_srch = make_short_hash ('anna')
This statement shows the corresponding record but searching for
'john' will give us nothing. Please do not rely on 2 char hash; it's
possible that you get wrong match between a real value and a search
value. For this size of table, 2 chars is a reasonable length.
We hope this tutorial helped you to understand how to
use XP_CRYPT effectively. Now you can see how these easy methods can
greatly improve the data safety.
|