Over view:-

In the ever-evolving landscape of cloud data warehousing, Snowflake stands out as a powerful platform offering elasticity, speed, and flexibility. As organizations harness the potential of Snowflake to manage and analyze their data, ensuring robust security measures becomes paramount. In this guide, we’ll walk through the process of creating a read-only user in Snowflake and implementing a granular access control strategy. By doing so, you not only enhance the security posture of your Snowflake instance but also enable efficient collaboration without compromising sensitive data.

Pre-requestites :-

• Snowflake account with admin access.

Procedure :-

Step-1:- Login to your snowflake account using admin credentials.

Step-2:- After logging into the account, create a worksheet. within the worksheet copy and paste the below queries

// create role for user
CREATE ROLE read_only;

//create user
CREATE USER mahiratechnology PASSWORD = 'Mahira@123' DEFAULT_ROLE = read_only 
MUST_CHANGE_PASSWORD = TRUE;

GRANT ROLE read_only TO USER mahiratechnology;

USE DATABASE MAHIRA_DB;
GRANT USAGE ON DATABASE MAHIRA_DB TO ROLE read_only;
GRANT SELECT ON ALL TABLES IN SCHEMA PUBLIC TO ROLE read_only;
GRANT USAGE ON SCHEMA PUBLIC TO ROLE read_only;
GRANT SELECT ON ALL VIEWS IN SCHEMA PUBLIC TO ROLE read_only;

Step-3:- Run the above queries to create a readonly role and assign it to a user with secific database permissions.

Step-4:- Once the statements or queries is successfully exicuted, check with the user whether the he/she is able to access to db with specific permissions.(Below is the user created with read_only role and limited permissions on db).

Conclusion:-

As data becomes an invaluable asset for organizations, securing it is non-negotiable. By creating a read-only user in Snowflake with carefully curated access, you strike a balance between collaboration and security. This guide aims to empower Snowflake users with the knowledge and tools to implement a robust access control strategy. Remember, it’s not just about restricting access but about enabling a secure, collaborative data environment. Embrace the power of Snowflake while safeguarding your data assets.