Firebird File and Metadata Security

Background

For an application (user) to access a Firebird database it must connect to the Firebird server process. On receiving a connect request the server process will authenticate the user credentials against the server users defined in the security database. If authentication is successful the server will allow the application access to any database that it requests and then use the roles and privileges defined in that database to provide fine grained access control to objects in that database.

At no time does the user connecting to the database require direct access to the database file itself. All access goes through the server process, which accesses the database file as needed to fulfil requests. It is the server that restricts or allows access to the logged-in user, according to the permissions defined for that user in that database.

Every Firebird server installation has a 'SYSDBA' user, this user has unrestricted access to any database available to that server. Database specific privileges are effectively ignored when the database is accessed using SYSDBA. When you first copy a database onto a server you can use SYSDBA to customise the privileges according to the users and requirements of the server. However it also means that, if I have direct access to a database file, I can copy that file from a server where I may not know the SYSDBA password onto a different server where I do know the SYSDBA password and so gain unrestricted access to the database.

As you can see from this description, Firebird security is predicated on the assumption that the Firebird server process will be running in an adequately [1] secure environment. Firebird itself takes no precautions to provide external security. Once a person has physical access to a database file there is no effective way to prevent that user reading all data (and metadata) within that file.

This means that, for reasonable security, installations should ensure that the database files are adequately secured. In most cases this means that the Firebird server process should run as its own specific operating system user and only that user (and probably the administrator/root user) should have direct operating system access to the database files. The database files should not be located in directories that are accessible from the network, or by any local users other than specifically authorised personnel. For the security to be effective it is also preferred that the server computer is stored in a secure location to prevent physical tampering that could permit unauthorised users to access the hard disk from beyond the strictures of the operating system.

However, the above explanation does not necessarily help developers who, having written databases for distribution and installation to remote sites, may wish to protect the intellectual property carried in their databases. Such concerns may include specifically the metadata (table structures and relationships, stored procedures and triggers) but may also include specific data carried in some tables. These concerns represent the main purpose of this article.

The problem

A developer creates a database (and usually an accompanying client application) for installation on servers at remote sites. At such sites it is usual for a person at that site to have full access to the computer on which the Firebird server is running – in order to be able to perform backups and other maintenance duties. As described in the background information, direct access to the database file provides the ability to gain full and unrestricted access to all the information in the database – both data and metadata.

In such cases the developer may not trust the users at these remote sites to keep the intellectual property represented by the database confidential. The fear may be that the users will reverse-engineer the database for their own purposes, or that these remote sites will fail to maintain the security necessary to prevent unauthorised access to the database.

This leads to the common questions on the Firebird lists along the lines of:

"I want to protect my database design (table structures, stored procedures, triggers etc.) from all users of the database at a remote installation. How can I do this with Firebird?"

or

"I want to stop any users at a remote installation from accessing these particular tables of data. They contain proprietary information used internally by the application."

Firebird (at least to v1.5) provides no built-in encryption facilities. The simple answer to both of these questions is that it cannot be done using the current capabilities of Firebird. A user who can get direct access to the file gains access to all details within that file.

In the first instance no workaround is feasible because the server itself must be able to read the metadata. In the second instance it may be possible to implement client side encryption/decryption features, but then you will lose the ability to make effective use of database indexes and search facilities – and key management remains a major problem (more below).

The solution

There is really only one possible solution to these requests: Host the database and server at your own site and let the clients connect to your server remotely, through dial-up or Internet facilities etc. Terminal server (Windows or Linux/Unix) capabilities could be a useful way to implement such requirements.

In this way you maintain control over the database file and can restrict access to the various features and structures of your database using the usual Firebird internal security features (roles and privileges etc.).

It is worth pointing out that there are difficulties even in this situation, if your intention is to protect the structure of your database.

Various database development libraries interrogate metadata, such as primary key, domain and similar structural information, in order to make development of client applications easier. Consequently, you may discover that you cannot prevent users from accessing metadata without also preventing your application from gathering the information that it requires. This may mean that you will need to choose between allowing metadata details to escape from your server via a sophisticated data access interface and spending the considerable extra time it takes to develop an application using a less sophisticated access library.

There is also the issue that most client applications inherently "leak" structural information about the database with which they interact. It is very rare for a database-centric application to have an interface that does not reveal many details about the table structures that it uses. Some details may be hidden behind views and selectable stored procedures, but defining such features purely to try and hide structural information is an exercise in frustration. It is probably futile, anyway, since some details will escape, whatever you try.

Protecting user data

Before continuing with other discussions relating to encryption of Firebird data, I do want to highlight that it is possible for users to protect their databases with encryption. This does not help developers who want to hide information from legitimate users, but it may help to meet the requirements of customers wanting to increase the security of their databases.

