Accounts and Security · Topic 4 of 17

System Roles, Functional and Access Roles, and Secondary Roles

Know the Snowflake system roles and best practices, how functional roles differ from access roles, and how secondary roles combine privileges.

  • 5 min
  • Includes a diagram
In a nutshell
  • USERADMIN manages users and roles; SECURITYADMIN manages grants and inherits USERADMIN; SYSADMIN owns objects; ACCOUNTADMIN sits above both.
  • ORGADMIN is the organization level role for accounts and organization usage.
  • Best practice: few ACCOUNTADMIN users with MFA, never default, no objects created with it; custom roles roll up to SYSADMIN.

What it is

Every account starts with a fixed set of system roles, and a good design adds custom roles on top. The recommended pattern splits custom roles into two kinds: access roles that carry object privileges, and functional roles that represent what a person does. Secondary roles then let a user combine several functional roles in one session.

The exam asks which system role performs which task, why ACCOUNTADMIN must be used sparingly, and how a custom role tree should be shaped.

Object privilegesheld byAccess rolesper object set, read or writegranted toFunctional rolesper job functiongranted toUsersUsersUSE SECONDARY ROLESCombined privilegesin one session

How it works

System roles

System role hierarchy: ACCOUNTADMIN above SECURITYADMIN, which is above USERADMIN, and above SYSADMIN, which owns the custom roles granted to it; PUBLIC is at the bottom and every user has it.

RolePurpose
ORGADMINManages the organization: creates accounts, views organization usage
ACCOUNTADMINTop account role; inherits SYSADMIN and SECURITYADMIN; sees billing and manages account level settings
SECURITYADMINHolds MANAGE GRANTS; creates and manages roles and grants; inherits USERADMIN
USERADMINCreates and manages users and roles (CREATE USER, CREATE ROLE)
SYSADMINCreates warehouses, databases and other objects; the root of the custom role tree
PUBLICGranted to every user and role automatically; objects owned by it are available to all
ACCOUNTADMINinheritsSECURITYADMINmanages grantsinheritsUSERADMINusers and rolesACCOUNTADMINinheritsSYSADMINobjectsinheritsCustom rolesaccess and functional

Best practices for system roles

  • Use ACCOUNTADMIN only for tasks that need it, and never as a daily or default role.
  • Assign it to a small number of people (at least two, so one absence does not lock you out), protect them with MFA, and give each a valid email address.
  • Do not create objects with ACCOUNTADMIN or SECURITYADMIN; a database owned by ACCOUNTADMIN is hard to delegate.
  • Create custom roles with USERADMIN (or SECURITYADMIN) and make every custom role a descendant of SYSADMIN, so administrators can manage the objects.
  • Give SYSADMIN ownership of databases and warehouses, then delegate through grants rather than through ACCOUNTADMIN.
  • Keep privileges off PUBLIC unless every user really needs them.
Watch out

If a custom role is not granted up to SYSADMIN, its objects are invisible to SYSADMIN and the administrators lose control over them.

Functional roles compared to access roles

Users get functional roles; functional roles inherit access roles, such as database roles, which hold the privileges on objects; functional roles are granted up to SYSADMIN. The recommended design separates what can be touched from who does the work.

AspectAccess roleFunctional role
RepresentsA set of privileges on objectsA job function or team
ExampleSALES_RO, SALES_RW, FINANCE_ROANALYST, DATA_ENGINEER, MARKETING_LEAD
HoldsObject privilegesOther roles (mostly access roles)
Granted toFunctional rolesUsers
Changed whenObjects or permissions changePeople change jobs
CREATE ROLE sales_ro;
CREATE ROLE finance_ro;
CREATE ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO ROLE sales_ro;
GRANT ROLE sales_ro TO ROLE analyst;
GRANT ROLE finance_ro TO ROLE analyst;
GRANT ROLE analyst TO USER ana;

Adding a table changes one access role. Moving a person to a different team changes one user grant. Neither change touches the other layer, which is the reason for the split. Database roles serve well as access roles, because they stay with the database and can go into a share.

Secondary roles

A session has one primary role and may have secondary roles. Privileges from the primary role and all active secondary roles are combined to authorize most actions, so a user does not have to switch roles to join data from two domains.

USE ROLE analyst;
USE SECONDARY ROLES ALL;
ALTER USER ana SET DEFAULT_SECONDARY_ROLES = ('ALL');
  • USE SECONDARY ROLES ALL activates every role granted to the user; NONE turns them off.
  • The user property DEFAULT_SECONDARY_ROLES sets what is active at login.
  • Creating objects is authorized by the primary role only, and the primary role owns the new object.
  • Policy functions differ: CURRENT_ROLE() returns only the primary role, while IS_ROLE_IN_SESSION('ROLE') is true if the role is the primary role, a secondary role, or inherited by either.
Exam tip

When a masking or row access policy must still work for users who combine roles, write it with IS_ROLE_IN_SESSION, not CURRENT_ROLE. Otherwise a user with the right secondary role is treated as unauthorized.

User anaprimary role ANALYSTaddsSecondary rolesFINANCE, MARKETINGunion of privilegesQuery authorizedbut new objects owned by primary

Common mistakes

Working as ACCOUNTADMIN all the time. One wrong command then runs with the most powerful role. Switch to it only for the task.

Leaving custom roles outside the SYSADMIN tree. Administrators then cannot see or manage the objects.

Granting object privileges straight to functional roles. Each team change turns into many grants. Use access roles in between.

Using CURRENT_ROLE() in policies. Secondary roles are ignored, so policies behave unexpectedly for combined sessions.

What to remember for the exam

  • USERADMIN manages users and roles; SECURITYADMIN manages grants and inherits USERADMIN; SYSADMIN owns objects; ACCOUNTADMIN sits above both.
  • ORGADMIN is the organization level role for accounts and organization usage.
  • Best practice: few ACCOUNTADMIN users with MFA, never default, no objects created with it; custom roles roll up to SYSADMIN.
  • Access roles hold object privileges; functional roles map to jobs and are granted to users.
  • Secondary roles combine privileges, but only the primary role creates and owns objects.
  • IS_ROLE_IN_SESSION sees secondary and inherited roles; CURRENT_ROLE does not.

Check yourself

  • Which system role should own databases and warehouses?

    SYSADMIN, with custom roles granted to it so that the whole tree is manageable.

  • Why separate access roles from functional roles?

    Object changes affect only access roles and job changes affect only user grants, so each change stays small.

  • A user has secondary roles active and creates a table. Who owns it?

    The primary role, because object creation is authorized and owned by the primary role only.

Topic 4 of 17 in Accounts and Security

Test yourself on Accounts and Security

The full Snowflake ARA-C01 question bank has 687 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.