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
- 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
CREATEand 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.
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;
| Property | Account role | Database role |
|---|---|---|
| Scope | Whole account | One database |
| Granted to | Users and roles | Account roles and database roles of the same database |
| Can be a primary or secondary role | Yes | No, grant it to an account role first |
| Can be granted to a share | No | Yes |
| Moves with database clone and replication | No | Yes |
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_VIEWERand similar are database roles on the shared SNOWFLAKE database.
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
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_ROLESsets 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. SHOWcommands that list objects still needUSAGEon the containing database and schema.
Use cases for each
| Need | Use |
|---|---|
| Join data from two domains in one query without switching roles | Secondary roles |
| Create objects owned by a specific team role | Set that role as primary |
| Keep a narrow, predictable session | USE SECONDARY ROLES NONE |
| Make BI tools show the full access of a user | DEFAULT_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.
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
CREATEand owns the result; secondary roles add privileges for other actions. DEFAULT_SECONDARY_ROLES = ('ALL')makes all granted roles active at login.IS_ROLE_IN_SESSIONsees secondary and inherited roles;CURRENT_ROLEdoes 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.