Firebird Documentation IndexFirebird 3.0.2 Release NotesSecurity → SQL Features for Managing Access
Firebird Home Firebird Home Prev: International Character Sets for User AccountsFirebird Documentation IndexUp: SecurityNext: Pseudo-Tables with List of Users

SQL Features for Managing Access

SQL-driven User Management
SET ROLE
GRANT/REVOKE Rights GRANTED BY Specified User
REVOKE ALL ON ALL
User Privileges for Metadata Changes
GRANT EXECUTE Privileges for UDFs
Improvement for Recursive Stored Procedures
Privileges to Protect Other Metadata Objects

Changes in architecture, stiffening of rules for security and data integrity, along with a bucket list of feature requests, have given rise in this release to a number of new SQL commands for managing users and access to objects.

SQL-driven User Management

Alex Peshkov

The SQL set of DDL commands for managing user accounts has been enhanced in Firebird 3, thus improving the capability to manage (add, modify or delete) users in a security database from a regular database attachment.

Syntax Forms

  CREATE USER username [ options_list ] [ TAGS ( tag [, tag [, tag ...]] ) ]
  ALTER USER username [ SET ] [ options_list ] [ TAGS ( tag [, tag [, tag ...]] ) ]
  ALTER CURRENT USER [ SET ] [ options_list ] [ TAGS ( tag [, tag [, tag ...]] ) ]
  CREATE OR ALTER USER username [ SET ] [ options ] [ TAGS ( tag [, tag [, tag ...]] ) ]
  DROP USER username [ USING PLUGIN plugin_name ]
      

OPTIONS is a (possibly empty) list with the following options:

  PASSWORD 'password'
  FIRSTNAME 'string value'
  MIDDLENAME 'string value'
  LASTNAME 'string value'
  ACTIVE
  INACTIVE
  USING PLUGIN plugin_name
      

Each TAG may have one of two forms:

  TAGNAME = 'string value'
      

or the DROP TAGNAME tag form to remove a user-defined attribute entirely:

  DROP TAGNAME
      

Note

The tagname side of the name/value pair can be any valid SQL identifier.

Older Methods Deprecated

From Firebird 3.0, multiple security databases are supported. This capability is not supported by either the gsec utility or the Services API. Use of both of these methods is deprecated.

Usage Details

The CREATE USER, CREATE OR ALTER USER and DROP USER clauses are available only for SYSDBA or another user granted the RDB$ADMIN role in the security database (and logged in under that role, of course.)

The PASSWORD clause is required when creating a new user.

An ordinary user can ALTER his own password, real name attributes and tags. Any attempt to modify another user will fail, as will an attempt to make “self” inactive or active.

If you want to modify “self”, you can use the simplified form ALTER CURRENT USER.

At least one of PASSWORD, FIRSTNAME, MIDDLENAME, LASTNAME, ACTIVE, INACTIVE or TAGS must be present in an ALTER USER or CREATE OR ALTER USER statement.

It is not a requirement to use any of the clauses FIRSTNAME, MIDDLENAME and LASTNAME. Any of them may be left empty or used to store short information about the user.

The INACTIVE clause is used to disable the user's login capability without dropping it. The ACTIVE clause restores that login ability.

Quick Tip

From v.3.0.1, the statement CREATE OR ALTER USER SYSDBA PASSWORD <password> can be used to initialize an empty securityN.fdb security database.

TAGS is a list of end-user defined attributes. The length of the string value should not exceed 255 bytes.

Setting a list of tags for the user retains previously set tags if they are not mentioned in the current list.

Note

A UID or GID that was entered by the deprecated gsec utility is treated as a tag in the SQL interface.

Examples

Generic:

  CREATE USER superhero PASSWORD 'test';
  ALTER USER superhero SET FIRSTNAME 'Clark' LASTNAME 'Kent';
  CREATE OR ALTER USER superhero SET PASSWORD 'IdQfA';
  DROP USER superhero;
  ALTER CURRENT USER SET PASSWORD 'SomethingLongEnough';
        

Working with tags:

  ALTER USER superhero SET TAGS (a='a', b='b');
        NAME             VALUE
        ================ ==============================
        A                a
        B                b

  ALTER USER superhero SET TAGS (b='x', c='d');
        NAME             VALUE
        ================ ==============================
        A                a
        B                x
        C                d

  ALTER USER superhero SET TAGS (drop a, c='sample');
        NAME             VALUE
        ================ ==============================
        B                x
        C                sample
        

