Click here to Skip to main content
Click here to Skip to main content
Go to top

How to write once, and test encrypted data storage for Windows, Mac, Linux, Solaris, Android and iOS

, 16 Apr 2014
How to write once, and test encrypted data storage for Windows, Mac, Linux, Solaris, Android and iOS

Editorial Note

This article is in the Product Showcase section for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

How to make life easy for us developers!

More and more requirements for mobile applications lead to native applications on each platform for a number of reasons including:

  • Speed
  • Performance
  • Usability

There is also another point that is increasingly important as more and more businesses turn to mobilising their work forces and that is:

  • Security.

To summarize, the key here is encryption! The challenge, however, is achieving this easily over multiple platforms and making life easier for us developers and safer for those we code for.

We all know the utopian answer to the challenge:

  • Write it once, test it once, and deploy it on multiple platforms.

InterBase provides this exact capability for developers and enterprises to benefit from, which is something we will explore though this article.

But first, let’s get this one question out the way.

  • Should we use local data in applications?

While out of the scope of this article (and covered in depth in this white paper http://embt.co/MobEntData) the short answer is yes! I am sure we all use email on our phone; we expect email to work wherever we are, be it train, plane, tall concrete buildings, etc. In short, anywhere. This would not be possible without offline data. Even Facebook, for example, allows you to create and view posts offline!

securityInTheDataLayer == easyStreet!

Before we get into creating code, I want to make a quick mention of data in the product life cycle. Data on device is only part of what we need to worry about when building and developing mobile applications. The definition of data and its usage is something that can change as requirements are gathered. This introduces a lot of risk around refactoring and also puts extra stress and responsibility on the development and testing team.

The solution to this is quite simple and has the nice side effect of massively reducing the chance of mistakes being made = less stress = quicker time to market.

  • Implement the data visibility rules in the data layer!

While, for example, it could be great fun to see data that may show how much your colleagues are earning, it can be a lot less fun to have sensitive data exposed and your head on the chopping block for it! Rather than have QA test the same security settings for multiple platforms, it would be a lot quicker to check it once and get the product testing guys focused on other things like the UI experience. (Keep this in mind when we look at integrated role-based authentication later).

Creating and Encrypting InterBase database

Now that we have some background, let’s get on and see how to encrypt a database for use on Windows, Mac, iOS, Android, Linux & Solaris! The encryption in InterBase supports not only database level, but also individual column level encryption - this is very important for cases where separate encryption keys are required for specific data columns, e.g. Credit Cards.

Over the next section we are going to break down the following SQL script. This script performs column-level encryption on the InterBase demo database Employee.gdb on the EMPLOYEE table Salary and Phone Ext field.

/* # Enable Embedded User Authentication and create users.
   SYSDSO: for encryption management
   HR_EMP: HR Dept. employee. Privilege to see "salary" info.
   NEW_EMP: New employee. No privilege to see "salary" info.
*/
CONNECT 'c:\data\employee_col_encrypt.ib' USER 'sysdba' PASSWORD 'masterkey';
ALTER DATABASE ADD ADMIN OPTION;
CREATE USER SYSDSO SET PASSWORD 'sysdso';
CREATE USER HR_EMP SET PASSWORD 'hr_emp';
CREATE USER NEW_EMP SET PASSWORD 'new_emp';
CREATE USER SALES_EMP SET PASSWORD 'sales_emp';
COMMIT;


/* # Create encryptions */
CONNECT 'c:\data\employee_col_encrypt.ib' USER 'sysdso' PASSWORD 'sysdso';
ALTER DATABASE SET SYSTEM ENCRYPTION PASSWORD 'Secret Password';
CREATE ENCRYPTION hr_key FOR DES WITH LENGTH 56 BITS;
CREATE ENCRYPTION sales_key FOR AES WITH LENGTH 256 BITS;
/* Password protected Encryption key required for database backup */
CREATE ENCRYPTION backup_key FOR AES WITH LENGTH 256 BITS PASSWORD 'backup_password';
GRANT ENCRYPT ON ENCRYPTION backup_key TO SYSDBA;
GRANT ENCRYPT ON ENCRYPTION hr_key TO SYSDBA;
GRANT ENCRYPT ON ENCRYPTION sales_key TO SYSDBA;
COMMIT;

/* # Encrypt EMPLOYEE(SALARY) */
CONNECT 'c:\data\employee_col_encrypt.ib' USER 'sysdba' PASSWORD 'masterkey';
ALTER TABLE EMPLOYEE ALTER COLUMN SALARY ENCRYPT WITH hr_key;
ALTER TABLE EMPLOYEE ALTER COLUMN SALARY DECRYPT DEFAULT 0;
GRANT DECRYPT(salary) ON EMPLOYEE TO HR_EMP;
ALTER TABLE EMPLOYEE ALTER COLUMN phone_ext ENCRYPT WITH sales_key;
ALTER TABLE EMPLOYEE ALTER COLUMN phone_ext DECRYPT DEFAULT 0;
GRANT DECRYPT(phone_ext) ON EMPLOYEE TO SALES_EMP;
REVOKE ALL ON EMPLOYEE FROM PUBLIC;
GRANT SELECT,INSERT,UPDATE,DELETE,REFERENCES ON EMPLOYEE TO PUBLIC;
COMMIT;

Introducing SYSDSO

The control of data encryption levels should NOT be managed by the database owner and definitely not managed by a developer (as the risk is too high and the developer needs to then have clearance to see data that is restricted), but rather by the owner of the data visibility policy. For this reason, InterBase recommends using SYSDSO (Data Security Officer) as the login to manage security.

The script first creates SYSDSO in the database (which requires that admin options / embedded user authentication is enabled). InterBases embedded user authentication places all users inside the database file to insure the security is transportable throughout the development lifecycle).

