Creating the Setup Script

This topic describes how to use the setup script to specify what objects are created when your application is installed. It also describes application roles and how they are used within the setup script.

About the Setup Script

The setup script of application contains SQL statements that are run when the consumer installs or upgrades an application or when a provider installs or upgrades an application for testing. Every application must contain a setup script. The location of the setup script is specified in the manifest file.

The setup script defines the objects that are created when an application is installed or upgraded.

Note

The setup script is required to create an application package. The name and path to the setup script are specified in the manifest.yml file.

Restrictions on the Setup Script

The following operations cannot be performed within a setup script:

  • USE DATABASE

  • USE SCHEMA

  • USE ROLE

  • USE SECONDARY ROLES

  • Setting the LOG_LEVEL or TRACE_LEVEL properties with the ALTER <object> command.

  • Creating or invoking procedures that are EXECUTE AS CALLER.

  • Creating Snowpark user-defined functions (UDFs) or procedures that utilize IMPORT from a named stage.

  • Calling procedures, functions or anonymous code blocks that make use of extension code.

  • Importing code files from a named stage when using the CREATE FUNCTION command.

  • Using CALL to call a procedure that has EXECUTE AS CALLER.

There are additional restrictions on objects created within a versioned schema.

Visibility of Objects Created in the Setup Script to Consumers

The setup script can define most database-level objects. Database objects created by the setup script are internal to the application. They are invisible and inaccessible to the consumer account directly.

You can make these objects visible to the consumer using application roles. Any application role created within the setup script is automatically granted to the role owning the instance of the installed application. These roles cannot be revoked.

Users with the role that owns the application instance can then grant application roles to other roles within their organization. For example, the setup script can define an application role, such as APP_ADMIN, to which it grants permission to access objects within the application. That user can then use these objects to administer the application.

The setup script can also define an application role (e.g. USER). Using this role, consumers are granted access to use the functionality provided by the application The setup script can define an application role, such as READ_ONLY, to provide restricted access to select areas of data within the application.

The setup script is used for both initial installation as well as upgrades. The script must be written in an idempotent fashion. For example, create objects with CREATE .. IF NOT EXISTS or CREATE OR REPLACE.

Best Practices When Creating the Setup Script

Snowflake recommends the following best practices when creating the setup script for an application:

  • When using CREATE command to create objects within the setup script, Snowflake recommends using the CREATE OR REPLACE or CREATE IF NOT EXISTS versions of the commands. The setup script can be run multiple times during installation and upgrade and in cases where an error occurs. In these situations, for example, a versioned schema may not be empty.

  • CREATE SCHEMA does not change the session context, so objects must be qualified with the target schema when created. For example, to create a schema within the setup script, use the following commands:

    CREATE SCHEMA IF NOT EXISTS app_config;
    CREATE TABLE IF NOT EXISTS app_config.params(...);
    
    Copy
  • Versioned schema objects can refer to un-versioned objects and visa versa. You must consider the possibility of failure when the setup script is running. Given that the setup script is written to be idempotent it is automatically run again if the initial execution fails.

    Consider this when granting to application roles within a schema. For example, consider using the following:

    CREATE OR REPLACE PROCEDURE app_state.proc()...;
    -- Additional CREATE statements
    GRANT USAGE ON PROCEDURE app_state.proc()
       TO APPLICATION ROLE app_user;
    
    Copy

    The CREATE OR REPLACE statement replaces the procedure which implicitly removes grants that had been previously made to that procedure. Although the grants are restored at later in the script, if the script fails when it is run, consumers may lose the ability to access the procedure.

    If setup script fails due to an issue that cannot be resolved by a retrying, for example a syntax error, the consumer cannot access the procedure until a new version or patch is upgraded and the grant is restored.

  • Views on shared content should always be defined in versioned schema. This ensures that any code accessing the view during an upgrade will use a consistent view of even if new columns or other attributes are added or removed.

  • If the setup script must perform very long running operations, such as upgrading large state tables, ensure that these updates are compatible with existing running code from the previous version.

About Application Roles

By default the consumer has no privileges on objects created within the application. Even the ACCOUNTADMIN role cannot view the objects within an application. Objects that the application creates outside itself, for example a database, are visible only to the ACCOUNTADMIN role of the consumer account.

Application roles are similar to database roles, but may only be created within the application. Unlike database roles, application roles can be granted privileges on objects that exist outside of the application.

Application roles should be created by the setup script when the application is installed and are automatically granted to the application owner’s role, who then can grant appropriate application roles to other roles in the consumer account.

Note

Application roles are the only type of role that can be created within an application. Database roles, for example are not permitted within the application.

Likewise, application roles can only be created in an application and not, for example, in a normal database or at the account level.

Any privileges granted to application roles is passed to the application owner, which is the role used to install the application. The owner may further delegate application roles to other roles within the consumer account.

Unlike database roles, application roles may also be granted privileges on objects outside of the installed application. They may therefore be used to grant privileges on objects outside of the application. However, the application role itself must be created within the application.

Supported SQL Commands for Working with Application Roles

The Snowflake Native App Framework provides the following SQL commands for working with application roles:

Using Application Roles in the Setup Script

Application roles defined in the setup script are automatically granted to the role owning the application instance. When the application is installed, the role used to installed the application is the owner of the application. However, the application owner can grant privileges to other account roles in the consumer account.

Application roles allow privileges on objects within the application to be granted to the consumer. For example:

CREATE APPLICATION ROLE admin;
CREATE APPLICATION ROLE user;
GRANT APPLICATION ROLE user TO APPLICATION ROLE admin;

CREATE OR ALTER VERSIONED SCHEMA app_code;
GRANT USAGE ON SCHEMA app_code TO APPLICATION ROLE admin;
GRANT USAGE ON SCHEMA app_code TO APPLICATION ROLE user;
CREATE OR REPLACE PROCEDURE app_code.config_app(...)
GRANT USAGE ON PROCEDURE app_code.config_app(..)
  TO APPLICATION ROLE admin;

CREATE OR REPLACE FUNCTION app_code.add(x INT, y INT)
GRANT USAGE ON FUNCTION app_code.add(INT, INT)
  TO APPLICATION ROLE admin;
GRANT USAGE ON FUNCTION app_code.add(INT, INT)
  TO APPLICATION ROLE user;
Copy

In this example, the setup script creates application roles named admin and a user. The setup script then grants both application roles access to the schema containing the application code. It also grants access to the add function within the schema. The admin role is also granted access to the config_app procedure.

Application Roles and Versions

Application roles are not versioned. This means that dropping an application role or revoking a permission from an object that is not in a versioned schema can impact the current version of an application or the version being upgraded. Application roles may only be safely dropped when you have dropped all versions of the application that use those roles.

Note

Application roles cannot be granted ownership of objects. This means that an application role defined in the setup script should only be used to allow consumers to access objects within the installed Snowflake Native App.