Database Encryption Running Firebird 3.0

In the modern information world, information plays a significant role in the life of a person, society and the state. The growing size of the accumulated and processed data raises questions about their storage and privacy. Already there are many technical solutions and proposals for solving such problems. Among them, of course, there are also database management systems (DBMS) that support encryption of stored data. That's about one of these solutions and will be discussed.

In April 2016, a new version of Firebird DBMS was released at number 3. Of the innovations, among other things, quite a few mechanisms appeared to protect the stored and transmitted data. There is also the protection of the data transmission channel, there is the management of users, and there is also encryption of the database itself, which is implemented as transparent encryption at the level of data pages. This is implemented by writing special extensions for Firebird. Of course, you can figure it out yourself and write these extensions, but why not take the existing ones . Moreover, to write, at a minimum, you need to understand cryptography, be armed with the knowledge of some cryptographic package and deal with the new C ++ Firebird API.

You can use the existing ready-made solution by downloading this link for the Windows x64 platform. In the free trial version, it is limited to the encryption algorithm used — Triple DES (3DES) is supported. But to protect your database this is quite enough. The package includes 4 libraries and files with interface descriptions.

File Name	Description
CiKeyHolder.dll	Firebird extension - secret key keeper
CiDbCrypt.dll	Firebird extension - data encryption
CiFbEnc_x86.dll	encryption key activation module for 32 bit platform.
CiFbEnc_x86-64.dll	encryption key activation module for 64 bit platform.
ICiFbEncActivator.h	description of the interface module for C + +
CI.ICiFbEncActivator.pas	description of the interface module for Delphi

As an example, you can write an application that uses a certain directory (conditionally with confidential data), which as an update is available via a link in the global network. Anyone can download it, but only our application can decrypt and work with it.

In order not to litter the article with unnecessary details of the implementation of such a task, in which it is necessary to ensure that the key is received and the database file is updated, we will assume that this is, and the key will simply be encrypted as a variable array and the database file will be encrypted using this key.

Let us take Windows 10 x64 OS as the platform, and use IDE Embarcadero RAD Studio as the task implementation tools (currently Tokyo version 10.2 is a 30-day trial), since it contains convenient components for working with the Firebird database and is often used to develop application solutions (a trial version can be downloaded from the link ) and Firebird 3.0.2 x64 DBMS , which can be downloaded from https://www.firebirdsql.org/en/firebird-3-0-2 for its platform. Since we do not need a constantly running server in development mode, and it may need to be restarted repeatedly, during installation we choose the way to start the server as an application.
')

The password for SYSDBA done in the old-fashioned way masterkey and unchecking the box responsible for starting the server after installation is not necessary.

After the Firebird package has been installed, it is necessary to configure it. If you plan to use Firebird in Embedded mode, then the same settings will need to be done in the directory where the library used by the client fbclient.dll will be located. The base configuration files are located in the root directory of the installed Firebird - %ProgramW6432%\Firebird\Firebird_3_0 .

Let us write the alias (alias) of the database in the databases.conf file, adding a line indicating the full path to the future database:

 TESTDB = C:\TESTAPP\DB\TESTDB.FDB 

In order for Firebird to know where to look for keys, it is necessary to define the KeyHolderPlugin parameter. It can be specified either specifically for the above-described alias or in the firebird.conf file. Add or change the configuration parameter of the firebird.conf file, defining the plug-in the keeper of the private key:

 KeyHolderPlugin = CiKeyHolder 

