Implementing a Connected Application
A Connected Application is a deployment model where you connect to your customer’s data platform directly. For Snowflake, this means that your application is connecting directly to a customer’s Snowflake account. For more information, see part 1 of this blog, Designing a Connected Application.
Once you’re ready to implement your app on a customer’s Snowflake account, you need to create a set of scripts to install the necessary objects for the application. These commands will likely need to be issued by a Snowflake ACCOUNTADMIN, or at least someone who has access to both USERADMIN (or SECURITYADMIN) and SYSADMIN roles.
The following commands should be altered for your application and available in your customer-facing documentation.
Step 0 — Setting Session Variables
SET PARTNER_NAME = '<your_name>';
SET DATABASE_NAME = $PARTNER_NAME || '_DB';
SET SCHEMA_NAME = $DATABASE_NAME || '.PUBLIC';
SET WAREHOUSE_NAME = $PARTNER_NAME || '_WH';
SET WAREHOUSE_SIZE = 'MEDIUM';
SET ROLE_NAME = $PARTNER_NAME || '_RL';
SET USER_NAME = $PARTNER_NAME || '_USER';If your app is going to use multiple schemas, warehouses, roles, or users, make sure you specify those here as well. This example app is using the default PUBLIC schema, but consider specifying specific schema to your app needs, like PARTNER_NAME.ANALYTICS or PARTNER_NAME.EVENTS, especially if your app is writing data to the customer’s account.
Step 1 — Creating the Database
USE ROLE SYSADMIN;CREATE DATABASE IF NOT EXISTS IDENTIFIER($DATABASE_NAME);
It’s best practice for your customer to use SYSADMIN to create any databases, warehouses, schemas, tables, and views. If your app will be using a defined schema and table/view DDLs, those likely shouldn’t be done here — instead, design your app to directly provision and maintain those. Later on, we’ll give examples on the grants required for this.
Step 2 — Creating the Warehouse
CREATE WAREHOUSE IF NOT EXISTS IDENTIFIER($WAREHOUSE_NAME)
WITH WAREHOUSE_SIZE = $WAREHOUSE_SIZE
AUTO_SUSPEND = 300
AUTO_RESUME = TRUE;While still in the SYSADMIN role, create the warehouse(s). The warehouses, as well as the auto_suspend, should be sized appropriately (in Step 0) for their workload in the app. Lastly, the auto resume set to true is likely required for any application.
Step 3 — Creating the Role
USE ROLE USERADMIN;CREATE ROLE IF NOT EXISTS IDENTIFIER($ROLE_NAME);
Switch role to USERADMIN to create the application’s dedicated role. While this demo app has the ability to create and maintain the objects inside the customer’s account, it will only be able to write data to the tables that it creates in its own database. If your app will be reading data outside of its own database, consider creating two roles and users — one to read from other databases, and another to write to the app database.
Another reason to create two roles is if your application will be reading sensitive, or PII, data and surfacing it to users in a user interface. You can create one role to let users preview data with dynamic data masking, and another without masking to execute production jobs.
Step 4 — Granting Access
GRANT ALL
ON WAREHOUSE IDENTIFIER($WAREHOUSE_NAME)
TO ROLE IDENTIFIER($ROLE_NAME);GRANT USAGE
ON DATABASE IDENTIFIER($DATABASE_NAME)
TO ROLE IDENTIFIER($ROLE_NAME);GRANT CREATE TABLE, CREATE VIEW, CREATE STAGE, CREATE PIPE, CREATE PROCEDURE, CREATE FUNCTION, MODIFY, USAGE
ON ALL SCHEMAS IN DATABASE IDENTIFIER($DATABASE_NAME)
TO ROLE IDENTIFIER($ROLE_NAME);GRANT USAGE
ON FUTURE SCHEMAS IN DATABASE IDENTIFIER($DATABASE_NAME)
TO ROLE IDENTIFIER($ROLE_NAME);GRANT SELECT
ON FUTURE TABLES IN SCHEMA IDENTIFIER($SCHEMA_NAME)
TO ROLE IDENTIFIER($ROLE_NAME);GRANT SELECT
ON FUTURE VIEWS IN SCHEMA IDENTIFIER($SCHEMA_NAME)
TO ROLE IDENTIFIER($ROLE_NAME);
This step is to grant access to all of the objects created above. Since this app only needs read access, the bare minimum access was applied for the app to select from all current and futures schemas, tables, and views in the app’s database. If you added any objects, like an external stage, make sure those grants are added here as well.
In most applications, you’ll need to also get read access to other data in the customer’s account, not just the app’s database. This data will likely not have consistent database, schema, and table names. It’s in the app provider’s best interest to consult with the customer where needed to ensure a secure setup.
Step 5 — Creating the User and Assigning the Role
CREATE USER IF NOT EXISTS IDENTIFIER($USER_NAME) password='<password>';GRANT ROLE IDENTIFIER($ROLE_NAME) TO USER IDENTIFIER($USER_NAME);
Finally, you’ll need to create the user(s) that the app will be using and grant the proper roles to the users. If possible, design your app to use Key Pair Authentication. If your app is storing a password or private key, it should always be stored encrypted and never be visible to users.
Partner Examples
Looking for how current Powered by Snowflake partners have set up their installation scripts? Check out Flywheel, RudderStack, and MessageGears as examples of direct account access, and Panther as an example for dedicated account access.
