sqlsnowflake-cloud-data-platformsql-grantdata-sharing

Snowflake Data Sharing - Grant partially executed | Grant not executed

Aim:

Grant more privileges to the Data Share

Description:

Please note that I'm using a 30 days trial version of Snowflake.

I've tried creating the Data Share via UI, but despite being able to Add a Consumer under the Full Account section, the user of that account was unable to Update or Insert the data shared.

enter image description here

The user of the account could only select data. But according to the documentation the user of the Full Account should be able to perform data manipulation (insert, update, etc).

enter image description here

I've also tried the manual way of creating the Data Share, but I kept getting the below Query Message

SQL Code Sample:

use role accountadmin;

create share SHARE_DATABASE;

grant usage,modify on database TEST_DATABASE to share SHARE_DATABASE;
--Message: Statement executed successfully.

grant usage,modify on schema TEST_DATABASE.PUBLIC to share SHARE_DATABASE;
--Message: Grant partially executed: privileges [MODIFY] not granted.

grant select,insert,update on table TEST_DATABASE.PUBLIC.TABLE_TEST_DATA to share SHARE_DATABASE;
--Message: Grant partially executed: privileges [INSERT, UPDATE] not granted.

--When trying to execute the grant for update only
grant update on table TEST_DATABASE.PUBLIC.TABLE_TEST_DATA to share SHARE_DATABASE;
--Message: Grant not executed: Operation not supported on a SHARE object.
Solution

  • Snowflake shared databases are "read only" - please take a peek at the documentation at this link for more details:

    https://docs.snowflake.com/en/user-guide/data-sharing-intro.html#introduction-to-secure-data-sharing