Section 1.3 - SnowPro Core Certification Study Guide

This series is intended to be a personal study guide. Information may not be comprehensive or accurate. I am sharing it in case others find it useful. Please feel free to comment if any information is inaccurate.

Return to Exam Guide

1.3 Define the entities and roles that are used in Snowflake

Overview of Access Control

• Outline how privileges can be granted and revoked

  • Securable objects
    • Every securable object resides within a logical container inside a hierarchy of containers
    • Account object contains database objects, which contain schemas, which contain tables/views/functions/stages
    • Access to securable objects is allowed via privileges assigned to roles, which are then assigned to other roles or to users
    • Each object also has an owner that can grant access to other roles
    • Each object is owned by a single role, by default the one that created it; when role is assigned to users, they share control of the object
    • In a managed access schema, only the schema owner or a role with MANAGE GRANTS can grant privileges on objects in the schema
  • Roles
    • Privileges are granted to or revoked from roles
    • Roles are assigned to users, who can be assigned multiple roles
    • Users can choose which role is active in the current session to perform different actions
    • Users with certain privileges can create custom roles
    • System defined roles cannot be dropped and their privileges cannot be revoked
      • ORGADMIN: create accounts, view all accounts and regions, view usage info across org
      • ACCOUNTADMIN: encapsulates SYSADMIN and SECURITYADMIN
      • SECURITYADMIN: manage any object grant globally, create/monitor/manage users and roles
      • USERADMIN: dedicated to user and role management and creation
      • SYSADMIN: creates warehouses and databases in an account
      • PUBLIC: pseudo-role granted to every role and user
    • Custom roles: create a hierarchy of custom roles with the top-most level assigned to SYSADMIN, otherwise SYSADMIN won't be able to manage objects created by the custom role
    • Each active user can have 1 primary role and any number of secondary roles, and can switch between them
    • CREATE statements can only be performed by primary roles

• Explain Role Hierarchy and Privilege Inheritance

  • Roles can be assigned to other roles, creating a role hierarchy
  • Roles inherit all of the privileges assigned to roles below them in the role hierarchy

Return to Exam Guide