Friday, February 24, 2012

Firebird: choosing an owner for database deployment

Like most users when I started using Firebird I connected using the SYSDBA username. That is the default username for server administration: every server has it.

It looked like a good idea because I did not have to care with users management, but I have now realized that using SYSDBA for database development can cause problems when the database is deployed to the customer's computer.

A Firebird database and all its tables have an owner: it is the username that was used for connecting to the server when the object was created. Tables can have a different owner than the database, but this an advanced topic. The simplest solution is having a single owner for the database and all the tables.

Both the owner and SYSDBA have full access to the database, so you need to connect to the server as the owner or as SYSDBA. Of course things can be more complicated about database security, but I want to keep things simple.

The server stores usernames and passwords in a special database (security2.fdb). When a user connects to the server the username and the password are checked using the security database. If the username does not exist or the password is incorrect the connection is refused. When a user is connected to a database he can open a table only if he is the owner or if he is SYSDBA.

I am targeting small customers, so most of them will use the embedded server. The embedded server works differently about security: it will not check the username and the password (there is no security2.fdb) so you will always be able to connect with any username/password couple. Nevertheless Firebird will still check that the username is the owner or SYSDBA before opening a table. Other usernames can connect to the server but cannot open tables.

Customers that will require a multi-user solution will need to install the full server, but they will probably have no experience with Firebird or other database servers. Most of them will use Firebird only for our software, but there is a chance that some of them will already have a Firebird server installed and used by other software.

Deploying a database owned by SYSDBA will cause problems to those users. The only way to connect to such a database is using the SYSDBA username, and since the server has been installed by other people users might not know the SYSDBA password. A database administrator would not like to let a software connect as SYSDBA, since that user has full access to all the databases handled by the server.

For this reason a much better solution is creating a new username and connecting with that username to create and develop the database, so that the owner is not SYSDBA. Then, after database deployment, the software can connect in different ways for different needs.

The software can connect to an embedded server using the SYSDBA username and any password. It will be able to access data without problems. Users will not not be asked for a username.

When connecting to a full server the software will ask for a username and a password. There are two options:
  • If the server will only host our database the simplest option is connecting as SYSDBA. It will be possible to access the database with minimal effort.
  • If using SYSDBA is not an option somebody must create a new user named as the database owner (the same username used for database development) using any password he likes. The software will connect using that username and password so it will be able to access the database without administrator privileges. The username must be the same as the owner, but the password does not need to be the same used for development. Remember that the password is only used to connect to the server: once you are connected Firebird only checks the username to grant access to tables.
If you already have a database owned by SYSDBA and you want to change its owner you can save a script to recreate the database using some administration tool ("Extract metadata" or similar). Then create a new user that will became the database owner, connect to Firebird using that username and create a new database. Then execute the script to recreate the database structure.

If you have a lot of data and you need to keep them you can try FBOwnerMigrator by Thomas Steinmaurer.

3 comments:

  1. A good summary!

    Perhaps it's worth to be mentioned that you can use the SYSDBA role trick to prevent SYSDBA from connecting. Others might say that this isn't bullet-proof if someone knows the Firebird database file internals and a HEX editor a bit, right, but at least you can dismiss SYSDBA connections issued "by accident", e.g. during development.

    Another option with Firebird 2.1 and higher is to create an ON CONNECT trigger with a user-defined condition (e.g. CURRENT_USER <> MYOWNER), which throws an exception and therefore aborts the connection process. Again, not bullet-proof, because e.g. isql has a command-line option to dismiss such database triggers.

    While FBOwnerMigrator has worked now for many years and a lot of people, I must say, it hacks system tables directly, which you should avoid whenever you can. But the whole process has been tested carefully and if you are interested in the tool, you have to request the download by sending an email with a notice that you have to use it on your own risks. ;-)

    Regards,
    Thomas Steinmaurer

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This a nice text indeed. The reasons why one would think about db owner change are very well explained.

    I wrote another tool for db owner change, see DBUSubst in my web page http://www1.cuni.cz/~svejdar/?s=oact It is based on Thomas Steinmaurer ideas, but is more detailed and more general. And I believe it is simple to use. It can actually substitute one user for another, regardless whether one of them is a db owner. The new user inherits all objects that the old user owned, he grants all the privileges that the old user granted, and he is granted all the privileges that were granted to the old user.

    Best wishes, Vitezslav Svejdar

    ReplyDelete