Everyone Can Read. No One Can Break. A Snowflake Governance Blueprint

Want to pass SnowPro Core COF-C03 but don’t know where to start? Follow IsmaFrost. A company that built their entire Snowflake setup from scratch, asked every question you will face on the exam, and made every decision the certification tests. Each chapter ends with 10 exam-style questions (15 for this one).

This chapter covers the question that holds everything together: how do you give four independent teams full autonomy over their data without letting any of them break what the others built?

The Problem: Autonomy Turning Into Chaos

IsmaFrost had solved the ingestion bottleneck. They had given autonomy to each team using Snowflake. Everything started with a good intention: give teams access, let them move fast, and remove the engineering bottlenecks.

But as Karim stared at the Snowflake query logs on a Friday afternoon, he realized a new problem was brewing. Without strict structure, autonomy was turning into chaos.

Tables were being modified across teams. Ownership of critical data models was becoming unclear. And downstream pipelines were breaking silently because someone in Marketing accidentally dropped an Operations table they thought was a duplicate.

Karim capped his marker. “The issue,” he told the team, “is that autonomy without boundaries creates chaos. We need to give feature teams the power to build, but we cannot lose control of the platform.”

The Access Model by Layer

Reminders on the foundations

To stop the bleeding, Karim reinforced the golden rule they had established in previous article: One Environment = One Database.

Whether it was DEV, PREPROD, or PROD, there was strictly one database per environment. This ensured the exact same structure existed everywhere, preventing configuration drift.

Inside each of these environment databases, Karim locked in four standard schemas:

• RAW: For untransformed, newly ingested data.
• STG (Staging): For lightly cleaned and standardized data.
• INT (Integration): For joined and modeled business entities.
• MARTS: For final, aggregated reporting tables.

RAW Layer: The Foundation of Trust

Karim pointed his marker at the very top layer of the database diagram: the RAW schema.

“This,” he said, tapping the board firmly, “is our vault. It is the exact, unedited mirror of our source systems. If our downstream transformation pipelines fail, or if the finance team completely changes how they calculate revenue, we must always be able to rebuild our models from scratch. That only works if the RAW schema is an absolute, incorruptible source of truth." To guarantee this integrity, Karim established a draconian set of rules for the RAW layer:

Karim stepped back and wrote the guiding philosophy next to the vault.

The Rule: “Trusted source. Never touched.” “When data enters the RAW schema," Karim concluded, "it is written in stone. We share it freely, but we protect it ruthlessly."

Controlled Ownership: Scaling Without Collisions

Karim moved his down up from the protected RAW vault to the next three layers on the whiteboard: **STG (Staging), INT (Integration), and MARTS (Reporting).**

“This is the factory floor,” Karim announced. “This is where the feature teams actually do their work, build their models, and create the tables that power our dashboards.”

But he knew this was also where the most damage could be done. If he gave the feature teams too much power, they could accidentally wipe out each other’s work. To solve this, Karim introduced a concept he called Controlled Ownership.

“It’s like renting an apartment,” Karim explained. “The platform team owns the building. You own the furniture inside your unit. You can arrange your living room however you want, but you don’t get a sledgehammer to knock down the walls.”

Here is how IsmaFrost strictly enforced that rule:

Karim drew a thick, red box around the final takeaway on the whiteboard.

The Rule: Ownership is at the table level, not the schema level.

“You own the data you create,” Karim concluded, capping his marker. “But the platform owns the ground it sits on. That is how we move fast without breaking things.”

The Key Principle & Philosophy

Karim stepped back, letting the team absorb the dense matrix of privileges and boundaries. It looked like a complicated administrative nightmare, but the logic keeping it all together was surprisingly elegant.

“Everyone can see everything,” Karim concluded. “No one can break everything.” “Philosophy is great,” the lead DevOps engineer said from the back of the room. “But how do we actually enforce this? If I have to log into the Snowflake UI and manually type GRANT SELECT every time someone builds a new table, I'm going to quit by Friday."

Karim smiled. “No one is clicking anything,” he promised. “And no one is running manual GRANT statements. Here is how we enforce this architecture at scale.”

