Course contents

SnowPro Core COF-C03 Study GuidePart 3: Access control and securityUser Management

User Management

User Management

In short

A user is a login identity with a type and a few defaults. Defaults only point to a role or a warehouse. Grants are what give access.

The problem

IsmaFrost has two very different kinds of login: people who open Snowsight, and programs that run at night. They need different sign-in rules, and both need to land in the right role without surprises.

The mental model

The first decision for any user is its TYPE, because it changes how the user may authenticate.

TYPEMeant forAuthentication
PERSON (default)Humans in Snowsight, notebooks, BI toolsSSO, or password with MFA, as the account's authentication policy allows (Authentication section)
SERVICEPipelines, apps, integrationsWorkload identity federation, OAuth, key pair or programmatic access tokens, each explained in the Authentication section. No password sign-in and no MFA prompts
LEGACY_SERVICEOld service users that still use passwordsAllowed password sign-in. Being retired during 2026: existing LEGACY_SERVICE users are converted to SERVICE

How it works: defaults point, grants give

DEFAULT_ROLE and DEFAULT_WAREHOUSE are pointers. Setting DEFAULT_ROLE = analyst doesn't grant ANALYST, and Snowflake doesn't even check that the role exists. A user can only use roles explicitly granted to them, so a default role without the matching GRANT ROLE never takes effect. A default warehouse without USAGE on it is just as empty. Figure 3.9 separates the two kinds of link.

User amine and what actually gives him access. His defaults point to the role ANALYST and the warehouse BI_WH but grant nothing. Access comes from two grants: ANALYST granted to user amine, and USAGE on BI_WH granted to ANALYST. With both grants in place his session starts as ANALYST on BI_WH.USERamineTYPE = PERSONDEFAULT_ROLE = analystDEFAULT_WAREHOUSE = bi_whROLEANALYSTaccount roleWAREHOUSEBI_WHSession starts as ANALYST on BI_WH because both grants exist.Remove either grant and the matching default does nothing.DEFAULT_ROLE points toDEFAULT_WAREHOUSE points toGRANT ROLE analyst TO USER amineUSAGE granted to ANALYST User amine and what actually gives him access. His defaults point to the role ANALYST and the warehouse BI_WH but grant nothing. Access comes from two grants: ANALYST granted to user amine, and USAGE on BI_WH granted to ANALYST. With both grants in place his session starts as ANALYST on BI_WH.ROLEANALYSTaccount roleUSERamineTYPE = PERSONDEFAULT_ROLE =analystDEFAULT_WAREHOUSE= bi_whWAREHOUSEBI_WHGRANT ROLEanalyst TOUSER amineDEFAULT_ROLEpoints toDEFAULT_WAREHOUSEpoints toUSAGE grantedto ANALYSTSession starts as ANALYSTon BI_WH because bothgrants exist.Remove either grant and thematching default does nothing.
Figure 3.9 Dashed arrows are defaults: they only point. Solid arrows are grants: they give access. Amine starts as ANALYST on BI_WH because both grants exist, not because of his defaults.

Worked example: onboarding Amine, the agency contractor, and the ETL service

USE ROLE useradmin;                  -- USERADMIN creates users
CREATE USER amine
  TYPE = PERSON
  LOGIN_NAME = 'amine@ismafrost.com'
  PASSWORD = '********'
  MUST_CHANGE_PASSWORD = TRUE      -- forces a new password at first login
  DEFAULT_ROLE = analyst           -- a pointer, not a grant
  DEFAULT_WAREHOUSE = bi_wh
  DEFAULT_SECONDARY_ROLES = ('ALL');   -- already the default for new users; written out to be explicit

CREATE USER svc_etl
  TYPE = SERVICE
  RSA_PUBLIC_KEY = 'MIIBIjANBgkq...'   -- key pair, no password
  DEFAULT_ROLE = data_engineer
  DEFAULT_WAREHOUSE = etl_wh;

USE ROLE securityadmin;              -- IsmaFrost grants through SECURITYADMIN (Custom Roles section)
GRANT ROLE analyst       TO USER amine;     -- without this, DEFAULT_ROLE does nothing
GRANT ROLE data_engineer TO USER svc_etl;

ALTER USER amine SET DISABLED = TRUE;   -- offboarding: login stops, history and grants kept

Expected result. Amine signs in, sets a new password, enrols in MFA (required for every person who signs in with a password, Authentication section), and lands in ANALYST on BI_WH. svc_etl can only sign in with its private key. When Amine's contract ends, disabling him stops login at once and keeps his query history and role grants in place for audit. (Objects belong to roles, not users, so tables built under his role don't move either way.) Dropping a user can't be undone, so disable first.

Limits and traps

Creating and changing users takes the CREATE USER privilege (USERADMIN has it by default) or OWNERSHIP of the user object. SECURITYADMIN can do both through inheritance. MODIFY, GRANT and SELECT are the usual distractors, and none of them is how user administration works.

Exam takeaway
  • CREATE USER (through USERADMIN) or OWNERSHIP of the user is what user administration needs.
  • TYPE = SERVICE users don't sign in with passwords and skip MFA. PERSON users with passwords need MFA.
  • DEFAULT_ROLE grants nothing. GRANT ROLE does.
  • Disable before you drop: DISABLED stops login at once, DROP USER can't be undone.