Frosty Friday

Snowflake Weekly Challenges

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

by on 6 Comments

Week 32 – Basic

This week we’re looking into some new security features that Snowflake has recently released

As you might know, the default idle time for Snowflake sessions is 4 hours BUT that was recently adjusted; you can now change this value on certain levels.

Because we can finetune these numbers, Management has asked us to enforce it for some of our new colleagues :

The challenge for this week :
– Create 2 users
– User 1 should have a max idle time of 8 minutes in the old Snowflake UI
– User 2 should have a max idle time of 10 minutes in SnowSQL and in their communication with Snowflake from tools like Tableau

One of the things we’d be looking for looks like 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

6 responses to “Week 32 – Basic”

  1. mat avatar
    mat

    My solutions for weeks 24 – 32 🙂

    • Solution URL – https://github.com/mateusz-kmon/frostyfridaychallenges
  2. Jamie Laird avatar
    Jamie Laird

    Here’s my solution

    • Solution URL – https://github.com/apd-jlaird/frosty-friday/blob/main/week_32/ff_week_32.sql
  3. dsmdavid avatar
    dsmdavid

    Two step approach:
    1. Create a dbt macro that queries the Session_Policies object in the snowflake share. https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_32.sql
    2. Create a macro that is run in the pre_hook of (init_for_challenge) and another that run as the post-hook (clean_up)

    It will take some time, but policies will be shown in the view from the share.

    • Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/macros/init_for_challenge_32.sql
  4. ChrisHastie avatar
    ChrisHastie

    My quick solution

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

    A nice feature to know of..

    • Solution URL – https://github.com/Atzmonky/snowflake/blob/main/ffch32_secure_session.sql
  6. Non-deterministicNorman avatar
    Non-deterministicNorman

    added a comment to the session policy just for giggles

    • Solution URL – https://github.com/NMangera/frosty_friday/blob/main/week%2032%20-%20basic%20/Session%20Security

Leave a Reply

Reader Interactions

Comments

  3. Two step approach:
    1. Create a dbt macro that queries the Session_Policies object in the snowflake share. https://github.com/dsmdavid/frostyfridays-sf/blob/main/models/challenge_32.sql
    2. Create a macro that is run in the pre_hook of (init_for_challenge) and another that run as the post-hook (clean_up)

    It will take some time, but policies will be shown in the view from the share.

    • Solution URL - https://github.com/dsmdavid/frostyfridays-sf/blob/main/macros/init_for_challenge_32.sql

  6. added a comment to the session policy just for giggles

    • Solution URL - https://github.com/NMangera/frosty_friday/blob/main/week%2032%20-%20basic%20/Session%20Security

Leave a Reply