Snowflake Security, RBAC, and User Administration · Topic 3 of 16

Custom Security Roles, Role Inheritance and Aligning Access to Business Functions

Create custom roles, understand how role inheritance affects grants and revokes, and align object access with business functions in Snowflake.

  • 4 min
In a nutshell
  • Custom roles model job functions and object access sets; USERADMIN creates them.
  • Every custom role should roll up to SYSADMIN.
  • Parents inherit from children; the flow is upward only.

What it is

System roles cover administration, but business access needs custom roles. A custom role is a role you create with CREATE ROLE to represent a set of privileges or a job function. Roles can be granted to other roles, and the parent inherits the privileges of its children, which makes inheritance the part of the design that decides who can really do what.

The exam asks when to use custom roles, who creates them, and what happens down and up the tree when a privilege or role is granted or revoked.

USERADMINCREATE ROLEthenCustom rolegranted up toSYSADMINmanages the objectsinherited byACCOUNTADMIN

How it works

Why custom roles

  • To model job functions (ANALYST, DATA_ENGINEER) and object access sets (SALES_RO, SALES_RW).
  • To give an administrator a narrow power, such as a role with MANAGE WAREHOUSES and nothing else.
  • To keep system roles clean. Snowflake advises against adding unrelated privileges to a system role; create a custom role and grant that role to the system role instead.

Creating and managing custom roles

CREATE ROLE needs the CREATE ROLE privilege, which USERADMIN holds. Create roles with USERADMIN or SECURITYADMIN, and make each one a descendant of SYSADMIN.

USE ROLE USERADMIN;
CREATE ROLE sales_ro COMMENT = 'Read sales data';
CREATE ROLE analyst;
GRANT ROLE sales_ro TO ROLE analyst;
GRANT ROLE analyst TO ROLE sysadmin;
GRANT ROLE analyst TO USER ana;

Managing access then means a small set of statements:

TaskStatement
Give a role privilegesGRANT <privilege> ON <object> TO ROLE r
Build the hierarchyGRANT ROLE child TO ROLE parent
Give a user a roleGRANT ROLE r TO USER u
Remove a linkREVOKE ROLE child FROM ROLE parent
Remove a privilegeREVOKE <privilege> ON <object> FROM ROLE r
Hand an object to another roleGRANT OWNERSHIP ON <object> TO ROLE r
Watch out

A custom role that is not granted up to SYSADMIN leaves its objects out of reach of SYSADMIN, and the administrators lose control of them.

Role inheritance and its implications

A role inherits every privilege of the roles below it, and a user who holds the parent can use all of them.

TableSELECTthensales_rogranted toanalystgranted tosenior_analystgranted toUser ana

Consequences that scenarios build on:

  • Granting a role to a parent role immediately gives the parent every privilege of that child, including privileges added to the child later.
  • Revoking a privilege from the child removes it for every role above, unless the parent also holds it by another path.
  • Revoking a role from a parent removes the whole branch of privileges coming through that link, but not privileges the parent receives from another child.
  • Inheritance flows up only. A child role never receives the parent's privileges.
  • Owning a role is not inheriting it. A role that owns another role does not inherit its privileges; only the grant hierarchy passes privileges.
  • Over inheritance is the usual failure: granting a broad role to a high level role gives every user of that role the whole set.
Exam tip

Before revoking from a role, ask which roles inherit it. The question "why does the user still have access after the revoke" almost always has a second path through another role.

Aligning object access with business functions

Use two steps.

  1. Create access roles by object domain and level: SALES_RO, SALES_RW, FINANCE_RO.
  2. Create functional roles by job: ANALYST, FINANCE_LEAD, and grant them the access roles they need.
SALES_ROreadgranted toANALYSTjob rolegranted toUser anaFINANCE_ROreadgranted toFINANCE_LEADgranted toUser fin

A new table changes one access role. A person changing jobs changes one user grant. Neither change touches the other layer, and a review of who can see sales data means reading one role.

Make the function role the user's default role (DEFAULT_ROLE), so a login does not land on a system role.

Common mistakes

Granting privileges directly to a functional role. Each team change turns into many grants.

Assuming a revoke ends access. Another role path can keep it alive.

Forgetting that new privileges on a child flow to every parent. Adding INSERT to sales_ro also gives it to every role above.

Creating custom roles with ACCOUNTADMIN. Use USERADMIN or SECURITYADMIN.

What to remember for the exam

  • Custom roles model job functions and object access sets; USERADMIN creates them.
  • Every custom role should roll up to SYSADMIN.
  • Parents inherit from children; the flow is upward only.
  • A revoke works only if no other path grants the same privilege.
  • Access roles hold object privileges; functional roles map to jobs and go to users.

Check yourself

  • Which role creates custom roles by best practice?

    USERADMIN (or SECURITYADMIN), and each role is then granted up to SYSADMIN.

  • You revoke SELECT from a child role but a parent still reads the table. Why?

    The parent holds SELECT by another path, such as another child role or a direct grant.

  • Does a parent role pass privileges to its child?

    No, inheritance flows only from the child role up to the parent.

Topic 3 of 16 in Snowflake Security, RBAC, and User Administration

Test yourself on Snowflake Security, RBAC, and User Administration

The full Snowflake ADA-C02 question bank has 814 questions, with timed practice exams and an explanation for every answer.

This study guide is independent and is not affiliated with, endorsed by, or sponsored by Snowflake. Vendor names, certification names and exam codes are trademarks of their respective owners and are used for identification only.