Creating a Readonly Access User in Snowflake

Introduction :-

Remember the last time you had to share your favorite book with someone, but you wanted to make sure they don’t scribble on the pages? Giving someone readonly access to your Snowflake database is somewhat similar. It’s all about sharing valuable insights while keeping your data pristine and untouched.

Pre-requisites :-

Before we put on our snowshoes and start the ascent, there are a few things you need to have ready:

• A Snowflake account: Obviously, you’ll need access to Snowflake. If you’re not the owner, make sure you have the required permissions to create users and roles.

• Basic understanding of Snowflake roles: Snowflake manages access through roles. Knowing how roles work is like having a map in your pocket.

Procedure :-

Creating a readonly access user in Snowflake involves crafting a role with readonly permissions and then assigning this role to a user. Here’s a step-by-step guide to help you through:

Step 1: Create a New Role

First, we need to create a role that has readonly access. Think of this role as a special key that only opens certain doors (in our case, the ability to view data without altering it).

CREATE ROLE IF NOT EXISTS readonly_role;

This SQL command is like whispering a secret spell that brings a new role into existence.

Step 2: Grant Permissions to the Role

Now, it’s time to define what our readonly_role can do. We want this role to view (select) data but not modify it.

GRANT USAGE ON DATABASE my_database TO ROLE readonly_role;
GRANT SELECT ON ALL TABLES IN DATABASE my_database TO ROLE readonly_role;

These commands are akin to setting rules in a game, ensuring our player (the role) knows what moves are allowed.

Step 3: Create the User

With our role ready, it’s time to introduce a new character to our story — the readonly user.

CREATE USER readonly_user PASSWORD = 'Mahira@1234' MUST_CHANGE_PASSWORD = TRUE;

Choosing a strong, unique password is like locking your diary. Only the right person (in this case, our readonly user) should be able to open it.

Step 4: Grant the Role to the User

Finally, we need to give our readonly role to our newly created user. Think of this as handing over the keys to the castle, but only the rooms we want them to enter.

GRANT ROLE readonly_role TO USER readonly_user;

And with that, our readonly user is ready to explore the data, without the risk of accidentally toppling over a vase.

Conclusion :-

Congrats! You’ve just climbed the Snowflake mountain and successfully created a readonly access user. This new user now has the ability to view data without the temptation or risk of modifying it. Just like sharing your cherished book with a friend, knowing it will come back in perfect condition.