how to display and set user rights in firebird database

Question

I tried but all time all failed about displaying and setting user rights in firebird databases. I used ;

GSEC> display

but it just display users name. I want to see all rights and I want to set them. Can we do such this option and management ?

Regards,

Answer 1

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.