Creating the Setup Script¶

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.