Where to look for plugins Firebird also learns from configuration files. Modify the plugins.conf file by adding the following lines:

 Plugin = CiKeyHolder { #      Module = $(dir_plugins)/CiKeyHolder } Plugin = CiDbCrypt { #      Module = $(dir_plugins)/CiDbCrypt } 

The directory under the pseudonym $ (dir_plugins) implies the plugins directory in the root directory of Firebird. We copy there previously downloaded plugins CiKeyHolder.dll and CiDbCrypt.dll.

Since we assume that we already have a database, we will create it with the means of the Firebird package, namely, using the isql application. Click Win + R and execute the following command:

 %ProgramW6432%\Firebird\Firebird_3_0\isql -q -user SYSDBA -password masterkey 

Or somehow we launch isql in a different way, for example, using the Far program.

The following commands will create a TESTDB database, an alias of which was previously registered in the settings and will display information about the created database:

 SQL> create database 'TESTDB'; SQL> create table t_1 (i1 int, s1 varchar(15), s2 varchar(15)); SQL> insert into t_1 values (1,'value 1-1','value 1-2'); SQL> insert into t_1 values (2,'value 2-1','value 2-2'); SQL> commit; SQL> select * from t_1; I1 S1 S2 ============ =============== =============== 1 value 1-1 value 1-2 2 value 2-1 value 2-2 SQL> show db; Database: TESTDB Owner: SYSDBA PAGE_SIZE 8192 Number of DB pages allocated = 196 Number of DB pages used = 184 Number of DB pages free = 12 Sweep interval = 20000 Forced Writes are ON Transaction - oldest = 4 Transaction - oldest active = 5 Transaction - oldest snapshot = 5 Transaction - Next = 9 ODS = 12.0 Database not encrypted Default Character set: NONE SQL> quit; 

As can be seen from the information received by the show db; command show db; database created is not currently encrypted - Database not encrypted .

Now everything is set up and you can start writing the application.

Create a new VCL Forms Application project in IDE Embarcadero RAD Studio. Save the project, for example, as a testapp in the C: \ TESTAPP directory.

For a start, we can simply associate our application with a database, providing connectivity and disconnection to it. Our test database has one table, the data of which can be displayed upon connection. From the visual design is enough a couple of buttons, a table and a navigator with data management. To access the Firebird database, use the FireDAC components. Below is a table with components and their customizable parameters.

Components placed on the test application form

Component class	Component Name	Parameter	Parameter value
TFDPhysFBDriverLink	FDPhysFBDriverLink1	-	-
Tfdconnection	FDConnection1	Drivername	Fb
		LoginPrompt	False
		Params \ Database	TESTDB
		Params \ UserName	Sysdba
		Params \ Password	masterkey
		Params \ Protocol	ipTCPIP
		Params \ Server	localhost
TFDGUIxWaitCursor	FDGUIxWaitCursor1	-	-
TFDTransaction	FDTransaction1	-	-
Tfdtable	FDTable1	Tablename	T_1
TDataSource	DataSource1	Dataset	FDTable1
TDBGrid	DBGrid1	Datasource	DataSource1
TDBNavigator	DBNavigator1	Datasource	DataSource1
Tbutton	Button1	Caption	Connect
Tbutton	Button2	Caption	Disconnect

As a result, the form may look something like this.


Let's write down the processing of clicking on the “Connect” button:

 FDConnection1->Connected = true; FDTable1->Active = true; 

And for “Disconnect”:

 FDTable1->Active = false; FDConnection1->Connected = false; 

In the case of Delphi, everything is the same:

 FDConnection1.Connected := True; FDTable1.Active := True; . . . FDConnection1.Connected := False; FDTable1.Active := False; 

Before you connect to the database, you must start the server. To do this, call the administrator command line:

Win + X → ()

And execute the command:

“%ProgramW6432%\Firebird\Firebird_3_0\firebird” –a

The tray icon appears running server. From the context menu called above this icon, you can complete its operation or view the properties of the running server.

Now you can build and run the application to test connectivity to the database. After connecting to the database, the table displays the data.


It is time to connect the key activation and encryption control module to our application. To do this, copy the files CiFbEnc_x86.dll and ICiFbEncActivator.h, CI.ICiFbEncActivator.pas to the directory C: \ TESTAPP. It is worth noting that since a project is created under 32-bit Windows by default in the development environment and the trial version of the development environment does not allow changing the target platform, the application needs the CiFbEnc_x86.dll file module.

Connect the header file with the description of the activation module interface to the application file:

 #include "ICiFbEncActivator.h" 

Or the Ci.ICiFbEncActivator.pas file for Delphi.

 uses . . . , CI.ICiFbEncActivator; 

Add three more buttons to the form: “Encrypt”, “Decrypt” and “Get State”.


First, consider the “Get State” button handler. What follows from the name, it will give us the opportunity to get the current state of the database.

The most important thing is to get access to the activation module interface. It can be obtained both for each operation, and globally or by aggregating into a certain object. In order not to fence the wrapper classes, we will assume that we get the object of the activation module during each operation. Below is one of the possible ways to do this.

 // C++ Builder //   std::unique_ptr<HINSTANCE__, decltype(&::FreeLibrary)> mHandle( ::LoadLibraryEx(L"C:\\TESTAPP\\CiFbEnc_x86.dll",0,LOAD_WITH_ALTERED_SEARCH_PATH), &::FreeLibrary); if (!mHandle) { MessageBox( NULL, L" CiFbEnc_x86.dll        fbclient.dll", L" ", MB_OK|MB_ICONERROR); return; } //   typedef CALL_CONV int(__stdcall *CREATEFUNCPTR)(CI::ICiFbEncActivator**); CREATEFUNCPTR GetActivator = (CREATEFUNCPTR)::GetProcAddress(mHandle.get(), "createCiFBEncActivator"); if (!GetActivator) { MessageBox( NULL, L"     CiFbEnc_x86.dll " "createCiFBEncActivator" " -  ,   tdump.", L" ", MB_OK|MB_ICONERROR); return; } CI::ICiFbEncActivator* pActivator = NULL; GetActivator(&pActivator); if (!pActivator) { ShowMessage("ERROR GetActivator!"); return; } // . . . // //      // // . . . //    pActivator->Destroy(); pActivator = NULL; 

 // Delphi var pActivator : ICiFbEncActivator; res : Integer; CreateActivator: TActivatorFunction; mHandle : HINST; . . . begin //   mHandle := LoadLibraryEx( PChar('C:\TESTAPP\CiFbEnc_x86.dll'), 0, LOAD_WITH_ALTERED_SEARCH_PATH); if mHandle = 0 then begin MessageBox( Application.Handle, ' CiFbEnc_x86.dll        fbclient.dll', ' ', MB_OK OR MB_ICONERROR); Exit; end; //   CreateActivator := GetProcAddress(mHandle, 'createCiFBEncActivator'); if not Assigned(CreateActivator) then begin MessageBox( Application.Handle, '     CiFbEnc_x86.dll  createCiFBEncActivator' + ' -  ,   tdump.', ' ', MB_OK OR MB_ICONERROR); Exit; end; pActivator := nil; res := CreateActivator(pActivator); if not Assigned(pActivator) then begin ShowMessage('ERROR CreateActivator!'); Exit; end; // . . . // //      // // . . . //    pActivator.Destroy; pActivator := nil; FreeLibrary(mHandle); end; 

Further we will assume that for each of the handlers for clicking on the button, the above code will be added.

Now you can go to getting the state of the database.

 // C++ Builder . . . //      pActivator->SetDBAccess("localhost:TESTDB", "SYSDBA", "masterkey"); // ,       Firebird char stat_buf[1024] = { 0 }; size_t bufsize = sizeof(stat_buf); int res = pActivator->GetStateSVC(stat_buf, bufsize); String sStatMsg = (String)stat_buf; if (Err_OK == res) { MessageBox(NULL, sStatMsg.c_str(), L" ", MB_OK|MB_ICONINFORMATION); } else { String sErrMsg = L"ERROR GetStateSVC: " + sStatMsg; MessageBox( NULL, sErrMsg.c_str(), L" ", MB_OK|MB_ICONERROR); } . . . 

 // Delphi var . . . stat_buf : array[0..1023] of AnsiChar; bufsize : NativeUInt; . . . //      res := pActivator.SetDBAccess('localhost:TESTDB', 'SYSDBA', 'masterkey'); // ,       Firebird bufsize := SizeOf(stat_buf); ZeroMemory(@stat_buf, bufsize); res := pActivator.GetStateSVC(stat_buf, bufsize); if Err_OK = res then begin MessageBox(Application.Handle, PChar(String(stat_buf)), ' ', MB_OK OR MB_ICONINFORMATION); end else begin MessageBox(Application.Handle, PChar(String(stat_buf)), '', MB_OK OR MB_ICONERROR); end; . . . 

In case you managed to connect to the database and get information, we will see a message with detailed information about the current state.


As you can see, the database is still not encrypted. It's time to fix it. But a key is needed for encryption. And we define it as a global constant in the framework of the example.

 // C++ Builder //  -     192  const uint8_t key[24] = { 0x06,0xDE,0x81,0xA1,0x30,0x55,0x1A,0xC9, 0x9C,0xA3,0x42,0xA9,0xB6,0x0F,0x54,0xF0, 0xB6,0xF9,0x70,0x18,0x85,0x04,0x83,0xBF }; 

 // Delphi const key : array [0..23] of Byte = ( $06,$DE,$81,$A1,$30,$55,$1A,$C9, $9C,$A3,$42,$A9,$B6,$0F,$54,$F0, $B6,$F9,$70,$18,$85,$04,$83,$BF ); 

In addition to obtaining the current state of the database, all other operations, such as encrypt, decrypt and access the encrypted data, require activation of the private key. To do this, add the following code after receiving the activation module object:

 // C++ Builder . . . //      pActivator->SetDBAccess("localhost:TESTDB", "SYSDBA", "masterkey"); //    int res = pActivator->SetKey(&key, sizeof(key)); if (Err_OK != res) { ShowMessage("ERROR SetKey!"); pActivator->Destroy(); return; } //     res = pActivator->Activate(); if (Err_OK != res) { //   char errmsg[512] = {0}; size_t esize = sizeof(errmsg); pActivator->GetFBStat(errmsg, esize); String sErrMsg = "ERROR Activate: " + String(errmsg); MessageBox( NULL, sErrMsg.w_str(), L"   ", MB_OK|MB_ICONERROR); pActivator->Destroy(); return; } . . . 

 // Delphi . . . //      res := pActivator.SetDBAccess('localhost:TESTDB', 'SYSDBA', 'masterkey'); //    res := pActivator.SetKey(@key, Length(key)); if Err_OK <> res then begin ShowMessage('ERROR SetKey!'); pActivator.Destroy; Exit; end; //     res := pActivator.Activate; if Err_OK <> res then begin bufsize := SizeOf(errmsg); ZeroMemory(@errmsg, bufsize); pActivator.GetFBStat(errmsg, bufsize); MessageBox( Application.Handle, PChar('ERROR Activate: ' + String(errmsg)), '   ', MB_OK OR MB_ICONERROR); pActivator.Destroy; Exit; end; . . . 

As you can see, the key is transferred to the activator and the activation function is called. In this case, the module registers a callback function for accessing the key by means of key keeper extension. And it does not depend on the Firebird mode. And the security of the key transfer is guaranteed by encryption on the session keys. In the above code, after calling the activation function, an example of handling the occurrence of a possible error at the Firebird library level is shown. Similarly, you can read the current status of the completed task for any of the operations with reference to the Firebird functionality.

Now we can write handlers for the “Encrypt” and “Decrypt” buttons. If you omit checking the status of the completed task and pre-activating the key, then all you need to do is add a call to the activation object of the function of the same name:

 // C++ Builder //  res = pActivator->Encrypt(); . . . //  res = pActivator->Decrypt(); 

 // Delphi //  res := pActivator.Encrypt; . . . //  res := pActivator.Decrypt; 

As a result, we get identical handlers with the exception of one call.

Now we can encrypt the database. And the call of her status will tell us that she is already encrypted.


After that, an attempt to connect to the database without changing the “Connect” button handler will result in an error.


The last step is to change the handler of the connection button to the database. There you need to add the receipt of the module's interface object and the activation key activation. Below is the complete code for processing connections to an encrypted database.



This is how you can simply work with an encrypted database.

Fully project can be taken here .

Demo package - here .