Firebird Documentation IndexFirebird 3.0.2 Release NotesSecurity → Mapping of Users to Objects
Firebird Home Firebird Home Prev: "Over the wire" Connection EncryptionFirebird Documentation IndexUp: SecurityNext: International Character Sets for User Accounts

Mapping of Users to Objects

The Mapping Rule
Syntax for MAPPING Objects
Legacy Mapping Rule
Alex Peshkov

Firebird 3 introduces new SQL privileges to map access between users and groups and security objects and between databases. See Tracker item CORE-1900.

With Firebird now supporting multiple security databases, some new problems arise that could not occur with a single, global security database. Clusters of databases using the same security database were efficiently separated. Mappings provide the means to achieve the same efficiency when multiple databases are using their own security databases. Some cases require control for limited interaction between such clusters. For example:

The single solution for all such cases is mapping the login information assigned to a user when it connects to a Firebird server to internal security objects in a database—CURRENT_USER and CURRENT_ROLE.

The Mapping Rule

The mapping rule consists of four pieces of information:

  1. mapping scope—whether the mapping is local to the current database or whether its effect is to be global, affecting all databases in the cluster, including security databases

  2. mapping name—an SQL identifier, since mappings are objects in a database, like any other

  3. the object FROM which the mapping maps. It consists of four items:

    • The authentication source

      plug-in name or
      the product of a mapping in another database or
      use of server-wide authentication or
      any method

    • The name of the database where authentication succeeded

    • The name of the object from which mapping is performed

    • The type of that name—user name | role | OS group—depending upon the plug-in that added that name during authentication

    Any item is accepted but only type is required.

  4. the object TO which the mapping maps. It consists of two items:

    • The name of the object TO which mapping is performed

    • The type, for which only USER or ROLE is valid

Syntax for MAPPING Objects

Mappings are defined using the following set of DDL statements:

  {CREATE | ALTER | CREATE OR ALTER} [GLOBAL] MAPPING name
    USING {
      PLUGIN name [IN database] | ANY PLUGIN [IN database | SERVERWIDE] |
        MAPPING [IN database] | '*' [IN database]}
    FROM {ANY type | type name}
    TO {USER | ROLE} [name]
    --
  DROP [GLOBAL] MAPPING name
      

Description

  • Any mapping may be tagged as GLOBAL.

    Global mapping works best if a Firebird 3 or higher version database is used as the security database. If you plan to use another database for this purpose—using your own provider, for example—then you should create a table in it named RDB$MAP, with the same structure as RDB$MAP in a Firebird 3 database and with SYSDBA-only write access.

    Beware!

    If global and local mappings of the same name exist then know and make it known that they are different objects!

  • The CREATE, ALTER and CREATE OR ALTER statements use the same set of options. The name (identifier) of a mapping is used to identify it, as in other DDL command sets.

  • The USING clause has a highly complicated set of options:

    • an explicit plug-in name means it will work only for that plug-in

    • it can use any available plug-in; although not if the source is the product of a previous mapping

    • it can be made to work only with server-wide plug-ins

    • it can be made to work only with previous mapping results

    • it can be left to use any method, using the asterisk (*) argument

    • it can be provided with the name of the database that originated the mapping for the FROM object

      Note

      This argument is not valid for mapping server-wide authentication.

  • The FROM clause takes a mandatory argument, the type of the object named.

    -> When mapping names from plug-ins, type is defined by the plug-in.
    -> When mapping the product of a previous mapping, type can be only USER or ROLE.
    -> If an explicit name is provided, it will be taken into account by this mapping
    -> Use the ANY keyword to work with any name of the given type.

  • In the TO clause, the USER or ROLE to which the mapping is made must be specified. NAME is optional: if it is not supplied, the name from the originating mapping is used.

Examples

The examples use the CREATE syntax. Usage of ALTER is exactly the same and the usage of DROP should be obvious.

  1. Enable use of Windows trusted authentication in all databases that use the current security database:

    CREATE GLOBAL MAPPING TRUSTED_AUTH
      USING PLUGIN WIN_SSPI
      FROM ANY USER
      TO USER;
              
  2. Enable SYSDBA-like access for windows admins in current database:

    CREATE MAPPING WIN_ADMINS
      USING PLUGIN WIN_SSPI
      FROM Predefined_Group
      DOMAIN_ANY_RID_ADMINS
      TO ROLE RDB$ADMIN;
              

    Note

    The group DOMAIN_ANY_RID_ADMINS does not exist in Windows, but such a name would be added by the win_sspi plug-in to provide exact backwards compatibility.

  3. Enable a particular user from another database to access the current database with another name:

    CREATE MAPPING FROM_RT
      USING PLUGIN SRP IN "rt"
      FROM USER U1 TO USER U2;
              

    Important

    Database names or aliases will need to be enclosed in double quotes on operating systems that have case-sensitive file names.

  4. Enable the server's SYSDBA (from the main security database) to access the current database. (Assume that the database is using a non-default security database):

    CREATE MAPPING DEF_SYSDBA
      USING PLUGIN SRP IN "security.db"
      FROM USER SYSDBA
      TO USER;
              
  5. Ensure users who logged in using the legacy authentication plug-in do not have too many privileges:

    CREATE MAPPING LEGACY_2_GUEST
      USING PLUGIN legacy_auth
      FROM ANY USER
      TO USER GUEST;
              

Legacy Mapping Rule

Previous versions of Firebird have one hard-coded global default rule: users authenticated in the security database are always mapped into any database one-to-one. It is a safe rule: it makes no sense for a security database not to trust itself!

For backward compatibility this rule is retained in Firebird 3.

Generic mapping is used to set the rule defining the user name under which the user accesses a database when performing a request from a database using one security database to a database using a different one, or when server-wide authentication, such as win_sspi, is used. The rule comes into action whenever the Firebird engine is processing the authentication block associated with a request to connect to a database.

Mapping Trusted Users to CURRENT_USER and CURRENT_ROLE

Each record in an authentication block contains the name of the plug-in that added it, the type of record (user name, OS group, role, etc.), the name of an object of the specified type (user SYSDBA, role PUBLIC, group DOMAIN_ADMINS) and the name of the security database in which authentication took place. Under server-wide authentication, the security database name could be NULL. The mapping rule assesses all these parameters to define the value to assign to CURRENT_USER and CURRENT_ROLE in the resulting attachment.

In Firebird 3, an explicit mapping must exist in systems with server-wide “trusted user” authentication enabled, including Win_Sspi authentication on Windows, in order for the system user's user name to be assigned to the context variables CURRENT_USER and CURRENT_ROLE.

Prev: "Over the wire" Connection EncryptionFirebird Documentation IndexUp: SecurityNext: International Character Sets for User Accounts
Firebird Documentation IndexFirebird 3.0.2 Release NotesSecurity → Mapping of Users to Objects