Role hierarchies
A role hierarchy is formed when one role is granted membership in another role.
A role contains another role when the other role is granted to the first role. The other role inherits all of the privileges of the first role. For example, if the role DOCTOR is granted to the role SURGEON, then SURGEON is said to contain DOCTOR. The role SURGEON inherits all the privileges of role DOCTOR.
Cycles in role hierarchies are not allowed. A cycle occurs if a role is granted in circular way such that one role is granted to another role and that other role is granted to the original role. For example, the role DOCTOR is granted to role SURGEON, and then the role SURGEON is granted back to the role DOCTOR. If you create a cycle in a role hierarchy, an error is returned (SQLSTATE 428GF).
Example of building a role hierarchy
The following example shows how to build a role hierarchy to represent the medical levels in a hospital.
Consider the following roles: DOCTOR, SPECIALIST, and SURGEON. A role hierarchy is built by granting a role to another role, but without creating cycles. The role DOCTOR is granted to role SPECIALIST, and role SPECIALIST is granted to role SURGEON.
Granting role SURGEON to role DOCTOR would create a cycle and is not allowed.
CREATE ROLE DOCTOR
CREATE ROLE SPECIALIST
CREATE ROLE SURGEON
GRANT ROLE DOCTOR TO ROLE SPECIALIST
GRANT ROLE SPECIALIST TO ROLE SURGEON