How the Data Platform Enforces This at Scale

Karim smiled at the DevOps engineer. “No one is clicking anything. No one is running manual GRANT statements. Here is how we enforce this at scale.” He drew three columns on the whiteboard: Feature Teams, Platform Roles, and Automation. Each one a layer of the enforcement model.

The Feature Team Blueprint

Every team gets exactly one role. Access is never granted to individual users. If you are in Marketing, you are assigned MARKETING_ROLE. That role receives three things, applied identically across every schema.

-- Every team role gets this exact pattern across every schema
GRANT USAGE ON SCHEMA ISMAFROST_PROD.MARTS TO ROLE MARKETING_ROLE;
GRANT USAGE ON SCHEMA ISMAFROST_PROD.INT   TO ROLE MARKETING_ROLE;
GRANT USAGE ON SCHEMA ISMAFROST_PROD.STG   TO ROLE MARKETING_ROLE;

-- Current objects
GRANT SELECT ON ALL TABLES IN SCHEMA ISMAFROST_PROD.MARTS TO ROLE MARKETING_ROLE;
GRANT SELECT ON ALL VIEWS  IN SCHEMA ISMAFROST_PROD.MARTS TO ROLE MARKETING_ROLE;

-- Future objects — no ticket, no manual regranting, ever
GRANT SELECT ON FUTURE TABLES IN SCHEMA ISMAFROST_PROD.MARTS TO ROLE MARKETING_ROLE;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA ISMAFROST_PROD.MARTS TO ROLE MARKETING_ROLE;

USAGE unlocks the door. SELECT on current objects covers everything that exists today. FUTURE grants are what make the model scale: when Operations builds a new table tomorrow, Marketing can query it instantly without submitting a single ticket.

The Platform Roles

Three system-defined roles sit above the feature teams. They are not custom roles. They are Snowflake’s built-in enforcers, and each has a single, non-overlapping responsibility.

The separation of duties is intentional and precise.

• SYSADMIN builds the structure but never hands out keys.
• SECURITYADMIN hands out keys but never builds the structure and owns no data object.
• ACCOUNTADMIN inherits from both but is reserved for governance and oversight. No single role can both build the environment and control who enters it.

From Chaos to Control: The Final Model

Karim wiped the complex matrix of roles and grants off the whiteboard, leaving only the core philosophy.

IsmaFrost had just solved the hardest balancing act in data engineering. They hadn’t handed over the keys to the kingdom with full, reckless control. Nor had they trapped their feature teams behind a wall of IT support tickets. Instead, they had built something far more precise: Controlled Ownership.

The new model systematically answered every question that had caused chaos just three months earlier:

• Who owns the foundation? (SYSADMIN): The platform strictly owns the databases, schemas, and virtual warehouses. No feature team can drop a schema, alter an environment, or fracture the core architecture.
• Who hands out the keys? (SECURITYADMIN): Every USAGE and SELECT grant is executed by a single role via automated CI/CD pipelines. No ad-hoc permissions, no manual clicks in the UI, and zero configuration drift.
• Who owns the tables? (The Feature Teams): When the MARKETING_ROLE creates the campaign_roi table, they own it absolutely. They can insert, update, alter, or drop it. They have total autonomy within their own four walls.
• Who can read the data? (Everyone): Thanks to FUTURE grants, the moment a new table is deployed, it becomes instantly visible to the rest of the company. No IT tickets, no waiting, no data silos.
• Who can break someone else’s work? (Nobody): Cross-team write access isn’t just against company policy, it is structurally, mathematically impossible.

Karim drew a thick red box around the four final takeaways on the whiteboard and capped his marker.

• Schemas are centralized.
• Tables are owned.
• Data is shared.
• Risk is contained.

“The rules are set, and the factory is officially open,” Karim told the room. “Now, let’s go build.”

Snowflake Governance & RBAC Practice Exam

Q1. Which Snowflake system role is responsible for creating custom roles and executing GRANT statements?

• A) SYSADMIN
• B) ACCOUNTADMIN
• C) SECURITYADMIN
• D) USERADMIN

