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.
1.3 Define the entities and roles that are used in Snowflake
-
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
- Securable objects
-
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
Top comments (0)