Requesting References and Object-level Privileges from Consumers¶

This topic describes how providers can request access to existing database objects in the consumer account.

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, a Snowflake Native 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 Snowflake Native App. The Snowflake Native App cannot determine the names of the schema and objects that exist in the consumer account. See to Object Name Resolution for additional information.

To allow the Snowflake Native App to connect to these kinds of objects, the Snowflake Native App Framework provides references that allow the customer to specify the names of the objects and grant the required privileges.

Workflow for Defining References in the Consumer Account¶

To request a reference and object-level privilege, the provider performs the following when developing and publishing a Snowflake Native App:

Determine which objects require references and their corresponding privileges.
Define the references in the manifest file.
Add a stored procedure in the setup script to handle the callback for each reference defined in the manifest file.

After installing the Snowflake Native App, the consumer performs the following:

View the references required by the Snowflake Native App.
Create the reference by calling the SYSTEM$REFERENCE system function.
Run the callback stored procedure passing the id of the reference.

After the consumer runs the callback stored procedure, the Snowflake Native App can access the requested object.

This workflow outlines the process where the consumer creates the reference manually. Refer to Creating a User Interface to Request Privileges and References for information on creating a user interface to allow consumers to create references and grant privileges using Snowsight.

Object Types and Privileges that a Reference Can Contain¶

The following table lists the object types that a reference can include and the privileges allowed for each object:

Object Type	Privileges Allowed
TABLE	SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES
VIEW	SELECT, REFERENCES
EXTERNAL TABLE	SELECT, REFERENCES
FUNCTION	USAGE
PROCEDURE	USAGE
WAREHOUSE	MODIFY, MONITOR, USAGE, OPERATE
API INTEGRATION	USAGE

Defining a Reference in the Manifest File¶

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

references:
- my_table:
  label: "My table"
  description: "A table that exists outside the Snowflake Native App"
  privileges:
  - SELECT
  object_type: TABLE
  multi_valued: false
  register_callback: config.register_reference

Copy

This example defines a reference named my_table that requires the SELECT privilege on a table. The register_callback property specifies a stored procedure used to bind the object to the Snowflake Native App.

Creating a Callback Stored Procedure for a Reference¶

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

The following example shows a stored procedure used to handle a call back for the reference show in Defining 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_CALLBACK(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);
        WHEN 'CLEAR' THEN
          SELECT SYSTEM$REMOVE_REFERENCE(ref_name);
      ELSE
        RETURN 'unknown operation: ' || operation;
      END CASE;
      RETURN NULL;
    END;
  $$;

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

Copy

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

Viewing 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;

Copy

Binding an Object to the Application¶

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

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

Copy

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_callback(
 SYSTEM$REFERENCE('TABLE', 'db1.schema1.tab1', 'PERSISTENT', 'SELECT', 'INSERT'), 'ADD', null);

Copy

After the consumer runs the callback stored procedure, 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);

Copy

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.

References do not work in an APPLICATION object created in development mode using files on a named stage. References only work in an APPLICATION object that has a version or a Snowflake Native App in a different account installed from a listing.

Examples of Using References in a Snowflake Native App¶

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

Running Queries Using a Reference¶

The following examples show how to run queries using references:

SELECT * FROM reference('enrichment_table');

Copy

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

Copy

Calling a Stored Procedure Using a Reference¶

The following example shows how to call a stored procedure using a reference:

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

Copy

Running DML Commands Using a Reference¶

The following examples show how to modify data in a table using references:

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

Copy

COPY INTO reference('the_table') ...

Copy

Running the DESCRIBE Command Using a Reference¶

The following example shows how to run the DESCRIBE operation using a reference:

DESCRIBE TABLE reference('the_table')

Copy

Using References in a Task¶

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

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

Copy

Using References in a View Definition¶

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

Copy

Using References in a Function Body¶

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

Copy

Using References in an External Function¶

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

Copy

Using References in a Policy¶

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
      );

Copy

Supported Reference Functions¶

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

System Function	Description
`set_reference`	`SYSTEM$SET_REFERENCE('<reference_name>', '<reference_string>')` Supports only for a single reference. If a reference has already been created using the same name, the existing reference is overwritten. Returns a unique system-generated alias for the reference.
`add_reference`	`SYSTEM$ADD_REFERENCE('<reference_name>', '<reference_string>')` Supports both single and multi-valued references. For single-value references, the function returns an error if a reference has already been created using the same value specified by <reference_name>. Returns a unique system-generated alias for the reference.
`remove_reference`	`SYSTEM$REMOVE_REFERENCE('<reference_name>'[, '<alias>'])` Supports both single and multi-valued references. An <alias> is required to remove multi-valued references. Removes an association with a multi-valued reference.
`remove_all_references`	`SYSTEM$REMOVE_ALL_REFERENCES('<reference_name>')` Removes all associations to the reference.
`get_all_references`	`SYSTEM$GET_ALL_REFERENCES('<reference_name>')` Returns an array of system-generated aliases of entities associated to a reference name: Empty list if the reference name is not bound to an entity All associations for multi-valued references 1 or 0 association for single-valued references Returned value does not contain consumer’s object name Used to iterate through all associations for a multi-valued reference in a loop.
`get_referenced_object_id_hash`	`SYSTEM$GET_REFERENCED_OBJECT_ID_HASH('<reference_name>'[, '<alias>'])` Takes system-generated alias for multi-valued reference. Returns the hash of the entity id of the bound object. This is the identifier of the entity resolved originally when a reference was created. This function is useful for the Snowflake Native App to determine if the object bound to a reference has been changed. The Snowflake Native App can save the value, then compare the current value to the previously known value.