0
0
Snowflakecloud~15 mins

Granting and revoking privileges in Snowflake - Deep Dive

Choose your learning style9 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Overview - Granting and revoking privileges
What is it?
Granting and revoking privileges means giving or taking away permissions to access or change data and resources in Snowflake. These permissions control who can do what, like reading data, creating tables, or managing users. This system helps keep data safe and organized by making sure only the right people can do certain actions. It works by assigning specific rights to users or roles.
Why it matters
Without controlling privileges, anyone could see or change sensitive data, causing mistakes or security problems. Imagine a library where anyone can take or destroy books without rules. Granting and revoking privileges ensures that only trusted people can access or modify important information, protecting the organization and its data. It also helps track who did what, which is important for accountability.
Where it fits
Before learning this, you should understand basic Snowflake concepts like accounts, users, roles, and objects such as databases and tables. After this, you can learn about role hierarchy, role-based access control (RBAC), and auditing user actions. This topic is a key step in managing Snowflake security and governance.
Mental Model
Core Idea
Privileges are like keys that unlock specific doors to data and actions, and granting or revoking them controls who holds these keys.
Think of it like...
Think of a building with many rooms. Each room has a lock, and only people with the right keys can enter. Granting a privilege is like giving someone a key to a room; revoking is taking that key back.
┌───────────────┐
│   Snowflake   │
│   Account     │
└──────┬────────┘
       │
       ▼
┌───────────────┐       ┌───────────────┐
│     Roles     │◄──────│    Users      │
└──────┬────────┘       └───────────────┘
       │
       ▼
