Server configuration and management

User management
Security
Administration tools

There are several things you should be aware of – and take care of – before you start using your freshly installed Firebird server. This part of the manual introduces you to some useful tools and shows you how to protect your server and databases.

User management

In Firebird 3, user management is done entirely through SQL commands. Users of previous versions are probably familiar with the gsec utility for this task. It is still present, but deprecated and it won't be discussed here.

Changing the SYSDBA password

One Firebird account is created automatically as part of the installation process: SYSDBA. This account has all the privileges on the server and cannot be deleted. Depending on version, OS, and architecture, the installation program will either

install the SYSDBA user with the password masterkey, or
ask you to enter a password during installation, or
generate a random password and store that in the file SYSDBA.password within your Firebird installation directory.

If the password is masterkey and your server is exposed to the Internet at all – or even to a local network, unless you trust every user with the SYSDBA password – you should change it immediately. Fire up isql or another Firebird client and connect to a database. In this example, the “employee” example database is used, because its alias is always present in a freshly installed Firebird setup:

connect localhost:employee user sysdba password masterkey;

If you do this in isql, it should respond with:

Database: localhost:employee, User: SYSDBA

Now alter the sysdba password:

alter user sysdba set password 'Zis4_viZuna83YoYo';

The SET keyword is optional, and instead of USER SYSDBA you can also use CURRENT USER, which always refers to the user you are logged in as.

If the command succeeds, you won't get any feedback. Instead, isql will just print the next “SQL>”-prompt, thus indicating that all is well and your further input is awaited.

Please notice that unlike “regular” user names, Firebird passwords are always case sensitive.

Adding Firebird user accounts

Firebird allows the creation of many different user accounts. Each of them can own databases and also have various types of access to databases and database objects it doesn't own.

Assuming you are connected to a database as SYSDBA, you can add a user account as follows:

create user billyboy password 'TooLongFor8099Comfort'

The full range of user management commands is:

CREATE USER name PASSWORD 'password' [<options>] [<tags>];
[CREATE OR] ALTER USER name [SET] [PASSWORD 'password'] [<options>] [<tags>];
ALTER CURRENT USER [SET] [PASSWORD 'password'] [<options>] [<tags>];
DROP USER name;

<options>  ::=  <option> [, <option> ...]
<option>   ::=  {FIRSTNAME | MIDDLENAME | LASTNAME} 'stringvalue'
                  | ACTIVE
                  | INACTIVE

<tags>     ::=  TAGS (<tag> [, <tag> ...])
<tag>      ::=  tagname = 'stringvalue'
                  | DROP tagname

Tags are optional key-value pairs that can be freely defined by the user. The key (tag name) must be a valid SQL identifier, the value a non-NULL string of at most 255 bytes.

Only SYSDBA and co-admins can use all these commands. Ordinary users can change their own parameters (such as password, name parts and tags, but not active/inactive) using ALTER USER name or ALTER CURRENT USER. It is not possible to change an account name.

Examples:

create user dubya password 'Xwha007_noma' firstname 'GW' lastname 'Shrubbery';
create user lorna password 'Mayday_domaka'
    tags (Street = 'Main Street', Number = '888');
alter user benny tags (shoesize = '8', hair = 'blond', drop weight);
alter current user set password 'SomethingEvenMoreSecretThanThis';
alter user dubya set inactive;
drop user ted;

The security database

Firebird user accounts are kept in a security database, which normally resides in the installation directory and is called security3.fdb (alias: security.db). Except in the case of so-called embedded connections (more about those later in this guide), connecting to a database always involves the security database, against which the user credentials are verified. Of course this is done transparently; the user doesn't have to make an explicit connection to the security database.

However, in Firebird 3 this is not the end of the story. Firebird now allows the use of multiple security databases on a system, each security database governing a specific set of databases. A database can even act as its own security database.

Showing how to set this up is outside the scope of this Quick Start Guide. You can find full details in the Release Notes, chapter Security. But it is important to realise that if a system has multiple security databases, managing user accounts while connected to a database will always affect the accounts in the security database that governs that specific database. To be on the safe side, you may want to connect to the security database itself before issuing your user management commands. Connecting to the security database used to be forbidden in recent versions of Firebird, but is now once again possible, albeit by default only locally (which means that even the localhost route is blocked).

