Chapter 13. Security

Databases must be secure and so must the data stored in them. Firebird provides three levels of data security: user authentication at the server level, SQL privileges within databases, and — optionally — database encryption. This chapter describes how to manage security at these three levels.

Note

There is also a fourth level of data security: wire protocol encryption, which encrypts data in transit between client and server. Wire protocol encryption is out of scope for this Language Reference.

13.1 User Authentication

The security of the entire database depends on identifying a user and verifying its authority, a procedure known as authentication. User authentication can be performed in several ways, depending on the setting of the AuthServer parameter in the firebird.conf configuration file. This parameter contains the list of authentication plugins that can be used when connecting to the server. If the first plugin fails when authenticating, then the client can proceed with the next plugin, etc. When no plugin could authenticate the user, the user receives an error message.

The information about users authorised to access a specific Firebird server is stored in a special security database named security4.fdb. Each record in security4.fdb is a user account for one user. For each database, the security database can be overridden in the databases.conf file (parameter SecurityDatabase). Any database can be a security database, even for that database itself.

A username, with a maximum length of 63 characters, is an identifier, following the normal rules for identifiers (unquoted case-insensitive, double-quoted case-sensitive). For backwards compatibility, some statements (e.g. isqls CONNECT) accept usernames enclosed in single quotes, which will behave as normal, unquoted identifiers.

The maximum password length depends on the user manager plugin (parameter UserManager, in firebird.conf or databases.conf). Passwords are case-sensitive. The default user manager is the first plugin in the UserManager list, but this can be overridden in the SQL user management statements. For the Srp plugin, the maximum password length is 255 characters, for an effective length of 20 bytes (see also Why is the effective password length of SRP 20 bytes?). For the Legacy_UserManager plugin only the first eight bytes are significant; whilst it is valid to enter a password longer than eight bytes for Legacy_UserManager, any subsequent characters are ignored.

Why is the effective password length of SRP 20 bytes?

The SRP plugin does not actually have a 20 byte limit on password length, and longer passwords can be used. Hashes of different passwords longer than 20 bytes are also — usually — different. This effective limit comes from the limited hash length in SHA1 (used inside Firebirds SRP implementation), 20 bytes or 160 bits, and the pigeonhole principle. Sooner or later, there will be a shorter (or longer) password that has the same hash (e.g. in a brute force attack). That is why often the effective password length for the SHA1 algorithm is said to be 20 bytes.

The embedded version of the server does not use authentication. However, the username, and — if necessary — the role, must be specified in the connection parameters, as they control access to database objects.

SYSDBA or the owner of the database get unrestricted access to all objects of the database. Users with the RDB$ADMIN role get similar unrestricted access if they specify the role when connecting.

13.1.1 Specially Privileged Users

In Firebird, the SYSDBA account is a Superuser that exists beyond any security restrictions. It has complete access to all objects in all regular databases on the server, and full read/write access to the accounts in the security database security4.fdb. No user has access to the metadata of the security database.

For Srp, the SYSDBA account does not exist by default; it will need to be created using an embedded connection. For Legacy_Auth, the default SYSDBA password on Windows and MacOS is masterkey — or masterke, to be exact, because of the 8-character length limit.

Extremely Important!

The default password masterkey is known across the universe. It should be changed as soon as the Firebird server installation is complete.

Other users can acquire elevated privileges in several ways, some of which are dependent on the operating system platform. These are discussed in the sections that follow and are summarised in Section 13.1.3, Administrators and Section 13.1.4, Fine-grained System Privileges.

13.1.1.1 POSIX Hosts

On POSIX systems, including MacOS, the POSIX username will be used as the Firebird Embedded username if username is not explicitly specified.

13.1.1.1.1 The SYSDBA User on POSIX

On POSIX hosts, other than MacOSX, the SYSDBA user does not have a default password. If the full installation is done using the standard scripts, a one-off password will be created and stored in a text file in the same directory as security4.fdb, commonly /opt/firebird/. The name of the password file is SYSDBA.password.

Note

In an installation performed by a distribution-specific installer, the location of the security database and the password file may be different from the standard one.

13.1.1.1.2 The root User

The root user can act directly as SYSDBA on Firebird Embedded. Firebird will treat root as though it were SYSDBA, and it provides access to all databases on the server.

13.1.1.2 Windows Hosts

On Windows server-capable operating systems, operating system accounts can be used. Windows authentication (also known as trusted authentication) can be enabled by including the Win_Sspi plugin in the AuthServer list in firebird.conf. The plugin must also be present in the AuthClient setting at the client-side.

