What you will be able to do
- Trace any securable object up through schema, database, account and organization
- Explain how object ownership, role hierarchies, database roles and managed access schemas decide who can grant access
- Use views as an access boundary and predict when a view query fails because of the view owner's privileges
- Place virtual warehouses in an architecture as independent, grantable compute
Key concept
Container hierarchy — Every securable object lives inside a nested chain of containers: schema-level objects sit in a schema, schemas sit in a database, databases sit in the account, and the organization is at the top. Naming, ownership and grant design all follow from where an object sits in this chain.
1.The object hierarchy: organization to object
Snowflake's access model starts from one rule: a securable object is something access can be granted on, and without a grant, access is denied. To decide where those grants go, you first need to know where each object lives. Snowflake arranges objects in a hierarchy of logical containers. The organization is at the top. Below it is the account, which holds every database. Each database holds schemas, and the schemas hold the objects people actually work with: tables, views, functions, stages and the rest.
Each level of the hierarchy is managed in its own place. Organization-level work, such as managing the lifecycle of accounts, belongs to the GLOBALORGADMIN role, which exists only in the organization account. Everything below that happens inside a single account. So when a scenario asks where an object 'lives', the full answer is a path: organization, account, database, schema, object.
| Level | What it contains |
|---|---|
| Organization | The top-most container; organization-level tasks run in the organization account |
| Account | All databases for the account |
| Database | One or more schemas |
| Schema | Securable objects such as tables, views, functions and stages |
Checkpoint 1 of 7· Check yourself
Which container directly holds a stage object?
Stages are schema-level securable objects, just like tables, views and functions. The schema in turn sits inside a database.
“Securable objects such as tables, views, functions, and stages are contained in a schema object, which are in turn contained in a database.”Source: docs.snowflake.com
Sources1
2.Databases and schemas as design boundaries
All data in Snowflake lives in databases. A database is made of schemas, and a schema is a logical grouping of objects such as tables and views. Snowflake sets no hard limit on how many databases, schemas or objects you create. So the number of databases is never forced on you by the platform. It is a design decision about how to group objects for naming and for access.
The hierarchy also decides how names resolve. A view definition can name a table fully qualified (database.schema.table), partly qualified (schema.table) or unqualified. If the schema is left out, Snowflake assumes the table is in the same schema as the view, not in the session's active schema. That way the view always refers to the same table. Once objects are spread across many databases, for example one per business unit, any query that joins across them has to qualify names with the database.
CREATE VIEW v1 AS SELECT ... FROM my_database.my_schema.my_table; CREATE VIEW v1 AS SELECT ... FROM my_schema.my_table; CREATE VIEW v1 AS SELECT ... FROM my_table;Checkpoint 2 of 7· Check yourself
A view is created with SELECT ... FROM my_table and no schema qualifier. Later, a user whose active schema is different queries the view. Which table does the view read?
Unqualified names in a view resolve to the view's own schema. That stops a view from pointing at different tables depending on who queries it.
“If the schema is not specified, then Snowflake assumes that the table is in the same schema as the view.”Source: docs.snowflake.com
Checkpoint 3 of 7· Exam question
A new Snowflake account has only the default system roles. The security team wants one role that can manage grants on any object across the account and administer users and roles globally, while a separate role should own the databases, schemas, and warehouses that the data teams actually build. Which pairing correctly matches these two responsibilities to the built-in roles designed for them?
Correct answer: A — Assign SECURITYADMIN to manage grants and user/role administration globally, and assign SYSADMIN to own the databases, schemas, and warehouses created for data teams.
- A. Correct - SECURITYADMIN can manage grants on any object account-wide and administers users and roles, while SYSADMIN is the recommended parent for custom roles that own the databases, schemas, and warehouses data teams build.
- B. Incorrect - USERADMIN is limited to creating and managing users and roles rather than managing grants on arbitrary objects, and ACCOUNTADMIN is reserved for top-level administration rather than routine object ownership.
- C. Incorrect - this swaps the intended purposes; SYSADMIN is meant to own compute and data objects, while SECURITYADMIN, not SYSADMIN, holds the global grant-management responsibility.
- D. Incorrect - PUBLIC is the default role automatically granted to every user with minimal privileges, so it cannot serve as the account's global grant-management role.
Sources2
3.Roles, ownership and privilege inheritance
Snowflake mixes discretionary control, where every object has an owner who can grant access, with role-based control, where privileges go to roles and roles go to users. Owning an object means a role holds the OWNERSHIP privilege on it. By default, that is the role used to create the object. GRANT OWNERSHIP moves ownership to another role, including a database role.
When you grant one role to another, you build a role hierarchy. Privileges flow upward: a role inherits everything granted to the roles beneath it. Owning a role is not the same thing. A role that owns another role does not inherit that role's privileges. Some roles are system-defined and cannot be dropped. ACCOUNTADMIN encapsulates SYSADMIN and SECURITYADMIN, and it should be granted to only a small, controlled number of users. Snowflake recommends against adding object privileges straight to system-defined roles. Grant them to a custom role instead, then grant that custom role to the system-defined role.
Two role types match two levels of the hierarchy. Account roles can hold privileges on any object in the account. Database roles limit privileges to a single database and the objects inside it. A database role can't be activated in a session by itself. You grant it to an account role, and users activate the account role. Schemas add one more control: in a managed access schema, object owners can no longer make grant decisions. Only the schema owner, or a role with MANAGE GRANTS, can grant privileges on objects in that schema.
Checkpoint 4 of 7· Match them up
Match each access-control construct to its behaviour
Tap a term, then the definition that fits it.
Each construct controls privileges at a different point: per database, per schema, through grants between roles, or at the top of the account.
“However, in a managed access schema, object owners lose the ability to make grant decisions.”Source: docs.snowflake.com
Checkpoint 5 of 7· Exam question
An architect is documenting how a new Snowflake account will be organized before any databases are created. They need to explain to stakeholders which container directly holds databases, and which container directly holds schema-level objects such as tables and views, so that naming conventions can be planned correctly. Which statement correctly describes this containment order?
Correct answer: A — A Snowflake account directly contains databases, and each database directly contains the schemas that in turn hold tables, views, and other schema-level objects.
- A. Correct - the containment order in Snowflake runs account, then database, then schema, then schema-level objects like tables and views, so an account holds databases and a database holds schemas.
- B. Incorrect - this reverses the real order; schemas do not own databases, databases contain schemas, and the database itself sits directly under the account.
- C. Incorrect - accounts sit between an organization and its databases, so a database is never a direct child of the organization while an account layer exists.
- D. Incorrect - virtual warehouses are account-level compute resources that are not nested inside databases, so a database cannot contain a warehouse in the object hierarchy.
Sources1
4.Views as an access boundary
A view is a named query whose result you can use as if it were a table. For architecture, the key point is that a view is a securable object in its own right. You can grant a privilege on a view to a role without giving that role any privilege on the tables underneath. The documentation's hospital example builds two views over one table, one for doctors and one for accountants, so each group sees only the columns it needs.
CREATE VIEW doctor_view AS SELECT patient_ID, patient_name, diagnosis, treatment FROM hospital_table; CREATE VIEW accountant_view AS SELECT patient_ID, patient_name, billing_address, cost FROM hospital_table;There is one condition. The view runs on the privileges of the role that owns it. If a user can access the view but the view's owner role can't access the underlying table, the query fails. The choice of view type also has cost effects. A non-materialized view runs its query every time it is referenced. A materialized view stores its results, which is faster to read but needs storage and ongoing maintenance, both of which cost money. Either type can be defined as secure.
Checkpoint 6 of 7· Check yourself
A role has SELECT on doctor_view but no privileges on hospital_table. What else must be true for its query on the view to succeed?
Users can query through a view without privileges on the base table, but only if the view's owner role can reach that table.
“then the user cannot query the view unless the owner role of the view has access to the underlying table.”Source: docs.snowflake.com
Sources3
5.Virtual warehouses: compute as a securable object
Databases and schemas organize storage. A virtual warehouse supplies compute. It is a cluster of compute resources that runs SQL and Snowpark code. You need one for queries and for all DML, including loading data. Each warehouse is independent and has no effect on the performance of other warehouses. That independence is what lets an architect give each workload its own warehouse.
A warehouse is also a securable object, controlled with privileges like any other. The built-in notebook warehouse, SYSTEM$STREAMLIT_NOTEBOOK_WH, shows how this works. ACCOUNTADMIN owns it, PUBLIC has USAGE on it by default, and MONITOR, OPERATE and APPLYBUDGET can be granted by ACCOUNTADMIN. A session starts with no warehouse. Until one is associated, either the user's default warehouse or one set by the client or connection, no queries can run.
Checkpoint 7 of 7· Check yourself
Two teams share one database but each runs queries on its own virtual warehouse. What happens to Team B's query performance when Team A runs a heavy workload?
Warehouses are independent compute clusters. Sharing a database doesn't link their performance.
“As a result, each virtual warehouse has no effect on the performance of other virtual warehouses.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A user can run USE ROLE on a database role to work inside that database.Why is that wrong?
A database role can't be the active role in a session. It has to be granted to an account role, and the user activates the account role.
Covered in Roles, ownership and privilege inheritance
2.Granting SELECT on a view is always enough for a role to query it, whatever the view's owner can access.Why is that wrong?
The view runs with its owner's access to the base tables. If the owner role loses access to a base table, queries through the view fail.
Covered in Views as an access boundary
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“The top-most container is the customer organization.”
↩︎ The object hierarchy: organization to object“All databases for your Snowflake account are contained in the account object.”
↩︎ The object hierarchy: organization to object“Securable object: An entity to which access can be granted. Unless allowed by a grant, access is denied.”
↩︎ The object hierarchy: organization to object“The role exists only in the organization account.”
↩︎ The object hierarchy: organization to object“Each securable object is owned by a single role, which by default is the role used to create the object.”
↩︎ Roles, ownership and privilege inheritance“The privileges associated with a role are inherited by any roles above that role in the hierarchy.”
↩︎ Roles, ownership and privilege inheritance“Snowflake recommends granting the additional privileges to a custom role and assigning the custom role to the system-defined role.”
↩︎ Roles, ownership and privilege inheritance“grant privileges on the object to a database role in the same database”
↩︎ Roles, ownership and privilege inheritance“or a role with the MANAGE GRANTS privilege can grant privileges on objects in the schema.”
↩︎ Roles, ownership and privilege inheritance“Role that encapsulates the SYSADMIN and SECURITYADMIN system-defined roles.”
↩︎ Roles, ownership and privilege inheritance“Securable objects such as tables, views, functions, and stages are contained in a schema object, which are in turn contained in a database.”
↩︎ Key concept“Note that database roles cannot be activated directly in a session.”
↩︎ Exam trap 1“Securable objects such as tables, views, functions, and stages are contained in a schema object, which are in turn contained in a database.”
↩︎ Checkpoint“A role owner (the role that has the OWNERSHIP privilege on the role) does not inherit the privileges of the owned role.”
↩︎ Prediction“However, in a managed access schema, object owners lose the ability to make grant decisions.”
↩︎ Checkpoint - 2.https://docs.snowflake.com/en/user-guide/databasesOfficial docs
“Each database consists of one or more schemas, which are logical groupings of database objects, such as tables and views.”
↩︎ Databases and schemas as design boundaries“Snowflake does not place any hard limits on the number of databases, schemas (within a database), or objects (within a schema) you can create.”
↩︎ Databases and schemas as design boundaries - 3.
“you can grant privileges on a particular view to a particular role, without the grantee role having privileges on the table(s) underlying the view.”
↩︎ Views as an access boundary“This allows faster access, but requires storage space and active maintenance, both of which incur additional costs.”
↩︎ Views as an access boundary“Both non-materialized and materialized views can be defined as secure.”
↩︎ Views as an access boundary“then the user cannot query the view unless the owner role of the view has access to the underlying table.”
↩︎ Exam trap 2“If the schema is not specified, then Snowflake assumes that the table is in the same schema as the view.”
↩︎ Checkpoint“then the user cannot query the view unless the owner role of the view has access to the underlying table.”
↩︎ Checkpoint - 4.
“A virtual warehouse is a cluster of compute resources in Snowflake.”
↩︎ Virtual warehouses: compute as a securable object“As a result, each virtual warehouse has no effect on the performance of other virtual warehouses.”
↩︎ Checkpoint - 5.
“Warehouses are required for queries, as well as all DML operations, including loading data into tables.”
↩︎ Virtual warehouses: compute as a securable object“By default, the PUBLIC role has USAGE privileges.”
↩︎ Virtual warehouses: compute as a securable object“Until a session has a warehouse associated with it, queries cannot be submitted within the session.”
↩︎ Virtual warehouses: compute as a securable object