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

Database Roles and Primary and Secondary Roles

Use database roles to scope access to one database, and decide when primary and secondary roles combine privileges in an active Snowflake session.

  • 4 min
  • Includes a diagram
In a nutshell
  • Database roles are scoped to one database, can be shared, and move with clones and replication.
  • Database roles go to account roles; they cannot be primary or secondary roles.
  • Primary role authorizes CREATE and owns the result; secondary roles add privileges for other actions.

What it is

A database role is a role that lives inside one database and holds privileges on objects of that database only. It travels with the database when it is cloned or replicated, and it can be granted to a share. That makes it the standard access role for data sharing and for databases that move between accounts.

A session has one primary role and can have any number of secondary roles. Secondary roles let one session use the combined privileges of several roles without switching.

DatabaseSALESholdsDatabase roleSALES.READERgranted toAccount roleANALYSTgranted toUser anaDatabase roleSALES.READERgranted toSharefor consumers

How it works

Database roles

CREATE DATABASE ROLE sales.reader;
GRANT USAGE ON SCHEMA sales.public TO DATABASE ROLE sales.reader;
GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO DATABASE ROLE sales.reader;
GRANT DATABASE ROLE sales.reader TO ROLE analyst;
SHOW DATABASE ROLES IN DATABASE sales;
SHOW GRANTS TO DATABASE ROLE sales.reader;
PropertyAccount roleDatabase role
ScopeWhole accountOne database
Granted toUsers and rolesAccount roles and database roles of the same database
Can be a primary or secondary roleYesNo, grant it to an account role first
Can be granted to a shareNoYes
Moves with database clone and replicationNoYes

Hierarchy limits from the docs: a database role granted to a share cannot have other database roles granted to it, and an account role cannot be granted to a database role. Database roles are cloned only when the whole database is cloned.

Use cases:

  • Sharing: give each consumer only the objects of a database role, instead of the whole database.
  • Delegation: the database owner manages access without needing account level role administration.
  • Portability: replicas and clones keep the access structure.
  • Snowflake provided: SNOWFLAKE.USAGE_VIEWER, SNOWFLAKE.SECURITY_VIEWER and similar are database roles on the shared SNOWFLAKE database.
Exam tip

If a scenario says access must stay intact after a database is replicated to another account, the answer is database roles, not account roles.

Primary and secondary roles

A query is authorized by the union of the primary role and the active secondary roles, but CREATE is authorized only by the primary role, which then owns the new object. The primary role is set with USE ROLE. Secondary roles are activated with USE SECONDARY ROLES.

USE ROLE analyst;
USE SECONDARY ROLES ALL;
USE SECONDARY ROLES finance_ro, marketing_ro;
USE SECONDARY ROLES NONE;
ALTER USER ana SET DEFAULT_SECONDARY_ROLES = ('ALL');

How authorization works:

  • Actions other than creation can be authorized by any privilege held by the primary role or an active secondary role.
  • CREATE <object> is authorized only by the primary role, and the new object is owned by the primary role.
  • DEFAULT_SECONDARY_ROLES sets what is active at login. () means none; ('ALL') means all roles granted to the user, and it is also the result of unsetting it.
  • Privileges granted directly to a user (UBAC) count only when secondary roles are ALL.
  • SHOW commands that list objects still need USAGE on the containing database and schema.
Primary roleANALYSTplusSecondary rolesFINANCE_RO, MARKETING_ROunionQuery authorizedPrimary roleANALYSTonlyCREATE TABLEowned by ANALYST

Use cases for each

NeedUse
Join data from two domains in one query without switching rolesSecondary roles
Create objects owned by a specific team roleSet that role as primary
Keep a narrow, predictable sessionUSE SECONDARY ROLES NONE
Make BI tools show the full access of a userDEFAULT_SECONDARY_ROLES = ('ALL')

Policies need care. CURRENT_ROLE() returns only the primary role, while IS_ROLE_IN_SESSION('ROLE') is true for the primary role, an active secondary role, or a role they inherit. Write masking and row access policies with IS_ROLE_IN_SESSION so combined sessions behave as intended.

Watch out

A user who relies on secondary roles to create objects will get permission errors or wrong ownership. Creation follows the primary role only.

Common mistakes

Trying to use a database role as a primary role. It must be granted to an account role first.

Granting an account role to a database role. Not allowed; the hierarchy goes from database role up to account role.

Expecting secondary roles to own new objects. The primary role owns them.

Writing policies with CURRENT_ROLE(). Secondary roles are ignored.

What to remember for the exam

  • Database roles are scoped to one database, can be shared, and move with clones and replication.
  • Database roles go to account roles; they cannot be primary or secondary roles.
  • Primary role authorizes CREATE and owns the result; secondary roles add privileges for other actions.
  • DEFAULT_SECONDARY_ROLES = ('ALL') makes all granted roles active at login.
  • IS_ROLE_IN_SESSION sees secondary and inherited roles; CURRENT_ROLE does not.

Check yourself

  • Which role type can be granted to a share?

    A database role.

  • Who owns a table created in a session with secondary roles active?

    The primary role.

  • How do you make all of a user's roles active at login?

    Set DEFAULT_SECONDARY_ROLES = ('ALL') on the user.

Topic 6 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.