CODEWITHSUNDEEP
apache-sparkpysparksnowflake-cloud-data-platformdatabricks
Kris
Kris

Reputation: 589

Granting permissions to a Snowflake database through Spark connecter in DataBricks

The role I'm currently using, say main_user, has full permissions to a particular Snowflake database, say live_database. There is also a role that currently doesn't any have permissions relating to this data, say temp_user. In the Snowflake UI I can simply run the following query and the temporary user has access as expected.

use role main_user;
grant usage on database live_database to role temp_user;
grant usage on schema live_database.example to role temp_user;
grant select on table live_database.example.sample_table to role temp_user;

I also have a pipeline that runs a model in DataBricks and reads from/writes to the aforementioned Snowflake database, via a Spark connector.

Unfortunately, every time I write to the table using:

# results is a PySpark dataframe containing the model results
results.write \
  .format("snowflake") \
  .option("sfUser", <username>) \
  .option("sfPassword", <password>) \
  .option("dbtable", "live_database.example.sample_table") \
...
  .mode('overwrite') \
  .save()

the permissions are reset and I have to go back into Snowflake UI to grant permissions. Is there a way to write to the table without resetting permissions? Or is there a way to run the first query directly from DataBricks to grant permissions? The issue arises when scheduling tasks, since then manually updating permissions defeats the purpose of scheduling.

Note: I am aware of this article but I have a DataBricks runtime v8 and thus according to this my Spark_connector is > v2.5.9

Upvotes: 0

Views: 1193

Answers (1)

Francesco Quaratino
Francesco Quaratino

Reputation: 590

As the Overwrite mode causes the table to be recreated, and therefore the existing permissions on it to be swept away, you could:

a) use the Append mode instead to reuse an existing table;

b) grant permissions for future tables within the relevant schema:

GRANT SELECT 
ON FUTURE TABLES 
IN SCHEMA live_database.example 
TO ROLE temp_user;

References:

a) https://docs.snowflake.com/en/user-guide/spark-connector-use.html

b) https://community.snowflake.com/s/article/How-to-grant-select-on-all-future-tables-in-a-schema-and-database-level

Upvotes: 1

Related Questions