CONNECT 'c:\data\employee_col_encrypt.ib' USER 'sysdba' PASSWORD 'masterkey';


ALTER DATABASE ADD ADMIN OPTION;
CREATE USER SYSDSO SET PASSWORD 'sysdso';


Creating and managing users

The script also creates (for the purpose of this demo) two new database users, HR_EMP and NEW_EMP. The HR employee is allowed to see Salary information, while the new employee user is not allowed to see this data and should instead be returned a default value (something we will get to later).

CREATE USER HR_EMP SET PASSWORD 'hr_emp';

CREATE USER NEW_EMP SET PASSWORD 'new_emp';

CREATE USER SALES_EMP SET PASSWORD 'sales_emp';


While outside the scope of this article, for large systems it is recommended that with a view to ISO9001 as a base, you can create User Roles in InterBase and assign the users to the roles they fulfil. This provides great flexibility in the administration of users.

Adding encryption key

Once the users are in the database the script follows on by creating the encryption keys that will be used.

/* # Create encryptions */
CONNECT 'c:\data\employee_col_encrypt.ib' USER 'sysdso' PASSWORD 'sysdso';
ALTER DATABASE SET SYSTEM ENCRYPTION PASSWORD 'Secret Password';
CREATE ENCRYPTION hr_key FOR DES WITH LENGTH 56 BITS;
CREATE ENCRYPTION sales_key FOR AES WITH LENGTH 256 BITS;
/* Password protected Encryption key required for database backup */
CREATE ENCRYPTION backup_key FOR AES WITH LENGTH 256 BITS PASSWORD 'backup_password';
GRANT ENCRYPT ON ENCRYPTION backup_key TO SYSDBA;
GRANT ENCRYPT ON ENCRYPTION hr_key TO SYSDBA;
GRANT ENCRYPT ON ENCRYPTION sales_key TO SYSDBA;
COMMIT;

InterBase supports both DES and AES strength encryption. (AES 256bit recommended). When using encryption you need to create a backup key that supports the highest level of encryption in the database file. This backup key will not allow access to the data, but will enable external scripts to backup the database. This is especially useful if the database will be backed up by network administrators who have no need to see the data.

A quick note about encryption level: The recommended level to use for at rest data to conform with data protection around the world is AES 256bit encryption. This is the level required by FIPS 140-2 and also recommended by leading organisations like the ICO http://ico.org.uk

Encrypting specific columns

With database users setup and the encryption keys created, it is now time to encrypt the data we specifically want encrypted. This can either be done at the database level (e.g. with the HR key)

