Snowflake

Opal's Snowflake App not only lets you manage access to your Snowflake Roles, but it also allows you to manage a Role's access to Securable Objects such as Databases, Schemas, and Tables.

The integration supports the following, and more:

• Import and sync Snowflake Roles and Securable Objects into Opal
• View granular privileges granted to each user and role
• Manage access to Snowflake Roles and Securable Objects
• Perform User Access Reviews (UARs) to assign managers or group admins to periodically review users with access to Snowflake Roles
• Audit Event logs for any access changes on managed Snowflake Resources
• Request JIT time-bound access enabling end users to self-service access requests to Snowflake Roles

To learn more about the feature set, see the Opal Snowflake Integration blog .

ℹ️

Snowflake usage limits

There is a limit to 10,000 users, roles, databases, schemas, and tables each. If you would like to increase this, please reach out to support@opal.dev.

Requirements

To configure Snowflake, you must be an Opal Admin and have appropriate permissions in Snowflake.

1. Configure your Snowflake Service Account

In order for Opal to manage access to your Snowflake Roles, you must first configure a Snowflake User that uses key pair authentication. In Snowflake,

1. Create a public/private key pair. Follow Snowflake’s Key Pair Authentication instructions.
2. Create a Snowflake User and Role in Snowflake. You can use the below script as a sample:

USE ROLE ACCOUNTADMIN; 

CREATE ROLE OPALSERVICE COMMENT = 'This role is for the Opal Snowflake integration and should not be modified';

--  Allow role to update a user/role's grants
GRANT MANAGE GRANTS ON ACCOUNT TO ROLE OPALSERVICE;
-- Allow role to view query history for usage insights
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE OPALSERVICE;

-- Add to the Role Hierarchy as recommended by Snowflake
GRANT ROLE OPALSERVICE TO ROLE SECURITYADMIN;

-- Grant warehouse to role
-- <Warehouse name> should be replaced with your warehouse
GRANT USAGE ON WAREHOUSE <Warehouse name> TO ROLE OPALSERVICE;

-- Create the service account
CREATE USER OPALADMIN
    DEFAULT_ROLE = 'OPALSERVICE'
    DEFAULT_WAREHOUSE = '<Warehouse name>'
    -- Replace <Public key> with the value from the previous step
    RSA_PUBLIC_KEY = '<Public key>'
    COMMENT = 'Service account used by Opal Security to sync user roles';

-- Grant the role to the service account
GRANT ROLE OPALSERVICE TO USER OPALADMIN;

2. Fill out Opal form to Connect to Snowflake

In Opal, go to the Inventory, select the +App button on the top right, then select the Snowflake tile. Fill in the details of your Snowflake configuration. You need the following information:

• Organization
• Account
• Locator
• Account identifier
• Snowflake URL
• Public key
• Private key
• Private key password

Reference Snowflake's documentation for help finding the above values.

3. Import Snowflake Roles and Securable Objects

After the Snowflake App is set up, select Import items under the ... on the top right to see a list of available Snowflake Roles and Securable Objects you can import and manage in Opal.

Admins can choose to import only a select Snowflake Role, or automatically import it along with all the Securable Objects it can access. Importing a Securable Object, such as a Table, automatically creates a resource hierarchy, making it easy to visualize the parent Schema and Database in Opal.

4. View and Manage your Snowflake Resources

After you import Snowflake Roles and Securable Objects you want to manage in Opal, you can easily:

• Navigate to each Role and understand which Users and Groups have access to it, and which Securable Objects it has access to
• Add or remove Users to and from the Snowflake Roles
• Add or remove Securable Objects to and from Snowflake Roles

In the above example, this Customer Support Snowflake Role has two users with access to it, zero groups with access to it, and it has access to zero Securable Object Resources. The image shows an admin who has selected the User Tester and is about to select Remove, which revokes Tester's access to the Snowflake Role.