FirebirdSQL logo
 SecuritySQL Statements for User Management 

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 [fblangref50-security-autoadminmapping] capability.

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:

create role MANAGE_USERS
  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.

    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.

    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 {secdb} — 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.

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, alter and drop 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 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:

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 clauses to the statements CREATE USER and ALTER USER.

Table 1. Parameters for RDB$ADMIN Role GRANT and REVOKE
Parameter Description

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.

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:

gsec -add new_user -pw password -admin yes
gsec -mo existing_user -admin yes
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, -role, or -trusted.