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:
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.
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;
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;
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;
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.
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;
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)
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.
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