Displaying the list of users:

  SELECT CAST(U.SEC$USER_NAME AS CHAR(20)) LOGIN,
    CAST(A.SEC$KEY AS CHAR(10)) TAG,
    CAST(A.SEC$VALUE AS CHAR(20)) "VALUE",
    SEC$PLUGIN "PLUGIN"
  FROM SEC$USERS U LEFT JOIN SEC$USER_ATTRIBUTES A
    ON U.SEC$USER_NAME = A.SEC$USER_NAME;

   LOGIN                TAG        VALUE                PLUGIN
   ==================== ========== ==================== ===============================
   SYSDBA               <null>     <null>               Srp
   SUPERHERO            B          x                    Srp
   SUPERHERO            C          sample               Srp
   SYSDBA               <null>     <null>               Legacy_UserManager
        

Note

Output depends upon the user management plug-in. If the legacy plug-in is used, bear in mind that some options are not supported and will simply be ignored.

SET ROLE

Alex Peshkov

See Tracker item CORE-1377.

The SQL2008-compliant operator SET ROLE allows the CURRENT_ROLE context variable to be set to one that has been granted to the CURRENT_USER or to a user assigned to the database attachment as trusted (SET TRUSTED ROLE).

Syntax Pattern for SET ROLE

Enable CURRENT_USER access to a role that has been previously granted:

  SET ROLE <rolename>
      

Example of SET ROLE Usage

  SET ROLE manager;
  select current_role from rdb$database;
      

Displays:

  ROLE
  ===============================
  MANAGER
      

SET TRUSTED ROLE

The idea of a separate SET TRUSTED ROLE command is that, when the trusted user attaches to a database without providing any role info, SET TRUSTED ROLE makes a trusted role (if one exists) the CURRENT_ROLE without any additional activity, such as setting it in the DPB.

A trusted role is not a specific type of role but may be any role that was created using CREATE ROLE, or a predefined system role such as RDB$ADMIN. It becomes a trusted role for an attachment when the security objects mapping subsystem finds a match between the authentication result passed from the plug-in and a local or global mapping for the current database. The role may be one that is not even granted explicitly to that trusted user.

Notes

  • A trusted role is not assigned to the attachment by default. It is possible to change this behaviour using an appropriate authentication plug-in and a CREATE/ALTER MAPPING command.

  • Whilst the CURRENT_ROLE can be changed using SET ROLE, it is not always possible to revert using the same command, because it performs an access rights check.

Syntax Pattern

Enable access to a trusted role, if the CURRENT_USER is logged in under Trusted User authentication and the role is available:

  SET TRUSTED ROLE
        

An example of the use of a trusted role is assigning the system role RDB$ADMIN to a Windows administrator when Windows trusted authentication is in use.

GRANT/REVOKE Rights GRANTED BY Specified User

Alex Peshkov

Previously, the grantor or revoker of SQL privileges was always the current user. This change makes it so that a different grantor or revoker can be specified in GRANT and REVOKE commands.

Syntax Pattern

  grant <right> to <object> [ { granted by | as } [ user ] <username> ]
  revoke <right> from <object> [ { granted by | as } [ user ] <username> ]
      

The GRANTED BY clause form is recommended by the SQL standard. The alternative form using AS is supported by Informix and possibly some other servers and is included for better compatibility.

Example (working as SYSDBA)

create role r1;
grant r1 to user1 with admin option;
grant r1 to public granted by user1;

-- (in isql)
show grant;
/* Grant permissions for this database */
GRANT R1 TO PUBLIC GRANTED BY USER1
GRANT R1 TO USER1 WITH ADMIN OPTION
      

REVOKE ALL ON ALL

When a user is removed from the security database or another authentication source, this new command is useful for revoking its access to all objects in the database.

Syntax Pattern

  REVOKE ALL ON ALL FROM [USER] username
  REVOKE ALL ON ALL FROM [ROLE] rolename
      

Example

# gsec -del guest
# isql employee
fbs bin # ./isql employee
Database:  employee
SQL> REVOKE ALL ON ALL FROM USER guest;
SQL>
      

User Privileges for Metadata Changes

Dmitry Yemanov

with Roman Simakov