In some office situations it may not be practicable to locate the Firebird server computer in a truly secure environment. During times when the office is attended, the likelihood that anyone will be able to access the computer to copy the database files (or steal the computer or hard disk to get the files later) may be quite low. However out of normal working hours (nights and weekends) is a different matter. Someone could gain access to the office, take the hard disk out of your computer and take it away to access the database.

While Firebird itself provides no built-in encryption features there are some excellent products that do. You could install software that creates an encrypted volume on your computer and locate the database file (and any other confidential data) on that volume. When the computer is shut down all data exists in an encrypted file and cannot be accessed without the key. When you start the computer you have to mount the encrypted volume and supply the secret key before the data can be accessed. This additional, and necessarily manual, step in the start up process may be inconvenient but it can provide excellent security for unattended computer systems.

Software with these capabilities include: "Bestcrypt" from Jetico (www.jetico.com) and "PGPDisk" (www.pgpi.org/products/pgpdisk/ note that this link goes to an old freeware version, that site has links to newer commercial versions of the product). There are others but these are two that I have used myself.

Why doesn't Firebird provide encryption?

Because of the needs described above, it is common for users to request that Firebird should, in a future version, add the ability to encrypt metadata, selected user data, or even the entire database. Not being a Firebird core developer, I cannot say categorically that it will not happen. However, the issue is not really whether encryption is practicable or useful, but a matter of whether key management would allow a practicable solution to the problems we are examining.

Encryption can only be as good as the secret key required for decryption. It can be worse but it cannot be better. There are several excellent encryption algorithms available that could be used. When good encryption is used, attacks are likely to be against the key rather than against the encryption itself.

So let's look at how things would work if Firebird were to encrypt the metadata in a database...

Before the database could be accessed the secret key would need to be supplied. Giving the decryption key to the user would be pointless, simply bringing us back to the original problem. So, presumably, whenever the customer restarts the server, they would call the developer, who would then dial in and enter the needed key. Even if this were practicable, it is not necessarily going to solve the problem. For example; the customer could install some monitoring software on their server to detect the key as it is entered.

There are hardware based solutions to provide a key to a decryption process. But again this would need to be in possession of the client, and if we don't trust the client we cant stop them from using it to gain access to the database from another server where the SYSDBA password is known.

Firebird is an open source product. If the encryption facilities were built in, or open source plug-in libraries were used, it would be feasible for users to build their own versions of the server or plug-in that not only performed the necessary encryption and decryption to access the protected database but also output the key, or simply output the decrypted details directly. The developer, not being in control of the server, can neither detect nor prevent such activity.

You might consider building your own version of the Firebird server with the decryption key hidden in the executable. However, decompilers are available. It would not take long to discover the key simply by comparing the decompiled versions of your custom Firebird build with the normal, unencrypted version.

Various database products do exist which purport to provide strong encryption. Perhaps the encryption is strong but, unless the key management is in place to support this feature, the encryption is not going to achieve the desired effect. It may encourage you to believe you are protected, but you need to study the key management to discover if this is really true.

The painful truth is that once you lose control of the hardware on which the encryption and decryption takes place all bets are off. If the decryption key cannot be kept reliably secure then even good encryption becomes little more than security by obscurity.

Limiting the distribution of data

Some people request encryption of the database data so that they can try and limit the dissemination of data. They are happy for the particular authorised user to see the data, but they wish to limit that user's ability to distribute the data to other people.

Just imagine for a moment that all the key management problems described above have been solved, so that it has become impractical for the user to just copy the database. In such cases the user would simply write a small program that extracted the data they were interested in (from the legitimately installed server) and copied that data to its own file.

I guess it is possible that Firebird may provide some form of application authentication system in the future that may make it possible to limit this form of data extraction, however most of the same problems exist. If you do not control the server you cannot prevent the user from installing a version of the server that does not require the authentication.

Removing SYSDBA access

At various times people have suggested that removing SYSDBA access to a database could be the solution. The idea behind it is that, when the database is moved to a new server where the SYSDBA password is known, it will not help the person because SYSDBA does not have access anyway. Some have reported limited success in this respect by creating an SQL role name of SYSDBA and making sure it does not have access to the database objects.

However it does not really solve the problem. The database file can be viewed with a hex viewer or similar utility and the list of available user names discovered. (Discovering the owners of the database objects would be particularly useful.) Once known, these names can be added to the new server and used directly. An even simpler workaround would be to compile your own version of the Firebird server that ignores security constraints or you can use the embedded version of Firebird server to gain direct access to the file.

There has been some suggestion about allowing the SYSDBA user name to be changed. This may offer some limited protection against brute-force network attacks against the SYSDBA password, since such attacks would need to guess both the user name and its password, but it does not help protect the system from a person with direct access to the database file.

