Request references and object-level privileges from consumers¶

About references¶

In some contexts an installed Snowflake Native App needs to access existing objects in the consumer account that exist outside the APPLICATION object. For example, an app might need to access existing tables in a consumer database.

In this context, it’s not sufficient for the consumer to grant access on an object to the APPLICATION object because the app cannot determine the name of the schema and object in the consumer account.

To allow the Snowflake Native App to access existing objects outside the APPLICATION object, the Snowflake Native App Framework provides references that allow the customer to specify the name and schema for an object and enable access to the object.

Define a reference in the manifest file¶

The following example shows how to define a reference in the manifest.yml file for a table in the consumer account that exists outside the APPLICATION object:

references:
  - consumer_table:
      label: "Consumer table"
      description: "A table in the consumer account that exists outside the APPLICATION object."
      privileges:
        - INSERT
        - SELECT
      object_type: TABLE
      multi_valued: false
      register_callback: config.register_single_reference

This example defines an reference named consumer_table that requires the INSERT and SELECT privileges on a table in the consumer account. The register_callback property specifies a stored procedure used to bind a consumer table to this reference definition.

Use multi_valued to bind multiple consumer objects to the same reference. When this property is specified, the same operations are performed on objects with a single value reference. The property can also be used with objects with multi-valued references. See Supported reference functions to learn more about Snowflake Native App Framework reference operations.

Reference definition '<REF_DEF_NAME>' cannot be found in the current version of the application '<APP_NAME>'

Create a callback stored procedure for a reference¶

After defining a reference in the manifest.yml file, a provider must add a stored procedure to the setup script to register the callback for the reference.

The following example shows a stored procedure used to handle a callback for the reference shown in Define a reference in the manifest file:

CREATE APPLICATION ROLE app_admin;

CREATE OR ALTER VERSIONED SCHEMA config;
GRANT USAGE ON SCHEMA config TO APPLICATION ROLE app_admin;

CREATE PROCEDURE CONFIG.REGISTER_SINGLE_REFERENCE(ref_name STRING, operation STRING, ref_or_alias STRING)
  RETURNS STRING
  LANGUAGE SQL
  AS $$
    BEGIN
      CASE (operation)
        WHEN 'ADD' THEN
          SELECT SYSTEM$SET_REFERENCE(:ref_name, :ref_or_alias);
        WHEN 'REMOVE' THEN
          SELECT SYSTEM$REMOVE_REFERENCE(:ref_name, :ref_or_alias);
        WHEN 'CLEAR' THEN
          SELECT SYSTEM$REMOVE_ALL_REFERENCES(:ref_name);
      ELSE
        RETURN 'unknown operation: ' || operation;
      END CASE;
      RETURN NULL;
    END;
  $$;

GRANT USAGE ON PROCEDURE CONFIG.REGISTER_SINGLE_REFERENCE(STRING, STRING, STRING)
  TO APPLICATION ROLE app_admin;

This example creates a stored procedure named REGISTER_SINGLE_REFERENCE that calls a system function to perform a specific operation on a reference that is passed as an argument to the stored procedure.

Create a callback stored procedure for requesting object configuration¶

For some object types, a provider must add a stored procedure to the setup script to provide additional configuration. This callback is used when consumers allow references using Snowsight.

The following example shows how to define a configuration callback stored procedure for the reference shown in Define a reference in the manifest file:

CREATE OR REPLACE CONFIG.GET_CONFIGURATION_FOR_REFERENCE(ref_name STRING)
  RETURNS STRING
  LANGUAGE SQL
  AS
  $$
  BEGIN
    CASE (ref_name)
      WHEN 'CONSUMER_EXTERNAL_ACCESS' THEN
        RETURN '{
          "type": "CONFIGURATION",
          "payload":{
            "host_ports":["google.com"],
            "allowed_secrets" : "LIST",
            "secret_references":["CONSUMER_SECRET"]}}';
      WHEN 'CONSUMER_SECRET' THEN
        RETURN '{
          "type": "CONFIGURATION",
          "payload":{
            "type" : "OAUTH2",
            "security_integration": {
              "oauth_scopes": ["https://www.googleapis.com/auth/analytics.readonly"],
              "oauth_token_endpoint": "https://oauth2.googleapis.com/token",
              "oauth_authorization_endpoint":
                "https://accounts.google.com/o/oauth2/auth"}}}';
     END CASE;
     RETURN '';
   END;
   $$;

GRANT USAGE ON PROCEDURE CONFIG.GET_CONFIGURATION_FOR_REFERENCE(STRING)
  TO APPLICATION ROLE app_admin;

This example creates a stored procedure named GET_CONFIGURATION_FOR_REFERENCE that returns a JSON-formatted configuration that is used to build a reference of type EXTERNAL ACCESS INTEGRATION or SECRET reference. The entries in the switch statement should map to the reference names in the manifest.yml file.