Windows operating system administrators are not automatically granted SYSDBA privileges when connecting to a database. To make that happen, the internally-created role RDB$ADMIN must be altered by SYSDBA or the database owner, to enable it. For details, refer to the later section entitled Section 13.1.2.3, AUTO ADMIN MAPPING.

Note

Prior to Firebird 3.0, with trusted authentication enabled, users who passed the default checks were automatically mapped to CURRENT_USER. In Firebird 3.0 and later, the mapping must be done explicitly using CREATE MAPPING.

13.1.1.3 The Database Owner

The owner of a database is either the user who was CURRENT_USER at the time of creation (or restore) of the database or, if the USER parameter was supplied in the CREATE DATABASE statement, the specified user.

Owner is not a username. The user who is the owner of a database has full administrator privileges with respect to that database, including the right to drop it, to restore it from a backup and to enable or disable the Section 13.1.2.3, AUTO ADMIN MAPPING capability.

Note

Prior to Firebird 2.1, the owner had no automatic privileges over any database objects that were created by other users.

13.1.1.4 Users with the USER_MANAGEMENT System Privilege

A user with the USER_MANAGEMENT system privilege in the security database can create, alter and drop users. To receive the USER_MANAGEMENT privilege, the security database must have a role with that privilege:

  1. create role MANAGE_USERS
  2. set system privileges to USER_MANAGEMENT;

There are two options for the user to exercise these privileges:

  1. Grant the role as a default role. The user will always be able to create, alter or drop users.

    1. grant default MANAGE_USERS to user ALEX;
  2. Grant the role as a normal role. The user will only be able to create, alter or drop users when the role is specified explicitly on login or using SET ROLE.

    1. grant MANAGE_USERS to user ALEX;

    If the security database is a different database than the user connects to — which is usually the case when using security4.fdb — then a role with the same name must also exist and be granted to the user in that database for the user to be able to activate the role. The role in the other database does not need any system privileges or other privileges.

Note

The USER_MANAGEMENT system privilege does not allow a user to grant or revoke the admin role. This requires the RDB$ADMIN role.

13.1.2 RDB$ADMIN Role

The internally-created role RDB$ADMIN is present in all databases. Assigning the RDB$ADMIN role to a regular user in a database grants that user the privileges of the SYSDBA, in that database only.

The elevated privileges take effect when the user is logged in to that regular database under the RDB$ADMIN role, and gives full control over all objects in that database.

Being granted the RDB$ADMIN role in the security database confers the authority to create, edit and delete user accounts.

In both cases, the user with the elevated privileges can assign RDB$ADMIN role to any other user. In other words, specifying WITH ADMIN OPTION is unnecessary because it is built into the role.

13.1.2.1 Granting the RDB$ADMIN Role in the Security Database

Since nobody — not even SYSDBA — can connect to the security database remotely, the GRANT and REVOKE statements are of no use for this task. Instead, the RDB$ADMIN role is granted and revoked using the SQL statements for user management:

  1. CREATE USER new_user
  2. PASSWORD 'password'
  3. GRANT ADMIN ROLE;
  4. ALTER USER existing_user
  5. GRANT ADMIN ROLE;
  6. ALTER USER existing_user
  7. REVOKE ADMIN ROLE;

Note

GRANT ADMIN ROLE and REVOKE ADMIN ROLE are not statements in the GRANT and REVOKE lexicon. They are three-word clauses to the statements CREATE USER and ALTER USER.

Table 13.1.2.1.1 Parameters for RDB$ADMIN Role GRANT and REVOKE

ParameterDescription

new_user

Name for the new user

existing_user

Name of an existing user

password

User password

The grantor must be logged in as an administrator.

See alsoCREATE USER, ALTER USER, GRANT, REVOKE

13.1.2.1.1 Doing the Same Task Using gsec

Warning

With Firebird 3.0, gsec was deprecated. It is recommended to use the SQL user management statements instead.

An alternative is to use gsec with the -admin parameter to store the RDB$ADMIN attribute on the user’s record:

  1. gsec -add new_user -pw password -admin yes
  2. gsec -mo existing_user -admin yes
  3. gsec -mo existing_user -admin no

Note

Depending on the administrative status of the current user, more parameters may be needed when invoking gsec, e.g. -user and -pass, or -trusted.

13.1.2.1.2 Using the RDB$ADMIN Role in the Security Database

To manage user accounts through SQL, the grantee must specify the RDB$ADMIN role when connecting or through SET ROLE. No user can connect to the security database remotely, so the solution is that the user connects to a regular database where they also have RDB$ADMIN rights, supplying the RDB$ADMIN role in their login parameters. From there, they can submit any SQL user management command.

If there is no regular database where the user has the RDB$ADMIN role, then account management via SQL queries is not possible, unless they connect directly to the security database using an embedded connection.

