FirebirdSQL logo

Appointing co-administrators

Tip

What follows here is not essential knowledge for beginners.You can skip it if you like and go on to the [qsg5-config-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.

Note
  • GRANT ADMIN ROLE and REVOKE ADMIN ROLE are not GRANT and REVOKE statements, although they look that way.They are clauses of 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 contrary to SYSDBA they have no automatic privileges in regular databases.

  • 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 connect to a regular database through the server, for example using localhost:employee.(This must be a database that uses the security database in question.)

    Since Firebird 4.0, a co-admin no longer needs to have the RDB$ADMIN privileges in the regular database to be able to execute user management statements against the security database, their privileges in the security database are sufficient.

    Note
    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 they are SYSDBA, but again they have no special privileges in other databases, and have no user administration privileges.

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

  • To grant a user admin rights in a regular database you can use 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 ADMINOPTION.

Security

Firebird 5 offers a number of security options, designed to make unauthorised access as difficult as possible.

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 directory 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 grant them the CREATE DATABASE privilege, 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 their 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 their 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,Engine13,Loopback

Now, either remove the hash mark and the Engine13 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) 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 = Engine13,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 — for example — 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:

  1. Srp (Secure Remote Password): The user must identify themselves 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 up to 255 characters.Wire encryption is used.The server supports various Srp authentication plugins, with Srp256 as the default (which uses SHA256 for the user proof).

  2. Win_Sspi (Windows Security Support Provider Interface): The user is logged in automatically with their Windows account name.Wire encryption is used.

  3. 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.Wire encryption is not supported.

Two configuration parameters control Firebird’s authentication behaviour:

  • AuthServer determines how a user can connect to the local server.It defaults to “Srp256”, or — on Windows — “Srp256, Win_Sspi”.In the latter case, the user will be authenticated with their Windows login if they fail to supply user credentials (causing the Srp256 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 “Srp256, Srp, Win_Sspi, Legacy_Auth”, allowing the user to connect to pre-Firebird-3 servers on remote machines.

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

If Legacy_Auth is enabled, you will also want to change the UserManager setting to Srp, Legacy_UserManager (or Legacy_UserManager, Srp if you want to manage legacy accounts by default and/or through gsec).In user management statements, you can use the USING PLUGIN name clause to specify the user manager to use (only user managers listed in UserManager are allowed).

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’tcome in.”

The solution is to create, as SYSDBA or a co-admin, 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 their own Windows account name in each database they connect 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 Engine13, where the client process directly opens the database file, the user is also logged in under their Windows account name if they don’t provide a username 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 Engine13 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 username.(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 following sites for more options:

Note

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