Snowflake : SQL access control error: Insufficient privileges to operate on schema

Question

I have written aws glue job where i am trying to read snowflake tables as spark dataframe and also trying to write a spark dataframe into the snowflake tables. My job is failing stating "Insufficient privileges to operate on schema" in both scenario.

But when i am directly writing insert statement on snowflake cli, i am able to insert data. So basically i have insert privilege.

So why my job is failing when i am trying to insert data from dataframe or reading data from snowflake table as a dataframe?

Below is my code to write data into snowflake table.

sfOptions = {
    "sfURL" : "xt30972.snowflakecomputing.com",
    "sfAccount" : "*****",
    "sfUser" : "*****",
    "sfPassword" : "****",
    "sfDatabase" : "*****",
    "sfSchema" : "******"
}

df=spark.read.format("csv").option("header","false").option("delimiter",',').load(aws s3 file_name)

df2.write.format("net.snowflake.spark.snowflake") \
.options(**sfOptions) \
.option("dbtable", table_name) \
.mode("append") \
.save()

I don't have snowflake environment but based snowflake architecture, you might have default role which do not have privileage. — Ramdev Sharma
yes.since i already have select, update, insert, delete privileges, exactly which privilege i need to add to write data from spark dataframe to snowflake table. I just cannot add all privileges in prod environment. I need to provide specefic privilege. — Basant Jain
If your current user have multiple roles then you can use one role in current session. So you have to validate which role is used when you are creating connection in spark code but if you are not setting any role then default role will be used. You can check using "show roles" to check default role. Once you know role can check all grants on that role to validate whether used role has correct grants. — Ramdev Sharma
Agreed but according to the client they do not want to give all grants to any role. They want to grant specific privilege. — Basant Jain
Yes, it should be very specific and you should set correct role in sfoptions. Can you set sfRole property to role that has relevant role to write on target table — Ramdev Sharma

mevdiven mevdiven · Accepted Answer · 2019-03-21T14:12:53

When you are using Snowflake CLI, I assume that you switch to a proper role to execute SELECT or INSERT. On Spark, you need to manually switch to the role that has SELECT/INSERT grants before operating on a table. You do this by issuing below.

Utils.runQuery(sfOptions, "USE ROLE <your_role>")

This will switch the role for the duration of your Spark session.

Also, please note that Snowflake's access structure is hierarchy based. That means you need to have "usage" privileges on the database and schema that houses the table you are trying to use. Please make sure that you have all the right grants to the role using to SELECT or INSERT.

Snowflake : SQL access control error: Insufficient privileges to operate on schema

1 Answers