/* # Encrypt database */
CONNECT 'c:\data\employee_col_encrypt.ib' USER 'sysdba' PASSWORD 'masterkey';
ALTER DATABASE ENCRYPT WITH hr_key; 
COMMIT;

or in this script at the column level. This can be a lot more efficient than having the entire database encrypted. In either version you can still have column level encryption that overrides the database level encryption key to enables unique column specific encryption that supports things like PCI compliance.

/* # Encrypt EMPLOYEE(SALARY) */
CONNECT 'c:\data\employee_col_encrypt.ib' USER 'sysdba' PASSWORD 'masterkey';
ALTER TABLE EMPLOYEE ALTER COLUMN SALARY ENCRYPT WITH hr_key;
ALTER TABLE EMPLOYEE ALTER COLUMN SALARY DECRYPT DEFAULT 0;
GRANT DECRYPT(salary) ON EMPLOYEE TO HR_EMP;
ALTER TABLE EMPLOYEE ALTER COLUMN phone_ext ENCRYPT WITH sales_key;
ALTER TABLE EMPLOYEE ALTER COLUMN phone_ext DECRYPT DEFAULT 0;
GRANT DECRYPT(phone_ext) ON EMPLOYEE TO SALES_EMP;
REVOKE ALL ON EMPLOYEE FROM PUBLIC;
GRANT SELECT,INSERT,UPDATE,DELETE,REFERENCES ON EMPLOYEE TO PUBLIC;
COMMIT;

You can see in the script above that, once connected, the encryption levels defined by the SYSDSO can be put in place by the database owner on the columns. The default value is also set for the columns when a user has no access to read the data. This is very powerful as it ensures that queries do not break, even if you can’t see the data. Here we can see the HR key set to manage the salary, and the sales key to see the phone number field with default values for both if you have no rights to read the data. Not the best real world example granted, but it does show the flexibility of having different encryption keys and visibility of data even in the same table.

The final part of the script revokes access to public, which for older databases is something you have to do before reaffirming the access to the employee table via a grant statement. And that is it! We are now ready to see this in action!

See it in action!

To view the output of this script in action we are going to look at the same query running for both users added in the script.

To view this in action I am going to show an Android and iOS application that has been built using the multi-platform native compiler that is part of RAD Studio. The sample application shown here has two database connections, with the only difference being the user name and password, the other database params are identical.

Selecting encrypted data

Using the first database connection with the HR_EMP login even at design time we can query the database and see data. (This is on Windows at this point).

Now using the same data file on Android or iOS we can login at run time and see the same results at run time.

Viewing encryption defaults

The second query that we run is identical to the first query except it goes via the database connection that has the NEW_EMP user (who is not allowed to see Salary). On windows at design time we can see the output as zeros for salary (as defined by the default return value in the script)

Again as we deploy the application this is controlled at runtime without having to write a line of code in the application layer.

Run time

Summary

Encryption of business data at rest is critical to the risk management of any application and doubly important when going mobile. InterBase provides a powerful, enterprise friendly way to manage the encryption of data in the data layer, removing the need of the developer to manage this in code. This vastly reduces the risk around data leakage that can lead to losing customers, regulatory action, fines and additional costs; it also speeds up the development and testing cycles by reduce the testing overheads around applications that have sensitive data stored in them.

More about InterBase

For a full white paper on this topic area visit http://embt.co/MobEntData

Source code from this paper http://cc.embarcadero.com/Item/29624

For more information about InterBase please visit http://www.embarcadero.com/products/interbase

Additional tutorial videos
http://www.embarcadero.com/products/interbase-labs
http://www.embarcadero.com/products/interbase/product-demos

For InterBase documentation visit
http://docs.embarcadero.com/products/interbase

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Stephen_Ball

United Kingdom United Kingdom
Stephen Ball is a Chartered IT Professional and product Evangelist for Embarcadero regularly speaking across Europe and Africa. Stephen is also the Associate Product Manager for InterBase. Stephen brings a unique sense of product and practical business insight from over a decades multi-national first-hand experience leading development teams on projects working with RadStudio and InterBase on products for a range of blue chip companies including Hilton, American Express, Virgin Active, Range Servant and Fitness First. Stephens blog is http://blogs.embarcadero.com

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 16 Apr 2014
Article Copyright 2014 by Stephen_Ball
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid