Course contents

SnowPro Core COF-C03 Study GuidePart 3: Access control and securitySystem-Defined Roles

System-Defined Roles

System-Defined Roles

In short

Every account starts with six roles that keep the most dangerous powers in a few known places, and your own roles join that tree under SYSADMIN.

The problem

Some powers can hurt the whole company: changing billing, reading every grant, creating account-wide integrations. If any custom role could hold them, nobody would know who can do what. Snowflake ships a fixed set of system roles so these powers live in a handful of well-known places.

The mental model

Picture a tree. Arrows point from child to parent, the direction the grants were made, so privileges pile up toward the top. ACCOUNTADMIN sits at the top of the account. ORGADMIN isn't part of an account's tree: its powers cover the whole organization, such as creating accounts and reading usage across them. Few people hold it, and the key fact is that it sits above accounts. PUBLIC sits under everything, because every user and role holds it automatically.

System-defined role hierarchy. ORGADMIN has organization-wide powers and is not part of an account's tree. Inside one account: USERADMIN is granted to SECURITYADMIN, SECURITYADMIN and SYSADMIN are granted to ACCOUNTADMIN, and the custom roles ANALYST and DATA_ENGINEER are granted to SYSADMIN. HR_ADMIN is deliberately granted to no parent. PUBLIC is held by every user and every role.ORGANIZATIONONE ACCOUNTORGADMINcreates and lists accounts; not part of the account treeSYSTEM ROLEACCOUNTADMINtop of the accountSYSTEM ROLESECURITYADMINMANAGE GRANTSSYSTEM ROLESYSADMINwarehouses, databasesSYSTEM ROLEUSERADMINusers and roles it ownsCUSTOM ROLEANALYSTCUSTOM ROLEDATA_ENGINEERCUSTOM ROLEHR_ADMINno parentSYSTEM ROLEPUBLICevery role inherits it, so it sits under every treeeach arrow: child role granted to its parent

ORGANIZATION

ORGADMINcreates and lists accounts; not part of the account tree

ONE ACCOUNT

SYSTEM ROLEACCOUNTADMINtop of the account

  • SYSTEM ROLESECURITYADMINMANAGE GRANTS

    • SYSTEM ROLEUSERADMINusers and roles it owns

  • SYSTEM ROLESYSADMINwarehouses, databases

    • CUSTOM ROLEANALYST

    • CUSTOM ROLEDATA_ENGINEER

CUSTOM ROLEHR_ADMINno parent

SYSTEM ROLEPUBLICevery role inherits it, so it sits under every tree

each arrow: child role granted to its parent

Figure 3.3 The system role hierarchy inside one account, with ORGADMIN above it at organization level. Custom roles join the tree by being granted to SYSADMIN. HR_ADMIN has no parent on purpose, so no admin role inherits access to unmasked HR data.

What each role is for

RoleWhat it's forIts limit
ORGADMINOrganization-level work: create and list accounts, read ORGANIZATION_USAGE, enable replication between accountsDoesn't manage objects inside an account
ACCOUNTADMINTop of the account. Inherits SECURITYADMIN and SYSADMIN. Creates resource monitors (credit limits for warehouses), views billing, sets account parameters, creates security integrationsNo practical limit inside the account, which is why it should be used rarely
SECURITYADMINHolds the global MANAGE GRANTS privilege, so it can grant and revoke any privilege. Inherits USERADMINDoesn't create resource monitors or view billing
USERADMINCREATE USER and CREATE ROLE. Administers the users and roles it ownsCan't change users or roles another role owns. SECURITYADMIN can step in: MANAGE GRANTS lets it transfer that ownership first
SYSADMINCreates warehouses and databases. By convention every custom role is granted to itCan't touch objects owned by custom roles that were never granted up to it
PUBLICGranted automatically to every user and every roleMust never hold anything sensitive: a grant to PUBLIC is a grant to everyone

Worked example: the custom-role rule

  1. Setup. Thomas's DATA_ENGINEER role creates SALES_DB.CORE.ORDERS.
  2. Symptom. Months later Youness, working as SYSADMIN, runs ALTER TABLE orders ADD COLUMN channel STRING and gets "Schema 'SALES_DB.CORE' does not exist or not authorized." SYSADMIN owns SALES_DB, but the schema and table belong to DATA_ENGINEER.
  3. Cause. SYSADMIN is high in the tree, but inheritance only travels along grants that exist, and owning a database gives nothing on the schemas inside it. DATA_ENGINEER was never granted to SYSADMIN.
  4. Fix. GRANT ROLE data_engineer TO ROLE sysadmin;
  5. Result. DATA_ENGINEER stays the owner. SYSADMIN inherits its privileges, including OWNERSHIP, so it can act as the owner would, and the ALTER succeeds.

Limits and traps

Distractors on this scenario either reverse the direction ("custom roles inherit SYSADMIN's privileges") or invent a monopoly ("only ACCOUNTADMIN can alter tables"). Both are wrong. The one intended exception is HR_ADMIN. Masking and row access policies test the roles in the session, so if HR_ADMIN were granted to SYSADMIN, a SYSADMIN session would match a policy condition such as IS_ROLE_IN_SESSION('HR_ADMIN') and see salaries (Column-Level Security section). Keeping HR_ADMIN out costs nothing operationally: it owns no objects. The HR tables belong to DATA_ENGINEER, which is in the tree, so SYSADMIN can still repair them; it just sees masked values.

ACCOUNTADMIN hygiene

Give ACCOUNTADMIN to at least two people, so one lost phone can't lock the company out. Require MFA for them. Never make it anyone's default role, and never run scripts or create everyday objects with it. It still has jobs only it can do, such as creating resource monitors and security integrations. At IsmaFrost, Youness and one backup hold it, and both work day to day as SECURITYADMIN or SYSADMIN.

Exam takeaway
  • ACCOUNTADMIN inherits SECURITYADMIN (which inherits USERADMIN) and SYSADMIN. The reverse is never true.
  • SECURITYADMIN has MANAGE GRANTS. Only ACCOUNTADMIN creates resource monitors.
  • Grant custom roles to SYSADMIN, or SYSADMIN can't manage what they build.
  • Anything granted to PUBLIC is granted to every user.