Administrative Features

Table of Contents

New RDB$ADMIN System Role
Trace and Audit Services
Monitoring Improvements

Certain improvements to Firebird's administrative features will be welcomed by many.

New RDB$ADMIN System Role

Alex Peshkov

Multiple Databases and Superusers
System “Superusers”
Escalating RDB$ADMIN Scope for User Management

A new pre-defined system role RDB$ADMIN has been added for transferring SYSDBA privileges to another user. Any user, when granted the role in a particular database, acquires SYSDBA-like rights when attaching to that database with the RDB$ADMIN role specified.

To assign it, SYSDBA should log in to that database and grant the role RDB$ADMIN to the user, in the same way one would grant any other role to a user. After the user has been granted the role, s/he must include it in the log-in in order to access the “superuser” privileges in that database.

Important

If the user attaches with a user database role passed in the DPB (connection parameters), it will not be replaced with RDB$ADMIN, i.e., he/she will not get SYSDBA rights.

The following example transfers SYSDBA privileges to users named User1 and Admins\ADMINS. The second user in our example is typical for a Windows system user with access enabled via trusted authentication:

GRANT RDB$ADMIN TO User1;
GRANT RDB$ADMIN TO "Admins\ADMINS";

Multiple Databases and Superusers

It should be understood that acquiring the RDB$ADMIN role does not make a regular user into SYSDBA. Rather, it gives that user the same privileges as SYSDBA over the objects in the database in which the role is granted to that user.

If the same user needs Superuser privileges in more than one database, the RDB$ADMIN role must be explicitly granted for that user in each database.
If more than one user is to have Superuser privileges in a database then each user needs to be granted the RDB$ADMIN role.
One user that has acquired the RDB$ADMIN role in the database can grant it to another user.
It is not necessary to specify WITH ADMIN OPTION (for the privilege to grant this role to others) or WITH GRANT OPTION (for the privilege to grant permissions on objects to other users without being the owner of those objects). The ADMIN and GRANT options are implicit.

System “Superusers”

On POSIX hosts, the root user always had SYSDBA privileges, but the same was not possible for a domain administrator on Windows until Firebird 2.1. In V.2.1, a configuration parameter, Authentication, was introduced, whereby a user logged in as a Windows domain administrator could automatically gain server access with SYSDBA privileges through trusted user authentication. On POSIX, the mechanism has not changed but on Windows, the introduction of the RDB$ADMIN role has changed the way Windows administrators acquire SYSDBA privileges.

Windows trusted user authentication is no longer available by default!

By default, the Authentication parameter in firebird.confis configured as native. It must be explicitly configured to either trusted or mixed to enable trusted user authentication.

Global Admin Privileges for Windows Administrators

For a trusted domain administrator to get SYSDBA access privileges on a Firebird server that is configured for trusted user authentication, the domain administrator must acquire the RDB$ADMIN role. The manual method described above for ordinary users will work, by granting RDB$ADMIN to each specific administrator, database by database.

However, there is a way for the SYSDBA to configure the server so that the RDB$ADMIN role will be mapped to administrators automatically when they log into any database, thus arriving at a situation parallel to the association of root-privileged users on POSIX systems with the SYSDBA privileges. The new ALTER ROLE statement achieves this (and only this) purpose.

ALTER ROLE Statement

To configure a database to map the RDB$ADMIN role to administrators automatically on a Windows server that is configured to enable trusted user authentication, the SYSDBA logs in to any database and issues the following statement:

ALTER ROLE RDB$ADMIN
  SET AUTO ADMIN MAPPING;

To revert to the default setting, preventing administrators from getting SYSDBA privileges automatically, issue this statement:

ALTER ROLE RDB$ADMIN
  DROP AUTO ADMIN MAPPING;

Services API Tag Items

The same effects are supported in the Services API by the provision of two tag items: isc_action_svc_set_mapping to enable the automatic mapping and isc_action_svc_drop_mapping to disable it.

These tags are supported in the fbsvcmgr utility.

Escalating RDB$ADMIN Scope for User Management

The new DDL command ALTER USER enables an “ordinary” user (a regular Firebird user, a non-root user on POSIX or a trusted user on a Windows system where trusted authentication is enabled) the ability to change his or her password and/or personal name elements, while logged in to any database. Superusers can also use the same command to create and drop users. For more information about this new command, refer to the topic CREATE/ALTER/DROP USER in the chapter Data Definition Language.

Because security2.fdb is created as (or should be upgraded to) an ODS 11.2 database, it has the pre-defined RDB$ADMIN role, too. Since no user—not even SYSDBA—can log into the security database, alternative means have been provided to enable the SYSDBA or a Superuser to apply the RDB$ADMIN role in security2.fdb to an ordinary user that needs the ability to create or drop users. There are three ways, each having the equivalent effect:

Use the optional parameter GRANT ADMIN ROLE with a CREATE USER or ALTER USER statement.

Notes

Notice that GRANT ADMIN ROLE and REVOKE ADMIN ROLE here are not GRANT/REVOKE statements but 3-keyword parameters to the CREATE USER and ALTER USER statements. There is no system role named 'ADMIN'.

Any user that acquires the RDB$ADMIN role in a database implicitly acquires the extended privileges WITH ADMIN OPTION and WITH GRANT OPTION.

Examples

To grant the RDB$ADMIN role to user alex in the security database:

      ALTER USER alex GRANT ADMIN ROLE;


To revoke the RDB$ADMIN role from user alex in the security database:

      ALTER USER alex REVOKE ADMIN ROLE;


To drop user alex and destroy his privileges in all databases:

      DROP USER alex;
Use the gsec utility with the new switch -admin. The switch takes one argument: YES to apply the RDB$ADMIN role to the user, or NO to revoke it. For more details, refer to the topic Granting the RDB$ADMIN Role to An Ordinary User in the gsec section of the Utilities chapter.
Use the new SPB parameter isc_spb_sec_admin which implements the assignment of the RDB$ADMIN role for ordinary users in security2.fdb via a SPB connection. It is described in more detail in the chapter Changes to the Firebird API and ODS, in the topic Parameter isc_spb_sec_admin.

The fbsvmgr utility also supports the use of this parameter.

Tip

Firebird 2.5 does not allow you to set up more than one security database on a server. From V.3.0, it is intended to be possible to have separate security databases for each database. For now, you can be connected to any database on the server (even employee.fdb) to update its one-and-only security2.fdb.

In future, it will be essential to send these requests from a database that is associated with the security database that is to be affected by them.