Chapter 14. Management Statements

Since Firebird 3.0 a new class of DSQL statement has emerged in Firebird’s SQL lexicon, usually for administering aspects of the client/server environment. Typically, such statements start with the verb SET.

ⓘ
Note

The isql tool also has a collection of SET commands. Those commands are not part of Firebird’s SQL lexicon. For information on isqls SET commands, see Isql Set Commands in Firebird Interactive SQL Utility.

Most of the management statements affect the current connection (attachment, or session) only, and do not require any authorization over and above the login privileges of the current user without elevated privileges.

14.1. Changing the Current Role

14.1.1. SET ROLE

Used forChanging the role of the current session

Available inDSQL

Syntax 

  |SET ROLE {role_name | NONE}

Table 14.1. SET ROLE Statement Parameters
ParameterDescription

role_name

The name of the role to apply

The SET ROLE statement allows a user to assume a different role; it sets the CURRENT_ROLE context variable to role_name, if that role has been granted to the CURRENT_USER. For this session, the user receives the privileges granted by that role. Any rights granted to the previous role are removed from the session. Use NONE instead of role_name to clear the CURRENT_ROLE.

When the specified role does not exist or has not been explicitly granted to the user, the error Role role_name is invalid or unavailable is raised.

14.1.1.1. SET ROLE Examples

  1. Change the current role to MANAGER

      |SET ROLE manager;
  |select current_role from rdb$database;
  | 
  |ROLE
  |=======================
  |MANAGER

  2. Clear the current role

      |SET ROLE NONE;
  |select current_role from rdb$database;
  | 
  |ROLE
  |=======================
  |NONE

See alsoSection 14.1.2, “SET TRUSTED ROLE”, GRANT

14.1.2. SET TRUSTED ROLE

Used forChanges role of the current session to the trusted role

Available inDSQL

Syntax 

  |SET TRUSTED ROLE

The SET TRUSTED ROLE statement makes it possible to assume the role assigned to the user through a mapping rule (see Mapping of Users to Objects). The role assigned through a mapping rule is assumed automatically on connect, if the user hasn’t specified an explicit role. The SET TRUSTED ROLE statement makes it possible to assume the mapped (or trusted) role at a later time, or to assume it again after the current role was changed using SET ROLE.

A trusted role is not a specific type of role, but can be any role that was created using CREATE ROLE, or a predefined system role such as RDB$ADMIN. An attachment (session) has a trusted role when the security objects mapping subsystem finds a match between the authentication result passed from the plugin and a local or global mapping to a role for the current database. The role may be one that is not granted explicitly to that user.

When a session has no trusted role, executing SET TRUSTED ROLE will raise error Your attachment has no trusted role.

ⓘ
Note

While the CURRENT_ROLE can be changed using SET ROLE, it is not always possible to revert to a trusted role using the same command, because SET ROLE checks if the role has been granted to the user. With SET TRUSTED ROLE, the trusted role can be assumed again even when SET ROLE fails.

14.1.2.1. SET TRUSTED ROLE Examples

  1. Assuming a mapping rule that assigns the role ROLE1 to a user ALEX:

       |CONNECT 'employee' USER ALEX PASSWORD 'password';
   |SELECT CURRENT_ROLE FROM RDB$DATABASE;
   | 
   |ROLE
   |===============================
   |ROLE1
   | 
   |SET ROLE ROLE2;
   |SELECT CURRENT_ROLE FROM RDB$DATABASE;
   | 
   |ROLE
   |===============================
   |ROLE2
   | 
   |SET TRUSTED ROLE;
   |SELECT CURRENT_ROLE FROM RDB$DATABASE;
   | 
   |ROLE
   |===============================
   |ROLE1

See alsoSection 14.1.1, “SET ROLE”, Mapping of Users to Objects