FirebirdSQL logo
  • Home
  • Anglaiskeyboard_arrow_down
 User AuthenticationSQL Privileges 

CREATE USER Examples

  1. Creating a user with the username bigshot:

    CREATE USER bigshot PASSWORD 'buckshot';

  2. Creating a user with the Legacy_UserManager user manager plugin

    CREATE USER godzilla PASSWORD 'robot'
  USING PLUGIN Legacy_UserManager;

  3. Creating the user john with custom attributes:

    CREATE USER john PASSWORD 'fYe_3Ksw'
  FIRSTNAME 'John' LASTNAME 'Doe'
  TAGS (BIRTHYEAR='1970', CITY='New York');

  4. Creating an inactive user:

    CREATE USER john PASSWORD 'fYe_3Ksw'
  INACTIVE;

  5. Creating the user superuser with user management privileges:

    CREATE USER superuser PASSWORD 'kMn8Kjh'
GRANT ADMIN ROLE;
See also

[fblangref50-security-user-alter], [fblangref50-security-user-createoralter], [fblangref50-security-user-drop]

ALTER USER

Alters a Firebird user account

Available in

DSQL

Syntax
ALTER {USER username | CURRENT USER}
  [SET] [<user_option> [<user_option> ...]]
  [TAGS (<user_var> [, <user_var> ...]]

<user_option> ::=
    PASSWORD 'password'
  | FIRSTNAME 'firstname'
  | MIDDLENAME 'middlename'
  | LASTNAME 'lastname'
  | {GRANT | REVOKE} ADMIN ROLE
  | {ACTIVE | INACTIVE}
  | USING PLUGIN plugin_name

<user_var> ::=
    tag_name = 'tag_value'
  | DROP tag_name

See [fblangref50-security-user-create] for details on the statement parameters.

Any user can alter their own account, except that only an administrator may use GRANT/REVOKE ADMIN ROLE and ACTIVE/INACTIVE.

All clauses are optional, but at least one other than USING PLUGIN must be present:

  • The PASSWORD parameter is for changing the password for the user

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

  • GRANT ADMIN ROLE grants the user the privileges of the RDB$ADMIN role in the security database ({secdb}), enabling them to manage the accounts of other users.It does not grant the user any special privileges in regular databases.

  • 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 their own

  • ACTIVE will enable a disabled account (not supported for Legacy_UserManager)

  • INACTIVE will disable an account (not supported for Legacy_UserManager).This is convenient to temporarily disable an account without deleting it.

  • USING PLUGIN specifies the user manager plugin to use

  • TAGS can be used to add, update or remove (DROP) additional custom attributes (not supported for Legacy_UserManager).Attributes not listed will not be changed.

See [fblangref50-security-user-create] for more details on the clauses.

If you need to change your own account, then instead of specifying the name of the current user, you can use the CURRENT USER clause.

Warning

The ALTER CURRENT USER statement follows the normal rules for selecting the user manager plugin.If the current user was created with a non-default user manager plugin, they will need to explicitly specify the user manager plugins with USING PLUGIN plugin_name, or they will receive an error that the user is not found.Or, if a user with the same name exists for the default user manager, they will alter that user instead.
Note

Remember to commit your work if you are working in an application that does not auto-commit DDL.

docnext count = 7

Who Can Alter a User?

To modify the account of another user, the current user must have

  • administrator privileges in the security database

  • the USER_MANAGEMENT system privilege in the security databaseUsers with the USER_MANAGEMENT system privilege can not grant or revoke the admin role.

Anyone can modify their own account, except for the GRANT/REVOKE ADMIN ROLE and ACTIVE/INACTIVE options, which require administrative privileges to change.

ALTER USER Examples

  1. Changing the password for the user bobby and granting them 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

[fblangref50-security-user-create], [fblangref50-security-user-drop]

CREATE OR ALTER USER

Creates a Firebird user account if it doesn’t exist, or alters a Firebird user account

Available in

DSQL

Syntax
CREATE OR ALTER USER username
  [SET] [<user_option> [<user_option> ...]]
  [TAGS (<user_var> [, <user_var> ...]]

<user_option> ::=
    PASSWORD 'password'
  | FIRSTNAME 'firstname'
  | MIDDLENAME 'middlename'
  | LASTNAME 'lastname'
  | {GRANT | REVOKE} ADMIN ROLE
  | {ACTIVE | INACTIVE}
  | USING PLUGIN plugin_name

<user_var> ::=
    tag_name = 'tag_value'
  | DROP tag_name

See [fblangref50-security-user-create] and [fblangref50-security-user-alter] for details on the statement parameters.

If the user does not exist, it will be created as if executing a CREATE USER statement.If the user already exists, it will be modified as if executing an ALTER USER statement.The CREATE OR ALTER USER statement must contain at least one of the optional clauses other than USING PLUGIN.If the user does not exist yet, the PASSWORD clause is required.

Note

Remember to commit your work if you are working in an application that does not auto-commit DDL.

CREATE OR ALTER USER Examples

Creating or altering a user
CREATE OR ALTER USER john PASSWORD 'fYe_3Ksw'
FIRSTNAME 'John'
LASTNAME 'Doe'
INACTIVE;
See also

[fblangref50-security-user-create], [fblangref50-security-user-alter], [fblangref50-security-user-drop]

DROP USER

Drops a Firebird user account

Available in

DSQL

Syntax
DROP USER username
  [USING PLUGIN plugin_name]
Table 1. DROP USER Statement Parameter
Parameter Description

username

Username

plugin_name

Name of the user manager plugin

The optional USING PLUGIN clause explicitly specifies the user manager plugin to use for dropping the user.Only plugins listed in the UserManager configuration for this database (firebird.conf, or overridden in databases.conf) are valid.The default user manager (first in the UserManager configuration) is applied when this clause is not specified.

Important

Users of the same name created using different user manager plugins are different objects.Therefore, the user created with one user manager plugin can only be dropped by that same plugin.
Note

Remember to commit your work if you are working in an application that does not auto-commit DDL.

Who Can Drop a User?

To drop a user, the current user must have

DROP USER Example

  1. Deleting the user bobby:

    DROP USER bobby;

  2. Removing a user created with the Legacy_UserManager plugin:

    DROP USER Godzilla
  USING PLUGIN Legacy_UserManager;
See also

[fblangref50-security-user-create], [fblangref50-security-user-alter]