Data collaboration and protection Study Guide — COF-C03

Data Collaboration and Protection: Access, RBAC, and Governance

This domain covers how Snowflake controls who can access data, what they can do with it, and how that access is authenticated and audited. It represents a moderate share of the SnowPro Core COF-C03 blueprint under the current estimated domain weighting. Questions test precise knowledge of role hierarchy, privilege mechanics, authentication methods, and data governance features. Expect scenario-based questions that require you to distinguish between similar features (e.g., masking policies vs. row access policies) or identify the correct privilege/role combination for a given requirement.

RBAC: Role Hierarchy, System Roles, Custom Roles, and Ownership

System-Defined Roles

Snowflake ships with six system roles that form a fixed hierarchy. These cannot be dropped.

Key hierarchy rule: SECURITYADMIN inherits USERADMIN. ACCOUNTADMIN inherits both SECURITYADMIN and SYSADMIN. SYSADMIN does NOT inherit SECURITYADMIN.

System role hierarchy — arrows show inheritance direction (child grants up to parent).

Custom Roles

• Created with CREATE ROLE. Best practice: assign ownership to SYSADMIN so SYSADMIN can manage objects owned by those roles.
• If a custom role is NOT assigned under SYSADMIN, SYSADMIN cannot manage objects owned by that role.
• Roles are granted to users or to other roles (role hierarchy).
• Ownership of an object belongs to the role that created it. The owner role holds the OWNERSHIP privilege, which supersedes all others on that object.

Common Traps — RBAC

• The exam asks who can create roles: USERADMIN or SECURITYADMIN (or ACCOUNTADMIN). SYSADMIN cannot create roles.
• ACCOUNTADMIN is recommended for two or more named users — not a single shared account.
• Dropping a role does NOT drop objects owned by that role; ownership transfer must happen first.
• PUBLIC is a role, not a user group — every user automatically inherits it but it can be granted privileges.
• ORGADMIN is separate and does not appear in the standard ACCOUNTADMIN → SYSADMIN chain.

GRANT/REVOKE, Future Grants, and Secondary Roles

GRANT and REVOKE Basics

• Syntax: GRANT <privilege> ON <object_type> <name> TO ROLE <role_name>;
• REVOKE <privilege> ON <object_type> <name> FROM ROLE <role_name>;
• Privileges are additive: a user's effective privileges = union of all privileges held by their active role(s).
• WITH GRANT OPTION: allows the grantee to further grant that privilege to others.
• OWNERSHIP cannot be granted via GRANT — use GRANT OWNERSHIP ON <object> TO ROLE <role>;

Future Grants

Future grants apply a privilege to all future objects of a specified type within a schema or database — they do not retroactively apply to existing objects.

GRANT SELECT ON FUTURE TABLES IN SCHEMA mydb.myschema TO ROLE analyst;

• Scope: IN SCHEMA or IN DATABASE
• Does NOT affect objects that already exist at the time the statement runs
• If both schema-level and database-level future grants exist, schema-level takes precedence

Secondary Roles

USE SECONDARY ROLES ALL — activates all roles granted to the current user simultaneously (read/query use cases).
USE SECONDARY ROLES NONE — deactivates secondary roles.

Common Traps — GRANT/REVOKE

• Future grants do not backfill existing objects. A common distractor asks whether existing tables are affected — they are not.
• Schema-level future grants override database-level future grants for objects in that schema.
• WITH GRANT OPTION is not the same as OWNERSHIP.
• Secondary roles cannot be used for DML writes that require object ownership decisions in certain contexts — they are primarily useful for read access across roles.
• REVOKE on a parent role does not cascade to objects granted by child roles unless explicitly stated.

Worked Scenario: A new analyst needs SELECT on all tables created in SALES.PUBLIC going forward, but the DBA doesn't want to manually re-grant every time. → Use GRANT SELECT ON FUTURE TABLES IN SCHEMA SALES.PUBLIC TO ROLE ANALYST_ROLE. This is the correct, exam-expected answer. Granting on the schema itself does NOT cascade to tables.