SecuritySQL Statements for User Management

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.

docnext count = 3

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