Snowflake Security — Service Accounts

Snowflake Security — Service Accounts

Learn about how designate User Accounts as Services Accounts for an Improved Security Posture

Photo by Kutan Ural on Unsplash

Recently Snowflake a made a change so that User Account can be made Services account so that MFA rules are not enforced, and we need to start using as it is super useful and goes without saying that it greatly improves security posture as not only Services accounts are now clear, and MFA can safely enforced for all users.

I will make this article useful and not only show you how to create service accounts but also find out how to enforce MFA through this article.

USER TYPE PROPERTY

Let’s look at the property TYPE, which can be set to PERSON | SERVICE | LEGACY_SERVICE | NULL.

PERSON or NULL, or when TYPE is unset

• For PERSON or NULL, or when TYPE is unset, users do not have restrictions

LEGACY_SERVICE

• LEGACY_SERVICE is a type that lets services or applications that cannot easily use more secure authentication methods authenticate using a password or SAML authentication. What it means? This is the previous account type that the users need MFA enabled on an individual basis. And are not affected by authentication policy multi-factor authentication (MFA) enforcement.

SERVICE

The type SERVICE User account is to be used in a non-interactive use case and has the following characteristics: no logins using passwords, no SAML SSO, no MFA, and no policy MFA enforcement.

The funny thing is that when you change your User Account to Services, the information remains saved but not used and not even displayed. However, if you ALTER the account back to a PERSON, then everything shows again.

The Practical

Here is how you create a SERVICE user; however, you will notice I am not just making a service user. I am using some good practices.

• Firstly, I am creating an authentication policy to enforce MFA, in case you still need to do one. I want to push this message daily.
• Secondly, I also attach a Network Policy to the user to control where it can go. Again, this is another nice control instead of leaving it wide open.
• Lastly, I create the SERVICE user.

-- Enable Default MFA
USE DATABASE INFOSTRUX;
CREATE AUTHENTICATION POLICY mfa_enforcement_policy
MFA_ENROLLMENT = 'REQUIRED'
MFA_AUTHENTICATION_METHODS = ('SAML', 'PASSWORD');

-- SET authentication policy
ALTER ACCOUNT SET AUTHENTICATION POLICY mfa_enforcement_policy;

-- Network Policy
CREATE OR REPLACE NETWORK POLICY INGESTIONSERVICENETWORKPOLICY ALLOWED_IP_LIST=('192.168.1.0/24')
                                BLOCKED_IP_LIST=('192.168.1.99');
-- Create Service User
CREATE OR REPLACE USER ingestionservice 
    DEFAULT_ROLE = 'INGESTION_ROLE' 
    TYPE = SERVICE DEFAULT_SECONDARY_ROLES = ('ALL') 
    NETWORK_POLICY = INGESTIONSERVICENETWORKPOLICY
    RSA_PUBLIC_KEY='MIIB....'
    COMMENT = 'Service User for Ingestion';

Let’s review what happens to the service user with a Describe command.

-- Describe Service User
DESC USER ingestionservice;

This describe command shows a few things. TYPE is SERVICE, PASSWORD is null, and MFA is disabled, even though we enforced it above.

You can convert back-and-forth accounts from PERSON to SERVICE as needed, and no fields and data are lost.

What Are The Differences?

Let me modify my own user and see what it shows.

ALTER USER AUGUSTO SET TYPE = SERVICE;
DESC USER AUGUSTO;

See the results that my MFA was turned off as SERVICE accounts do have MFA enabled.

ALTER USER AUGUSTO SET TYPE = PERSON;
DESC USER AUGUSTO;

As you can see changing the USER TYPE, changes properties automatically but does not delete them from the USER. Just an interesting behaviour.

Conclusion

It is great to see security continuing to improve at Snowflake, both with the enforcement of MFAs and the ability to clearly identify which accounts are designated as Service accounts.

One request is to implement this as an Organizational policy so it can be enforced from the root account into any sub-accounts.

I am Augusto Rosa, VP of Engineering for Infostrux Solutions. Snowflake Data Super Hero and Snowflake SME. Thanks for reading my blog post. You can follow me on LinkedIn.

Subscribe to Infostrux Medium Blogs https://medium.com/infostrux-solutions for the most interesting Data Engineering and Snowflake news.

Sources:

• https://docs.snowflake.com/en/sql-reference/sql/alter-user
• https://docs.snowflake.com/en/sql-reference/sql/create-network-policy

---