Access Control in Snowflake : Enhancing Data Security

In the era of data-driven decision making, securing access to data is paramount. Snowflake, a powerful data cloud platform, offers robust access control mechanisms to ensure that data remains secure, compliant, and accessible to the right people.

If you're pursuing the SnowPro Core Certification, understanding access control in Snowflake is critical.

This article delves into the essentials of access control in Snowflake, providing insights to help you ace your certification and bolster your data security practices.

Understanding Snowflake's Access Control Framework

Snowflake's access control framework is pivotal in ensuring data security and integrity within the platform.

It combines elements of Discretionary Access Control (DAC) and Role-based Access Control (RBAC) models to provide a robust system for managing access to data and resources.

• Discretionary Access Control (DAC): Each object has an owner, who can in turn grant access to that object.
• Role-based Access Control (RBAC): Access privileges are assigned to roles, which are in turn assigned to users.

Let's delve into the key concepts:

1. Securable Object: Every entity in Snowflake is a securable object to which access can be granted or denied. These objects, such as tables, views, functions, and stages, reside within a hierarchical structure.
2. Role: Roles are entities to which privileges can be assigned, and users are granted these roles. Roles can also be assigned to other roles, forming a role hierarchy.
3. Privilege: Privileges define the level of access granted to a securable object. Snowflake offers a range of privileges, allowing fine-grained control over data access.
4. User: Users are recognized identities within Snowflake, associated with either individuals or programs.

1. Securable Object

In Snowflake, every entity is considered a securable object, meaning access to it can be either granted or denied.

These objects encompass a wide range of data elements, including tables, views, functions, and stages.

They are organized within a hierarchical structure, with higher-level containers such as databases containing lower-level objects like tables and views.

Ownership of an object in Snowflake means that a role holds the OWNERSHIP privilege for that object. Each object is owned by a single role, typically the role that created it.

When users are assigned this role, they share control over the object. Ownership can be transferred between roles using the GRANT OWNERSHIP command.

• Regular Schema: The owner role has all privileges on the object, including granting and revoking access to other roles. Ownership can be transferred to other roles.
• Managed Access Schema: Object owners cannot grant privileges. Only the schema owner or roles with the MANAGE GRANTS privilege can grant access.

2. Role

Roles serve as entities to which privileges are assigned. Users are then granted these roles to define their level of access within Snowflake.

Roles play a crucial role in access management, allowing administrators to group users based on their responsibilities or functional requirements.

Additionally, roles can be assigned to other roles, creating a role hierarchy that simplifies access control administration.

Snowflake offers various role types, including:

• Account Roles: For broad access across the entire account.
• Database Roles: For access limited to a single database.
• Instance Roles: For access to instances of a class.
• Application Roles: Enabling access to Snowflake Native Apps.
• Service Roles: Granting access to service endpoints.

System-defined Roles

1. ORGADMIN (Organization Administrator)

- Manages operations at the organization level.

- Can create and view accounts in the organization.

- Can view usage information across the organization.

2. ACCOUNTADMIN (Account Administrator)

- Encapsulates SYSADMIN and SECURITYADMIN roles.

- Top-level role, should be granted to a limited number of users.

3. SECURITYADMIN (Security Administrator)

- Manages object grants globally.

- Can create, monitor, and manage users and roles.

- Has the MANAGE GRANTS privilege.

4. USERADMIN (User and Role Administrator)

- Dedicated to user and role management.

- Can create users and roles.

- Manages users and roles it owns or has ownership over.

5. SYSADMIN (System Administrator)

- Creates warehouses, databases, and other objects.

- Can grant privileges on these objects to other roles.

6. PUBLIC

- Pseudo-role granted to every user and role in the account.

- Owns securable objects accessible to all users and roles.

Custom Roles

• Custom account roles can be created by USERADMIN or higher roles.
• Custom database roles can be created by the database owner.
• Newly created roles are not assigned to any user or other roles by default.
• Ensures system administrators manage all objects, while USERADMIN manages users and roles.
• Roles not in SYSADMIN hierarchy cannot be managed by system administrators, except those with MANAGE GRANTS privilege (default is SECURITYADMIN).

Recommended to create a hierarchy of custom roles, with the top-most custom role assigned to SYSADMIN.

3. Privilege

Privileges determine the level of access granted to securable objects within Snowflake.

These privileges range from basic read and write permissions to more granular controls over specific actions like creating or deleting objects.

Common privileges include:

• SELECT: Grants read access to data.
• INSERT: Allows inserting new data.
• UPDATE: Enables updating existing data.
• DELETE: Permits deleting data.

Snowflake offers a comprehensive set of privileges, enabling administrators to tailor access permissions to the precise needs of users and roles.

Refer to this snowflake documentation for deep dive??.

4. User

Users are recognized identities within Snowflake, representing either individuals or programs accessing the platform.

Each user is associated with specific roles that define their access privileges.

Users play a crucial role in Snowflake's access control framework, as they are the entities that interact with securable objects within the platform.

Practical Example: Setting Up Access Control in Snowflake

Grab a trial account of Snowflake, your default role will be ACCOUNTADMIN by default.

1. Creating Roles and Users

-- Create roles
CREATE ROLE data_analyst;
CREATE ROLE data_engineer;

-- Create users
CREATE USER alice PASSWORD='StrongPassword123';
CREATE USER bob PASSWORD='AnotherStrongPassword456';
CREATE USER sid PASSWORD='AnotherStrongPassword';

-- Assign roles to users
GRANT ROLE data_analyst TO USER alice;
GRANT ROLE data_engineer TO USER bob;        

2. Assigning Privileges to Roles

-- Granting privileges to roles
GRANT USAGE ON DATABASE TRAINING TO ROLE data_analyst;
GRANT MODIFY ON DATABASE TRAINING TO ROLE data_engineer;        

3. Creating Role Hierarchies

-- Create a parent role
CREATE ROLE data_team;

-- Grant child roles to the parent role
GRANT ROLE data_analyst TO ROLE data_team;
GRANT ROLE data_engineer TO ROLE data_team;

-- Assign parent role to a user
GRANT ROLE data_team TO USER sid;        

4. Hierarchy of custom roles, with the top-most custom role assigned to SYSADMIN.

-- Grant child roles to the SYSADMIN role
GRANT ROLE data_team TO ROLE SYSADMIN;        

After the execution of the above code, you can logout from current user and try logging in as another user (any out of three users created in starting of the code snippet) to test and check how the Access control functionality works in Snowflake.

Above code snippets can be found on my Github ??.

Conclusion

Access control forms the backbone of data security in Snowflake, ensuring that sensitive data is safeguarded while enabling efficient data utilization.

By leveraging Snowflake's robust access control features and adhering to best practices, organizations can fortify their data ecosystem against potential threats, ensuring compliance and data integrity.

Understanding these concepts is not only essential for passing the SnowPro Core Certification but also for maintaining a robust and secure data infrastructure.

Feel free to follow me Sudeep Kumar ? for more insights and tips on mastering Snowflake and other data engineering tools!

To Your Transformation??

Sudeep Kumar

Azure Certified Data Engineering Professional | Data Engineering Career Mentor & Coach

References:-

https://docs.snowflake.com/en/user-guide/security-access-control-overview

https://docs.snowflake.com/en/user-guide/security-access-control-privileges#all-privileges-alphabetical