Chapter 11. Security

Databases must be secure and so must the data stored in them. Firebird provides two levels of data security protection: user authentication at the server level and SQL privileges within databases. This chapter tells you how to manage security at both levels.

11.1 User Authentication

The security of the entire database depends on identifying a user on verifying its authority, a procedure known as authentication. The information about users authorised to access a specific Firebird server is stored in a special security database named security2.fdb. Each record in security2.fdb is a user account for one user.

A user name, consisting of up to 31 characters, is a case-insensitive system identifier. A user must have a password, of which the first eight are significant. Whilst it is valid to enter a password longer than eight characters, any subsequent characters are ignored. Passwords are case-sensitive.

If the user specified during the connection is the SYSDBA, the database owner or a specially privileged user, that user will have unlimited access to the database.

11.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 security2.fdb. No user has access to the metadata of the security database.

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 11.1.3, Administrators.

11.1.1.1 POSIX Hosts

On POSIX systems, including MacOSX, Firebird will interpret a POSIX user account as though it were a Firebird user account in its own security database, provided the server sees the client machine as a trusted host and the system user accounts exist on both the client and the server. To establish a trusted relationship with the client host, the corresponding entries must be included in one of the files /etc/hosts.equiv or /etc/gds_hosts.equiv on Firebird’s host server.

  • The file hosts.equiv contains trusted relationships at operating system level, encompassing all services (rlogin, rsh, rcp, and so on)

  • The file gds_hosts.equiv contains trusted relationships between Firebird hosts only.

The format is identical for both files and looks like this:

hostname [username]
11.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 security2.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.

11.1.1.1.2 The root User

The root user can act directly as SYSDBA on POSIX host systems. Firebird interprets root as though it were SYSDBA and it provides access to all databases on the server.

11.1.1.2 Windows Hosts

On Windows server-capable operating systems, operating system accounts can be used. Trusted Authentication must be enabled by setting the Authentication parameter to Trusted or Mixed in the configuration file, firebird.conf.

Even with trusted authentication enabled, Windows operating system Administrators are not automatically granted SYSDBA privileges when they connect 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 11.1.2.3, AUTO ADMIN MAPPING.

The embedded version of Firebird server on Windows does not use server-level authentication. However, because objects within a database are subject to SQL privileges, a valid user name and, if applicable, a role, may be required in the connection parameters.

11.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, if the parameters USER and PASSWORD were supplied in the CREATE DATABASE statement, the user cited there.

Owner is not a user name. The user who is the owner of a database has full administrator rights with respect to that database, including the right to drop it, to restore it from a backup and to enable or disable the Section 11.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.

11.1.2 RDB$ADMIN Role

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

The elevated privileges take effect when the user is logged in to that regular database under the RDB$ADMIN role and give full control over all objects in the 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.

11.1.2.1 Granting the RDB$ADMIN Role in the Security Database

Since nobody — not even SYSDBA — can connect to the security database, 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:

CREATE USER new_user
  PASSWORD 'password'
  GRANT ADMIN ROLE;
 
ALTER USER existing_user
  GRANT ADMIN ROLE;
 
ALTER USER existing_user
  REVOKE ADMIN ROLE;
🛈︎
Note

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

Table 11.1.2.1.1 Parameters for RDB$ADMIN Role GRANT and REVOKE
ParameterDescription

new_user

Using CREATE USER, name for the new user

existing_user

Using ALTER USER, Name of an existing user

password

Using CREATE USER, password for the new user. Its theoretical limit is 31 bytes but only the first 8 characters are considered.

The grantor must be already logged in as an administrator.

See alsoSection 11.1.4.1, CREATE USER, Section 11.1.4.2, ALTER USER

11.1.2.1.1 Doing the Same Task Using gsec

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

gsec -add new_user -pw password -admin yes
gsec -mo existing_user -admin yes
gsec -mo existing_user -admin no
🛈︎
Note

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

11.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. No user can connect to the security database, so the solution is that the user connects to a regular database where he also has RDB$ADMIN rights, supplying the RDB$ADMIN role in his login parameters. From there, he can submit any SQL user management command.

The SQL route for the user is blocked for any database in which he has not been the granted the RDB$ADMIN role.

11.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.

11.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:

GRANT [ROLE] RDB$ADMIN TO username
 
REVOKE [ROLE] RDB$ADMIN FROM username

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

See alsoSection 11.2.2.1, GRANT, Section 11.2.3.1, REVOKE

