Trying to setup a user to be able to use Data Loading Using the Web Interface (Limited) to load tables with a csv spreadsheet. What permissions are required to do this type of Load? Looking at Snowflake doc, it shows how to do the load, and says you can as long as you have to correct permissions, but it never says what exact permissions are required. Any help would be appreciated.
1
-
What do you mean by "csv spreadsheet"? Just a CSV file?Felipe Hoffa– Felipe Hoffa2020-09-14 18:05:59 +00:00Commented Sep 14, 2020 at 18:05
Add a comment
|
2 Answers
Please note loading via the Snowflake Web UI has limitations compared to other data loading methods.
To answer your question, in this scenario (when using the Web UI), a table stage is created and automatically used. As mentioned in the documentation, to load the files onto a table stage, you must be the table owner (have the role with the OWNERSHIP privilege on the target table). https://docs.snowflake.com/en/user-guide/data-load-local-file-system-create-stage.html#table-stages
1 Comment
Jimbo
I understand the limitations, just trying to understand the permissions. I have granted DATABASE USAGE, SCHEMA USAGE, as well as INSERT, UPDATE, DELETE, SELECT access on the TABLE all to the ROLE i'm trying to use. The ROLE did not create the initial TABLE, so OWNERSHIP of the TABLE is a different ROLE. I am trying to change the TABLE OWNERSHIP to the ROLE trying to do the LOAD and see if that resolves.
First create a role for the user and try this
GRANT ROLE <ROLE NAME> TO USER <USER NAME>;
GRANT USAGE ON DATABASE <DB NAME> TO ROLE <ROLE NAME>;
GRANT USAGE ON SCHEMA <SCHEMA NAME> TO <ROLE NAME>;
GRANT CREATE TABLE ON SCHEMA <SCHAME NAME> TO ROLE <ROLE NAME>;
5 Comments
Jimbo
I have granted DATABASE USAGE, SCHEMA USAGE, as well as INSERT, UPDATE, DELETE, SELECT access on the TABLE all to the ROLE i'm trying to use. The ROLE did not create the initial TABLE, so OWNERSHIP of the TABLE is a different ROLE. I am trying to change the TABLE OWNERSHIP to the ROLE trying to do the LOAD and see if that resolves.
Jimbo
So I am getting the following error when trying to change the OWNERSHIP on TABLE to a new ROLE... SQL execution error: Ownership restriction violation in a managed access schema. Grantee need to be a subordinate role of the schema owner.
Jimbo
so this is a single table in a security schema. Ownership of the SCHEMA and all objects in the schema was initially set to a FULL deployment role. We are now trying to change the Ownership of one table to a SECURITY role and cannot.
Suzy Lockwood
Right, it doesn't make sense to change the owner of a table in a managed access schema. With managed access schemas, the whole idea is that the individual object owners lose their ability to manage their objects (like tables) and the schema owner manages them all. docs.snowflake.com/en/user-guide/…
Suzy Lockwood
Because managed access schemas and their associated objects are intended to be managed by the schema owner, the objects within the schema cannot have their ownership transferred to a non-schema-owner role (as that would defeat the purpose of managed access schemas). If it was a regular schema (disable managed access), you could transfer ownership on the individual objects as desired. There's other options such as changing the schema owner role or you could just use a different table loading method than the UI.