13.1.2.1.2.1 Using gsec with RDB$ADMIN Rights

To perform user management with gsec, the user must provide the extra switch -role rdb$admin.

13.1.2.2 Granting the RDB$ADMIN Role in a Regular Database

In a regular database, the RDB$ADMIN role is granted and revoked with the usual syntax for granting and revoking roles:

  1. GRANT [ROLE] RDB$ADMIN TO username
  2. REVOKE [ROLE] RDB$ADMIN FROM username

Table 13.1.2.2.1 Parameters for RDB$ADMIN Role GRANT and REVOKE

ParameterDescription

username

Name of the user

In order to grant and revoke the RDB$ADMIN role, the grantor must be logged in as an administrator.

See alsoGRANT, REVOKE

13.1.2.2.1 Using the RDB$ADMIN Role in a Regular Database

To exercise their RDB$ADMIN privileges, the grantee has to include the role in the connection attributes when connecting to the database, or specify it later using SET ROLE.

13.1.2.3 AUTO ADMIN MAPPING

Windows Administrators are not automatically granted RDB$ADMIN privileges when connecting to a database (if Win_Sspi is enabled, of course) The AUTO ADMIN MAPPING switch now determines whether Administrators have automatic RDB$ADMIN rights, on a database-by-database basis. By default, when a database is created, it is disabled.

If AUTO ADMIN MAPPING is enabled in the database, it will take effect whenever a Windows Administrator connects:

  1. using Win_Sspi authentication, and

  2. without specifying any role

After a successful auto admin connection, the current role is set to RDB$ADMIN.

If an explicit role was specified on connect, the RDB$ADMIN role can be assumed later in the session using SET TRUSTED ROLE.

13.1.2.3.1 Auto Admin Mapping in Regular Databases

To enable and disable automatic mapping in a regular database:

  1. ALTER ROLE RDB$ADMIN
  2. SET AUTO ADMIN MAPPING; -- enable it
  3. ALTER ROLE RDB$ADMIN
  4. DROP AUTO ADMIN MAPPING; -- disable it

Either statement must be issued by a user with sufficient rights, that is:

  • The database owner

  • An administrator

  • A user with the ALTER ANY ROLE privilege

Note

The statement

  1. ALTER ROLE RDB$ADMIN
  2. SET AUTO ADMIN MAPPING;

is a simplified form of a CREATE MAPPING statement to create a mapping of the predefined group DOMAIN_ANY_RID_ADMINS to the role of RDB$ADMIN:

  1. CREATE MAPPING WIN_ADMINS
  2. USING PLUGIN WIN_SSPI
  3. FROM Predefined_Group DOMAIN_ANY_RID_ADMINS
  4. TO ROLE RDB$ADMIN;

Accordingly, the statement

  1. ALTER ROLE RDB$ADMIN
  2. DROP AUTO ADMIN MAPPING

is equivalent to the statement

  1. DROP MAPPING WIN_ADMINS;

For details, see Section 13.7, Mapping of Users to Objects

In a regular database, the status of AUTO ADMIN MAPPING is checked only at connect time. If an Administrator has the RDB$ADMIN role because auto-mapping was on when they logged in, they will keep that role for the duration of the session, even if they or someone else turns off the mapping in the meantime.

Likewise, switching on AUTO ADMIN MAPPING will not change the current role to RDB$ADMIN for Administrators who were already connected.

13.1.2.3.2 Auto Admin Mapping in the Security Database

The ALTER ROLE RDB$ADMIN statement cannot enable or disable AUTO ADMIN MAPPING in the security database. However, you can create a global mapping for the predefined group DOMAIN_ANY_RID_ADMINS to the role RDB$ADMIN in the following way:

  1. CREATE GLOBAL MAPPING WIN_ADMINS
  2. USING PLUGIN WIN_SSPI
  3. FROM Predefined_Group DOMAIN_ANY_RID_ADMINS
  4. TO ROLE RDB$ADMIN;

Additionally, you can use gsec:

  1. gsec -mapping set
  2. gsec -mapping drop

Note

Depending on the administrative status of the current user, more parameters may be needed when invoking gsec, e.g. -user and -pass, or -trusted.

Only SYSDBA can enable AUTO ADMIN MAPPING if it is disabled, but any administrator can turn it off.

When turning off AUTO ADMIN MAPPING in gsec, the user turns off the mechanism itself which gave them access, and thus they would not be able to re-enable AUTO ADMIN MAPPING. Even in an interactive gsec session, the new flag setting takes effect immediately.

13.1.3 Administrators