Appointing co-administrators

Note: What follows here is not essential knowledge for beginners. You can skip it if you like and go on to the Security section.

In Firebird 2.5 and up, SYSDBA (and others with administrator rights) can appoint co-administrators. This is done with the GRANT ADMIN ROLE directive:

create user bigbill password 'bigsekrit7foryou' grant admin role;
alter user littlejohn grant admin role;

The first command creates user bigbill as a Firebird administrator, who can add, alter and drop users. The second command grants administrator privileges to the existing user littlejohn.

To revoke administrator privileges from an account, use ALTER USER ... REVOKE ADMIN ROLE.

Notes

GRANT ADMIN ROLE and REVOKE ADMIN ROLE are not GRANT and REVOKE statements, although they look that way. They are parameters to the CREATE and ALTER USER statements. The actual role name involved here is RDB$ADMIN. This role also exists in regular databases; more about that in a minute.
Every user who has received administrator rights can pass them on to others. Therefore, there is no explicit WITH ADMIN OPTION.
Just for completeness, administrators can also grant admin rights to an existing user by connecting to the security database and issuing a regular GRANT statement:
```
grant rdb$admin to littlejohn
```

Differences between co-administrators and SYSDBA

Co-admins can create, alter and drop users, but they have no automatic privileges in regular databases, like SYSDBA has.
Unlike SYSDBA, co-admins must specify the RDB$ADMIN role explicitly if they want to exert their rights as system administrator:
```
connect security.db user bigbill password bigsekrit7foryou role rdb$admin
```
For reasons explained elsewhere in this guide, connecting to the security database like this may fail if a Superserver is running. On Windows, you may circumvent this by prepending xnet:// to the database path or alias, but on Posix, you're stuck. The only solution there is to grant the co-admin the RDB$ADMIN role in at least one regular database as well. (A database that uses the security database in question, of course.) This is done in the usual way that roles are granted:
```
grant rdb$admin to bigbill
```
Grantors can be the database owner, SYSDBA, and every other user who has the RDB$ADMIN role in that database and has specified it while connecting. Every RDB$ADMIN member in a database can pass the role on to others, so again there is no WITH ADMIN OPTION. Once the co-admin has obtained the role, he can connect to the (regular) database with it and use the SQL user management commands. It's not the most elegant of solutions, but it works.

Please remember:

The RDB$ADMIN role in a database gives the grantee SYSDBA rights in that database only!

If it is the security database, the grantee can manage user accounts, but has no special privileges in other databases.
If it is a regular database, the grantee can control that database like he was SYSDBA, but again has no special privileges in other databases, and has no user administration privileges.

Of course it is possible to grant a user the RDB$ADMIN role in several databases, including the security database.

Security

Firebird 3 offers a number of security options, designed to make unauthorised access as difficult as possible. Be warned however that some configurable security features default to the old, “insecure” behaviour inherited from InterBase and earlier Firebird versions, in order not to break existing applications.

It pays to familiarise yourself with Firebird's security-related configuration parameters. You can significantly enhance your system's security if you raise the protection level wherever possible. This is not only a matter of setting parameters, by the way: other measures involve tuning filesystem access permissions, an intelligent user accounts policy, etc.

Below are some guidelines for protecting your Firebird server and databases.

Run Firebird as non-system user

On Unix-like systems, Firebird already runs as user firebird by default, not as root. On Windows server platforms, you can also run the Firebird service under a designated user account (e.g. Firebird). The default practice – running the service as the LocalSystem user – poses a security risk if your system is connected to the Internet. Consult README.instsvc.txt in the doc subdir to learn more about this.

Change SYSDBA's password

As discussed before, if your Firebird server is reachable from the network and the system password is masterkey, change it.

Don't create user databases as SYSDBA

SYSDBA is a very powerful account, with full (destructive) access rights to all your Firebird databases. Its password should be known to a few trusted database administrators only. Therefore, you shouldn't use this super-account to create and populate regular databases. Instead, generate normal user accounts, and provide their account names and passwords to your users as needed. You can do this with the SQL user management commands as shown above, or with any decent third-party Firebird administration tool.

Protect databases on the filesystem level

