FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 SQL PrivilegesStatements for Granting Privileges 

A role is a database object that packages a set of privileges.Roles implement the concept of access control at a group level.Multiple privileges are granted to the role and then that role can be granted to or revoked from one or many users, or even other roles.

A role that has been granted as a “default” role will be activated automatically.Otherwise, a user must supply that role in their login credentials — or with SET ROLE — to exercise the associated privileges.Any other privileges granted to the user directly are not affected by their login with the role.

Logging in with multiple explicit roles simultaneously is not supported, but a user can have multiple default roles active at the same time.

In this section the tasks of creating and dropping roles are discussed.

CREATE ROLE

Creates a role

Available in

DSQL, ESQL

Syntax
CREATE ROLE rolename
  [SET SYSTEM PRIVILEGES TO <sys_privileges>]

<sys_privileges> ::=
  <sys_privilege> [, <sys_privilege> ...]

<sys_privilege> ::=
    USER_MANAGEMENT | READ_RAW_PAGES
  | CREATE_USER_TYPES | USE_NBACKUP_UTILITY
  | CHANGE_SHUTDOWN_MODE | TRACE_ANY_ATTACHMENT
  | MONITOR_ANY_ATTACHMENT | ACCESS_SHUTDOWN_DATABASE
  | CREATE_DATABASE | DROP_DATABASE
  | USE_GBAK_UTILITY | USE_GSTAT_UTILITY
  | USE_GFIX_UTILITY | IGNORE_DB_TRIGGERS
  | CHANGE_HEADER_SETTINGS
  | SELECT_ANY_OBJECT_IN_DATABASE
  | ACCESS_ANY_OBJECT_IN_DATABASE
  | MODIFY_ANY_OBJECT_IN_DATABASE
  | CHANGE_MAPPING_RULES | USE_GRANTED_BY_CLAUSE
  | GRANT_REVOKE_ON_ANY_OBJECT
  | GRANT_REVOKE_ANY_DDL_RIGHT
  | CREATE_PRIVILEGED_ROLES | GET_DBCRYPT_INFO
  | MODIFY_EXT_CONN_POOL | REPLICATE_INTO_DATABASE
  | PROFILE_ANY_ATTACHMENT
Table 1. CREATE ROLE Statement Parameter
Parameter Description

rolename

Role name.The maximum length is 63 characters

sys_privilege

System privilege to grant

The statement CREATE ROLE creates a new role object, to which one or more privileges can be granted subsequently.The name of a role must be unique among the names of roles in the current database.

Warning

It is advisable to make the name of a role unique among usernames as well.The system will not prevent the creation of a role whose name clashes with an existing username, but if it happens, the user will be unable to connect to the database.

docnext count = 8

Who Can Create a Role

The CREATE ROLE statement can be executed by:

  • Administrators

  • Users with the CREATE ROLE privilege

    • Setting system privileges also requires the system privilege CREATE_PRIVILEGED_ROLES

The user executing the CREATE ROLE statement becomes the owner of the role.

CREATE ROLE Examples

Creating a role named SELLERS
CREATE ROLE SELLERS;
Creating a role SELECT_ALL with the system privilege to select from any selectable object
CREATE ROLE SELECT_ALL
  SET SYSTEM PRIVILEGES TO SELECT_ANY_OBJECT_IN_DATABASE;
See also

[fblangref50-security-alterrole], [fblangref50-security-role-drop], GRANT, REVOKE, Fine-grained System Privileges

ALTER ROLE

Alters a role

Available in

DSQL

Syntax
ALTER ROLE rolename
 { SET SYSTEM PRIVILEGES TO <sys_privileges>
 | DROP SYSTEM PRIVILEGES
 | {SET | DROP} AUTO ADMIN MAPPING }

<sys_privileges> ::=
  !! See [fblangref50-security-role-create] !!
Table 1. ALTER ROLE Statement Parameter
Parameter Description

rolename

Role name;specifying anything other than RDB$ADMIN will fail

sys_privilege

System privilege to grant

ALTER ROLE can be used to grant or revoke system privileges from a role, or enable and disable the capability for Windows Administrators to assume administrator privileges automatically when logging in.

This last capability can affect only one role: the system-generated role RDB$ADMIN.

For details on auto admin mapping, see AUTO ADMIN MAPPING.

It is not possible to selectively grant or revoke system privileges.Only the privileges listed in the SET SYSTEM PRIVILEGES clause will be available to the role after commit, and DROP SYSTEM PRIVILEGES will remove all system privileges from this role.

Who Can Alter a Role

The ALTER ROLE statement can be executed by:

  • Administrators

  • Users with the ALTER ANY ROLE privilege, with the following caveats

    • Setting or dropping system privileges also requires the system privilege CREATE_PRIVILEGED_ROLES

    • Setting or dropping auto admin mapping also requires the system privilege CHANGE_MAPPING_RULES

ALTER ROLE Examples

Drop all system privileges from a role named SELECT_ALL
ALTER ROLE SELLERS
  DROP SYSTEM PRIVILEGES;
Grant a role SELECT_ALL the system privilege to select from any selectable object
ALTER ROLE SELECT_ALL
  SET SYSTEM PRIVILEGES TO SELECT_ANY_OBJECT_IN_DATABASE;
See also

[fblangref50-security-role-create], GRANT, REVOKE, Fine-grained System Privileges

DROP ROLE

Drops a role

Available in

DSQL, ESQL

Syntax
DROP ROLE rolename

The statement DROP ROLE deletes an existing role.It takes a single argument, the name of the role.Once the role is deleted, the entire set of privileges is revoked from all users and objects that were granted the role.

Who Can Drop a Role

The DROP ROLE statement can be executed by:

  • Administrators

  • The owner of the role

  • Users with the DROP ANY ROLE privilege

DROP ROLE Examples

Deleting the role SELLERS
DROP ROLE SELLERS;
See also

[fblangref50-security-role-create], GRANT, REVOKE