Frosty Friday

Snowflake Weekly Challenges

You are here: Home / Uncategorized / Week 22 – Basic

by on 8 Comments

Week 22 – Basic

The sales manager at Frosty Sales has no friends. Therefore, instead of giving each sales reps a list of cities, he decided to order them so that city-districts with an odd id belong to rep1, and even to rep2.

Your job is to create a secure view that will:

  • Ensure rep1 can only see city-districts with an odd id
  • Ensure rep2 can only see city-districts with an even id
  • The ‘id’ fields is actually a randomly generated id (so we can fulfil secure view best practice )
Start-Up Code

-- File format to read the CSV
create or replace file format frosty_csv
    type = csv
    field_delimiter = ','
    field_optionally_enclosed_by = '"'
    skip_header = 1;
    
-- Creates stage to read the CSV
create or replace stage w22_frosty_stage
  url = 's3://frostyfridaychallenges/challenge_22/'
  file_format = frosty_csv;
  
-- Roles needed for challenge
create role rep1;
create role rep2;

-- Grant roles to self for testing
grant role rep1 to user <INSERT_YOUR_USERNAME>;
grant role rep2 to user <INSERT_YOUR_USERNAME>;

-- Enable warehouse usage. Assumes that `public` has access to the warehouse
grant role public to role rep1;
grant role public to role rep2;

-- Create the table from the CSV in S3
create table <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.week22 as
select t.$1::int id, t.$2::varchar(50) city, t.$3::int district from @w22_frosty_stage (pattern=>'.*sales_areas.*') t;

-- Code for creating the secure view
<ENTER_CODE_HERE>
    
-- Roles need DB access
grant usage on database <YOUR_DB_NAME> to role rep1;
grant usage on database <YOUR_DB_NAME> to role rep2;
-- And schema access
grant usage on schema <YOUR_DB_NAME>.challenges to role rep1;
grant usage on schema <YOUR_DB_NAME>.challenges to role rep2;
-- And usage of view
grant select on view <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities to role rep1;
grant select on view <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities to role rep2;

-- Get the result of queries
use role rep1
select * from <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities;

use role rep2
select * from <YOUR_DB_NAME>.<YOUR_SCHEMA_NAME>.secure_cities;

RESULTS

When rep1 queries, they should see this:

When rep2 queries, they should see this:

Remember, if you want to participate:

  1. Sign up as a member of Frosty Friday. You can do this by clicking on the sidebar, and then going to ‘REGISTER‘
  2. Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
  3. Post the URL in the comments of the challenge

8 responses to “Week 22 – Basic”

  1. dsmdavid avatar
    dsmdavid

    went with a row access policy

    — the model
    https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_22_01.sql
    — init (including creation of the row access policy)
    https://github.com/dsmdavid/frostyfridays-sf/blob/main/macros/init_for_challenge_22.sql

    • mat avatar
      mat

      Thanks for another puzzle

      • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w22.sql
    • ChrisHastie avatar
      ChrisHastie

      Apologies for my tardiness, I was away for the weekend. To make up for it, my solution includes 4 different methods to achieve these requirements. I’ve also thrown in a column masking policy to provide the randomly generated ID field when the executing role does not have access to a specific role.

      • Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
    • Atzmonky avatar
      Atzmonky

      Added my ‘secure view’ approach: No policy this time, but with a mapping table.
      Was wondering though… Shouldn’t the ‘district id’ be checked for even/odd and not ‘id’ – not that it really matters .. 🙂

      • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ch22_rls_secure_views.sql
    • Jamie Laird avatar
      Jamie Laird

      Here’s my solution

      • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_22/ff_week_22.sql
    • zlzlzl2 avatar
      zlzlzl2

      Learning new functions on row level access policy, need to practice my grants better though.

      • Solution URL – https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF022_LZ.sql
    • Non-deterministicNorman avatar
      Non-deterministicNorman

      picked up a few things along the way 🙂

      • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2022%20-%20basic%20/RLS
    • gergana98 avatar
      gergana98

      This is my version of the solution for this task. I hope you find it helpful! ^^

      • Solution URL – https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2022%20%E2%80%93%20Basic

    Leave a Reply

    Reader Interactions

    Comments

    3. Apologies for my tardiness, I was away for the weekend. To make up for it, my solution includes 4 different methods to achieve these requirements. I’ve also thrown in a column masking policy to provide the randomly generated ID field when the executing role does not have access to a specific role.

      • Solution URL - https://github.com/ChrisHastieIW/Frosty-Friday

    4. Added my ‘secure view’ approach: No policy this time, but with a mapping table.
      Was wondering though… Shouldn’t the ‘district id’ be checked for even/odd and not ‘id’ – not that it really matters .. 🙂

      • Solution URL - https://github.com/Atzmonky/snowflake/blob/main/ch22_rls_secure_views.sql

    6. Learning new functions on row level access policy, need to practice my grants better though.

      • Solution URL - https://github.com/zlzlzl2-data/FrostyFriday/blob/main/FF022_LZ.sql

    7. picked up a few things along the way 🙂

      • Solution URL - https://github.com/NMangera/frosty_friday/blob/main/week%2022%20-%20basic%20/RLS

    8. This is my version of the solution for this task. I hope you find it helpful! ^^

      • Solution URL - https://github.com/GerganaAK/FrostyFridays/blob/main/Week%2022%20%E2%80%93%20Basic

    Leave a Reply