Account Management and Data Governance · Topic 2 of 8

System-Defined, Functional and Secondary Roles

Know what ACCOUNTADMIN, SECURITYADMIN, USERADMIN, SYSADMIN and PUBLIC do, how account, database and custom roles differ, and how secondary roles work.

  • 5 min
  • Includes a diagram
In a nutshell
  • Hierarchy: ACCOUNTADMIN above SECURITYADMIN (above USERADMIN) and SYSADMIN. PUBLIC is granted to everyone.
  • SECURITYADMIN holds MANAGE GRANTS. USERADMIN creates users and roles. SYSADMIN creates warehouses and databases.
  • ORGADMIN works at organization level and is not part of the account role hierarchy.

What it is

Every Snowflake account comes with system-defined roles for administration. You add your own custom roles to model job functions and data access.

Roles come in two scopes. Account roles exist at the account level and can be activated in a session. Database roles exist inside one database and must be granted to an account role to be used.

A session always has one primary role and can also use secondary roles, so a user can combine the privileges of several roles without switching.

ACCOUNTADMINtopinheritsSECURITYADMINgrantsinheritsUSERADMINusers and rolesACCOUNTADMINtopinheritsSYSADMINwarehouses and databasesinheritsCustom rolesjob functions

How it works

System-defined roles

System-defined role hierarchy: ACCOUNTADMIN inherits SECURITYADMIN and SYSADMIN, SECURITYADMIN inherits USERADMIN, custom roles roll up to SYSADMIN, PUBLIC is granted to every user and role, and ORGADMIN sits outside the account role hierarchy.

RolePurpose
ACCOUNTADMINTop-level role. Inherits SYSADMIN and SECURITYADMIN. Manages billing, resource monitors and account settings
SECURITYADMINManages grants globally with the MANAGE GRANTS privilege. Inherits USERADMIN
USERADMINCreates and manages users and roles (CREATE USER, CREATE ROLE)
SYSADMINCreates warehouses, databases and other objects. Custom roles should roll up to it
PUBLICGranted automatically to every user and every role. Anything granted to PUBLIC is available to all
ORGADMINManages organization-level tasks, such as listing accounts. Sits outside the account role hierarchy

Organizations that use an organization account also have GLOBALORGADMIN there, and Snowflake plans to phase out ORGADMIN in its favor.

ACCOUNTADMIN should be granted to a small number of users, each protected with MFA. It should not be the default role for daily work, and it should not own everyday objects.

Watch out

If custom roles are not granted to SYSADMIN, system administrators cannot manage the objects those roles own; only roles with MANAGE GRANTS can see and regrant them. Roll every custom role tree up to SYSADMIN.

Functional roles and access roles

A common design separates two kinds of role. Access roles hold privileges on objects, such as read or write on one schema. Functional roles match a job, such as analyst or data engineer, and receive the access roles that job needs.

Database rolesales readgranted toAccount roleanalystgranted toUserMariaDatabase rolesales writegranted toAccount roledata engineergranted toSYSADMINadmin tree

This keeps object privileges in one place. When a new analyst joins, you grant one functional role instead of dozens of object privileges.

Account roles, database roles and custom roles

FeatureAccount roleDatabase role
ScopeWhole accountOne database
Can be the session roleYes, with USE ROLENo
Can be granted toUsers and other account rolesAccount roles and other database roles in the same database
Can be granted to a shareNoYes
Created withCREATE ROLECREATE DATABASE ROLE

Database roles are ideal access roles: they travel with the database, they can be granted to a share for consumers, and they keep privileges scoped to one database. To use one, grant it to an account role.

A custom role is any role you create, account or database. Create custom account roles with USERADMIN (or any role with CREATE ROLE), then grant privileges and grant the role up to SYSADMIN.

Primary and secondary roles

The primary role is the one set with USE ROLE. It authorizes CREATE <object> statements, and the new object is owned by the primary role.

Secondary roles add privileges for everything else, such as SELECT, INSERT or USAGE. USE SECONDARY ROLES ALL activates every role granted to the user, and USE SECONDARY ROLES NONE turns them off.

SessionUSE ROLEPrimary rolecreates and ownsplusSecondary rolesquery and modifyresultUnion of privileges

The user property DEFAULT_SECONDARY_ROLES sets which secondary roles start with each session. When the property is unset, all granted roles are active as secondary roles.

Exam tip

A user with secondary roles active can join a table from one role's database with a table from another role's database in one query. Only the CREATE step depends on the primary role.

Common mistakes

Using ACCOUNTADMIN for everything. It works, but it breaks least privilege and makes ACCOUNTADMIN the owner of everyday objects. Use SYSADMIN or custom roles.

Trying to USE a database role. Database roles cannot be activated in a session. Grant them to an account role first.

Mixing up SECURITYADMIN and USERADMIN. USERADMIN creates users and roles. SECURITYADMIN can also grant or revoke any privilege through MANAGE GRANTS.

Expecting secondary roles to own objects. Objects are always owned by the primary role that created them.

What to remember for the exam

  • Hierarchy: ACCOUNTADMIN above SECURITYADMIN (above USERADMIN) and SYSADMIN. PUBLIC is granted to everyone.
  • SECURITYADMIN holds MANAGE GRANTS. USERADMIN creates users and roles. SYSADMIN creates warehouses and databases.
  • ORGADMIN works at organization level and is not part of the account role hierarchy.
  • Custom roles should be granted to SYSADMIN.
  • Database roles are scoped to one database, cannot be the session role and can be granted to shares.
  • Functional roles model jobs; access roles hold object privileges.
  • Only the primary role authorizes CREATE; secondary roles add privileges for other statements.

Check yourself

  • Which system role should own the hierarchy of custom roles?

    SYSADMIN. Granting custom roles to SYSADMIN lets system administrators manage the objects those roles create.

  • Can a user run USE ROLE with a database role?

    No. A database role must be granted to an account role, and the account role is activated in the session.

  • A user's primary role is ANALYST and secondary roles are ALL. Which role owns a table the user creates?

    ANALYST, because only the primary role authorizes CREATE statements and owns the new object.

Topic 2 of 8 in Account Management and Data Governance

Test yourself on Account Management and Data Governance

The full Snowflake SnowPro Core COF-C03 question bank has 845 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.