snowflakedb / snowflake-connector-python

Snowflake Connector for Python
https://pypi.python.org/pypi/snowflake-connector-python/
Apache License 2.0
601 stars 473 forks source link

SNOW-1658882: Please provide the option to copy grants when overwriting table using write_pandas #2049

Open surpatean opened 2 months ago

surpatean commented 2 months ago

What is the current behavior?

When overwrite=True and auto_create_table=True, the new table created by write_pandas is created with fresh grants.

What is the desired behavior?

I would like the option to copy the grants of the old table to the new table, similar to the optional COPY GRANTS parameter of CREATE OR REPLACE TABLE.

How would this improve snowflake-connector-python?

In some use-cases, it is preferred to have table-level grants, instead of db/schema-level future grants.

If the table needs to be overwritten with a new schema definition (e.g. for schema evolution), the option to maintain the permission profile of the table would be useful.

References and other background

I will follow-up with a PR.

sfc-gh-sghosh commented 2 months ago

Hello @surpatean ,

Thanks for raising the request; we are checking and will update you.

Regards, Sujan

sfc-gh-sghosh commented 2 months ago

Hello @surpatean ,

We are checking if its feasible to add such feature or not with write_pandas API

Meanwhile, the following workaround can be used

  1. Take a backup of Grants ( SHOW GRANTS ON TABLE ;)
  2. wite_pandas ( session, )
  3. Reapply the grants GRANT SELECT ON TABLE TO ROLE ;

Regards, Sujan

surpatean commented 2 months ago

Thank you @sfc-gh-sghosh I also submitted a PR (based on the SQL copy grants parameter), which I think solves it.

sfc-gh-sghosh commented 2 months ago

Hello @surpatean ,

Thanks for submitting the PR, we are reviewing it, will update.

Regards, Sujan