11.1.2.2.1 Using the RDB$ADMIN Role in a Regular Database

To exercise his RDB$ADMIN privileges, the grantee simply includes the role in the connection attributes when connecting to the database.

11.1.2.3 AUTO ADMIN MAPPING

In Firebird 2.1, Windows Administrators would automatically receive SYSDBA privileges if trusted authentication was configured for server connections. In Firebird 2.5, it is no longer automatic. The setting of the AUTO ADMIN MAPPING switch now determines whether Administrators have automatic SYSDBA 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 trusted authentication, and

  2. without specifying any role

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

11.1.2.3.1 Auto Admin Mapping in Regular Databases

To enable and disable automatic mapping in a regular database:

ALTER ROLE RDB$ADMIN
  SET AUTO ADMIN MAPPING;  -- enable it
 
ALTER ROLE RDB$ADMIN
  DROP AUTO ADMIN MAPPING; -- disable it

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

In regular databases, the status of AUTO ADMIN MAPPING is checked only at connection time. If an Administrator has the RDB$ADMIN role because auto-mapping was on when he logged in, he will keep that role for the duration of the session, even if he 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.

11.1.2.3.2 Auto Admin Mapping in the Security Database

No SQL statements exist to switch automatic mapping on and off in the security database. Instead, gsec must be used:

gsec -mapping set
 
gsec -mapping drop

More gsec switches may be needed, depending on what kind of log-in you used to connect, e.g., -user and -pass, or -trusted.

Only SYSDBA can set the auto-mapping on if it is disabled. Any administrator can drop (disable) it.

11.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 11.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 access to the security database

root user on POSIX

Auto

Exactly like SYSDBA

Superuser on POSIX

Auto

Exactly like SYSDBA

Windows Administrator

Set as CURRENT_ROLE if login succeeds

Exactly like SYSDBA if all of the following are true:

In firebird.conf file

Authentication = mixed or trusted and Firebird is restarted before proceeding

AUTO ADMIN MAPPING

Enabled in all databases in which the user needs Superuser privileges

Login

Does not include a role

Database owner

Auto

Like SYSDBA, but only in the database of which he is the owner

Regular user

Must be previously granted; must be supplied at login

Like SYSDBA, but only in the database(s) where the role is granted

POSIX OS user

Must be previously granted; must be supplied at login

Like SYSDBA, but only in the database(s) where the role is granted

Windows user

Must be previously granted; must be supplied at login

Like SYSDBA, but only in the database(s) where the role is granted. Not available if config file parameter Authentication = native

11.1.4 SQL Statements for User Management

In Firebird 2.5 and above, user accounts are created, modified and deleted using a series of SQL statements that can be submitted by a user with full administrator rights in the security database.

🛈︎
Note

For a Windows Administrator, AUTO ADMIN MAPPING enabled only in a regular database is not sufficient to permit management of other users. For instructions to enable it in the security database, see Section 11.1.2.3.2, Auto Admin Mapping in the Security Database.

Non-privileged users can use only the ALTER USER statement and then only to edit some data in their own accounts.

11.1.4.1 CREATE USER

Used forCreating a Firebird user account

Available inDSQL

Syntax

CREATE USER username PASSWORD 'password'
  [FIRSTNAME 'firstname']
  [MIDDLENAME 'middlename']
  [LASTNAME 'lastname']
  [GRANT ADMIN ROLE]

Table 11.1.4.1.1 CREATE USER Statement Parameters
ParameterDescription

username

User name. The maximum length is 31 characters, following the rules for Firebird regular identifiers. It is always case-insensitive

password

User password. Its theoretical limit is 31 bytes but only the first 8 characters are considered. Case-sensitive

firstname

Optional: User’s first name. Maximum length 31 characters

middlename

Optional: User’s middle name. Maximum length 31 characters

lastname

Optional: User’s last name. Maximum length 31 characters

Use a CREATE USER statement to create a new Firebird user account. The user must not already exist in the Firebird security database, or a primary key violation error message will be returned.

The username argument must follow the rules for Firebird regular identifiers: see Identifiers in the Structure chapter. User names are always case-insensitive. Supplying a user name enclosed in double quotes will not cause an exception: the quotes will be ignored. If a space is the only illegal character supplied, the user name will be truncated back to the first space character. Other illegal characters will cause an exception.

The PASSWORD clause specifies the user’s password. A password of more than eight characters is accepted with a warning but any surplus characters will be ignored.

