11.1.4. SQL Statements for User Management

In Firebird 2.5 and above, user accounts are created, modified and deleted using a series of SQL statements that can be submitted by a user with full administrator rights in the security database.

Non-privileged users can use only the ALTER USER statement and then only to edit some data in their own accounts.

CREATE USER

Used for

Creating a Firebird user account

Available in

DSQL

Syntax

CREATE USER username PASSWORD 'password'
  [FIRSTNAME 'firstname']
  [MIDDLENAME 'middlename']
  [LASTNAME 'lastname']
  [GRANT ADMIN ROLE]

Use a CREATE USER statement to create a new Firebird user account. The user must not already exist in the Firebird security database, or a primary key violation error message will be returned.

The username argument must follow the rules for Firebird regular identifiers: see Identifiers in the Structure chapter. User names are always case-insensitive. Supplying a user name enclosed in double quotes will not cause an exception: the quotes will be ignored. If a space is the only illegal character supplied, the user name will be truncated back to the first space character. Other illegal characters will cause an exception.

The PASSWORD clause specifies the user’s password. A password of more than eight characters is accepted with a warning but any surplus characters will be ignored.

The optional FIRSTNAME, MIDDLENAME and LASTNAME clauses can be used to specify additional user properties, such as the person’s first name, middle name and last name, respectively. They are just simple VARCHAR(31) fields and can be used to store anything you prefer.

If the GRANT ADMIN ROLE clause is specified, the new user account is created with the privileges of the RDB$ADMIN role in the security database (security2.fdb). It allows the new user to manage user accounts from any regular database he logs into, but it does not grant the user any special privileges on objects in those databases.

To create a user account, the current user must have administrator privileges in the security database. Administrator privileges only in regular databases are not sufficient.

Examples

1. Creating a user with the username bigshot:

   CREATE USER bigshot PASSWORD 'buckshot';

2. Creating the user john with additional properties (first and last names):

   CREATE USER john PASSWORD 'fYe_3Ksw'
   FIRSTNAME 'John'
   LASTNAME 'Doe';

3. Creating the user superuser with user management privileges:

   CREATE USER superuser PASSWORD 'kMn8Kjh'
   GRANT ADMIN ROLE;

See also

ALTER USER, DROP USER

ALTER USER

Used for

Modifying a Firebird user account

Available in

DSQL

Syntax

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

Use an ALTER USER statement to edit the details in the named Firebird user account. To modify the account of another user, the current user must have administrator privileges in the security database. Administrator privileges only in regular databases are not sufficient.

Any user can alter his or her own account, except that only an administrator may use GRANT/REVOKE ADMIN ROLE.

All of the arguments are optional but at least one of them must be present:

• The PASSWORD parameter is for specifying a new password for the user

• FIRSTNAME, MIDDLENAME and LASTNAME allow updating of the optional user properties, such as the person’s first name, middle name and last name respectively

• Including the clause GRANT ADMIN ROLE grants the user the privileges of the RDB$ADMIN role in the security database (security2.fdb), enabling him/her to manage the accounts of other users. It does not grant the user any special privileges in regular databases.

• Including the clause REVOKE ADMIN ROLE removes the user’s administrator in the security database which, once the transaction is committed, will deny that user the ability to alter any user account except his or her own

Examples

1. Changing the password for the user bobby and granting him user management privileges:

   ALTER USER bobby PASSWORD '67-UiT_G8'
   GRANT ADMIN ROLE;

2. Editing the optional properties (the first and last names) of the user dan:

   ALTER USER dan
   FIRSTNAME 'No_Jack'
   LASTNAME 'Kennedy';

3. Revoking user management privileges from user dumbbell:

   ALTER USER dumbbell
   DROP ADMIN ROLE;

See also

CREATE USER, DROP USER

DROP USER

Used for

Deleting a Firebird user account

Available in

DSQL

Syntax

DROP USER username

Use the statement DROP USER to delete a Firebird user account. The current user requires administrator privileges.

Example

Deleting the user bobby:

DROP USER bobby;

See also

CREATE USER, ALTER USER