As a general description, an administrator is a user that has sufficient rights to read, write to, create, alter or delete any object in a database to which that user’s administrator status applies. The table summarises how Superuser privileges are enabled in the various Firebird security contexts.

Table 13.1.3.1 Administrator (Superuser) Characteristics

UserRDB$ADMIN RoleComments

SYSDBA

Auto

Exists automatically at server level. Has full privileges to all objects in all databases. Can create, alter and drop users, but has no direct remote access to the security database

root user on POSIX

Auto

Exactly like SYSDBA. Firebird Embedded only.

Superuser on POSIX

Auto

Exactly like SYSDBA. Firebird Embedded only.

Windows Administrator

Set as CURRENT_ROLE if login succeeds

Exactly like SYSDBA if all of the following are true:

  • In firebird.conf file, AuthServer includes Win_Sspi, and Win_Sspi is present in the client-side plugins (AuthClient) configuration

  • In databases where AUTO ADMIN MAPPING is enabled, or an equivalent mapping of the predefined group DOMAIN_ANY_RID_ADMINS for the role RDB$ADMIN exists

  • No role is specified at login

Database owner

Auto

Like SYSDBA, but only in the databases they own

Regular user

Must be previously granted; must be supplied at login

Like SYSDBA, but only in the databases where the role is granted

POSIX OS user

Must be previously granted; must be supplied at login

Like SYSDBA, but only in the databases where the role is granted. Firebird Embedded only.

Windows user

Must be previously granted; must be supplied at login

Like SYSDBA, but only in the databases where the role is granted. Only available if in firebird.conf file, AuthServer includes Win_Sspi, and Win_Sspi is present in the client-side plugins (AuthClient) configuration

13.1.4 Fine-grained System Privileges

In addition to granting users full administrative privileges, Firebird 4.0 introduced system privileges which makes it possible to grant regular users a subset of administrative privileges that have historically been limited to SYSDBA and administrators only. For example:

  • Run utilities such as gbak, gfix, nbackup and so on

  • Shut down a database and bring it online

  • Trace other users’ attachments

  • Access the monitoring tables

  • Run management statements

The implementation defines a set of system privileges, analogous to object privileges, from which lists of privileged tasks can be assigned to roles.

It is also possible to grant normal privileges to a system privilege, making the system privilege act like a special role type.

The system privileges are assigned through CREATE ROLE and ALTER ROLE.

Warning

Be aware that each system privilege provides a very thin level of control. For some tasks it may be necessary to give the user more than one privilege to perform some task. For example, add IGNORE_DB_TRIGGERS to USE_GSTAT_UTILITY because gstat needs to ignore database triggers.

13.1.4.1 List of Valid System Privileges

The following table lists the names of the valid system privileges that can be granted to and revoked from roles.

USER_MANAGEMENT

Manage users (given in the security database)

READ_RAW_PAGES

Read pages in raw format using Attachment::getInfo()

CREATE_USER_TYPES

Add/change/delete non-system records in RDB$TYPES

USE_NBACKUP_UTILITY

Use nbackup to create database copies

CHANGE_SHUTDOWN_MODE

Shut down database and bring online

TRACE_ANY_ATTACHMENT

Trace other users’ attachments

MONITOR_ANY_ATTACHMENT

Monitor (tables MON$) other users’ attachments

ACCESS_SHUTDOWN_DATABASE

Access database when it is shut down

CREATE_DATABASE

Create new databases (given in security.db)

DROP_DATABASE

Drop this database

USE_GBAK_UTILITY

Use gbak utility

USE_GSTAT_UTILITY

Use gstat utility

USE_GFIX_UTILITY

Use gfix utility

IGNORE_DB_TRIGGERS

Instruct engine not to run DB-level triggers

CHANGE_HEADER_SETTINGS

Modify parameters in DB header page

SELECT_ANY_OBJECT_IN_DATABASE

Use SELECT for any selectable object

ACCESS_ANY_OBJECT_IN_DATABASE

Access (in any possible way) any object

MODIFY_ANY_OBJECT_IN_DATABASE

Modify (up to drop) any object

CHANGE_MAPPING_RULES

Change authentication mappings

USE_GRANTED_BY_CLAUSE

Use GRANTED BY in GRANT and REVOKE statements

GRANT_REVOKE_ON_ANY_OBJECT

GRANT and REVOKE rights on any object in database

GRANT_REVOKE_ANY_DDL_RIGHT

GRANT and REVOKE any DDL rights

CREATE_PRIVILEGED_ROLES

Use SET SYSTEM PRIVILEGES in roles

MODIFY_EXT_CONN_POOL

Use command ALTER EXTERNAL CONNECTIONS POOL

REPLICATE_INTO_DATABASE

Use replication API to load change sets into database