The optional FIRSTNAME, MIDDLENAME and LASTNAME clauses can be used to specify additional user properties, such as the person’s first name, middle name and last name, respectively. They are just simple VARCHAR(31) fields and can be used to store anything you prefer.

If the GRANT ADMIN ROLE clause is specified, the new user account is created with the privileges of the RDB$ADMIN role in the security database (security2.fdb). It allows the new user to manage user accounts from any regular database he logs into, but it does not grant the user any special privileges on objects in those databases.

To create a user account, the current user must have administrator privileges in the security database. Administrator privileges only in regular databases are not sufficient.

🛈︎
Note

CREATE/ALTER/DROP USER are DDL statements. Remember to COMMIT your work. In isql, the command SET AUTO ON will enable autocommit on DDL statements. In third-party tools and other user applications, this may not be the case.

Examples
  1. Creating a user with the username bigshot:

    CREATE USER bigshot PASSWORD 'buckshot';
    
  2. Creating the user john with additional properties (first and last names):

    CREATE USER john PASSWORD 'fYe_3Ksw'
    FIRSTNAME 'John'
    LASTNAME 'Doe';
    
  3. Creating the user superuser with user management privileges:

    CREATE USER superuser PASSWORD 'kMn8Kjh'
    GRANT ADMIN ROLE;
    

See alsoSection 11.1.4.2, ALTER USER, Section 11.1.4.3, DROP USER

11.1.4.2 ALTER USER

Used forModifying a Firebird user account

Available inDSQL

Syntax

ALTER USER username [SET]
  [PASSWORD 'password']
  [FIRSTNAME 'firstname']
  [MIDDLENAME 'middlename']
  [LASTNAME 'lastname']
  [{GRANT | REVOKE} ADMIN ROLE]

Table 11.1.4.2.1 ALTER USER Statement Parameters
ParameterDescription

username

User name. Cannot be changed.

password

User password. Its theoretical limit is 31 bytes but only the first 8 characters are considered. Case-sensitive

firstname

Optional: User’s first name, or other optional text. Max. length is 31 characters

middlename

Optional: User’s middle name, or other optional text. Max. length is 31 characters

lastname

Optional: User’s last name, or other optional text. Max. length is 31 characters

Use an ALTER USER statement to edit the details in the named Firebird user account. To modify the account of another user, the current user must have administrator privileges in the security database. Administrator privileges only in regular databases are not sufficient.

Any user can alter his or her own account, except that only an administrator may use GRANT/REVOKE ADMIN ROLE.

All of the arguments are optional but at least one of them must be present:

  • The PASSWORD parameter is for specifying a new password for the user

  • FIRSTNAME, MIDDLENAME and LASTNAME allow updating of the optional user properties, such as the person’s first name, middle name and last name respectively

  • Including the clause GRANT ADMIN ROLE grants the user the privileges of the RDB$ADMIN role in the security database (security2.fdb), enabling him/her to manage the accounts of other users. It does not grant the user any special privileges in regular databases.

  • Including the clause REVOKE ADMIN ROLE removes the user’s administrator in the security database which, once the transaction is committed, will deny that user the ability to alter any user account except his or her own

🛈︎
Note

Remember to commit your work if you are working in an application that does not auto-commit DDL.

Examples
  1. Changing the password for the user bobby and granting him user management privileges:

    ALTER USER bobby PASSWORD '67-UiT_G8'
    GRANT ADMIN ROLE;
    
  2. Editing the optional properties (the first and last names) of the user dan:

    ALTER USER dan
    FIRSTNAME 'No_Jack'
    LASTNAME 'Kennedy';
    
  3. Revoking user management privileges from user dumbbell:

    ALTER USER dumbbell
    DROP ADMIN ROLE;
    

See alsoSection 11.1.4.1, CREATE USER, Section 11.1.4.3, DROP USER

11.1.4.3 DROP USER

Used forDeleting a Firebird user account

Available inDSQL

Syntax

DROP USER username

Table 11.1.4.3.1 DROP USER Statement Parameter
ParameterDescription

username

User name

Use the statement DROP USER to delete a Firebird user account. The current user requires administrator privileges.

🛈︎
Note

Remember to commit your work if you are working in an application that does not auto-commit DDL.

ExampleDeleting the user bobby:

DROP USER bobby;

See alsoSection 11.1.4.1, CREATE USER, Section 11.1.4.2, ALTER USER