In Firebird 3, the system tables are read-only. This SQL syntax provides the means to assign metadata write privileges to specified users or roles for specified objects. See Tracker item CORE-735.

Note

Some people have been applying the nickname “DDL privileges” to this feature. Don't confuse it with “DDL triggers”! A more useful nickname would be “Metadata privileges”.

Syntax Patterns

Granting metadata privileges:

  GRANT CREATE <object-type>
    TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION];
  GRANT ALTER ANY <object-type>
    TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION];
  GRANT DROP ANY <object-type>
    TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION];
      

Revoking metadata privileges:

  REVOKE [GRANT OPTION FOR] CREATE <object-type>
    FROM [USER | ROLE] <user-name> | <role-name>;
  REVOKE [GRANT OPTION FOR] ALTER ANY <object-type>
    FROM [USER | ROLE] <user-name> | <role-name>;
  REVOKE [GRANT OPTION FOR] DROP ANY <object-type>
    FROM [USER | ROLE] <user-name> | <role-name>;
      

Special form for database access:

  GRANT CREATE DATABASE TO [USER | ROLE] <user-name> | <role-name>;
  GRANT ALTER DATABASE
    TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION];
  GRANT DROP DATABASE
    TO [USER | ROLE] <user-name> | <role-name> [WITH GRANT OPTION];

  REVOKE CREATE DATABASE FROM [USER | ROLE] <user-name> | <role-name>;
  REVOKE [GRANT OPTION FOR] ALTER DATABASE
    FROM [USER | ROLE] <user-name> | <role-name>;
  REVOKE [GRANT OPTION FOR] DROP DATABASE
    FROM [USER | ROLE] <user-name> | <role-name>;
      

Notes on Usage

  • <object-type> can be any of the following:

    CHARACTER SET COLLATION DOMAIN EXCEPTION
    FILTER FUNCTION GENERATOR PACKAGE
    PROCEDURE ROLE SEQUENCE TABLE
    VIEW      

    Note

    The metadata for triggers and indices are accessed through the privileges for the table that owns them.

  • If the ANY option is used, the user will be able to perform any operation on any object

  • If the ANY option is absent, the user will be able to perform operations on the object only if he owns it

  • If the ANY option was acquired via a GRANT operation then, to revoke it, the REVOKE operation must accord with that GRANT operation

Example

GRANT CREATE TABLE TO Joe;
GRANT ALTER ANY TABLE TO Joe;
REVOKE CREATE TABLE FROM Joe;
      

GRANT EXECUTE Privileges for UDFs

Dmitry Yemanov

CORE-2554: EXECUTE permission is now supported for UDFs (both legacy and PSQL based ones).

Syntax Pattern

  GRANT EXECUTE ON FUNCTION <name> TO <grantee list>
  [<grant option> <granted by clause>]
  --
  REVOKE EXECUTE ON FUNCTION <name> FROM <grantee list>
  [<granted by clause>]
      

Note

The initial EXECUTE permission is granted to the function owner (user who created or declared the function).

Improvement for Recursive Stored Procedures

Alex Peshkov

A recursive stored procedure no longer requires the EXECUTE privilege to call itself. See Tracker item CORE-3242.

Privileges to Protect Other Metadata Objects

New SQL-2008 compliant USAGE permission is introduced to protect metadata objects other than tables, views, procedures and functions.

Syntax Pattern

  GRANT USAGE ON <object type> <name> TO <grantee list>
  [<grant option> <granted by clause>]
  --
  REVOKE USAGE ON <object type> <name> FROM <grantee list>
  [<granted by clause>]
  --
  <object type> ::= {DOMAIN | EXCEPTION | GENERATOR | SEQUENCE | CHARACTER SET | COLLATION}
      

Notes

The initial USAGE permission is granted to the object owner (user who created the object).

In Firebird 3.0.0, only USAGE permissions for exceptions (CORE-2884) and generators/sequences (gen_id, next value for: CORE-2553) are enforced. Granting privileges for character sets, collations and domains is disabled, making these object types unavailable for any type of GRANT or REVOKE commands. Access to them is not subject to any form of enforcement, although this could change in future releases if it is deemed appropriate.

Prev: International Character Sets for User AccountsFirebird Documentation IndexUp: SecurityNext: Pseudo-Tables with List of Users
Firebird Documentation IndexFirebird 3.0.2 Release NotesSecurity → SQL Features for Managing Access