Grant Manager

The Grant Manager is used to administrate database security by controlling user permissions for a specific database. It allows you to specify the access rights for users, roles and database objects. It is possible to grant rights for database objects on the Grants page in the object editors. (This feature is unfortunately not included in the free IBExpert Personal Edition.)

(1) Toolbar: The toolbar displays the alias name for the current selected connected database. Another database on this server can be selected from the drop-down list at the top of the window. To the right of the selected database, there are two icon options to enable Refresh and Save privileges to script. The DDL privileges mode button was added in IBExpert version 2015.03.14. When enabled, this allows you to assign rights and privileges to metadata objects. Please refer Firebird 3.0 DDL privileges to for further information.

(2) Privileges for: The drop-down list (default = Users) allows a group for the processing of privileges to be selected. The options include:

Once a database object has been selected, a full list of such users/objects in this database is displayed in the panel directly below.

Firebird 2.5 introduced a new role, RDB$ADMIN, for databases ODS version 11.2 and higher. This role allows regular users to be granted SYSDBA-similar rights at database level.

(3) Grants toolbar: The Grants toolbar enables the user to quickly assign or revoke rights to one or more objects, or for one or more operations. These can also be found in the right-click pop-up menu (see below).

(4) Filters: It is possible, using the drop-down lists, to specify exactly which grants should be displayed, i.e. for all database objects (default), just the tables, just the views or just the procedures. Furthermore the user can determine whether all of the selected objects should be displayed, or only those with grants, or only those not granted. To the right of these drop-down lists is an empty filter field for user-defined filters. It is also possible to specify whether system tables should be included or the user-defined filter inverted, using the check boxes provided.

(5) The main window displays the object grants in a grid, displaying the granted operations Select, Update, Delete, Insert, Execute and Reference for the listed objects. A green circle indicates that access for this operation on this database object has been granted; a green circle held by a hand indicates that the GRANT WITH GRANT AUTHORITY option has been granted. UPDATE/REFERENCE privileges on certain columns only for tables/views is also indicated visually. A gray ball means that there is at least one column with a granted privilege. A gray ball in the hand means that there is at least one column with a privilege granted with the grant option. An empty field indicates logically that either no rights have been granted, or they have been revoked.

A further menu option here is Show Column Privileges (checkbox). This blends the lower window in and out (6), which displays the individual columns for tables and views, allowing Update and Reference rights to be granted and revoked for individual fields in the selected object. Reference rights are important when working with primary and foreign keys. For example, TABLE_A creates a foreign key on TABLE_B. If a data set is inserted into TABLE_A, TABLE_B needs to be referenced, whether the data entry is permitted. Therefore, TABLE_A will require REFERENCE rights (also known as read permission) on TABLE_B.

Rights can be simply granted and revoked by double-clicking (or using the space bar) on the grid fields (in both the upper (object) and lower (column) windows). Alternatively, to assign several rights (i.e. select, update, delete and insert) to a single object or to assign one operative right to all objects displayed, use either the Grant Manager toolbar or the right-click menu.

Please note that Reference rights only allow the user to read data sets if there is a foreign key relationship to other data. And the Grant All to All command may only be performed by the database owner or the SYSDBA.

The majority of these operations can also be performed in the Grants pages, found in the individual database object editors. These were introduced to remind the developer not to forget the assignment of rights, when creating or altering a database object! They allow the developer to check existing permissions for the object concerned and, if necessary, subsequently assign rights for a new or existing object.

Rights are however in practice usually administered at the front end. There is, as a rule, only one system user, with which the program can log into the database. For those preferring direct SQL input, please refer to GRANT and REVOKE.

Granting access to stored procedures

To grant a user the right to execute stored procedures, use the Grant Manager Execute column:

or the SQL EXECUTE statement. For example, to grant Janet and John the right to execute the stored procedure SP_Delete_Employee, use the following:

GRANT EXECUTE
ON PROCEDURE SP_Delete_Employee
TO Janet, John;

Firebird/InterBase® considers stored procedures as virtual users of the database. If a stored procedure modifies a table, the procedure needs the relevant privileges on that table. So the user only needs Execute privileges on the procedure and not any separate rights for the table. In this situation, the stored procedure performs the changes on behalf of the user.

If a stored procedure needs the ability to execute another stored procedure, simply select Procedures from the Privileges For list and Procedures from the Grants On list, to grant the Execute privilege on the desired procedure. Using SQL the GRANT statement is necessary, naming the procedure instead of one or more users (<user_list>).

Using the GRANT AUTHORITY option

A user that has been granted certain privileges, may also be assigned the authority to grant those privileges in turn to other users. This is known as assigning grant authority. Firebird/InterBase® allows by default only the creator of a table and the SYSDBA to grant additional privileges onto other users.

Grant authority can be assigned in the IBExpert or the Grants pages in the relevant object editors, using the Grant All with GRANT OPTION or the Grant to All with GRANT OPTION icons or right-click menu items:

It is also simple to see which grant authorities have already been assigned to which users and roles.

In SQL the WITH GRANT OPTION clause may be used in conjunction with a grant of privileges, to assign users the authority to grant their privileges in turn to other users (refer to GRANT statement for the full syntax and examples).

NotesThe initial USAGE permission is granted to the object owner (user who created the object). In Firebird 3.0 Alpha 1, only USAGE permissions for exceptions (CORE-2884) and generators/sequences (gen_id, next value for: CORE-2553) are enforced. Permissions for other object types will be validated in subsequent releases.