Share security and governance in Snowflake - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to grant usage on a share to a specific role.

Snowflake

GRANT USAGE ON SHARE [1] TO ROLE analyst_role;

Drag options to blanks, or click blank then click option'

Amy_schema

Bmy_database

Cmy_share

Dmy_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using a database or table name instead of the share name.

Forgetting to specify the role.

The USAGE privilege on a share allows the specified role to access the shared data. Here, my_share is the correct share name.

2fill in blank

medium

Complete the code to create a share with a comment describing its purpose.

Snowflake

CREATE SHARE [1] COMMENT = 'Share for sales data';

Drag options to blanks, or click blank then click option'

Asales_share

Bsales_db

Csales_schema

Dsales_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using database or schema names instead of a share name.

Omitting the comment or using invalid syntax.

The CREATE SHARE command creates a new share named sales_share. The comment helps document the share's purpose.

3fill in blank

hard

Fix the error in the code to revoke share usage from a role.

Snowflake

REVOKE USAGE ON SHARE [1] FROM ROLE data_scientist;

Drag options to blanks, or click blank then click option'

Adata_schema

Bdata_database

Cdata_table

Ddata_share

Attempts:

3 left

💡 Hint

Common Mistakes

Using database or schema names instead of share name.

Misspelling the share name.

The REVOKE USAGE ON SHARE command removes access for the role data_scientist on the share named data_share. Using a database or table name causes an error.

4fill in blank

hard

Fill both blanks to grant select privilege on a shared table to a role.

Snowflake

GRANT SELECT ON [1].[2] TO ROLE reporting_role;

Drag options to blanks, or click blank then click option'

Ashared_db

Bshared_schema

Cshared_table

Dshared_share

Attempts:

3 left

💡 Hint

Common Mistakes

Using share name instead of database or table name.

Confusing schema and table names.

The command grants SELECT privilege on the table shared_table inside the schema shared_schema to the role reporting_role. The database is not specified here, so the schema and table are used.

5fill in blank

hard

Fill all three blanks to create a share, add a database to it, and grant usage to a role.

Snowflake

CREATE SHARE [1];
ALTER SHARE [1] ADD DATABASE [2];
GRANT USAGE ON SHARE [1] TO ROLE [3];

Drag options to blanks, or click blank then click option'

Afinance_share

Bfinance_db

Cfinance_analyst_role

Dfinance_schema

Attempts:

3 left

💡 Hint

Common Mistakes

Using schema name instead of database name when adding to share.

Granting usage to a database or schema instead of a role.

Confusing share and database names.

This sequence creates a share named finance_share, adds the database finance_db to the share, and grants usage on the share to the role finance_analyst_role. The schema is not added directly to the share.

Practice

1. What is the main purpose of a share in Snowflake?

easy

A. To safely share data without copying or moving it

B. To create a backup of data in another region

C. To encrypt data before storage

D. To delete data from multiple accounts

Solution

1. Step 1: Understand the concept of a share

   A share in Snowflake allows data to be accessed by other accounts without physically moving or copying the data.

2. Step 2: Compare options with the concept

   The options describing encryption before storage, creating backups in another region, and deleting data from multiple accounts are unrelated to sharing data safely.

3. Final Answer:

   To safely share data without copying or moving it -> Option A

4. Quick Check:

   Share = Safe data access without moving [OK]

Hint: Shares let others see data without moving it [OK]

Common Mistakes:

• Thinking shares copy data to other accounts
• Confusing shares with backups
• Assuming shares encrypt data automatically

2. Which SQL command correctly grants usage on a share to a specific account in Snowflake?

easy

A. GRANT USAGE TO SHARE my_share ACCOUNT 'account_name';

B. GRANT SHARE ON USAGE my_share TO ACCOUNT 'account_name';

C. GRANT USAGE ON SHARE my_share TO ACCOUNT = 'account_name';

D. GRANT ACCOUNT USAGE ON SHARE my_share TO 'account_name';

Solution

