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.
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
Reorganised my submission repo, new master solution URL here.
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)
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…
Tried my hand at this too
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.
Here’s my result 🙂
https://github.com/CSimonini/Frosty_Friday/blob/main/W09_Int_Tags_Masking%20Policies.sql