Build a secure Multi-device Application Using Embedded InterBase ToGo

Posted by on in Programming

No One is immune

According to the Identity Theft Resource Center, in 2016 there have already been 522 reported breaches as of the middle of July, exposing more than 13 million records (that number does not include the majority of breaches that did not report number of records affected). While we have yet to see a mega-breach that will define the year, as the Office of Personnel Management, Target and Sony have in years past, trends have emerged in the year so far, including multiple attacks targeting W-2 information, federal agencies, health-care organizations and telecom providers. 

"eBay asks 145 million users to change passwords after data breach"

"Data Breaches Lead To Over 1 Billion Records Exposed In The First Half of 2016"

"IT HAPPENED AGAIN: Yahoo says 1 billion user accounts stolen in what could be biggest hack ever"


Graphic Source:

Data protection is a key worry for all businesses today. A data breach typically leads to 4% loss of your customer base with regulatory fines that can lead into millions of dollars and corrective action plans that require staff re-training, customer credit score monitoring etc. all on top. This all adds up to a rather expensive exercise best avoided. It is far better to build this in upfront rather than pay for it later.

InterBase Database Encryption

When it comes to data protection, InterBase offers role based authentication to control access to data inside the database however this is just the foundation and encryption at rest is key to safe storage of data and avoiding the bigger fines if you have a data breach.

InterBase offers encryption at rest with fast granular column level encryption across all supported paid platforms. When it comes to backing up data, InterBase includes strong encrypted backup in all paid editions.

How to Build a secure Multi-device Application Using Embedded InterBase ToGo

In this Part 1, we will show how to add database and column level encryption to an InterBase database.  Part 2 will show how to build a secure multi-device application that uses our encrypted InterBase database.

There are two ways to encrypt a database in InterBase. You can enable and implement encryption using isql:

1. Connect as SYSDBA and create embedded users:


2. Connect as SYSDSO and create the encryption keys, BUT first it is necessary to set the System 


    Encryption Password (SEP).  Up to 255 characters and may include spaces. 



or use the IBConsole to set the System Encryption Password (SEP):




or you can encrypt a database with IBConsole. For this Encrypting an InterBase Database topic, we'll use the second option - Encrypt a Database with IBConsole.

IBConsoleEncryptDB   IBConsoleEncryptDB_3

There are two levels of encryption in InterBaseDatabase level and Column-Level Encryption.

For database-level encryption, it’s used for encrypting the entire database using a single encryption key.


With Column-level Encryption, you can encrypt individual columns using different keys with different settings, including password protection.


For the Database Level Encryption, we'll create a database level encryption key called “db_level_key”.  We'll select Strong Encryption (AES) for the Cipher, a key length of 256, Init Vector and Padding as RANDOM.  This is the highest level of security, and it gives the fewest clues about our data. 




Now that we added Database Encryption to our Employee Database (EMPLOYEE_COL_ENCRYPT.IB) , next lets add Column Level Encryption to the Salary Column of the Employee Database.

 Column Level Encryptions

Next, we’ll log in again as Database owner, SYSDBA, and perform some Column Level Encryption.

Remember, SYSDBA is the only one that can perform encryption.

Select the EMPLOYEE table.  Looking at the data, we see we have a Salary column.

We want the Salary column to be more secure, so that not everyone that has access to the database can see Salary information.

Salary_Column    Salary_Data

Select Salary column >> Alter Selected Item

Select SALARY >> Edit Field

Add Col_Level_Key that we created earlier by SYSDSO.

There is a “Decrypt default” property.  We will set that to zero (0), so whenever a user without Decrypt permissions selects from the Salary column, rather than getting an error or “no permission for decrypt access for column Salary”, they will get the Decrypt default value of 0, in this case.



Encrypted notice shows up in the Encrypted column on the Table Editor and you can also see it on the Encryption column of the columns Properties.



ISQL with Column Level Encryption 

Now we want to see what happens when HR_USER connects to Employee table and tries to select from the database.

Connect As (Login) as HR_USER.

We get the full Salary, as expected for HR_USER, because HR_USER has decrypt permissions on the Salary column.


We get our Default value of 0 for Salary for REG_USER.



Salary_Data    Salary_Default


Salary_HR_USER        Salary_REG_USER


 Column Level Encryptions with Password Protection

Now, let’s suppose we want another level of security on the Salary column.

Connect as SYSDSO.

We want to create another Column Level Encryption key, called Col_Level_P, that will be password protected, meaning the user will need to provide a password to see the Salary data, even if the user has Decrypt permissions.




Disconnect as SYSDSO.

Connect as SYSDBA because we want to apply the new Password protected Encryption key to the Salary column.  Go to Salary field property editor and select our new “Col_Level_P” encryption key.



When you Click OK, you already get prompted for a Password for the Col_Level_P encryption key.

Enter your password and click OK.


Disconnect as SYSDBA and connect as HR_USER and let’s see what happens when HR_USER selects from the Salary column which is now encrypted with a password protected encryption key.  We get prompted to enter our password like this.



Now, do the same for REG_USER and see what happens:  We get the Decrypt default of 0 and that’s the correct result.


 Using isql for Encryption

So far I did all database encryption using the IBConsole, but you can do all the same using ISQL from command line.  Here is the DDL to Connect as SYSDBA and create embedded users, encryption keys and the System Encryption Password.

1. Connect as SYSDBA and create embedded users:


 2. Connect as SYSDSO and create the encryption keys, BUT first it is necessary to set the System 

    Encryption Password (SEP).  Up to 255 characters and may include spaces.



3. Reconnect as SYSDBA because we want to perform the encryptions.

    First we do DB level encryption, using ALTER DATABASE ENCRYPT.

Second, to use the Col_Level_Key_P, we first need to set a Password for it, and then we can use it.

NOTE:  We need to REVOKE DECRYPT on EMPLOYEE FROM PUBLIC, because PUBIC has it set by default.


Here is the DDL to perform the encryptions and how to use the Column Level Key with Password:


4. What happens when HR_USER CONNECTS?

Here is the SQL  for connecting as Human Resource user (with Decrypt permissions) and the Regular user without decrypt permissions asking for encrypted Salary column data.




5. What happens when REG_USER connects?


Lastly, You can also specify your encryptions at table creation time, like this:


This ends Part 1 of this post where we showed how to add database and column level encryption to an InterBase database.  Part 2 will show how to build a secure multi-device application that uses our encrypted InterBase database.



Gold User, Rank: 90, Points: 4
Al Mannarino has 25+ years of software development experience, including object-oriented analysis and design (OOAD) and developing and deploying production applications. He is currently a Principal Software Consultant and Evangelist for Embarcadero Technologies. Prior to joining Embarcadero, Al spent three years working with CodeGear, a division of Borland that was acquired by Embarcadero in 2008. He also worked for five years as a lead systems engineer for Borland supporting application lifecycle management, software delivery optimization and developer tools solutions. Prior to Borland, Al served as a systems engineer for companies including Objectivity, Versant, Red Brick Systems, Information Builders, and was an electrical engineer for Grumman Aerospace performing application implementations on complex electrical-mechanical systems. Al has a bachelor's of science degree in electrical engineering from Manhattan College.


Check out more tips and tricks in this development video: