FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 SecuritySQL Statements for User Management 

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.

Using the RDB$ADMIN Role in the Security Database

To manage user accounts through SQL, the user must have the RDB$ADMIN role in the security database.No user can connect to the security database remotely, so the solution is that the user connects to a regular database.From there, they can submit any SQL user management command.

Contrary to Firebird 3.0 or earlier, the user does not need to specify the RDB$ADMIN role on connect, nor do they need to have the RDB$ADMIN role in the database used to connect.

docnext count = 9

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 [DEFAULT] RDB$ADMIN TO username

REVOKE [DEFAULT] RDB$ADMIN FROM username
Table 1. Parameters for RDB$ADMIN Role GRANT and REVOKE
Parameter Description

username

Name of the user

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 their RDB$ADMIN privileges, the role must either have been granted as a default role, or the grantee has to include the role in the connection attributes when connecting to the database, or specify it later using SET ROLE.

AUTO ADMIN MAPPING

Windows Administrators are not automatically granted RDB$ADMIN privileges when connecting to a database (when Win_Sspi is enabled).The AUTO ADMIN MAPPING switch determines whether Administrators have automatic RDB$ADMIN 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 Win_Sspi authentication, and

  2. without specifying any role

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

If an explicit role was specified on connect, the RDB$ADMIN role can be assumed later in the session using SET TRUSTED ROLE.

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

  • A user with the ALTER ANY ROLE privilege

Note

The statement

ALTER ROLE RDB$ADMIN
  SET AUTO ADMIN MAPPING;

is a simplified form of a CREATE MAPPING statement to create a mapping of the predefined group DOMAIN_ANY_RID_ADMINS to the role of RDB$ADMIN:

CREATE MAPPING WIN_ADMINS
  USING PLUGIN WIN_SSPI
  FROM Predefined_Group DOMAIN_ANY_RID_ADMINS
  TO ROLE RDB$ADMIN;

Accordingly, the statement

ALTER ROLE RDB$ADMIN
  DROP AUTO ADMIN MAPPING

is equivalent to the statement

DROP MAPPING WIN_ADMINS;

For details, see [fblangref50-security-mapping]

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

The ALTER ROLE RDB$ADMIN statement cannot enable or disable AUTO ADMIN MAPPING in the security database.However, you can create a global mapping for the predefined group DOMAIN_ANY_RID_ADMINS to the role RDB$ADMIN in the following way:

CREATE GLOBAL MAPPING WIN_ADMINS
  USING PLUGIN WIN_SSPI
  FROM Predefined_Group DOMAIN_ANY_RID_ADMINS
  TO ROLE RDB$ADMIN;

Additionally, you can use gsec:

gsec -mapping set

gsec -mapping drop
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.

Only SYSDBA can enable AUTO ADMIN MAPPING if it is disabled, but any administrator can turn it off.

When turning off AUTO ADMIN MAPPING in gsec, the user turns off the mechanism itself which gave them access, and thus they would not be able to re-enable AUTO ADMIN MAPPING.Even in an interactive gsec session, the new flag setting takes effect immediately.

Administrators

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

root user on POSIX

Auto

Exactly like SYSDBA.Firebird Embedded only.

Superuser on POSIX

Auto

Exactly like SYSDBA.Firebird Embedded only.

Windows Administrator

Set as CURRENT_ROLE if login succeeds

Exactly like SYSDBA if the following are all true:

  • In firebird.conf file, AuthServer includes Win_Sspi, and Win_Sspi is present in the client-side plugins (AuthClient) configuration

  • In databases where AUTO ADMIN MAPPING is enabled, or an equivalent mapping of the predefined group DOMAIN_ANY_RID_ADMINS for the role RDB$ADMIN exists

  • No role is specified at login

Database owner

Auto

Like SYSDBA, but only in the databases they own

Regular user

Must be previously granted;must be supplied at login or have been granted as a default role

Like SYSDBA, but only in the databases where the role is granted

POSIX OS user

Must be previously granted;must be supplied at login or have been granted as a default role

Like SYSDBA, but only in the databases where the role is granted.Firebird Embedded only.

Windows user

Must be previously granted;must be supplied at login

Like SYSDBA, but only in the databases where the role is granted.Only available if in firebird.conf file, AuthServer includes Win_Sspi, and Win_Sspi is present in the client-side plugins (AuthClient) configuration

Fine-grained System Privileges

In addition to granting users full administrative privileges, system privileges make it possible to grant regular users a subset of administrative privileges that have historically been limited to SYSDBA and administrators only.For example:

  • Run utilities such as gbak, gfix, nbackup and so on

  • Shut down a database and bring it online

  • Trace other users' attachments

  • Access the monitoring tables

  • Run management statements

The implementation defines a set of system privileges, analogous to object privileges, from which lists of privileged tasks can be assigned to roles.

It is also possible to grant normal privileges to a system privilege, making the system privilege act like a special role type.

The system privileges are assigned through CREATE ROLE and ALTER ROLE.

Warning

Be aware that each system privilege provides a very thin level of control.For some tasks it may be necessary to give the user more than one privilege to perform some task.For example, add IGNORE_DB_TRIGGERS to USE_GSTAT_UTILITY because gstat needs to ignore database triggers.

List of Valid System Privileges

The following table lists the names of the valid system privileges that can be granted to and revoked from roles.

USER_MANAGEMENT

Manage users (given in the security database)
READ_RAW_PAGES

Read pages in raw format using Attachment::getInfo()
CREATE_USER_TYPES

Add/change/delete non-system records in RDB$TYPES
USE_NBACKUP_UTILITY

Use nbackup to create database copies
CHANGE_SHUTDOWN_MODE

Shut down database and bring online
TRACE_ANY_ATTACHMENT

Trace other users' attachments
MONITOR_ANY_ATTACHMENT

Monitor (tables MON$) other users' attachments
ACCESS_SHUTDOWN_DATABASE

Access database when it is shut down
CREATE_DATABASE

Create new databases (given in the security database)
DROP_DATABASE

Drop this database
USE_GBAK_UTILITY

Use gbak utility
USE_GSTAT_UTILITY

Use gstat utility
USE_GFIX_UTILITY

Use gfix utility
IGNORE_DB_TRIGGERS

Instruct engine not to run DB-level triggers
CHANGE_HEADER_SETTINGS

Modify parameters in DB header page
SELECT_ANY_OBJECT_IN_DATABASE

Use SELECT for any selectable object
ACCESS_ANY_OBJECT_IN_DATABASE

Access (in any possible way) any object
MODIFY_ANY_OBJECT_IN_DATABASE

Modify (up to drop) any object
CHANGE_MAPPING_RULES

Change authentication mappings
USE_GRANTED_BY_CLAUSE

Use GRANTED BY in GRANT and REVOKE statements
GRANT_REVOKE_ON_ANY_OBJECT

GRANT and REVOKE rights on any object in database
GRANT_REVOKE_ANY_DDL_RIGHT

GRANT and REVOKE any DDL rights
CREATE_PRIVILEGED_ROLES

Use SET SYSTEM PRIVILEGES in roles
GET_DBCRYPT_INFO

Get database encryption information
MODIFY_EXT_CONN_POOL

Use command ALTER EXTERNAL CONNECTIONS POOL
REPLICATE_INTO_DATABASE

Use replication API to load change sets into database
PROFILE_ANY_ATTACHMENT

Profile attachments of other users