SQL user management commands

Table of Contents

CREATE USER
ALTER USER
DROP USER

Available in: DSQL

Added in: 2.5

Description: Firebird 2.5 and up provide SQL statements for user account management. Except in one case, they are only available to the following privileged users:

SYSDBA;
Any user who has been granted the RDB$ADMIN role in the security database and at least one other database. The user must specify the role when connecting to the database.
If AUTO ADMIN MAPPING is on for the security database: any Windows administrator connected to any database using trusted authentication without specifying a role. Whether AUTO ADMIN MAPPING is on in the connection database is unimportant.

Non-privileged users can only use ALTER USER, to change their own account details.

CREATE USER

Description: Creates a Firebird user account.

Syntax:

CREATE USER username PASSWORD 'password'
   [FIRSTNAME 'firstname']
   [MIDDLENAME 'middlename']
   [LASTNAME 'lastname']
   [GRANT ADMIN ROLE]
GRANT ADMIN ROLE gives the new user the RDB$ADMIN role in the security database. This allows him to manage user accounts, but doesn't give him any special privileges in regular databases. For more information, see The RDB$ADMIN role.

Examples:

create user bigshot password 'buckshot'
create user john password 'fYe_3Ksw' firstname 'John' lastname 'Doe'
create user mary password 'lamb_chop' firstname 'Mary' grant admin role

ALTER USER

Description: Alters details of a Firebird user account. This is the only account management statement that can also be used by non-privileged users, in order to change their own account details.

Syntax:

ALTER USER username
   [PASSWORD 'password']
   [FIRSTNAME 'firstname']
   [MIDDLENAME 'middlename']
   [LASTNAME 'lastname']
   [{GRANT|REVOKE} ADMIN ROLE]

-- At least one of the optional parameters must be present.
-- GRANT/REVOKE ADMIN ROLE is reserved to privileged users.

Examples:

alter user bobby password '67-UiT_G8' grant admin role
alter user dan firstname 'No_Jack' lastname 'Kennedy'
alter user dumbbell revoke admin role

DROP USER

Description: Removes a Firebird user account.

Syntax:

DROP USER username

Example:

drop user timmy