A managed access schema centralizes privilege administration for the objects it contains. Unlike a regular schema, where the creating role owns an object and can grant, revoke, or transfer its privileges, a managed access schema has no individual object owners. The schema owner, or a role with MANAGE GRANTS, controls object grants. section 10.1.2
Use managed access schemas to keep object creators from independently sharing individual tables or views. They fit role-based access control designs that separate access roles, which hold common privilege sets, from functional roles, which are assigned to users by job function. section 10.1.3 section 10.4
Create and operate a managed access schema through a centralized grant path:
- Create the schema with `CREATE SCHEMA <schema_name> WITH MANAGED ACCESS` using an administrative role. section 11.1.5
- Grant schema privileges to access roles with a role that has MANAGE GRANTS, then grant those access roles to functional roles. section 11.1.5
- Add grants on future tables and views when new objects must automatically be readable by an access role. section 10.1.3
- Reuse established access roles when adding pipeline-layer or orchestration schemas so functional roles inherit the new access without additional grants. section 11.1.5 section 13.1.1
Pipeline schemas with centralized access
Create `EXT`, `STG`, `DWH`, and `MGMT` with managed access. Grant an access role such as `BAKERY_FULL` full privileges on each schema. Grant a read role access to current and future tables and views in `MGMT`; functional roles that already hold these access roles inherit the corresponding privileges. section 11.1.5
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.
The role diagram shows access roles granted to functional roles, illustrating where centrally managed schema privileges enter the RBAC hierarchy. section 10.1.3
