Request references and object-level privileges from consumers¶
This topic describes how providers can configure a Snowflake Native App to request access to objects in the consumer account that exist outside the APPLICATION object.
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.
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.
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 Create 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 |
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.
Remove a reference definition¶
Note
Snowflake recommends against removing a reference definition from the manifest file in a new version of an app. If you need to remove a defined reference, update any code that uses the removed reference in the same version release and notify the consumer in the README file.
If an app defines a reference then later deletes the reference definition from a subsequent version of the app, calling any function or
procedure that still uses the deleted reference results in an error for consumers. For example, the manifest file for version V1 of app
my_app
includes a reference definition for REF_TO_TABLE and a stored procedure CREATE_VIEW_FROM_TABLE that uses the table reference
REF_TO_TABLE to create a view VIEW_SELECT_FROM_DEFINED_REF.
In version V2 of my_app
, the reference definition for REF_TO_TABLE is removed from the manifest file. When a consumer upgrades
their installed app my_app
to version V2, calling the CREATE_VIEW_FROM_TABLE procedure results in the following error:
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 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 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);
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_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.
Note
Because the stored procedure uses the SYSTEM$SET_REFERENCE system function, the stored procedure only works for a reference with a single value in the description. To associate a reference with multiple values, use the SYSTEM$ADD_REFERENCE system function.
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.
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.
Note
The reference()
functions in the following examples can only be called in a stored procedure
in the APPLICATION object.
Run queries using a reference¶
The following examples show how to run queries using references:
SELECT * FROM reference('consumer_table');
SELECT reference('encrypt_func')(t.c1) FROM consumer_table t;
Call 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');
Run 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 INTO reference('the_table') ...
Run the DESCRIBE command using a reference¶
The following example shows how to run the DESCRIBE operation using a reference:
DESCRIBE TABLE reference('the_table')
Use references in a task¶
CREATE TASK app_task
WAREHOUSE = reference('consumer_warehouse')
...;
ALTER TASK app_task SET WAREHOUSE = reference('consumer_warehouse');
Use references in a view definition¶
CREATE VIEW app_view
AS SELECT reference('function')(T.C1) FROM reference('table') AS T;
Use references in a function body¶
CREATE FUNCTION app.func(x INT)
RETURNS STRING
AS $$ select reference('consumer_func')(x) $$;
Use references in an external function¶
CREATE EXTERNAL FUNCTION app.func(x INT)
RETURNS STRING
...
API_INTEGRATION = reference('app_integration');
Use 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
);
Supported reference functions¶
The Snowflake Native App Framework supports the following functions to perform different operations related to references.
System Function |
Description |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|