Photo by Author
I imagine myself as a grumpy DBA some ten years previous and getting the task of assessing security on a database with the following JDBC URL:
jdbc:snowflake://mycompany.snowflakecomputing.com/?443?warehouse=COMPUTE_WH"¡No me digas!" No way, no how, are we going to publish our production database through the web!
Coming from the world of hardened internet gateways, firewalls, DMZ subnets, application subnets, and database subnets, it's quite a shock to see the database endpoint open to the world on port 443 (HTTPS). It's even more shocking that the database provider adopting this strategy is one of the hottest new databases on the market.
Right then -- with my paranoid mind reeling -- I've got to figure out how on earth to secure this endpoint.
Step 1: Create a Role Hierarchy Based on Least Privilege
The Principle of Least Privilege guides me in how I set up roles. Snowflake provides you with several default roles, but any Grumpy DBA will want to restrict roles to least privilege based on specific business rules.
For Snowflake, the Admin Starting point can be a specific database, a specific warehouse, and full access to a specific schema.
See an example SQL template to create a database Admin role.
To further ratchet up the security, create the ADMIN schema in the database with Managed access.
Step 2: Build Operations Processes for Users and Roles
In an empty database, users can gather the roles they need, but on an active database, the Grumpy DBA wants to start with user audits to see what ROLES people have. Again, the goal is: Ensure each user has the least privilege access.
Snowflake doesn't have a native user table. The "show users" is a privileged command that requires the powerful MANAGE GRANTS global privilege. That won't do for a Grumpy DBA like me. I want finer-grained permission on the USER table, and I want to query the table in SQL statements.
My preferred method of creating a base user table is using stored procedures and scheduled tasks to maintain a USER table with READ-ONLY permissions on an Application Administrator account.
Step 3: Lockdown Network Access
Users fall into one of. two categories: People and Service users. Security issues happen when the line between the two is blurred. A person needs to be a single individual. A service needs to be a specific service from a fixed machine. People who share service accounts are setting the database up to be hacked.
Setup tables to track network policies and visualize the data through Metabase.
Create Table to Capture the Network Policy
Create a Stored Procedure to Copy Snowflake Metadata to Table
Schedule Snowflake Task to keep the Table up to Date
By creating a table to an admin schema, you can set a service user in read-only mode to read network information and provide alerts on security issues.
The Raw Data
The Default Network Policy Captured by Monitoring Software and Visualized in Metabase shows no restrictions on user or service accounts. We will want to alert on these conditions because it means:
Service accounts can connect from anywhere
Users can share passwords because their identity is not tied to a single device
Get the IP address and whitelist that address.
select *
from table(information_schema.login_history_by_user('AZRIUS', result_limit=>1000))
order by event_timestamp;Set the network policy at the user level to enforce single origin for service users.
alter user AZRIUS set network_policy = whitelist_policy;Step 3a - Require Two Factor Authentication for all Person Users
Person accounts can be secured through Multi-Factor Authentication. Users can enroll in MFA by selecting "Preferences" and then "Enroll in MFA."
We can query users who are not enrolled in MFA by excluding all the service users with whitelist policies, then checking their 2nd-factor authentication.
with start_mfa as (
select user_name,
max(event_timestamp) event_timestamp
from admin.SNOWFLAKE_LOGIN_HISTORY lh
inner join admin.SNOWFLAKE_USER_NETWORK_POLICY np
on lh.user_name = np.name
where len(np.value) = 0
group by lh.user_name
)
select logins.USER_NAME, logins.event_timestamp, logins.SECOND_AUTHENTICATION_FACTOR
from admin.SNOWFLAKE_LOGIN_HISTORY logins
inner join start_mfa on logins.user_name = start_mfa.user_name
where logins.event_timestamp >= start_mfa.event_timestamp
order by logins.event_timestamp;Conclusion
As a Grumpy DBA, I am satisfied that I can lock down my snowflake instance so that service accounts can only connect from whitelisted IP addresses, and I can alert on accounts that don't use MFA. Of course, being grumpy, I'll lock them out and wait for them to come whining about access and then give them a patronizing and condescending lecture on security.
Just kidding, I'm not really that grumpy. But the code to secure Snowflake and set up the queries is here: