User Authentication

Table of Contents

Specially Privileged Users
RDB$ADMIN Role
Administrators
SQL Statements for User Management

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.

Specially Privileged Users

Table of Contents

POSIX Hosts
Windows Hosts
The Database Owner

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

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]

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.

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.

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

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

RDB$ADMIN Role

Table of Contents

Granting the RDB$ADMIN Role in the Security Database
Granting the RDB$ADMIN Role in a Regular Database
AUTO ADMIN MAPPING

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.

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 10.1. Parameters for RDB$ADMIN Role GRANT and REVOKE

Parameter	Description
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.

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.

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.

Using gsec with RDB$ADMIN Rights

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

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.

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.

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

using trusted authentication, and
without specifying any role

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

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:

the database owner
an administrator

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.

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.

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 10.2. Administrator (“Superuser”) Characteristics

User RDB$ADMIN Role Comments

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 / 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

SQL Statements for User Management

Table of Contents

CREATE USER
ALTER USER
DROP USER

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 Auto Admin Mapping in the Security Database.

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

CREATE USER

Used for: Creating a Firebird user account

Available in: DSQL

Syntax:

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

Table 10.3. CREATE USER Statement Parameters

Parameter	Description
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:

Creating a user with the username bigshot:

CREATE USER bigshot PASSWORD 'buckshot';

Creating the user john with additional properties (first and last names):

CREATE USER john PASSWORD 'fYe_3Ksw'
FIRSTNAME 'John'
LASTNAME 'Doe';

Creating the user superuser with user management privileges:

CREATE USER superuser PASSWORD 'kMn8Kjh'
GRANT ADMIN ROLE;

ALTER USER

Used for: Modifying a Firebird user account

Available in: DSQL

Syntax:

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

Table 10.4. ALTER USER Statement Parameters

Parameter	Description
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:

Changing the password for the user bobby and granting him user management privileges:
```
ALTER USER bobby PASSWORD '67-UiT_G8'
GRANT ADMIN ROLE;
              
```

Editing the optional properties (the first and last names) of the user dan:

ALTER USER dan
FIRSTNAME 'No_Jack'
LASTNAME 'Kennedy';

Revoking user management privileges from user dumbbell:

ALTER USER dumbbell
DROP ADMIN ROLE;

DROP USER

Used for: Deleting a Firebird user account

Available in: DSQL

Syntax:

DROP USER username;

Table 10.5. DROP USER Statement Parameter

Parameter	Description
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.

Example: Deleting the user bobby:

DROP USER bobby;