by
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:
- 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)
- Post your code to GitHub and make it publicly available (Check out our guide if you don’t know how to here)
- Post the URL in the comments of the challenge.
13 responses to “Week 9 – Intermediate”
-
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!
-
Reorganised my submission repo, new master solution URL here.
- Solution URL – https://github.com/ChrisHastieIW/Frosty-Friday
-
-
Thank you for the challenge.
My solution: https://github.com/mateusz-kmon/frostyfridaychallenges/blob/main/w09.sql -
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
-
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
-
Tried my hand at this too
- Solution URL – https://github.com/ankitsr92/Ankit_Work/blob/main/FrostyFriday/Week1/Week9.sql
-
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
-
I see other people use `SYSTEM$GET_TAG_ON_CURRENT_COLUMN`, which I think is better than what I did: create 2 tags 😉 Was a bit confused about what *role checking feature* meant, but otherwise nice challenge.
https://github.com/marioveld/frosty_friday/tree/main/ffw9
- Solution URL – https://github.com/marioveld/frosty_friday/tree/main/ffw9
-
🙂
- Solution URL – https://github.com/darylkit/Frosty_Friday/blob/main/Week%209%20-%20Tags%2C%20Masking%20Policies/tags_masking_policies.sql
-
A nice one! I’ve used current_role() only to retrieve the tag associate with the role, I hope this comply with the request (I’m actually checking the tag, not the role to choose if a column has to be masked)
- Solution URL – https://github.com/marco-scatassi-nimbus/Frosty-Friday/tree/main/week9
-
2パターンで回答しました。
２つ目は、問題でだめっていわれてたcurrent_role() を使ったものですｗ
——-
I answered in two patterns.
The second one used current_role() which was said to be problematic in the question, haha.- Solution URL – https://github.com/gakut12/Frosty-Friday/blob/main/week9_intermediate_tags_masking_policies/week9.sql
Leave a Reply
You must be logged in to post a comment.