Anybody who has filesystem-level read access to a database file can copy it, install it on a system under his or her own control, and extract all data from it – including possibly sensitive information. Anybody who has filesystem-level write access to a database file can corrupt it or totally destroy it.

Also, anybody with filesystem-level access to a database can make an embedded connection to it posing as any Firebird user (including SYSDBA) without having his credentials checked. This can be especially disastrous if it concerns the security database!

As a rule, only the Firebird server process should have access to the database files. Users don't need, and should not have, access to the files – not even read-only. They query databases via the server, and the server makes sure that users only get the allowed type of access (if at all) to any objects within the database.

As a relaxation of this rule, most Firebird configurations allow users to create and use databases in their own filesystem space and make embedded connections to them. Since these are their files and their data, one may argue that unrestricted and possibly destructive access should be their own concern, not yours.

If you don't want or need this relaxation, follow the instructions in the next item.

Disable embedded connections

If you don't want any type of direct access, you may disable embedded mode (= direct filesystem-level access) altogether by opening firebird.conf and locating the Providers entry. The default (which is probably commented out) is:

#Providers = Remote,Engine12,Loopback

Now, either remove the hash mark and the Engine12 provider (this is the one that makes the embedded connections), or – better – add an uncommented line:

Providers = Remote,Loopback

The Remote provider takes care of remote connections; the Loopback provider is responsible for TCP/IP connections via localhost, as well as (on Windows) WNET/NetBEUI and XNET connections to databases on the local machine. All these connection types require full authentication and have the server process, not the user process, open the database file.

Please notice that you can also set the Providers parameter on a per-database basis. You can set a default in firebird.conf as shown above, and then override it for individual databases in databases.conf like this:

bigbase = C:\Databases\Accounting\Biggus.fdb
{
  Providers = Engine12,Loopback
}

The first line defines the alias (see next item), and everything between the curly brackets are parameters for that specific database. You'll find databases.conf in the same directory as firebird.conf. Refer to the Release Notes, chapter Configuration Additions and Changes, section Per-database Configuration, for more information about the various parameters.

Use database aliases

Database aliases hide physical database locations from the client. Using aliases, a client can e.g. connect to “frodo:zappa” without having to know that the real location is frodo:/var/firebird/music/underground/mothers_of_invention.fdb. Aliases also allow you to relocate databases while the clients keep using their existing connection strings.

Aliases are listed in the file databases.conf, in this format on Windows machines:

poker = E:\Games\Data\PokerBase.fdb
blackjack.fdb = C:\Firebird\Databases\cardgames\blkjk_2.fdb

And on Linux:

books = /home/bookworm/database/books.fdb
zappa = /var/firebird/music/underground/mothers_of_invention.fdb

Giving the alias an .fdb (or any other) extension is fully optional. Of course if you do include it, you must also specify it when you use the alias to connect to the database.

Aliases, once entered and saved, take effect immediately. There is no need to restart the server.

Restrict database access

The DatabaseAccess parameter in firebird.conf can be set to Restrict to limit access to explicitly listed filesystem trees, or even to None to allow access to aliased databases only. Default is Full, i.e. no restrictions.

Note that this is not the same thing as the filesystem-level access protection discussed earlier: when DatabaseAccess is anything other than Full, the server will refuse to open any databases outside the defined scope even if it has sufficient rights on the database files.

Choose your authentication method(s)

Firebird supports three authentication methods when connecting to databases:

Srp (Secure Remote Password): The user must identify him/herself with a Firebird username and password, which the server checks against the security database. The maximum effective password length is around 20 bytes, although you may specify longer passwords. Wire encryption is used.
Win_Sspi (Windows Security Support Provider Interface): The user is logged in automatically with his Windows account name.
Legacy_Auth: Insecure method used in previous Firebird versions. Passwords have a maximum length of 8 bytes and are sent unencrypted across the wire. Avoid this method if possible.

Two configuration parameters control Firebird's authentication behaviour:

AuthServer determines how a user can connect to the local server. It is usually “Srp” or, on Windows machines, “Srp, Win_Sspi”. In the latter case, the user will be authenticated with his Windows login if he fails to supply user credentials (causing the Srp method, which is tried first, to fail).
AuthClient defines how the local client tries to authenticate the user when making a connection. It is usually “Srp, Win_Sspi, Legacy_Auth”, allowing the user to connect to pre-Firebird-3 servers on remote machines.

