Archives for October 2024

Your company, ElfCrypt Solutions, recently received an email from Snowflake announcing the upcoming enforcement of Multi-Factor Authentication (MFA) by default for all new accounts, as part of the BCR Bundle 2024_08. This update aims to strengthen security, particularly for human users (TYPE=PERSON) logging in with Snowflake’s built-in password authentication.

In light of this change, ElfCrypt Solutions has decided to proactively implement a custom authentication policy that enforces MFA for sensitive users and the entire account. As the Snowflake Security Administrator, you’ll apply these security configurations and delegate policy management responsibilities to another team role.

Challenge steps:

• Define the Custom Authentication Policy: Create an MFA-enforced authentication policy called ‘enhanced_auth_policy‘.
• Apply the Policy Across the Account: Set enhanced_auth_policy as the default authentication policy for the entire Snowflake account to require MFA for all users.
• Enforce the Policy for a Sensitive User: Assign enhanced_auth_policy to a specific user, Alicia, who requires stricter security controls.
• Create a Policy Management Role: Set up a role named policy_manager and grant it the APPLY AUTHENTICATION POLICY privilege, so team members can manage authentication policies without full security access.
• Verify the Policy Manager Role: Use the policy_manager role to apply enhanced_auth_policy to another sensitive user, Marco, to confirm that the role permissions are working as intended.
• Optional Reset: Remove enhanced_auth_policy from Alice and Bob if needed, and delete the policy from the account to restore default settings.

Take on this challenge to be the Celebrimbor of custom authentication policies in Snowflake—secure your data, empower your team, and become the go-to expert in Snowflake security.

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.

We all have tools at our disposal that are very powerful but go unused for a long time. A tool that once we stop using it, we start losing our ability to understand how it worked.

The tool somewhere near the top of our list that follows that reference is Regular Expressions (or RegEx for short).
To give everyone the opportunity to stretch those muscles again , we’ve got 4 RegEx challenge for you to tackle!

Challenge 1: Validate Complex Email Addresses

Objective: Create a query that uses REGEXP_LIKE to validate complex email addresses.

Pattern Requirements:

• Must begin with alphanumeric characters (a-z, A-Z, 0-9).
• May include dots (.) or hyphens (-), but these cannot be at the start or end.
• The domain should have two parts separated by a dot: the domain name and a valid top-level domain (TLD).
• TLD must be 2 to 6 letters long.

CREATE TABLE users (
user_id INT AUTOINCREMENT ,
email VARCHAR
);

INSERT INTO users (email) VALUES
('john.doe@example.com'),
('invalid-email@.com'),
('alice_smith@sub-domain.co.uk'),
('bob@domain.com'),
('user@invalid_domain@com');

Challenge 2: Extract Valid Dates in Multiple Formats

Objective: Use REGEXP_SUBSTR to extract dates in different formats from a string column, assuming the dates follow one of these formats:

• DD/MM/YYYY
• MM-DD-YYYY
• YYYY.MM.DD

CREATE TABLE documents (
doc_id INT AUTOINCREMENT PRIMARY KEY,
text_column VARCHAR(500)
);

INSERT INTO documents (text_column) VALUES
('This document was created on 15/03/2023.'),
('The report is due by 04-15-2022.'),
('Version 1.0 released on 2021.08.30.'),
('No date provided in this text.'),
('Invalid date 32/13/2020.');

Challenge 3: Mask Credit Card Numbers

Objective: Use REGEXP_REPLACE to mask all but the last 4 digits of credit card numbers in a string. The credit card numbers can be in the format XXXX-XXXX-XXXX-XXXX or XXXXXXXXXXXX.

CREATE TABLE transactions (
transaction_id INT AUTOINCREMENT PRIMARY KEY,
card_number VARCHAR(50)
);
INSERT INTO transactions (card_number) VALUES
('1234-5678-9012-3456'),
('9876 5432 1098 7654'),
('1111222233334444'),
('4444-3333-2222-1111'),
('Invalid number 12345678901234567');

Challenge 4: Extract Hashtags from a Text Block

Objective: Use RegEx to extract all hashtags from a string, where hashtags:

• Begin with #
• Are followed by alphanumeric characters or underscores but no spaces.

CREATE TABLE social_posts (
post_id INT AUTOINCREMENT PRIMARY KEY,
text_column VARCHAR(500)
);
INSERT INTO social_posts (text_column) VALUES
('Check out our new product! #launch #excited'),
('Loving the weather today! #sunnyDay #relax'),
('Follow us at #example_page for more updates!'),
('No hashtags in this sentence.'),

Professor Frosty – as a scholar and a gentleman – is always very keen to advance learning and there’s no better way than to use some artificial intelligence provided by Snowflake!

In this S3 bucket we have a series of open access journals from the Oxford University Press.

Your job is to create a CORTEX Search service that will help our dear users identify the topics of these journals.

A test question could be “Which of these is about economics?” and return:

• The file name
• The title of the document (hint: EXTRACT_ANSWER() might help here)
• A pre-signed URL for the document

In a notebook, it will look something like:

(Note: accuracy may vary)