Build a secure multi-device application using encrypted InterBase ToGo database

Build a secure Multi-device Application Using Embedded encrypted InterBase ToGo

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

In Part 1, we saw how to get our InterBase ToGo Deployment license.  Now we show the steps to build a secure multi-device application using RAD Studio, Delphi or C++ Builder and our InterBase ToGo encrypted database that we created in Part 1 with both database and column level encryptions. 

We created the above app starting with a Multi-Device FireMonkey Tabbed application that gives us these four tabs to start with.  We renamed the four tabs; Info, Database, HR Dept, and Employee.

The Delphi 10.1 Berlin project is here: https://cc.embarcadero.com/item/30686

The C++ Builder 10.1 Berlin project is here: https://cc.embarcadero.com/item/30685

This InterBase ToGo application shows embedded User Security and Encryption to manage access to private data.  Along with being password protected, the data is also encrypted on disk to prevent reading the database file for clear text.  The app has two users embedded in the database.  The HR_EMP and NEW_EMP.  The HR user is allowed to see the employee’s salary, but the new Employee is not, this is because we added decrypt permissions to the Salary column for the HR_EMP, but the New_Emp does not have decrypt permissions to the Salary Column but instead will get a Default value of 0.

Using two different connections to the database we can show what you get to see when connected to our embedded InterBase ToGo database that has both database and column level encryption.

InterBase ToGo applications can use FireDAC, DBExpress or InterBase Express components to build InterBase Togo applications.  This application is using FireDAC to connect to InterBase ToGo database.

We have two FireDAC connections, to our encrypted InterBase ToGo database.  One is called FDConnectionHR connected to our Employee encrypted database.  For the HR connection, we pass in the Username and password for the HR user that has decrypt permissions on the Salary Column.  The FD Connection for a NEW Employee connects to the same encrypted Employee database but has the Username and password for the NEW_EMP user that does not have decrypt permissions on the Salary Column.  

The first tab INFO has information on what the application does display in a Memo component.  The Database TAB, lets us connect to the database as the HR Employee and as the NEW Employee at the same time.  The HR Connection connects as the HR_EMP user and the Employee Connection connects as the NEW_EMP user.

Selecting the HR Dept tab, we see the FULL NAME and the Salary of the employees, because we gave the HR_EMP decrypt permission on the Salary Column, but when we select the Employee tab, we see the FULL Name and we see 0 as the default value for Salary because the New_Emp user does not have decrypt permissions on the Salary column, so all this is working correctly, and it’s using our embedded InterBase ToGo database with both database and column level encryptions.

The app also uses two FD Query components with the query:  Select FULL_NAME, Salary from EMPLOYEE.  So when the HR_EMP executes this query, because they are connected as the HR_EMP with decrypt permissions on the Salary column, they get to see Salary data.  

The FD Query for the New_Emp, has the same query; Select FULL_NAME, Salary from EMPLOYEE.  But since they are connected as the New_EMP without decrypt permissions on the Salary Column, get the default value of 0 that we set when we created the column level encryption key for the Salary column.  

Looking at the OnSwitch Event for the TSwitch for the HR_EMP connection, we see:

  FDConnectionHR.Params.Values['USER_NAME'] := edtHRUser.Text;

  FDConnectionHR.Params.Values['Password'] := edtHRPassword.Text;

  FDConnectionHR.Connected := SwitchHR.IsChecked;

  FDQueryHR.Active := SwitchHR.IsChecked;

We set the properties for the USER_NAME and Password for the FDConnection and we set the FDQuery for the HR_EMP to be active.

We do the same for the OnSwitch Event for the TSwitch for the NEW_EMP connection,  we see:

  FDConnectionSTAFF.Params.Values['USER_NAME'] := edtEMPUserName.Text;

  FDConnectionSTAFF.Params.Values['Password'] := edtEMPPassword.Text;

  FDConnectionSTAFF.Connected := SwitchEMP.IsChecked;

  FDQuerySTAFF.Active := SwitchEMP.IsChecked;

We set the properties for the USER_NAME and Password for the FDConnection and we set the FD Query for the NEW_EMP to be active.

To define the location of our InterBase ToGo database on mobile devices we are using the Form’s OnCreate Event.  If we are NOT running the application on Windows, then we use the

TPath->GetDocumentsPath that Returns the path to the directory where user documents are stored / InterBase and the name of our InterBase ToGo database.

The Android Documents  Folder is:  .assetsinternal

The iOS Documents Folder is:  StartUpDocuments

On Android the TPath->GetDocumentsPath returns the Android documents folder which is .assetsinternal.

And for iOS Devices the TPath->GetDocumentsPath returns the iOS documents folder which is StartUpDocuments

And this is where the mobile device will look for to find the Intercase Togo Database. Up to this point, we have used InterBase database on our desktop.  This means that the actual database is located on your local hard drive.  On the mobile devices, the application is sandboxed, and typically you can only read and write data that is located in the Documents folder for an IOS device and internal storage for an Android device, under your application folder.  So to connect to a local database on mobile, we need to perform a few actions.  Those actions are:

1. For our mobile devices, we need to deploy both our InterBase ToGo database and our InterBase ToGo license file.  To make this easy for us, we can use Project | Deployment.  Select All Configurations - Android platform, and using the Add file button we can add both our InterBase ToGo database file and InterBase ToGo license file.  Here we added our IB TOGO database file, and for the remote path on our mobile device, we set it to the Android’s Documents folder which is the internal storage location for Android,  which is .assetsinternal.  and we do the same for our IB ToGo license file and give it the Remote Path of .assetsinternal.

We can also use the Deployment Managers | Add Featured Files dialog box, that shows a nested list of database drivers and libraries that can be added to the deployment list:

And similar for iOS devices, select iOS Devices (32 and 64 bit) ALL Configurations.  The IB ToGo database file goes in the iOS Device Documents folder using the Remote Path property, which is  StartUpDocuments.  

And the same for the IBToGo license file in the StartUpDocuments folder on the iOS Device.

So those are the files that need to be deployed on the mobile devices to allow you to use IBToGo with its license file on mobile devices (iOS and Android). 

To display our database values from the Employee table, on the HR Tab, we are using a ListView Component, Aligned to Client, and then using Visual Live Bindings, we take the results from the FireDAC query, the FULL_NAME and displaying it on the Item.Text property of the ListView and the Salary data gets displayed on the Item.Detail property of the ListView.

And we do the same thing on the Employee tab.  the FULL_NAME and displaying it on the Item.Text property of the ListView and the Salary data gets displayed on the Item.Detail property of the ListView:

Since this is a FMX Multi-Device application, we take this same application we ran on 32-bit Windows and build and deploy to an Android device without making any code changes create an Android version of the same application.  Selecting the Android Target, when we build the application, it will create the Android .APK file that we can load into an Android device and run and see that it works the same as the 32-bit Windows Application. 

Here’s is the app running on my Android Nexus 7 Tablet.  It starts on the Info Tab.  Clicking the Database tab, I can connect as the HR_MP and also as the New_EMP.   And now when I click on the HR DEPT tab, we see FULL NAME and Salary data, because the HR user has decrypt permissions on the Salary column, and when I click on the Employee Tab, we get FULL NAME, but we get the default value of 0 for Salary, because the New_Emp does not have decrypt permissions on the Salary Column.  All of this is working correctly using our embedded InterBase ToGo encrypted database!