Empress Technical News – November 2008

                                                Database Encryption - Safeguarding Confidential Data – Part 2
                                                 Database With Encryption – Using Where Clause and Index

Introduction

Empress Database with Encryption that was shown in the September 2008 Technical News is used to show what happens when using an index and a “where clause” on encrypted data. The objective is to show that Empress Database with Encryption does two things. First, it can index encrypted data and select it. Second, it can select encrypted data from a table based on a “where clause”. All this can be done without any need to modify the SQL commands that are used for non-encrypted data.

In this example, the Linux system is Ubuntu Linux and the encryption algorithms are part of the “libgcrypt” library. This “libgcrypt” library is a standard part of most 2.6 Linux systems such as Red Hat Enterprise Linux, Novell Linux, Ubuntu, etc, and implements encryption algorithms such as AES (Advanced Encryption Standard) using key sizes of 128, 192 or 256 bits.

Preparing the EncryptionEnvironment and Database

Log onto a Linux system that has the Empress Database with encryption option installed. Type in “pwd” to find out what directory you are in. In this case it is “/home/alex”.

alex@knopit:~$ pwd
/home/alex

Use the Empress environment variable “MSCIPHERKEYINFO” to associate the new database name “testdb” with a short hexadecimal encryption key “74657374696e6731”. You can choose the most appropriate database name and encryption key.

alex@knopit:~$ MSCIPHERKEYINFO="/home/alex/testdb":74657374696e6731
alex@knopit:~$ export MSCIPHERKEYINFO
alex@knopit:~$ echo $MSCIPHERKEYINFO
/home/alex/testdb:74657374696e6731

Nowcreate the database “testdb” in “/home/alex” using the “empmkdb” command with the “cipher” option set to “AES256”. Encryption and decryption on this database will be performed using the Advanced Encryption Standard (AES) algorithm with a key size of 256 bits (32 bytes or 64 hexadecimal digits) through the “libgcrypt” library. For illustrative purposes only, we use a shorter and less secure key of 16 hex digits.

alex@knopit:~$ empmkdb -cipher AES256 testdb 

Start the Database and Create Two Tables, Hello1 & Hello2

Start up Empress Interactive SQL using the “empsql” command on the database “testdb”. The Interactive SQL prompt should appear.

alex@knopit:~$ empsql testdb
   EMPRESS V8.62 (Evaluation copy: licence will expire on Oct 29, 2009)
  (c) Copyright Empress Software Inc. 1983, 2008

1*

Create two new tables called “Hello1” and “Hello2” with two fields called “Name” and “Message”. “Name” will store 10 characters and “Message” will store 15 characters. The “Message” field in “Hello2” is also specified as “encrypted”.

1* create table Hello1 (Name character (10), Message character (15));
2* create table Hello2 (Name character (10), Message character (15) encrypted);

Store Values &Create Index on Two Tables, Hello2 Encrypted

Store three values for “Name” and “Message” into the “Hello1” and “Hello2” tables using the “insert” command. Use “Alex”, “Tom” and “Sam” for “Name” and “Hello World”, “Good Day” and “Welcome” for “Message”.

3* insert into Hello1 values ("Alex", "Hello World");
4* insert into Hello1 values ("Tom", "Good Day");
5* insert into Hello1 values ("Sam", "Welcome");
6* insert into Hello2 values ("Alex", "Hello World");
7* insert into Hello2 values ("Tom", "Good Day");
8* insert into Hello2 values ("Sam", "Welcome");

Create an index on “Messages” in both tables “Hello1” and “Hello2”.

9* create unique index Hello1x on Hello1( Message );
10* create unique index Hello2x on Hello2( Message );

Select ALL Data from TwoTables, Hello2 Encrypted

Select all the data from the “Hello1” and “Hello2” tables. The Name “Alex”, and the Message “Hello World” followed by “Sam” and “Welcome” is printed on the terminal. The Messages in “Hello2” are the same as in “Hello1” even though “Hello2” Messages are encrypted at the file level.

11* select * from Hello1;
Name        Message
Alex          Hello World
Tom          Good Day
Sam          Welcome

12*select * from Hello2;
Name        Message
Alex          Hello World
Tom          Good Day
Sam         Welcome

Select Using a Where Clause from Two Tables, Hello2 Encrypted

Selectall the data from the “Hello1” and “Hello2” tables where the “Message” is equal to “Welcome”. The Message in “Hello2” is the same as “Hello1” even though “Hello2” is encrypted.

13* select * from Hello1 where Message = 'Welcome';

Name        Message
Sam         Welcome

14*select * from Hello2 where Message = 'Welcome';

Name        Message
Sam         Welcome

Show All Properties of Two Tables, Hello2 Encrypted

Show all properties of the “Hello1” and “Hello2” tables using the “display table” command with the option “all”. Note that the “Hello1” table is shown as Table #  6 by the “display table all” command and that the “Hello2” table is shown as Table #  7. In Empress, all tables are stored as files named “nnnn.rel”, so the “Hello1” table would be a file in the database named “0006.rel” and the “Hello2” table would be a file named “0007.rel”.

15* display table Hello1 all;

***  Table: Hello1  ***

  Attributes:

    Name                             character(10,1)
    Message                        character(15,1)

  Creator:     alex
  Indices:     UNIQUE BTREE Hello1x ON (Message)
  Lock Level:  RECORD

  Table #:            6
  Records:           3
  Record size:   26
 

