Snowflake Secondary Roles: Privilege Evaluation and Audit Impact

In Snowflake's role model, every session has exactly one Primary Role and zero or more Secondary Roles. The Primary Role is the traditional role you switch to with USE ROLE; when Secondary Roles are enabled, the privileges of every other role granted to the user are also evaluated at query time. This drastically reduces the operational overhead of constantly running USE ROLE to swap roles.

Basic Syntax

-- Enable Secondary Roles in the session (use privileges from every granted role)
USE SECONDARY ROLES ALL;

-- Disable Secondary Roles (evaluate only the Primary Role)
USE SECONDARY ROLES NONE;

-- Check the current Secondary Roles setting
SELECT CURRENT_SECONDARY_ROLES();

-- Check the current Primary Role
SELECT CURRENT_ROLE();

-- Change the default for a user
ALTER USER analyst_user
  SET DEFAULT_SECONDARY_ROLES = ('ALL');

-- Revert the default to NONE
ALTER USER analyst_user
  SET DEFAULT_SECONDARY_ROLES = ();

Privilege Evaluation Logic

When Secondary Roles are enabled, Snowflake combines the role hierarchies of the Primary Role and every Secondary Role to check privileges on the objects referenced by a query. The query succeeds as long as any role hierarchy in scope has the required privilege (for example, SELECT).

Detailed Comparison of Privilege Evaluation

Real-World Scenario

Let's walk through the behavior for a user who has ANALYST_ROLE (Primary) and DATA_ENGINEER_ROLE (a Secondary Role candidate) granted.

-- Set ANALYST as the Primary Role
USE ROLE ANALYST_ROLE;

-- In this state, tables owned by DATA_ENGINEER_ROLE are not accessible
SELECT * FROM raw.events;  -- ERROR: Insufficient privileges

-- Enable Secondary Roles
USE SECONDARY ROLES ALL;

-- DATA_ENGINEER_ROLE's privileges are now also evaluated, so access succeeds
SELECT * FROM raw.events;  -- success

-- CURRENT_ROLE() still returns ANALYST_ROLE
SELECT CURRENT_ROLE();  -- 'ANALYST_ROLE'

-- IS_ROLE_IN_SESSION() can confirm both roles
SELECT IS_ROLE_IN_SESSION('DATA_ENGINEER_ROLE');  -- TRUE
SELECT IS_ROLE_IN_SESSION('ANALYST_ROLE');         -- TRUE

Audit Log Impact

When Secondary Roles are enabled, the ROLE_NAME column of SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY records the Primary Role. Whether Secondary Roles were actually used is not directly recorded in QUERY_HISTORY, so for a complete audit picture you need to join it with LOGIN_HISTORY or SESSIONS.

-- Audit Secondary Roles usage per session
SELECT
  s.session_id,
  s.user_name,
  s.login_event_id,
  q.role_name AS primary_role,
  q.query_text,
  q.start_time
FROM SNOWFLAKE.ACCOUNT_USAGE.SESSIONS s
JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY q
  ON s.session_id = q.session_id
WHERE q.start_time >= DATEADD(DAY, -7, CURRENT_TIMESTAMP())
  AND q.query_text ILIKE '%USE SECONDARY ROLES%'
ORDER BY q.start_time DESC;

Policy Design Considerations

• Limits of CURRENT_ROLE(): If a Masking Policy or Row Access Policy uses CURRENT_ROLE() for access control, Secondary Role privileges are not considered. Replace CURRENT_ROLE() with IS_ROLE_IN_SESSION() to evaluate Secondary Roles as well.
• Tension with least privilege: Secondary Roles ALL is convenient but carries the risk of applying privileges from unintended roles. In environments handling sensitive data, prefer IS_ROLE_IN_SESSION()-based policy design.
• Managing DEFAULT_SECONDARY_ROLES: Must be set per user via ALTER USER, so any bulk change requires a script.

Migration Pattern: CURRENT_ROLE() to IS_ROLE_IN_SESSION()

-- Before: Masking Policy based on CURRENT_ROLE()
CREATE OR REPLACE MASKING POLICY mask_ssn AS (val STRING)
  RETURNS STRING →
  CASE
    WHEN CURRENT_ROLE() IN ('HR_ADMIN', 'COMPLIANCE')
      THEN val
    ELSE '***-**-****'
  END;

-- After: based on IS_ROLE_IN_SESSION() (Secondary Roles aware)
CREATE OR REPLACE MASKING POLICY mask_ssn AS (val STRING)
  RETURNS STRING →
  CASE
    WHEN IS_ROLE_IN_SESSION('HR_ADMIN')
      OR IS_ROLE_IN_SESSION('COMPLIANCE')
      THEN val
    ELSE '***-**-****'
  END;

Check Your Understanding

Frequently Asked Questions