Note: The isql prompt is SQL>, with CON> being a continuation of the current sql command
Created Sunday 3/12/2006
This document outlines installation notes and simple usage for the Firebird database (previouly called Interbase). This document focuses on Firebird 2.0. The online manual for Firebird is at www.firebirdsql.org/manual and 3rd party gui and administration tools are available at www.ibphoenix.com
The firebird install script is pretty self explanatory. The installation script deploys to /opt/firebird by default
The firebird installation binaries are split into 2 types, the Classic server (see #1.A.i) and the Super server (see #1.A.ii)
Creates a separate process for every client connection, each with its own cache. Less resource use if the number of connections is low. The Classic server permits fast, direct I/O to database files for local connections on Linux. The client process must have filesystem-level access rights to the database for this to work.
A single process serves all connections, using threads to handle requests. The Super server uses shared cache space and is a more efficient configuration if the number of simultaneous connections grows.
On Linux, all local connections are made via the network layer, using localhost (often implicitly). Only the server process needs access rights to the database file.
The installation creates the user firebird and a group called firebird all binaries and scripts within /opt/firebird are owned by this user and group.
The firebird user is created with a shell of /bin/false and, unlike other dbms, does not require that the user account be logged in to perform administrative tasks. The server process when it starts, is started as firebird.
In embedded mode, there is no server process and the shell account must have rw access to the physical db file. ''' '''
The installation process creates a dba user called sysdba and writes the password to the file /opt/firebird/SYSDBA.password. The script changeDBAPassword.sh which is available from the local firebird bin directory, can be used to change the dba password.
The Firebird lock manager instance should be present if an isql session is created:
bash $ ps -elf | egrep fb_lock_mgr
The server process name fb_inet_server and there will be one instance of this process running for each network connection. However if there are no active connections, or if there are only direct local connections, there won't be an fb_inet_server process active. The fb_lock_mgr process will always be present as soon as any kind of Classic connection has been established.
The Firebird server processes for a super server installation are fbguard and fbserver. These should be installed and running as a service.
bash # ps -elf | egrep 'fbguard|fbserver'
The fbguard is a watchdog that restarts the fbserver process if it termintes. The fbguard process should be activated in a super server installation. ''' '''
Note: The isql prompt is SQL>, with CON> being a continuation of the current sql command
Firebird creates on disk structures (refered to as ODS) for it databases. A datbase instance can be created using isql (provided with the Firebird release). The location of the ODS database can be any where that the firebird user has access to. The following shows the database create process (SQL> and CON> are the firebird isql promps, with CON> being a CONtinuation of the current statement):
For the Classic Server, prepending localhost: to the database path ensures that the database instance being created is owned by the firebird server process. Otherwise the database instance will be owned by the current shell user.
bash $ isql SQL> create database 'localhost:/u01/firebird/database/leaf.fdb' page_size 8192 CON> user 'sysdba' password 'sysdba';
To test the database:
SQL> select * from rdb$relations;
The firebird installation includes a security database called security2.fdb, where all users and database permissions are stored. The installation process creates a single DBA user called sysdba (with the password stored in SYSDBA.password).
The gsec command line utility can be used to manage database users and permissions. Filesystem-level permissions to security2.fdb are required when running gsec. This is best achived by ensuring that security2.fdb has group firebird with read+write permission. Users that should be allowed to run gsec can then be added to the firebird group (or perhaps fbadmin group).
Note that user passwords are limited to 8 characters, with any other characters ignored. Interestingly, the default sysdba password of masterkey, which is 9 characters, is actually stored as masterke (an absent final 'y' character).
Most gsec operations require an executing user (typically sysdba), which is the use that will perform the gsec action. This information is passed to gsec with the -user and -pass options. Note these options do not indicate the user to operate on; they indicate the user to operated as. See #3.A which changes the sysdba password. In this case, gsec -user is sysdba and the -modify action also specifies the sysdba user as the user doing the work is the same as the user being worked on.
The sysdba password can be modifed using gsec with the -mo(dify) action. The current password must be known.
The following gsec command changes the sysdba password. The first -user and -pass args identify the database user to perform gsec operations on. The -mo(dify) and -pw tell gsec that a modification of the password should occur. Note that the changeDBAPassword.sh can be used to perform this task.
bash $ gsec -user sysdba -pass masterkey -mo sysdba -pw newpassword
Adding new users with gsec is fairly trivial. The sysdba user is used with an action of -add. The following adds a new user called foo with password foo (assumes that sysdba password is still masterkey):
bash $ gsec -user sysdba -pass masterkey -add foo -pw foo
Firebird uses the filesystem for its database (which are termed On Disk Structures or ODS). In this way a database is then refered to either by its phsyical location (not recommended) like /u01/firebird/db/db_1.fdb or by an alias such as db_1
Aliases are stored with in the aliases.conf file in the firebird installation directory. Filesystem permissions for this file should be set as read/write for owner only. This is a security precaution as this makes it more difficult for users to find the actual db file and extracting data, thereby circumventing db permissions.
Example alias content:
bash $ cat aliases.conf db_1 = /u01/firebird/db/db_1.fdb
Firebird provides different connection modes. The embedded or local mode connection and the tcp connection. JDBC connections can be made in either local or tcp/ip network mode.
Connections created in Local mode are pretty trivial and simply require either the full path to the physical db file or a db alise:
bash $ isql /u01/firebird/database/leaf.fdb
Or, if leaf is defined as an alias, simply provide the alias name as in:
bash $ isql leaf
A TCP/IP connection conists of a server name or ip followed by a colon (":") and then either the physical db file location or alias. If connecting via firebird's isql via TCP/IP, then the user and password must be given:
bash $ isql localhost:/u01/firebird/database/leaf.fdb -u foo -p foo
Or, if leaf is defined as an alias:
bash $ isql localhost:leaf -u foo -p foo
Jaybird is Firebirds JDBC implementation. Jaybird is JDBC 2.0 compliant and the jdbc driver is bundled in firebirdsql.jar. The name of the Firebird jdbc driver is org.firebirdsql.jdbc.FBDriver and it connects, by default, via port 3050. The format of the connect string is for Jaybird is pretty typical, however there are two forms:
The standard format (recommended) is:
jdbc:firebirdsql:[//host[:port]/]<database>
And the old FB format is:
jdbc:firebirdsql:[host[/port]:]<database>
The Jaybird jdbc driver can be downloaded from sourceforge.net/projects/firebird
Stuart Moorfoot © 3 Dec 2006 foo@bund.com.au