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.

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:

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

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

CREATE USER, ALTER USER

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:

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

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:

  1. GRANT [ROLE] RDB$ADMIN TO username
  3. 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 also

GRANT, REVOKE

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:

  1. using trusted authentication, and

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

  1. ALTER ROLE RDB$ADMIN
  2.   SET AUTO ADMIN MAPPING;  -- enable it
  4. ALTER ROLE RDB$ADMIN
  5.   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.

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:

  1. gsec -mapping set
  3. 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.