SECURITYADMIN is a built-in Snowflake role with the MANAGE GRANTS privilege. It manages grants and administers users and roles, but is usually not granted data access. section 10.1.1
Use SECURITYADMIN for access-control administration rather than routine object creation:
- Switch to SECURITYADMIN before granting privileges to access roles. section 10.1.3
- Grant database, schema, table, view, and future-object privileges to access roles. section 10.1.3
- Grant access roles to functional roles, then grant functional roles to SYSADMIN. section 10.1.3
- Keep data access separate from administrative authority; USERADMIN usually creates users and roles, while SYSADMIN creates account objects. section 10.1.1
Granting reporting access
use role SECURITYADMIN; grant usage on database BAKERY_DB to role BAKERY_READ; grant usage on schema BAKERY_DB.RPT to role BAKERY_READ; grant select on all tables in schema BAKERY_DB.RPT to role BAKERY_READ; grant select on all views in schema BAKERY_DB.RPT to role BAKERY_READ; grant select on future tables in schema BAKERY_DB.RPT to role BAKERY_READ; grant select on future views in schema BAKERY_DB.RPT to role BAKERY_READ; 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.
Trace the role hierarchy from access roles through functional roles to SYSADMIN in the bakery RBAC configuration. section 10.1.3
