how to display and set user rights in firebird database

firebird, rights, sql

Solution

The Firebird `gsec` utility only manages users and their passwords in the global security database (`security2.fdb` in Firebird 2.0 and higher). The rights of a user are managed per database (except for the user `SYSDBA`) and cannot be managed with `gsec`.

Per database user rights are controlled by defining roles (optionally), assigning rights to roles and/or users, and assigning roles to users in a specific database. Rights assigned to a role are only applied when the role is specified on attach, otherwise only the user rights apply.

Assigning rights is done with `GRANT` and `REVOKE`. For details, check the Firebird 2.5 Language Reference chapter Security and changes for Firebird 3 documented in the Firebird 3 release notes, and/or the Interbase 6.0 Operations Guide, chapter 5 Database Security, Interbase 6.0 Language Reference and Interbase 6.0 Data Definition Guide (both downloadable from the same location as the operations guide) and changes documented in the Firebird Language Reference Update.

To display the current rights granted in a database, you can use the `isql` tool, command `show grants`. You will need to connect to the specific database you want the information for. For other tools or normal client connections you will need to query the system tables (specifically `RDB$USER_PRIVILEGES`) yourself, or use the features of the tool (eg Flamerobin can display the rights as well).

If no rights are granted (which means only `SYSDBA` and the database owner have rights), the `show grants;` will output:

SQL> show grants;
There is no privilege granted in this database

For example assume I have table `ITEMS` and I grant `SELECT` rights to a role `TESTROLE`, the output will be:

SQL> CREATE ROLE TestRole;
SQL> GRANT SELECT ON ITEMS TO TestRole;
SQL> SHOW GRANTS;

/* Grant permissions for this database */
GRANT SELECT ON ITEMS TO ROLE TESTROLE

Now if I also assign additional `UPDATE` rights:

SQL> GRANT UPDATE ON ITEMS TO TestRole;
SQL> SHOW GRANTS;

/* Grant permissions for this database */
GRANT SELECT, UPDATE ON ITEMS TO ROLE TESTROLE
SQL>

If instead you query the system table `RDB$USER_PRIVILEGES` you'll see the list below (+ more rights for the DB owner):

USER      GRANTOR  PRIVILEGE GRANT_OPTION RELATION_NAME FIELD_NAME USER_TYPE OBJECT_TYPE
----------------------------------------------------------------------------------------
TESTROLE  SYSDBA   S         0            ITEMS         [null]     13        0
TESTROLE  SYSDBA   U         0            ITEMS         [null]     13        0

(left out the `RDB$` prefix from the column names for brevity)

- `RDB$USER` is the name of the object that received the rights. Here it is `TESTROLE` which is a role (indicated by column `RDB$USER_TYPE` value 13).

- `RDB$GRANTOR` is the user that granted the right.

- `RDB$PRIVILEGE` is the right granted

- `S`: `SELECT`

- `U`: `UPDATE`

- `D`: `DELETE`

- `I`: `INSERT`

- `R`: `REFERENCE` (for creating foreign keys referencing the specified table (or specific column))

- `X`: `EXECUTE` (for stored procedures, maybe also UDFs)

- `RDB$GRANT_OPTION`: `1`: User can grant right to another user, `0` (sometimes `NULL`), user cannot grant this right

- `RDB$RELATION_NAME`: Name of the table (or other database object)

- `RDB$FIELD_NAME` : Name of column in a table (`NULL` means right is granted for all columns)

- `RDB$USER_TYPE` : Object type of the `RDB$USER` column (`13` is a role, `8` a normal user, other object types are possible as well, eg granting rights to a stored procedure)

- `RDB$OBJECT_TYPE`: Object type of the `RDB$RELATION_NAME` (eg 0: Relation, a normal table).

A full list of object types can be obtained from `RDB$TYPES` (this doesn't mean rights can be granted on or to all object types). Check the documentation for details.

Problem

Original source