A role is a container object that can be used to package a collection of privileges. Use of the role is then granted to each user or role that requires those privileges. A role can also be granted to a list of users or roles.

The role must exist before privileges can be granted to it. See CREATE ROLE for the syntax and rules. The role is maintained by granting privileges to it and, when required, revoking privileges from it. When a role is dropped  — see DROP ROLE — all users lose the privileges acquired through the role. Any privileges that were granted additionally to an affected user by way of a different grant statement are retained.

Unless the role is granted as a default role, a user that is granted a role must explicitly specify that role, either with their login credentials or activating it using SET ROLE, in order to exercise the associated privileges. Any other privileges granted to the user or received through default roles are not affected by explicitly specifying a role.

More than one role can be granted to the same user. Although only one role can be explicitly specified, multiple roles can be active for a user, either as default roles, or as roles granted to the current role.

A role can be granted to a user or to another role.

The ability to grant roles to other roles and default roles results in so-called cumulative roles. Multiple roles can be active for a user, and the user receives the cumulative privileges of all those roles.

When a role is explicitly specified on connect or using SET ROLE, the user will assume all privileges granted to that role, including those privileges granted to the secondary roles (including roles granted on that secondary role, etc). Or in other words, when the primary role is explicitly specified, the secondary roles are also activated. The function RDB$ROLE_IN_USE can be used to check if a role is currently active.

See also Section 13.5.1.1.3, “Default Roles” for the effects of DEFAULT with cumulative roles, and Section 13.5.1.10.1, “The WITH ADMIN OPTION Clause” for effects on granting.

A role can be granted as a default role by prefixing the role with DEFAULT in the GRANT statement. Granting roles as a default role to users simplifies management of privileges, as this makes it possible to group privileges on a role and granting that group of privileges to a user without requiring the user to explicitly specify the role. Users can receive multiple default roles, granting them all privileges of those default roles.

The effects of a default role depend on whether the role is granted to a user or to another role:

• When a role is granted to a user as a default role, the role will be activated automatically, and its privileges will be applied to the user without the need to explicitly specify the role.

  Roles that are active by default are not returned from CURRENT_ROLE, but the function RDB$ROLE_IN_USE can be used to check if a role is currently active.

• When a role is granted to another role as a default role, the rights of that role will only be automatically applied to the user if the primary role is granted as a default role to the user, otherwise the primary role needs to be specified explicitly (in other words, it behaves the same as when the secondary role was granted without the DEFAULT clause).

  For a string of granted roles, all roles need to be granted as a default role for them to be applied automatically. That is, for GRANT DEFAULT ROLEA TO ROLE ROLEB, GRANT ROLEB TO ROLE ROLEC, GRANT DEFAULT ROLEC TO USER USER1 only ROLEC is active by default for USER1. To assume the privileges of ROLEA and ROLEB, ROLEC needs to be explicitly specified, or ROLEB needs to be granted DEFAULT to ROLEC.

Firebird has a predefined user named PUBLIC, that represents all users. Privileges for operations on a particular object that are granted to the user PUBLIC can be exercised by any authenticated user.

The non-standard AS clause is supported as a synonym of the GRANTED BY clause to simplify migration from other database systems.

1. SELECT and INSERT privileges to the user ALEX:

     |GRANT SELECT, INSERT ON TABLE SALES
     |  TO USER ALEX;
   

2. The SELECT privilege to the MANAGER, ENGINEER roles and to the user IVAN:

     |GRANT SELECT ON TABLE CUSTOMER
     |  TO ROLE MANAGER, ROLE ENGINEER, USER IVAN;
   

3. All privileges to the ADMINISTRATOR role, together with the authority to grant the same privileges to others:

     |GRANT ALL ON TABLE CUSTOMER
     |  TO ROLE ADMINISTRATOR
     |  WITH GRANT OPTION;
   

4. The SELECT and REFERENCES privileges on the NAME column to all users and objects:

     |GRANT SELECT, REFERENCES (NAME) ON TABLE COUNTRY
     |TO PUBLIC;
   

5. The SELECT privilege being granted to the user IVAN by the user ALEX:

     |GRANT SELECT ON TABLE EMPLOYEE
     |  TO USER IVAN
     |  GRANTED BY ALEX;
   

6. Granting the UPDATE privilege on the FIRST_NAME, LAST_NAME columns:

     |GRANT UPDATE (FIRST_NAME, LAST_NAME) ON TABLE EMPLOYEE
     |  TO USER IVAN;
   

7. Granting the INSERT privilege to the stored procedure ADD_EMP_PROJ:

     |GRANT INSERT ON EMPLOYEE_PROJECT
     |  TO PROCEDURE ADD_EMP_PROJ;
   

1. Granting the EXECUTE privilege on a stored procedure to a role:

     |GRANT EXECUTE ON PROCEDURE ADD_EMP_PROJ
     |  TO ROLE MANAGER;
   

2. Granting the EXECUTE privilege on a stored function to a role:

     |GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
     |  TO ROLE MANAGER;
   

3. Granting the EXECUTE privilege on a package to user PUBLIC:

     |GRANT EXECUTE ON PACKAGE APP_VAR
     |  TO USER PUBLIC;
   

4. Granting the EXECUTE privilege on a function to a package:

     |GRANT EXECUTE ON FUNCTION GET_BEGIN_DATE
     |  TO PACKAGE APP_VAR;
   

1. Granting the USAGE privilege on a sequence to a role:

     |GRANT USAGE ON SEQUENCE GEN_AGE
     |  TO ROLE MANAGER;
   

2. Granting the USAGE privilege on a sequence to a trigger:

     |GRANT USAGE ON SEQUENCE GEN_AGE
     |  TO TRIGGER TR_AGE_BI;
   

3. Granting the USAGE privilege on an exception to a package:

     |GRANT USAGE ON EXCEPTION
     |  TO PACKAGE PKG_BILL;
   

1. Allow user JOE to create tables

     |GRANT CREATE TABLE
     |  TO USER Joe;
   

2. Allow user JOE to alter any procedure

     |GRANT ALTER ANY PROCEDURE
     |  TO USER Joe;
   

The optional WITH ADMIN OPTION clause allows the users specified in the user list to grant the role(s) specified to other users or roles.

For cumulative roles, a user can only exercise the WITH ADMIN OPTION of a secondary role if all intermediate roles are also granted WITH ADMIN OPTION. That is, GRANT ROLEA TO ROLE ROLEB WITH ADMIN OPTION, GRANT ROLEB TO ROLE ROLEC, GRANT ROLEC TO USER USER1 WITH ADMIN OPTION only allows USER1 to grant ROLEC to other users or roles, while using GRANT ROLEB TO ROLE ROLEC WITH ADMIN OPTION allows USER1 to grant ROLEA, ROLEB and ROLEC to other users.

1. Assigning the DIRECTOR and MANAGER roles to the user IVAN:

     |GRANT DIRECTOR, MANAGER
     |  TO USER IVAN;
   

2. Assigning the MANAGER role to the user ALEX with the authority to assign this role to other users:

     |GRANT MANAGER
     |  TO USER ALEX WITH ADMIN OPTION;
   

3. Assigning the DIRECTOR role to user ALEX as a default role:

     |GRANT DEFAULT DIRECTOR
     |  TO USER ALEX;
   

4. Assigning the MANAGER role to role DIRECTOR:

     |GRANT MANAGER
     |  TO ROLE DIRECTOR;
   

See alsoREVOKE