Privileges Through Roles - Firebird

Implementing roles is a four-step process:

  • Create a role using the CREATE ROLE statement.
  • Assign privileges to the role using GRANT privilege TO rolename.
  • Grant the role to users using GRANT rolename TO user.
  • Specify the role, along with the user name, when attaching to a database.

Creating a Role

The syntax pattern for creating a role is simple:

CREATE ROLE <role-name>;

SYSDBA or the database owner can create roles, grant privileges to them and, initially, grant these “loaded” roles to users. If a role is granted WITH ADMIN OPTION, the recipient of the role can grant it on to other users, WITH ADMIN OPTION or without.

Assigning Role Privileges

To “load” a role with privileges, just grant the required privileges as if the role were a user:

GRANT <privileges> TO <role-name>;

Granting a Role to Users

The GRANT statement for granting a role to users omits the ON clause—it’s implicit in the permissions “loaded” into the role.

GRANT <role-name> [, <role-name> [, ...]]]
TO [USER] <user-name> [, [USER] <user-name> [, ...]]]
[WITH ADMIN OPTION];

The optional WITH ADMIN OPTION allows grantees to grant the role to other users and to revoke it. It works in a similar way to WITH GRANT OPTION for regular permissions—see the section “Granting the Right to Grant Privileges.”

The following example creates the MAITRE_D role, grants ALL privileges on the DEPARTMENT table to this role, and then grants the role to HORTENSE. This gives HORTENSE the privileges SELECT, INSERT, UPDATE, DELETE, and REFERENCES on DEPARTMENT.

CREATE ROLE MAITRE_D;
COMMIT;
GRANT ALL
ON DEPARTMENT
TO MAITRE_D;
GRANT MAITRE_D TO HORTENSE;

Attaching to the Database Under a Role

When connecting, include ROLE in the connection parameters and specify the role whose privileges you want to acquire for that connection. It will only work if your username has been granted the role:

CONNECT <database-path>
USER <your-user-name>
ROLE <role-name>
PASSWORD <your-password>;

Dropping a Role

If you drop a role, all privileges that were conferred by that role are revoked. To drop the role MAITRE_D:

DROP ROLE MAITRE_D;

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Firebird Topics