✅ Correct answer: C SECURITYADMIN is responsible for creating users, building custom roles, and executing all GRANT statements. SYSADMIN creates databases and schemas but never touches GRANT statements. USERADMIN can create users and roles but cannot grant object-level privileges.

Q2. A developer using MARKETING_ROLE runs CREATE TABLE campaign_roi inside the MARTS schema. Which role owns the table?

• A) SYSADMIN, because it owns the MARTS schema
• B) SECURITYADMIN, because it manages all role privileges
• C) MARKETING_ROLE, because the role that creates an object automatically becomes its owner
• D) ACCOUNTADMIN, because it inherits everything

✅ Correct answer: C In Snowflake, the role that executes the CREATE statement automatically becomes the owner of the object. SYSADMIN owns the schema but not the tables inside it created by other roles.

Q3. A team wants to ensure that every new table created in the MARTS schema is immediately accessible to ANALYTICS_ROLE without any manual intervention. Which Snowflake feature enables this?

• A) Dynamic data masking applied at the schema level
• B) GRANT SELECT ON FUTURE TABLES IN SCHEMA MARTS TO ROLE ANALYTICS_ROLE
• C) A scheduled task that runs GRANT SELECT every hour
• D) Row access policies on the MARTS schema

✅ Correct answer: B FUTURE GRANTS automatically apply a privilege to all objects created in the future within a specified schema. New tables created by any role inherit the SELECT grant for ANALYTICS_ROLE without any manual regranting.

Q4. Which privilege on a schema allows a role to see the objects inside it without being able to modify the schema structure?

• A) OWNERSHIP
• B) MODIFY
• C) USAGE
• D) CREATE TABLE

✅ Correct answer: C USAGE on a schema allows a role to access and query objects inside it. It does not allow altering the schema structure, dropping the schema, or creating objects. OWNERSHIP would grant full control over the schema including DROP SCHEMA.

Q5. The platform team wants to drop MARKETING_ROLE. Before doing so, what must happen to the tables owned by MARKETING_ROLE?

• A) Nothing. Snowflake automatically reassigns ownership to SYSADMIN when a role is dropped
• B) The tables must be dropped first before the role can be dropped
• C) Ownership of all objects must be transferred to another role before dropping the role
• D) SECURITYADMIN automatically inherits the tables when any role is dropped

✅ Correct answer: C When a role is dropped, its owned objects become orphaned and unmanageable. Ownership must be explicitly transferred to another role, typically SYSADMIN, before the role is dropped. Snowflake does not automatically reassign ownership.

Q6.IsmaFrost wants to guarantee that whenever a team creates a new table in the future, all other teams can immediately read it without requiring manual IT intervention. Which Snowflake feature enables this? A) GRANT PUBLIC ACCESS

B) FUTURE grants

C) Auto-Ingest Snowpipe

D) Row-Level Security Policies

✅ Correct Answer: B. FUTURE grants allow administrators to define access rules for objects that do not exist yet. When the object is eventually created, the SELECT privilege is automatically applied.

Q7.Why is the ACCOUNTADMIN role considered dangerous to use for day-to-day data engineering tasks?

A) It consumes twice as many compute credits.

B) It cannot create tables or views.

C) It is the top-level role with ultimate power over billing, security, and data, meaning a mistake could compromise the entire account.

D) It automatically bypasses all CI/CD pipelines.

✅Correct Answer: C. ACCOUNTADMIN should be tightly locked down and used strictly for top-level account setup, integrations, and financial auditing. Day-to-day work should be done via SYSADMIN or custom roles.

Q8. What is the primary risk of a platform team assigning user privileges manually by clicking through the Snowflake UI?

A) It bypasses the SYSADMIN hierarchy.

B) It triggers automatic data sharing with external Snowflake accounts.

C) It creates configuration drift, where the permissions in production no longer match the code repository, leading to security gaps.

D) It permanently locks the SECURITYADMIN role.

✅Correct Answer: C. Manual UI changes cannot be tracked, version-controlled, or safely replicated across environments. Governance at scale requires Infrastructure as Code.

