CODEWITHSUNDEEP
snowflake-cloud-data-platformstreamlit
Ethan Hawkins
Ethan Hawkins

Reputation: 11

How to switch role in streamlit on Snowflake?

within my streamlit code in snowflake, I use Snowpark's session.sql to send the following command:

session.sql("USE ROLE SECURITYADMIN").collect()

and I get the following error message:

SQL execution failed with error: (1304): 01b9ac90-0001-abd6-0000-17ed2f9d380e: 090236 (42601): 01b9ac90-0001-abd6-0000-17ed2f9d380e: Stored procedure execution error: Unsupported statement type 'USE'.

In Snowflake's docs, I don't see any reference to suggest that USE is unsupported. I've used it in snowpark before, and it has worked. What about the session ran by streamlit is different that it doesn't support this? Is there any workaround?

I'm also confused about what exactly the stored procedure is here. My impression is that this was running my sql directly in using my token from my session, not calling a stored proc

I expected the query to run and switch the session role to securityadmin. I have all tried USE SECONDARY ROLES ALL to no avail. I want to be able to issue certain grants without giving the role running the streamlit too high a privilege.

Upvotes: 1

Views: 83

Answers (1)

Hujaakbar
Hujaakbar

Reputation: 1082

The model for Streamlit in Snowflake closely maps to the owner’s rights model in stored procedures. In other words, the code you execute in the Streamlit app behaves like the code executed inside the stored procedures with the owner’s rights.

Streamlit apps run with the privileges of the owner, not the privileges of the caller.

Streamlit apps running in Streamlit in Snowflake run with owner’s rights and follow the same security model as other Snowflake objects that run with owner’s rights. source

Unlike caller’s rights, owner’s rights stored procedures can call only a subset of SQL statements.

The following SQL statements can be called from inside an owner’s rights stored procedure:

  • SELECT.

  • DML.

  • DDL. (Some restrictions on the ALTER USER statement.)

  • GRANT/REVOKE.

  • Variable assignment.

  • DESCRIBE and SHOW.

Other SQL statements cannot be called from inside an owner’s rights stored procedure. source

Upvotes: 1

Related Questions