CODEWITHSUNDEEP
snowflake-cloud-data-platform
Kenny Leftin
Kenny Leftin

Reputation: 41

How to grant access to a table in a Snowflake native app?

I'm trying to develop a Snowflake native app where the consumer can choose which table they'd like to load into the app.

In my manifest.yml, I have:

references:
  - my_table:
      label: "My Table"
      description: "My table"
      privileges:
        - SELECT
      object_type: Table
      multi_valued: false
      register_callback: app_instance_schema.update_reference

And in my streamlit app, I have:

table_reference_associations = permission.get_reference_associations(
    "my_table")
if len(table_reference_associations) == 0:
  permission.request_reference("my_table")
  sys.exit(0)

After installing the app and running it, I do get the pop-up to select a table but I also get a warning:

You don't have privileges required to grant access or the app is not set up properly. Use a role with MANAGE_GRANTS privilege or ask for support from the app provider.

I can't figure out how to resolve this. Do I need to add a privilege in my manifest? If so, what does that privilege look like? Or do I need to ensure that the role that's running the app has the right permissions? Since I'm running in the ACCOUNTADMIN role (on a trial account), I tried:

GRANT MANAGE GRANTS ON ACCOUNT TO ROLE ACCOUNTADMIN;

but that seemed to have no effect.

Thanks!

Upvotes: 2

Views: 660

Answers (1)

Chenow
Chenow

Reputation: 11

I got a similar issue, and it was because my procedure wasn't correctly build. according to this tutorial, the fallback procedure must have 3 arguments:

  • the first_one is the reference name specified in manifest.yaml
  • second one is either "ADD", "REMOVE", or "CLEAR"
  • last one is a reference to the object selected by the user

the procedure should look like this:

create or replace procedure app_instance_schema.update_reference(ref_name string, operation string, ref_or_alias string)
returns string
language sql
as $$
begin
  case (operation)
    when 'ADD' then
       -- do something

    when 'REMOVE' then
       -- do something

    when 'CLEAR' then
       -- do something

    else
       return 'Unknown operation: ' || operation;
  end case;
  return 'Success';
end;
$$;

Upvotes: 1

Related Questions