16* display table Hello2 all;

***  Table: Hello2  ***

  Attributes:

    Name                              character(10,1)
    Message                         character(15,1)  Encrypted

  Creator:     alex

  Indices:     UNIQUE BTREE Hello2x ON (Message)
  Lock Level:  RECORD
  Table #:     7
  Records:     3
  Record size: 27

Verify that the Index Data is Encrypted

Use theLinux escape “!” in Empress SQL to do a directory listing on “testdb”.
17* ! ls testdb
00010001.ix   00020009.dtf  00020016.dtf  00030013.dtf  00040002.ix   0005.rel     cdinator  tabzero
00010005.dtf  00020010.dtf  00020017.dtf  00030014.dtf  00040002.ixl  00060001.ix  dd_cache  _trn
0001.rel      00020011.dtf  0002.rel      0003.rel      0004.rel      0006.rel     _index2
00020001.ix   00020012.dtf  00030001.ix   00040001.ix   00050001.ix   00070001.ix  _lock
00020008.dtf  00020013.dtf  00030001.ixl  00040001.ixl  00050001.ixl  0007.rel     _module

Use the Linux escape to do a grep for the word “Welcome”. Since “Hello1” is not encrypted on “Message”, we would expect to have grep find the word “Welcome” in the data file, 0006.rel, and the index file, 00060001.ix. Since “Hello2” is encrypted on “Message”, we would expect that grep will not find the word “Welcome” in the data file, 0007.rel or the index file 00070001.ix.

18* !grep Welcome testdb/*
Binary file testdb/0006.rel matches
Binary file testdb/00060001.ix matches

And this indeed is what happens.

All the SQL commands, other than the initial “create table” to specify which field is encrypted is exactly the same for both encrypted table and the non-encrypted table. The output of the select commands is also identical. The difference of the time to do the select commands between the encrypted table and the non-encrypted table is insignificant.

A Simple Example With More Data, Hello2 Encrypted

A “C” program was written to add 1,000,000 records to the two tables, “Hello1” and “Hello2”. The Name data was a sequence of 1 million values of the form “A0000nnnnnnn”.  The Message data was a sequence of 1 million values of the form “MSG00000nnnnnnn”. For each data pair, the values of “nnnnnnn” were the same.

The “C” program stub below was used as the starting point to generate the data and insert it into the two tables. Note: Only part of the whole “C” program is shown for brevity. Please email Empress ivy.wong@empress.com to request a copy of the complete program.

printf ("Want to add %d records to table '%s'\n", total, table_name);

        for (i = 0; i < total; i++)
        {
                sprintf (buf1, "A%9.9d", i);
                if (!mrputvs (rec_desc, attr1_desc, buf1))
                {
                        fprintf (stderr, "Failure:mroperr:%d => %s\n",
                                mroperr, mrerrmsg());
                        break;
                }
                sprintf (buf2, "MSG%11.11d", i);
                if (!mrputvs (rec_desc, attr2_desc, buf2))
                {
                        fprintf (stderr, "Failure:mroperr:%d => %s\n",
                                mroperr, mrerrmsg());
                        break;
                }
                mradd (rec_desc);
                if ((i % 10000)== 0)
                        printf ("Inserted %d\n", i +1);
        }

EmpressSQL was invoked from the “shell” command so that the “time” command could be used to get timing values.

          alex@knopit:~$  time empcmd testdb "select * from Hello1 where Message = 'MSG00000900033'"

Name        Message

A000900033  MSG00000900033

real    0m0.009s
user    0m0.000s
sys     0m0.008s

alex@knopit:~$ time empcmd testdb "select * from Hello2 where Message = 'MSG00000900033'"

Name        Message

A000900033  MSG00000900033

real    0m0.009s
user    0m0.000s
sys     0m0.008s

Even with 1,000,000 records, there was little real difference in this example between the “select” times from the encrypted table “Hello2” and the non-encrypted table “Hello1”.

Appendix 1: A List of Commands Used in This Technical Note

The following may be cut and pasted to repeat the examples. You will need to change the database path location to suit your computer login. You may want to change the database name and the 16-digit hexadecimal encryption key as well.

pwd
MSCIPHERKEYINFO="/home/alex/testdb":74657374696e6731
export MSCIPHERKEYINFO
echo $MSCIPHERKEYINFO
empmkdb -cipher AES256 testdb
empsql testdb
create table Hello1 (Name character (10), Message character (15));
create table Hello2 (Name character (10), Message character (15) encrypted);
insert into Hello1 values ("Alex", "Hello World");
insert into Hello1 values ("Tom", "Good Day");
insert into Hello1 values ("Sam", "Welcome");
insert into Hello2 values ("Alex", "Hello World");
insert into Hello2 values ("Tom", "Good Day");
insert into Hello2 values ("Sam", "Welcome");
create unique index Hello1x on Hello1 (Message);
create unique index Hello2x on Hello2 (Message);
select * from Hello1;
select * from Hello2;
select * from Hello1 where Message = "Welcome";
select * from Hello2 where Message = "Welcome";
display table Hello1 all;
display table Hello2 all;
! ls testdb
! grep Welcome testdb/*

Empress Software Inc.
www.empress.com