SnowPro Advanced Architect

By Snowflake · Question Mode

QuestionQ101

Secure Data Sharing and Consumption

A company stores data in two databases, DB1 and DB2, with these roles:

  • Role R1 has SELECT access to every table in database DB1.
  • Role R2 has SELECT access to every table in database DB2.

By default, users must be permitted to access data from only one database, so the DEFAULT_ROLE setting must be used. A small number of users who have SELECT access to DB1 also require SELECT access to DB2 within the same query.

How can access be provided to these limited users with the least operational overhead?

Explanation

Snowflake authorizes SELECT statements using the aggregate privileges of the active primary role and active secondary roles. Granting R2 directly to the exceptional users and having them activate it with USE SECONDARY ROLES R2 lets their primary/default role retain DB1 access while R2 supplies DB2 access for the session that needs a cross-database query. This avoids permanently broadening default access and avoids creating a parent role solely for the exception.

Learn more

Community Discussion

No comments yet. Be the first to start the discussion!