Company

Products

Services

Partners

Media

 

 

 

Home

Empress Markets

News & Events


Presentations

Press Releases

Product Profile

Success Stories

Technical News


White Papers

Join Mailing List

For More Info

         

Empress Technical News -  Jan 2010

        Database Encryption - Safeguarding Confidential

Data in Microsoft Windows Environments

Database with Encryption – Using Microsoft CryptoAPI

 

Introduction

Empress Database with Encryption in the September 2008 Technical News was used to show what happens when using an index and a “where clause” on encrypted data. The objective was 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 that example, the Linux system was 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.

 

In Microsoft Windows environments it is also possible to use the “libgcrypt” library.   However, it is far more appropriate to use native Microsoft cryptographic abilities. Microsoft cryptographic technologies include CryptoAPI, CryptoAPI Tools, Cryptographic Service Providers (CSP) and others. Empress has interfaced CryptoAPI calls in order to encrypt/decrypt data at rest in its database.

 

 

Preparing the Encryption Environment and Database

Log onto a Microsoft Windows system that has an Empress Database with encryption option installed. We will use Visual Studio 2005 Command Prompt for this demonstration of Empress capabilities. Other shell based environments could also be used such as Windows PowerShell or Cygwin Bash Shell to accomplish similar tasks.

 

Change folder to \tmp to use this space for demonstration:

 

$ C:

$ cd \tmp

Use the Empress 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.

 

$ set MSCIPHERKEYINFO=" C:\tmp\testdb”:74657374696e6731

 

$ echo %MSCIPHERKEYINFO%

“C:/tmp/testdb”:74657374696e6731

 

 

Now create the database “testdb” in “\tmp” using the “empmkdb” command with the “cipher” option set to “MS-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 Microsoft Cryptographic Service provider. For illustrative purposes only, we use a shorter and less secure key of 16 hex digits.

 

$ empmkdb -cipher MS-AES256 testdb

 

There are other ciphers that potentially could be utilized. The following empadm -help utility shows available ciphers in the Microsoft Windows environment:

$ empadm -help
*** Usage ***    empadm  [option] DATABASE command
where option is:
-help                                        displays this message and exit
and command is one of the following:
coordinfo                                reports coordinator information

setcipher CIPHER                  set CIPHER to encrypt/decrypt assigned attributes
                                                                              value. Available ciphers:

                                                            COPY MS-AES128 MS-AES192 MS-AES256 MS-DES
                                                            MS-RC2 NONE SIMPLE

 

 

 

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.

 

$ empsql testdb

  EMPRESS V10.20

  (c) Copyright Empress Software Inc. 1983, 2010

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 Two Tables, 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

Select all 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 # 5 by the “display table all” command and that the “Hello2” table is shown as Table In Empress, all tables are stored as files named “nnnn.rel”, so the “Hello1” table would be a file in the database named “0005.rel” and the “Hello2” table would be a file named “0006.rel”.

 

15* display table Hello1 all

***  Table: Hello1  ***

 

  Attributes:

      Name                                                              character(10,1)

      Message                                                   

 

  Creator:        srdjan

  Indices:        UNIQUE BTREE Hello1x ON (Message)

  Lock Level:  RECORD

 

  Table #:        5

  Records:        3

Record size: 26

 

 

16* display table Hello2 all

***  Table: Hello2  ***

 

  Attributes:

      Name                                                              character(10,1)

      Message                                                    Encrypted

 

  Creator:        srdjan

  Indices:        UNIQUE BTREE Hello2x ON (Message)

  Lock Level:  RECORD

 

  Table #:        6

  Records:        3

  Record size: 27

 

 

Verify that the Index Data is Encrypted

Use the operating system escape “!” character in Empress SQL to do a folder listing on “testdb”.

 

17* ! DIR testdb

2010/01/22                        1,678 0001.rel

2010/01/22                        3,072 00010001.ix

2010/01/22                      10,240 00010005.dtf

2010/01/22                        2,206 0002.rel

2010/01/22                        3,072 00020001.ix

2010/01/22                        1,374 00020008.dtf

2010/01/22                        1,024 00020009.dtf

2010/01/22                        1,024 00020010.dtf

2010/01/22                        1,024 00020011.dtf

2010/01/22                        1,024 00020012.dtf

2010/01/22                        1,024 00020013.dtf

2010/01/22                        1,024 00020016.dtf

2010/01/22                        1,024 00020017.dtf

2010/01/22                        8,908 0003.rel

2010/01/22                        3,072 00030001.ix

2010/01/22                        1,120 00030001.ixl

2010/01/22                        1,024 00030013.dtf

2010/01/22                        1,024 00030014.dtf

2010/01/22                        1,648 0004.rel

2010/01/22                        3,072 00040001.ix

2010/01/22  15:53                        1,024 00040001.ixl

2010/01/22  15:59                        3,072 00040002.ix

2010/01/22  15:53                         1,024 00040002.ixl

2010/01/22  16:01                        1,102 0005.rel

2010/01/22  16:01                        3,072 00050001.ix

2010/01/22  16:01                        1,105 0006.rel

2010/01/22                        3,072 00060001.ix

2010/01/22                      15,684

2010/01/22                              48 dd_cache

2010/01/22                        1,210 tabzero.txt

2010/01/22      < DIR>                   _lock

2010/01/22      < DIR>                   _trn

 

Use the escape character to search for the word “Welcome”. Since “Hello1” is not encrypted on “Message”, we would expect search to find the word “Welcome” in the data file, 0005.rel, and the index file, 00050001.ix. Since “Hello2” is encrypted on “Message”, we would expect that search will not find the word “Welcome” in the data file, 0006.rel or the index file 00060001.ix.

 

To search for the word “Welcome” the Search facility in Windows environment after invoking Start button. Supply the following input:

 

Search by any or all of the criteria bellow

 

A word or phrase in the file:

Welcome

 

Look in:

testdb

 

And this indeed is what happens. The following image displays the Search Results for the defined query.

 

 

All the SQL commands, other than the initial “create table” to specify which field is encrypted are 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 negligible.

 

 

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.

 

C:

CD \tmp

SET MSCIPHERKEYINFO=" C:\tmp\testdb”:74657374696e6731

echo %MSCIPHERKEYINFO%

empmkdb -cipher MS-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

! DIR testdb

 

 

 

 

 

 

Empress Software Inc.

www.empress.com

 

                       
                             
                         

Company
Information
GSA Contracts
Careers
Privacy Policy 
Contact US

Products
Empress RDBMS
Empress Servers
Empress API
Free Trial 
Empress iPad Apps
 

Services
Consulting
Mobile Apps Development
Technical Support

Training Courses
Empress Extreme  

Partners
Distributors
Business Alliances
Partner Programs  
GSA Program
Distributors Program

Media 
Empress Markets
News and Events
Press releases
Technical News
Success Stories

        USA: 301-220-1919                 Canada & International:       905-513-8888         
Copyright © 2014           Empress Software Inc.           info@empress.com           Join Empress Mailing List