The CREATE ROLE privilege permits a role to create custom access-control roles. USERADMIN has this privilege, and other roles can receive it. After creation, each role must be granted to a user or another role before it takes effect. A role hierarchy can place access roles beneath functional roles and grant the top-level custom role to SYSADMIN, allowing inherited privilege management. section 10.1.2
Apply the privilege in an RBAC setup by separating role creation from object privilege grants:
- Switch to USERADMIN or another role with CREATE ROLE, then create access and functional roles. section 10.1.3
- Switch to SECURITYADMIN or a role with MANAGE GRANTS to assign database, schema, table, view, and future-object privileges. section 10.1.3
- Grant access roles to functional roles, then grant functional roles to SYSADMIN or users as required. section 10.1.2 section 10.1.3
Create bakery RBAC roles
use role USERADMIN; create role BAKERY_FULL; create role BAKERY_READ; create role DATA_ENGINEER; create role DATA_ANALYST; section 10.1.3
Figure 10.1 Role-based access control configuration of the environment used in the example in this chapter. The environment includes two functional roles, DATA_ENGINEER and DATA ANALYST, and two access roles, BAKERY_FULL and BAKERY_READ. Access roles are granted to functional roles, and functional roles are granted to the SYSADMIN role.
Role hierarchy diagram showing access roles granted to functional roles and functional roles granted to SYSADMIN. section 10.1.3