View the references defined in an application¶

When a provider defines references in the manifest.yml file, they are included as part of the installed Snowflake Native App.

To view the references defined for a Snowflake Native App, run the SHOW REFERENCES command as shown in the following example:

SHOW REFERENCES IN APPLICATION hello_snowflake_app;

Bind an object to the application¶

After viewing the reference definition for a Snowflake Native App, the consumer creates a reference by running the SYSTEM$REFERENCE system function as shown in the following example:

SELECT SYSTEM$REFERENCE('table', 'db1.schema1.table1', 'persistent', 'select', 'insert');

This command returns an identifier for the reference. The consumer can pass the identifier to the callback stored procedure for the reference as shown in the following example:

CALL app.config.register_single_reference(
  'consumer_table' , 'ADD', SYSTEM$REFERENCE('TABLE', 'db1.schema1.table1', 'PERSISTENT', 'SELECT', 'INSERT'));

In this example, consumer_table is the name of the reference defined in the manifest.yml file. After the consumer runs the stored procedure that associates the reference, the Snowflake Native App can access the table in the consumer account.

The callback stored procedure in the previous section calls the SYSTEM$SET_REFERENCE system function as shown in the following example:

SELECT SYSTEM$SET_REFERENCE(:ref_name, :ref_or_alias);

Refer to Supported reference functions for other system functions related to references.

Considerations when using references¶

Snowflake recommends that you do not modify reference definitions across versions. To update a reference definition in a new version, for example, to change the privileges to SELECT, INSERT from SELECT, you must define a new reference definition with a different name The updated Snowflake Native App can use this new reference in the new version of the app.

To embed a reference within another object, for example to assign a reference to a variable, the reference must already be bound to an object in the consumer account. For example, you cannot create a task unless you first bind the reference to the consumer warehouse.

Examples of using references in a Snowflake Native App¶

The following sections provide examples of using references in different contexts.

SELECT * FROM reference('consumer_table');

SELECT reference('encrypt_func')(t.c1) FROM consumer_table t;

CALL reference('consumer_proc')(11, 'hello world');

INSERT INTO reference('data_export')(C1, C2)
  SELECT T.C1, T.C2 FROM reference('other_table')

COPY INTO reference('the_table') ...

DESCRIBE TABLE reference('the_table')

CREATE TASK app_task
  WAREHOUSE = reference('consumer_warehouse')
  ...;

ALTER TASK app_task SET WAREHOUSE = reference('consumer_warehouse');

CREATE VIEW app_view
  AS SELECT reference('function')(T.C1) FROM reference('table') AS T;

CREATE FUNCTION app.func(x INT)
  RETURNS STRING
  AS $$ select reference('consumer_func')(x) $$;

CREATE EXTERNAL FUNCTION app.func(x INT)
  RETURNS STRING
  ...
  API_INTEGRATION = reference('app_integration');

CREATE FUNCTION app.func(x INT)
  RETURNS STRING
  ...
  EXTERNAL_ACCESS_INTEGRATIONS = (reference('consumer_external_access_integration'), ...);
  SECRETS = ('cred1' = reference('consumer_secret'), ...);

CREATE ROW ACCESS POLICY app_policy
  AS (sales_region varchar) RETURNS BOOLEAN ->
  'sales_executive_role' = reference('get_sales_team')
    or exists (
      select 1 from reference('sales_table')
        where sales_manager = reference('get_sales_team')()
        and region = sales_region
      );

JSON format for the configuration callback response¶

The configuration callback function returns a response in JSON format. The JSON format returned is different for external access integration and secret references.

{
  "type": "CONFIGURATION",
  "payload": {
    "host_ports": ["host_port_1", ...],
    "allowed_secrets": "NONE|ALL|LIST",
    "secret_references": ["ref_name_1", ...]
  }
}

{
  "type": "CONFIGURATION",
    "payload": {
            "type": "OAUTH2",
            "security_integration": {
                    "oauth_scopes": ["scope_1", "scope_2"],
                    "oauth_token_endpoint" : "token_endpoint",
                    "oauth_authorization_endpoint" : "auth_endpoint"
            }
    }
}

 {
   "type": "ERROR",
   "payload":{
     "message": "The reference is not available for configuration ..."
  }
}

Supported reference functions¶

The Snowflake Native App Framework supports the following functions to perform different operations related to references:

• SYSTEM$ADD_REFERENCE

• SYSTEM$GET_ALL_REFERENCES

• SYSTEM$GET_REFERENCED_OBJECT_ID_HASH

• SYSTEM$REMOVE_ALL_REFERENCES

• SYSTEM$REMOVE_REFERENCE

• SYSTEM$SET_REFERENCE