If Win_Sspi and/or Legacy_Auth are allowed on the server side, you must also set the WireCrypt parameter to Enabled or Disabled, but not Required.

Likewise, if a server (not a client!) supports Legacy_Auth, the UserManager parameter must be set to Legacy_UserManager instead of Srp. (The default Srp user manager can still be addressed by adding USING PLUGIN SRP to your user management commands.)

The AuthServer, AuthClient, WireCrypt and UserManager parameters are all set in firebird.conf en can be overridden per database in databases.conf.

Please notice: enabling Win_Sspi on the server activates the plugin but doesn't grant Windows accounts any type of access to databases yet. Logging in to, say, the employee database without credentials (and making sure no embedded connection is made) will result in this error message:

SQL> connect xnet://employee;
Statement failed, SQLSTATE = 28000
Missing security context for employee

In other words: “We know who you are (because the Win_Sspi plugin identified you) but you can't come in.”

The solution is to create, as SYSDBA, a global mapping that gives any Windows account access to databases – but no special privileges – under the same name. This is done with the following command:

create global mapping trusted_auth
using plugin win_sspi
from any user to user

Trusted_auth is just a chosen name for the mapping. You may use another identifier. From any user means that the mapping is valid for any user authenticated by the Win_Sspi plugin. To user indicates that every user will be made known under his own Windows account name in each database he connects to. If instead we had specified to user bob, then every Windows user authenticated by the Win_Sspi plugin would be bob in every database.

With the mapping in effect, the “Windows trusted” connection succeeds:

SQL> connect xnet://employee;
Database: xnet://employee, User: SOFA\PAUL
SQL> select current_user from rdb$database;

USER
===============================
SOFA\PAUL

Note

With embedded connections, i.e. serverless connections handled by Engine12, where the client process directly opens the database file, the user is also logged in under his Windows account name if he doesn't provide a user name when connecting. However, this doesn't require Win_Sspi to be enabled, nor does it need any explicit mapping:

SQL> connect employee;
Database: employee, User: PAUL
SQL> select current_user from rdb$database;

USER
===============================
PAUL

Consider whether Windows administrators should have SYSDBA rights

In Firebird 2.1, if the (now defunct) configuration parameter Authentication was trusted or mixed, Windows administrators would automatically receive SYSDBA privileges in all databases, including the security database. In Firebird 2.5 and later, this is no longer the case. This reduces the risk that administrators with little or no Firebird knowledge mess up databases or user accounts.

If you still want to apply the automatic SYSDBA mapping as it was in Firebird 2.1, login as SYSDBA and give the command:

create global mapping win_admin_sysdba
using plugin win_sspi
from predefined_group domain_any_rid_admins
to user sysdba

This grants all Windows administrators automatic SYSDBA rights in every database (including the security database, so they can manage user accounts), provided that they are authenticated by the Win_Sspi plugin. To achieve this, they must connect

without supplying any user credentials, and
making sure that the Engine12 provider doesn't kick in. This is easily achieved with a connection string like xnet://local-path-or-alias.

To give just one administrator – or indeed any user – full SYSDBA power, use this command:

create global mapping frank_sysdba
using plugin win_sspi
from user "sofa\frank"
to user sysdba

The double quotes are necessary because of the backslash in the user name. (Specifying just frank will be accepted by Firebird, but won't result in a working mapping on most, if not all, Windows systems.)

You can drop any mapping with the command:

DROP [GLOBAL] MAPPING mapping_name

E.g.:

drop global mapping win_admin_sysdba;
drop global mapping frank_sysdba;

The GLOBAL keyword is necessary if it concerns a global mapping and you're not directly connected to the security database where the mapping is registered.

Administration tools

The Firebird kit does not come with a GUI admin tool. It does have a set of command-line tools – executable programs which are located in the bin subdirectory of your Firebird installation (on Windows, they are in the installation directory itself). One of them, isql, has already been introduced to you.

The range of excellent GUI tools available for use with a Windows client machine is too numerous to describe here. At least one of them, FlameRobin, is also available for Linux.

Explore the Download > Tools > Administration page at http://www.ibphoenix.com for all of the options.

Note

Remember: you can use a Windows client to access a Linux server and vice-versa.