1. Step 1: Recall correct GRANT syntax for shares

   The correct syntax is: GRANT USAGE ON SHARE <share_name> TO ACCOUNT = '<account_name>';

2. Step 2: Check each option's syntax

   Only GRANT USAGE ON SHARE my_share TO ACCOUNT = 'account_name'; matches the correct syntax. Others have incorrect order or missing keywords.

3. Final Answer:

   GRANT USAGE ON SHARE my_share TO ACCOUNT = 'account_name'; -> Option C

4. Quick Check:

   GRANT USAGE ON SHARE ... TO ACCOUNT = ... [OK]

Hint: GRANT USAGE ON SHARE ... TO ACCOUNT = 'name' [OK]

Common Mistakes:

• Swapping keywords order
• Omitting the equal sign after ACCOUNT
• Using incorrect object types in GRANT

3. Given the following SQL commands, what will be the result when account 'A' tries to query the shared table?

CREATE SHARE my_share;
GRANT USAGE ON DATABASE my_db TO SHARE my_share;
GRANT SELECT ON TABLE my_db.public.my_table TO SHARE my_share;
GRANT USAGE ON SHARE my_share TO ACCOUNT = 'A';

medium

A. Account 'A' cannot query because database usage is not granted

B. Account 'A' can query my_table successfully

C. Account 'A' cannot query because SELECT privilege is missing

D. Account 'A' cannot query because share usage is not granted

Solution

1. Step 1: Check granted privileges on share

   The share has USAGE on the database and SELECT on the table granted, and account 'A' has USAGE on the share.

2. Step 2: Confirm account 'A' can access shared data

   With these grants, account 'A' can query the shared table successfully.

3. Final Answer:

   Account 'A' can query my_table successfully -> Option B

4. Quick Check:

   Proper grants = Access allowed [OK]

Hint: Grant database usage, table select, and share usage to account [OK]

Common Mistakes:

• Forgetting to grant USAGE on database to share
• Missing SELECT privilege on table
• Not granting USAGE on share to account

4. You receive an error when trying to grant SELECT on a table to a share: SQL compilation error: Insufficient privileges. What is the most likely cause?

medium

A. You forgot to create the database

B. The share does not exist

C. The account name is misspelled

D. You do not have OWNERSHIP privilege on the table

Solution

1. Step 1: Understand privilege requirements for granting SELECT to share

   Granting SELECT on a table to a share requires OWNERSHIP or appropriate privileges on that table.

2. Step 2: Analyze error cause

   Insufficient privileges error usually means the user lacks OWNERSHIP or necessary rights on the table.

3. Final Answer:

   You do not have OWNERSHIP privilege on the table -> Option D

4. Quick Check:

   OWNERSHIP needed to grant SELECT to share [OK]

Hint: OWNERSHIP needed to grant privileges on objects [OK]

Common Mistakes:

• Assuming share existence causes privilege errors
• Confusing account name typos with privilege errors
• Ignoring ownership requirements

5. You want to share a subset of data securely with multiple external accounts but restrict access to only certain tables and columns. Which Snowflake feature combination should you use?

hard

A. Create a share with specific tables and use secure views to limit columns, then grant share usage to accounts

B. Create a share with all tables and rely on account-level access control

C. Copy data to each account and grant full access

D. Use database replication to share data and restrict columns via masking policies

Solution

1. Step 1: Identify how to limit tables and columns in shares

   Shares can include specific tables only. To restrict columns, secure views can be created exposing only allowed columns.

2. Step 2: Confirm how to share with multiple accounts securely

   Granting USAGE on the share to multiple accounts allows controlled access without copying data.

3. Final Answer:

   Create a share with specific tables and use secure views to limit columns, then grant share usage to accounts -> Option A

4. Quick Check:

   Shares + secure views = controlled multi-account access [OK]

Hint: Use secure views inside shares to limit columns [OK]

Common Mistakes:

• Sharing all tables without column restrictions
• Copying data instead of sharing
• Using replication which is for disaster recovery, not sharing