Firebird File and Metadata Security

If you stumble on this page and don’t know about Firebird, see this link: www.firebirdsql.org

This article discusses the security of Firebird database files and in particular access to the metadata stored in those files. It has been written in response to frequent questions on the Firebird lists concerning these issues. The article avoids technical specifics. To discover how to secure files in your particular operating system refer to relevant documentation for that operating system, to discover how to apply user and object security in Firebird databases refer to documentation available from the Firebird website (noted above) or buy The Firebird Book by Helen Borrie.

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 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 from 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.

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 tasks. 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:

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

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

There is a special version of the Firebird server referred to as “embedded”. This is a special client library that includes the server itself. When an application links to this library it loads the server and allows direct access to any database that is accessible on the local computer. This version of the server does not use a security database. The user name specified during the “logon” (no password authentication occurs) is used to manage user access to database objects (via SQL permissions) but if that user name is SYSDBA (or the owner of the database) then unrestricted access is possible.

The features of embedded are useful for developers wanting to create easy to distribute single-user applications that do not need security.

From that brief description it appears that having an embedded server client installed on a server hosting other databases could present itself as a major security risk. In reality the risk is no greater than if the embedded client did not exist.

When an application loads the embedded server, the server operates in the application (and therefore the user’s) security context. This means that the embedded server will only be able to access database files that the user could access directly through the operating system. Giving an untrusted user access to install programs on a secure server is bad news in any case, but provided you have specified appropriate file permissions on secure databases, the embedded server itself is no threat.

The threat comes from all the other things that the user could install.

The fact that the embedded server exists only serves to highlight what is possible given direct access to a database file, especially in an open source environment. If it did not already exist then it would certainly be possible for someone to compile an equivalent capability.

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 and, 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?

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 browser and make it at least inconvenient for the more advanced thief.

I have used such schemes myself in various places. Often there is no point in throwing Twofish, AES or whatever at such schemes because those 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) may be sufficient. If the key can be discovered by the thief then 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 were to implement some sort of encryption 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 needed 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 whether it would actually solve your problem, if the user also takes a copy of the specially compiled executables along with the database; or if it remains possible for a user to extract the secrets directly from your running server.

There is also 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.

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.

I would like to thank the various people that have reviewed and commented on this article. I would also like to thank the many people that contribute to the Firebird support list, which is the source of much of the information that appears in this article.

The exact file history — starting at version 0.5 — is recorded in the firebird-documentation git repository; see https://github.com/FirebirdSQL/firebird-documentation

I have tried to make this document accurate at the time of writing but I cannot guarantee that there are no mistakes. Security is a complex subject, where security is important to your product or installation you should seek professional advice.

I place no particular restrictions on the use of this document. You are free to reproduce, modify or translate this document. However, altered versions of the document should be annotated with the changes that have been made and the name of author of the change (so that my name is not associated with text that I did not write). --G.W.