Snowflake

Ploy connects to one Snowflake account at a time. If your organisation has several Snowflake accounts, connect each one separately.

Once connected, Ploy shows every user in the account, people and service users, with the roles granted to them, when they last signed in and whether they are disabled. Ploy can also add, disable, re-enable and delete users, and grant them a role.

Ploy reads Snowflake with SHOW commands, which need no warehouse, so the connection does not use any of your credits.

Before you start

You need:

• a Snowflake user with the ACCOUNTADMIN role, to run the setup below

• a terminal with openssl (macOS and Linux include it)

1. Create a key pair

Ploy signs in with a key pair rather than a password. In a terminal, run:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out ploy_snowflake.p8 -nocrypt
openssl rsa -in ploy_snowflake.p8 -pubout -out ploy_snowflake.pub

This makes two files. ploy_snowflake.p8 is the private key, which you paste into Ploy. ploy_snowflake.pub is the public key, which you give to Snowflake. Keep the private key somewhere safe, such as your password manager.

2. Create a role and a user for Ploy

In Snowsight, open a SQL worksheet and run the following as ACCOUNTADMIN. Replace the key with the contents of ploy_snowflake.pub, without the BEGIN and END lines.

USE ROLE ACCOUNTADMIN;

CREATE ROLE PLOY_ROLE;
GRANT MANAGE GRANTS ON ACCOUNT TO ROLE PLOY_ROLE;

CREATE USER PLOY_SERVICE
  TYPE = SERVICE
  DEFAULT_ROLE = PLOY_ROLE
  RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';
GRANT ROLE PLOY_ROLE TO USER PLOY_SERVICE;

MANAGE GRANTS lets Ploy see every user and every role grant. Without it, Snowflake hides user details from Ploy.

If you want Ploy to manage users

To let Ploy add, disable, re-enable and delete users and grant roles, also run:

GRANT ROLE USERADMIN TO ROLE PLOY_ROLE;

Snowflake only lets a role change users it owns. Users created by USERADMIN (or by Ploy) can be managed straight away. To let Ploy manage a user that was created by another role, such as ACCOUNTADMIN, hand that user to USERADMIN:

GRANT OWNERSHIP ON USER "<user name>" TO ROLE USERADMIN COPY CURRENT GRANTS;

3. Find your account identifier

In the worksheet, run:

SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();

It returns your account identifier, in the form ORGNAME-ACCOUNTNAME.

4. Connect in Ploy

In Ploy, go to Integrations, choose Snowflake and enter:

• your account identifier

• the user name, PLOY_SERVICE

• the private key: the whole contents of ploy_snowflake.p8, including the BEGIN and END lines

Select Test connection. If Ploy can sign in and read your users and roles, save the connection. Your first scan starts straight away.

What Ploy shows

• Users: people and service users. Service users show as non-human identities.

• Roles: each role granted directly to a user shows as an entitlement. A user with no granted role shows PUBLIC, which every Snowflake user holds. Roles inherited through other roles are not shown yet.

• Disabled users: users who are disabled, expired or locked by Snowflake lose their access in Ploy straight away.

• Last sign-in and, for people, whether multi-factor authentication is set up.

What Ploy can change

• Add a user, with their email as their user name and login name, and an optional role. No password is set, so they sign in through single sign-on, or an admin sets one in Snowflake.

• Disable and re-enable a user.

• Delete a user.

• Grant a role to a user. Their other roles stay in place. To remove a role, use Snowflake.

Troubleshooting

• Snowflake rejected the login: check the account identifier and user name, and that the public key set on the user matches the private key you pasted.

• Ploy signed in but cannot read your users' details: grant PLOY_ROLE MANAGE GRANTS on the account and make it the user's default role.

• That is the public key: paste the .p8 private key, not the .pub file.

• Ploy's Snowflake role does not own this user: hand the user to USERADMIN with the GRANT OWNERSHIP command above.