Q9. In a well-designed Snowflake role hierarchy, why should custom feature roles be granted to the SYSADMIN role?

A) So the SYSADMIN role inherits their privileges, allowing the platform team to manage, alter, or drop the tables created by the feature teams if necessary.

B) So feature teams can manage the company’s virtual warehouses.

C) So the SECURITYADMIN role cannot delete them.

D) To reduce the monthly compute cost.

✅Correct Answer: A. Role hierarchies flow upward. By granting feature roles to SYSADMIN, the system administrator role absorbs their powers, ensuring orphaned objects can always be managed by the platform team.

Q10. What SQL command is used by SECURITYADMIN to remove a previously granted USAGE privilege from a specific role?

A) DROP USAGE ON SCHEMA

B) DELETE USAGE FROM ROLE

C) REMOVE USAGE ON SCHEMA

D) REVOKE USAGE ON SCHEMA

✅ Correct Answer: D. REVOKE is the standard SQL command used in Snowflake to strip a privilege away from a role.

Q11. What is the purpose of the system-defined PUBLIC role in Snowflake?

A) To share data openly on the public internet.

B) It is a pseudo-role automatically granted to every user and role in the account, typically used for basic, non-sensitive baseline access.

C) It is used exclusively by Snowflake support engineers to troubleshoot your account.

D) It manages public data shares via the Snowflake Marketplace.

✅ Correct Answer: B. PUBLIC is the baseline. Because every user inherits it automatically, it should only be granted privileges that are universally safe for every single person in the company to access.

Q12. What is the best practice for assigning permissions to individual users in Snowflake?

A) Grant privileges directly to the user so you can track exactly who has access.

B) Grant privileges to the PUBLIC role, and let row-level security handle the rest.

C) Grant privileges to custom roles, and then grant those roles to the users. D) Assign all users the SYSADMIN role and rely on audit logs to catch mistakes.

✅Correct Answer: C. Snowflake relies on Role-Based Access Control. Best practice dictates that privileges should never be granted directly to users, but rather to roles that represent specific business functions.

Q13. Why does Snowflake separate the SECURITYADMIN role from the SYSADMIN role?

A) To force companies to purchase higher-tier Snowflake editions.

B) To prevent the role that manages user access from also owning the data structures, enforcing a separation of duties.

C) Because SYSADMIN is only used for billing purposes.

D) To allow feature teams to act as their own security administrators.

✅Correct Answer: B.Separation of duties is a core security principle. By keeping SECURITYADMIN (access management) separate from SYSADMIN (object ownership), no single role below ACCOUNTADMIN has the power to both build data structures and grant themselves access to everything.

Q14. Which of the following correctly describes the difference between OWNERSHIP and ALL PRIVILEGES on a table?

A) They are identical. ALL PRIVILEGES includes OWNERSHIP

B) ALL PRIVILEGES grants every privilege except OWNERSHIP. Ownership must be transferred separately

C) OWNERSHIP is a subset of ALL PRIVILEGES

D) ALL PRIVILEGES includes OWNERSHIP only when granted by ACCOUNTADMIN

✅ Correct answer: B ALL PRIVILEGES grants every available privilege on an object except OWNERSHIP. Ownership is always transferred explicitly using GRANT OWNERSHIP and cannot be included in an ALL PRIVILEGES grant.

Q15. A junior analyst using MARKETING_ROLE attempts to run DROP TABLE ops_inventory, a table owned by OPERATIONS_ROLE. What happens?

A) The DROP succeeds because MARKETING_ROLE has SELECT on all tables in MARTS

B) The DROP fails because MARKETING_ROLE only has SELECT on that table and does not hold OWNERSHIP

C) The DROP is queued for review by SECURITYADMIN before executing

D) The DROP succeeds but triggers an alert to ACCOUNTADMIN

✅ Correct answer: B DROP TABLE requires OWNERSHIP of the object. MARKETING_ROLE has only SELECT on tables owned by other teams. Without OWNERSHIP, the DROP is rejected immediately by Snowflake’s access control layer.