┌───────────────┐
│  Privileges   │
│ (Keys to data)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│   Objects     │
│(Databases,    │
│ Tables, etc.) │
└───────────────┘
Build-Up - 7 Steps
1
FoundationUnderstanding Snowflake privileges basics
🤔
Concept: Privileges are permissions that allow users or roles to perform specific actions on Snowflake objects.
In Snowflake, privileges control actions like SELECT (read data), INSERT (add data), CREATE (make new objects), and USAGE (use an object). These privileges are assigned to roles, which are then assigned to users. This separation helps manage permissions easily for many users.
Result
You know that privileges are the building blocks of access control in Snowflake and that they are assigned to roles, not directly to users.
Understanding that privileges are assigned to roles, not users, is key to managing access efficiently and securely.
2
FoundationRoles and their relationship to privileges
🤔
Concept: Roles group privileges and can be assigned to users or other roles to organize access control.
A role is like a job title that bundles privileges. For example, a 'data_analyst' role might have SELECT privileges on certain tables. Users get roles, so they inherit all privileges of those roles. Roles can also inherit from other roles, creating a hierarchy.
Result
You understand how roles simplify privilege management by grouping permissions and assigning them to users.
Knowing roles group privileges helps prevent managing permissions individually for each user, reducing errors and complexity.
3
IntermediateGranting privileges to roles
🤔Before reading on: do you think privileges are granted directly to users or to roles? Commit to your answer.
Concept: Privileges are granted to roles using the GRANT command, specifying the privilege, object, and role.
To give a role permission, use: GRANT ON TO ROLE ; Example: GRANT SELECT ON TABLE sales TO ROLE data_analyst; This means the data_analyst role can now read the sales table. Users with this role inherit this permission.
Result
The specified role gains the ability to perform the granted action on the object.
Understanding the syntax and effect of GRANT commands is essential to controlling access precisely and safely.
4
IntermediateRevoking privileges from roles
🤔Before reading on: does revoking a privilege remove it from users directly or from roles? Commit to your answer.
Concept: Privileges can be taken away from roles using the REVOKE command, which removes the permission on an object.
To remove a privilege, use: REVOKE ON FROM ROLE ; Example: REVOKE SELECT ON TABLE sales FROM ROLE data_analyst; This stops the data_analyst role from reading the sales table. Users lose this permission if they only had it through this role.
Result
The role no longer has the specified privilege on the object, restricting access.
Knowing how to revoke privileges helps maintain security by removing outdated or incorrect permissions.
5
IntermediateGranting and revoking role inheritance
🤔Before reading on: do you think roles can inherit privileges from other roles? Commit to your answer.
Concept: Roles can be granted to other roles, allowing inheritance of privileges and simplifying complex permission structures.
Use: GRANT ROLE TO ROLE ; Example: GRANT ROLE data_analyst TO ROLE senior_analyst; Now, senior_analyst inherits all privileges of data_analyst. To remove inheritance, use REVOKE ROLE similarly.
Result
Roles can build on each other, creating layered access control.
Role inheritance enables scalable and maintainable permission management in large organizations.
6
AdvancedUsing future grants for automatic privilege assignment
🤔Before reading on: do you think privileges granted on existing objects apply automatically to new objects? Commit to your answer.
Concept: Future grants allow privileges to be automatically assigned to roles for objects created later in a database or schema.
Syntax: GRANT ON FUTURE IN SCHEMA TO ROLE ; Example: GRANT SELECT ON FUTURE TABLES IN SCHEMA sales TO ROLE data_analyst; This means any new table created in sales schema will automatically grant SELECT to data_analyst role.
Result
Privileges are automatically applied to new objects, reducing manual work and errors.
Understanding future grants helps automate privilege management and keeps access consistent as data grows.
7
ExpertManaging privilege hierarchy and ownership conflicts
🤔Before reading on: do you think ownership of objects affects privilege granting? Commit to your answer.
Concept: Object ownership controls who can grant or revoke privileges on that object, and managing this hierarchy avoids conflicts and security risks.
Only the owner of an object or a role with the MANAGE GRANTS privilege can grant or revoke privileges on that object. Ownership can be transferred using ALTER commands. Mismanaging ownership can cause privilege conflicts or lockouts.
Result
Proper ownership management ensures secure and smooth privilege administration without accidental permission loss.
Knowing how ownership interacts with privileges prevents common security mistakes and access issues in production.
Under the Hood
Snowflake stores privileges as metadata linked to roles and objects. When a user tries an action, Snowflake checks the user's active roles and their privileges on the target object. This check happens in real-time, ensuring only authorized actions proceed. Role inheritance is resolved by combining privileges from all assigned roles and their parent roles. Ownership metadata controls who can change privileges, enforcing strict control.
Why designed this way?
This design separates users from privileges via roles to simplify management and improve security. It avoids assigning permissions directly to users, which is error-prone at scale. Role inheritance allows flexible, hierarchical permission structures. Ownership controls prevent unauthorized privilege changes. This model balances security, flexibility, and ease of use.
┌───────────────┐
│   User logs   │
│   in with     │
│   Roles       │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Privilege     │
│ Check Engine  │
│ (Checks all   │
│ roles assigned│
│ and inherited)│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Object Access │
│ Allowed or    │
│ Denied        │
└───────────────┘
Myth Busters - 4 Common Misconceptions
Quick: Does granting a privilege to a role automatically grant it to all users in the account? Commit to yes or no.
Common Belief:Granting a privilege to a role means every user in the account gets that privilege.
Tap to reveal reality
Reality:Only users assigned that specific role (or roles inheriting it) get the privilege, not all users.
Why it matters:Assuming all users get privileges can lead to overestimating security risks or misconfiguring access.
Quick: Can a user perform an action if they have the privilege granted to a role they do NOT have assigned? Commit to yes or no.
Common Belief:If a privilege exists on a role, any user in the system can perform that action.
Tap to reveal reality
Reality:Users must have the role assigned to inherit its privileges; otherwise, they cannot perform the action.
Why it matters:This misconception can cause confusion about why users cannot access resources despite privileges existing.
Quick: Does revoking a privilege from a role remove it from all roles that inherit from it? Commit to yes or no.
Common Belief:Revoking a privilege from a role automatically revokes it from all child roles inheriting it.
Tap to reveal reality
Reality:Revoking removes the privilege only from that role; child roles may still have the privilege if granted separately or inherited from other roles.
Why it matters:Misunderstanding this can cause unexpected access or security holes.
Quick: Can any user grant privileges on any object? Commit to yes or no.
Common Belief:Any user can grant privileges on objects they can access.
Tap to reveal reality
Reality:Only the object owner or users with MANAGE GRANTS privilege can grant or revoke privileges on that object.
Why it matters:Assuming otherwise can lead to unauthorized privilege escalation or security breaches.
Expert Zone
1
Granting the OWNERSHIP privilege is different from granting other privileges; it transfers full control and can override security boundaries.
2
Future grants do not retroactively apply to existing objects; they only affect objects created after the grant.
3
Role hierarchy can create complex privilege inheritance chains that require careful auditing to avoid privilege creep.
When NOT to use
Avoid granting privileges directly to users; always use roles for scalability and security. For temporary access, consider session-based role activation or time-limited roles instead of permanent grants.
Production Patterns
Use a layered role hierarchy separating duties (e.g., read-only, data editor, admin). Automate future grants for new objects to reduce manual errors. Regularly audit role assignments and ownership to maintain least privilege.
Connections
Role-Based Access Control (RBAC)
Granting and revoking privileges in Snowflake is a specific implementation of RBAC.
Understanding RBAC principles helps grasp why Snowflake uses roles to manage privileges instead of assigning them directly to users.
Physical Security Key Management
Both involve controlling access by distributing and revoking keys or permissions.
Knowing how physical keys control access to rooms helps understand digital privilege management as controlling access to data.
Organizational Hierarchies
Role inheritance mirrors organizational structures where managers inherit responsibilities from their teams.
Seeing role inheritance like job roles in a company clarifies how permissions flow and why hierarchy matters.
Common Pitfalls
#1Granting privileges directly to users instead of roles.
Wrong approach:GRANT SELECT ON TABLE sales TO USER alice;
Correct approach:GRANT SELECT ON TABLE sales TO ROLE data_analyst; GRANT ROLE data_analyst TO USER alice;
Root cause:Misunderstanding that privileges should be managed via roles for scalability and security.
#2Assuming future grants apply to existing objects.
Wrong approach:GRANT SELECT ON FUTURE TABLES IN SCHEMA sales TO ROLE data_analyst; -- expecting existing tables to be accessible
Correct approach:GRANT SELECT ON FUTURE TABLES IN SCHEMA sales TO ROLE data_analyst; GRANT SELECT ON TABLE sales.existing_table TO ROLE data_analyst;
Root cause:Not realizing future grants only affect objects created after the grant.
#3Revoking a privilege from a parent role expecting child roles to lose it automatically.
Wrong approach:REVOKE SELECT ON TABLE sales FROM ROLE data_analyst; -- expecting senior_analyst role to lose SELECT if it inherits data_analyst
Correct approach:REVOKE SELECT ON TABLE sales FROM ROLE data_analyst; REVOKE SELECT ON TABLE sales FROM ROLE senior_analyst;
Root cause:Misunderstanding that revoking affects only the specified role, not inherited roles.
Key Takeaways
Privileges in Snowflake control what actions roles can perform on objects, and users gain these privileges through assigned roles.
Granting and revoking privileges should always be done on roles, not directly on users, to maintain scalable and secure access control.
Role inheritance allows building complex permission structures but requires careful management to avoid unintended access.
Future grants automate privilege assignment for new objects but do not affect existing ones, so manual grants may still be needed.
Ownership of objects determines who can manage privileges, making it a critical part of secure privilege administration.