Week 9 – Intermediate



STORY
It’s not just bad guys that need to guard their secrets!
Superheroes are our first line of defence against those evil-doers so we really need to protect their information.
Running a superhero organisation however is a big job so we’ve got a lot of people that have access to our systems and we need to make sure that the true identity of our heroes is never revealed!
HR is advocating for a more personal touch to our business though and has requested that some staff should be able to see the first names of the superheroes to connect on a more basic level. Higher ups should still be able to see everything !
With the constant changing roles within the organisation , we’d really like something that’s dynamic and can handle roles that haven’t been created yet.

CHALLENGE

With the use of Tags and Masking , we want to mask the first_name and last_name columns from our data_to_be_masked table.
We want the following :

  • The default user that has access can only see the hero_name data unmasked
  • Role foo1 can only see hero_name and first_name
  • Role foo2 can see the contents of the whole table
  • The used masking policy should NOT use a role checking feature. (current_role = … etc.)

START UP CODE

--CREATE DATA
CREATE OR REPLACE TABLE data_to_be_masked(first_name varchar, last_name varchar,hero_name varchar);
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Eveleen', 'Danzelman','The Quiet Antman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Harlie', 'Filipowicz','The Yellow Vulture');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Mozes', 'McWhin','The Broken Shaman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Horatio', 'Hamshere','The Quiet Charmer');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Julianna', 'Pellington','Professor Ancient Spectacle');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Grenville', 'Southouse','Fire Wonder');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Analise', 'Beards','Purple Fighter');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Darnell', 'Bims','Mister Majestic Mothman');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Micky', 'Shillan','Switcher');
INSERT INTO data_to_be_masked (first_name, last_name, hero_name) VALUES ('Ware', 'Ledstone','Optimo');

--CREATE ROLE
CREATE ROLE foo1;
CREATE ROLE foo2;
GRANT ROLE foo1 TO USER <ENTER USERNAME HERE>;
GRANT ROLE foo2 TO USER <ENTER USERNAME HERE>;

RESULTS

–Can you see the data?

USE ROLE ACCOUNTADMIN;
SELECT * FROM data_to_be_masked;

–What can foo1 see?

USE ROLE foo1;


SELECT * FROM data_to_be_masked;

–And foo2?

USE ROLE foo2;


SELECT * FROM data_to_be_masked;

ADMIN NOTE :
This challenge has been clarified on 30-09-2022 and the last bullet point for the challenge was added.
Any solutions given before this time might not be a total solution to the question.

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‘ (note joining our mailing list does not give you a Frosty Friday account)
  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 9 – Intermediate”

  1. ChrisHastie avatar
    ChrisHastie

    Another week, another fun challenge. Please excuse the use of so many variables in my solution, I couldn’t decide on object names for a while and lost patience with changing the names everywhere each time!

    Solution URL:
    https://github.com/ChrisHastieIW/Frosty-Friday/blob/main/Week%209%20-%20Intermediate%20-%20Tags%2C%20Masking%20Policies/Week%209%20-%20Intermediate%20-%20Tags%2C%20Masking%20Policies.sql

      1. ChrisHastie avatar
        ChrisHastie

        Reorganised my submission repo, new master solution URL here.

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

      Still using dbt… it’s becoming a bit of a stretch :sweat:

      – Create a pre-hook to set up environment, table, roles, tags, masking policies, etc.
      – Add the table created in the pre-hook as a source.
      – Alter the table to set a tag in each of the relevant columns.
      – Alter the tags to set a masking policy for each.
      – Create 3 tables querying the table as…
      – role_01,
      – role_02,
      – regular role not present in masking policy
      – Create a final table showing the union of the 3 tables above, as well as the role used to query them (using roles for each table & thread may be a bit cumbersome)

      • Solution URL – https://github.com/dsmdavid/frostyfridays-sf/blob/main/macros/init_for_challenge_09.sql
    • Pekka Kanerva avatar
      Pekka Kanerva

      Learned new function because you told not to use the current_role-function (The used masking policy should NOT use a role checking feature. (current_role = … etc.) )
      Or if you mean by “role checking feature” also the other function, then I don’t know how this should have been done…

      • Solution URL – https://github.com/pekka-kanerva/frosty-friday/blob/main/week9/ff-week9-solution.sql
    • 2018.ankitsr avatar
      2018.ankitsr

      Tried my hand at this too

      • Solution URL – https://github.com/ankitsr92/Ankit_Work/blob/main/FrostyFriday/Week1/Week9.sql
    • jameskalfox avatar
      jameskalfox

      Like previous comments, couldn’t figure this out without using a role checking feature (current_role, is_role_in_session), but otherwise a fun solve.

      • Solution URL – https://github.com/jameskalfox/frosty-friday-snowflake-challenges/blob/main/Week_09.sql

    Leave a Reply