By
What is Changing?
Snowflake recently sent an email to their customers regarding new default security policies that will start to be rolled out and become default shortly. This is all in response to a breach earlier this year, amplifying the importance of introducing these changes. If you create a new account, these will automatically be in place; however, if you have an existing account, you can get in front of these new policies and enable them yourself.
New User Types
Three different types are now available: PERSON, SERVICE, and LEGACY_SERVICE. I won't get into the legacy type since you hopefully won't need that. Still, the other two now let you differentiate policies based on whether it's a person or a system authenticating to Snowflake. Snowflake notes that you should explicitly set one of these values for all new users created.
MFA Enforced for all Human + Password Users
Multi-factor authentication, or MFA, will now be required on any user whose type is PERSON and whose authentication method is PASSWORD. This doesn't affect accounts that have third-party SSO like Okta or AzureAD. You can use the built-in DUO MFA support for accounts without a third-party SSO provider.
Service Accounts with RSA Key Pairs + Network Policies
Service accounts are used when you have another tool or system communicating with Snowflake. This can be an ELT tool like Fivetran or a tool that helps you manage your SQL Transforms like dbt. Service accounts are a special case where you can limit access to a very small data in your system and very specific IP address access points.
Service Accounts
Let's start by securing all of the service accounts. I have five: Fivetran, Census, dbt, Prefect, and Streamlit. Each of these performs specific tasks, and each of these supports Key Pair authentication. Setting this up was fairly straightforward. Here is what I did.
Generating RSA Tokens
The first step is to generate two matching tokens, a Private Key and a Public Key that's based on the private key. This is a super simple process. I created a separate key pair for each service account and stored them in my Password Manager.
Generate a Private Key
Open a Terminal window and paste in this command, and run it. We're going to create these as Unencrypted keys. Most services will also support encrypted keys, but this method is simpler. Note that I named the key for the service, suffixing it with _fivetran.
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key_fivetran.p8 -nocrypt
If you then inspect the file that it created, you should see contents that look like this:
-----BEGIN PRIVATE KEY-----
MIIEvgIBADANBgkqhkiG9wPykVx
...
NXIkLHI0e3RWT7vPQqhDCERzQs8
-----END PRIVATE KEY-----
Generate the Matching Public Key
Next, you need a matching Public Key. This is done with the following command referencing the .p8 file you created and outputting a .pub file with the new Public Key.
openssl rsa -in rsa_key_fivetran.p8 -pubout -out rsa_key_fivetran.pub
Programmatic Access Policy
Before creating our users, we can create a policy that will ensure that your service accounts can only use a Key Pair (or OAuth if you have it) and not a standard PW. It also specifies that it will use one of the programmatic access methods and not a client-like web interface.
CREATE AUTHENTICATION POLICY programmatic_access_user_auth
CLIENT_TYPES = ('DRIVERS', 'SNOWSQL')
AUTHENTICATION_METHODS = ('OAUTH', 'KEYPAIR');
Limiting Network Access
The next thing we want to do with service accounts is to restrict the IP addresses from which they can operate. Between using RSA key pairs and a Network Policy, you have a robust security setup for them.
You will need the list of IP addresses from which the service operates. Typically, the service you're using will let you know their IP addresses as you are configuring your access to Snowflake, or you can do a quick search; you probably will find a page similar to Fivetran's. or dbt's.
The first step is to create Network Rules, and then you associate that rule with a new Network Policy.
CREATE OR REPLACE NETWORK RULE prog_net_public
TYPE = IPV4 VALUE_LIST = (
'3.220.140.57',
'54.81.195.173',
'35.234.176.144/29',
) MODE = INGRESS;
CREATE NETWORK POLICY prog_net_public
ALLOWED_NETWORK_RULE_LIST = ('prog_net_public');
Creating Service Accounts
We can re-create the user account and set all the above policies at the creation time. Here, you can see a string set for RSA_PUBLIC_KEY, which you can find in the .pub file you created above. It will also set the Authentication and Network Policies created above. And, of course, the TYPE is set to SERVICE.
CREATE OR REPLACE USER fivetran
RSA_PUBLIC_KEY = 'MIIBIjANBgkqhk....<< ~400 character string >>'
DEFAULT_ROLE = 'FIVETRAN'
DEFAULT_WAREHOUSE = 'FIVETRAN'
AUTHENTICATION POLICY programmatic_access_user_auth
NETWORK_POLICY prog_net_public
COMMENT = 'Fivetran Service Account'
TYPE = 'SERVICE';
Setting the Application's Private Key
Finally, tell your application to use Key Pair authentication and set the Private Key from the .p8 file created above.
One tip I have here is when you paste in your private key, please make sure to do so with the header (-----BEGIN PRIVATE KEY-----) and footer, and all of the line breaks exactly like it says in your terminal window. The following screenshot is from dbt.
Note: Test all your connections after you've set them up with the new authentication methods and network policies.
Adding MFA for Human Accounts
Now that our service accounts are secure let's ensure our humans are also. This is a critical step needed to secure the users with the most access to your data!
Note: I should also be setting a network policy for the human users. However, in my situation, I don't have a fixed IP address that I can trust to log in with all the time. Because of that, I'm skipping that step. However, I recommend you do it if you have the ability.
MFA Policy
This one is slightly less involved. We will create a policy and set it globally on the account so that all new PERSON-type users will be forced to enable MFA.
CREATE OR REPLACE AUTHENTICATION POLICY human_access_mfa
AUTHENTICATION_METHODS = ('PASSWORD', 'SAML')
MFA_AUTHENTICATION_METHODS = ('PASSWORD')
MFA_ENROLLMENT = 'REQUIRED';
ALTER ACCOUNT SET AUTHENTICATION POLICY human_access_mfa;
Note: You can enable DUO on your account by navigating to your User Profile (lower left corner) and following the prompts to install and configure DUO.
Other Security Policies
Snowflake also provides several other recommendations to help secure your account. Here are a couple of good ones that I also implemented.
Password Policy
This is a must for me. Don't let users have weak passwords. Set up something that fits your needs by modifying the parameters below.
CREATE OR REPLACE PASSWORD POLICY password_policy_account
PASSWORD_MIN_LENGTH = 21
PASSWORD_MIN_UPPER_CASE_CHARS = 3
PASSWORD_MIN_LOWER_CASE_CHARS = 3
PASSWORD_MIN_NUMERIC_CHARS = 3
PASSWORD_MIN_SPECIAL_CHARS = 3
PASSWORD_LOCKOUT_TIME_MINS = 30
PASSWORD_HISTORY = 24;
ALTER ACCOUNT SET PASSWORD POLICY password_policy_account;
Session Policy
A session policy will ensure that users are logged out of their sessions when left unattended. I created two for mine. One is for general users and service accounts (SESSION_IDLE_TIMEOUT_MINS applies to service accounts), and one is for administrators. The recommendation by Snowflake is that all admins time out after 15 minutes, regardless of the client they're using.
CREATE OR REPLACE SESSION POLICY session_policy_account
SESSION_IDLE_TIMEOUT_MINS = 240
SESSION_UI_IDLE_TIMEOUT_MINS = 15
COMMENT = 'DKA Session Policy';
CREATE SESSION POLICY session_policy_admin
SESSION_IDLE_TIMEOUT_MINS = 15,
SESSION_UI_IDLE_TIMEOUT_MINS = 15;
-- Setting account-wide, and on a specific user
ALTER ACCOUNT SET SESSION POLICY session_policy_account;
ALTER USER brian SET SESSION POLICY session_policy_admin;
Trust Center
Finally, I want to point out Snowflake's great addition to their security posture. They have a new trust center that allows you to scan your environment, and it will make recommendations based on their best practices. By default, a simple scanner is on, but there is a much more robust scanner called CIS Benchmarks, which states:
The CIS Snowflake Foundations Benchmark is a set of industry-recognized best practices and security configurations intended to keep Snowflake accounts secure. All CIS Benchmarks focus on technical configuration settings used to maintain and increase the security of the addressed technology. They should be used with other essential cyber hygiene tasks...
Make sure to enable this and check out their recommendations.
Conclusion
In conclusion, implementing Snowflake's new security policies is crucial for safeguarding your data and maintaining a secure environment. You can significantly enhance your account's protection by differentiating user types, enforcing MFA, securing service accounts with RSA keys and network policies, and applying stringent password and session policies. Additionally, leveraging Snowflake's Trust Center and CIS Benchmarks ensures you're following best practices for security.