Deleting stored procedure and trigger source code

When you write and define a stored procedure or trigger for a Firebird database, the server stores an almost complete copy of the procedure source code along with a "compiled" copy referred to as BLR (Binary Language Representation). It is the BLR that is executed by the server, the source code is not used.

Some developers attempt to protect at least some of their database metadata by deleting the source code from the database before distributing the database (a simple direct update against the relevant metadata table fields). I recommend that you don't do this for two reasons.

BLR is a fairly simplistic encoding of the source code. It would not be difficult to decode the BLR back to a human readable form. Such a decoding would be without comments and formatting, but the SQL that goes into a stored procedure or trigger is rarely so complicated that this would cause much of a problem. Hence the protection offered by the removal of source code is not very significant.

Secondly the source code can be useful for other purposes. It allows fixes to be applied directly to the database without needing to bring in the full source from elsewhere (and then remembering to remove it again when the fix is applied). The source code is also used by various utilities, such as my own DBak application – an alternative backup program to "gbak". I have not bothered to write my own BLR decoder at this stage, so DBak relies on the availability of the source code in order to be able to build a DDL script to reconstruct a database.

Other forms of obscurity

Various other forms of security by obscurity have been proposed. Such things as special events that fire on login and log off to call user functions to prevent or deny access. Such features may offer some limited use for closed source systems, where the obscurity of the implementation helps to hide exactly how information is being protected. But for an open source system the work around for such hacks is to simply build your own version of the server that bypasses the event or code which is preventing access. It is difficult to offer obscurity in an open source system.

Consider also what happens when you distribute your compiled executables. Compiled programs are great examples of obscurity. No encryption is used (usually), all steps of the code are there to be analysed by anyone with the time and knowledge, indeed there are decompilers available to assist with this process. Once a person discovers what libraries your code was compiled with, isolating the results to only your own "secret" code makes the whole process much faster. Have you written to Borland, Microsoft or whoever requesting that they somehow encrypt their compiled binaries?

Acceptable low security

My comments so far have been directed at the idea of strong security, and I guess the concept of security by obscurity has been written with some contempt. However at times weak security is all that you want. Sometimes the data is just not that valuable. You want to stop the casual thief and make it at least inconvenient for the more advanced thief. Make it difficult enough so that its not worth the effort involved to work around the security.

I have used such schemes myself in various places. But there is no point throwing twofish, AES or whatever at such schemes because these are all about strong encryption - they are heavy with processing overhead and complication relating to keeping the security strong. A simple XOR against some known string (the key) is sufficient - this will obscure, and while the key is not known to the thief it will be relatively difficult to break as long as some care is taken. It will be enough for weak security and does not impose such serious overheads. Once the key is known to the thief it does not matter whether you have used weak or strong encryption, the game is over anyway.

The thing about security by obscurity is that it must be obscure! If Firebird was to implement some sort of simple XOR obscuring into its disk reads and writes then it would not be obscure because it is an open source project. It would take almost no time at all to recompile the source to discover the key being provided and everything is lost.

So if you really need this feature you would obtain the Firebird source, insert your own obscuring code into the disk read and write methods and compile your own variation of the Firebird server. (Such code could be discovered by decompiling the executable but it does take a fairly serious thief to try this.) Before you do this, try to work out if this solves your problem if the user also takes a copy of the specially compiled executables along with the database.

The philosophical argument

There is the philosophical question of why you would choose an open source database server product to build a closed source database. Many people have contributed to the project in the firm belief that open source is the best way to provide software.

But more particularly, when it comes to the storage of users' data I am a firm believer that the users should insist on the ability to access their own data – which will often include the need to understand the structures and the processes you have built (the metadata). If you go out of business or become otherwise unavailable it may be of critical importance that the users can at least extract their own data (in appropriate formats) in order to be able to move to alternative systems.

Can you trust the users to respect your intellectual property while you are still in business and available? Provide the necessary services and facilities and hopefully they will. If not, there is a good chance that there is little you can do to stop them.

Conclusions

The problem has been that too many people do not understand security and how difficult it is to do well. Regrettably there have been many software products that have encouraged such misunderstandings by implementing obscurity rather than true security. Witness the number of companies around that provide "data recovery" services, by which they mean; bypassing or breaking the supposed security of obscured data.

Encryption is not a panacea for security. If you are not in control of the environment (the hardware, the operating system and all software running on that system) then you have no control over the security - regardless of what encryption schemes you may have in place. This is the situation when you distribute your database to remote server installations.

If you really need to protect the data or metadata in your database then you will need to retain control of the database file and the environment in which it is accessed. No other solution will offer you the same level of security.

• [1] Adequate security is dependent on the level of security required for a particular installation. This will vary considerably from installation to installation.