Question about CREATE TABLE ... CLONE {COPY GRANTS} behaviour

Question

I am new to Snowflake and I am confused about the CREATE TABLE ... CLONE COPY GRANTS explanations of the documentation and what I see when trying:

The CREATE TABLE … CLONE syntax includes the COPY GRANTS keywords, which affect a new table clone as follows: https://docs.snowflake.com/en/sql-reference/sql/create-clone.html [1]

If the COPY GRANTS keywords are used, then the new object clone does not inherit any explicit access privileges granted on the original table but does inherit any future grants defined for the object type in the schema (using the GRANT … TO ROLE … ON FUTURE syntax).
If the COPY GRANTS keywords are not used, then the new object inherits any explicit access privileges granted on the original table but does not inherit any future grants defined for the object type in the schema.

Object Cloning: https://docs.snowflake.com/en/sql-reference/sql/grant-privilege.html [2]

When a database is cloned, the schemas in the cloned database copy the future privileges from the source schemas. This maintains consistency with the regular object grants, in which the grants of the source object (i.e. database) are not copied to the clone, but the grants on all the children objects (i.e. schemas in the database) are copied to the clones.
When a schema is cloned, the future grants from the source schema are not copied to the clone.
When an object in a schema is cloned, any future grants defined for this object type in the schema are applied to the cloned object unless the COPY GRANTS option is specified in the CREATE statement for the clone operation; in that case, the new object retains the access permissions of the original object and does not inherit any future grants for objects of that type.

The link 1. of the documentation indicates that not using COPY GRANTS inherits the source table privileges but not the future ones and the link 2. (bold line) indicates that not using COPY GRANTS inherits the future privileges I am quite confused of the interpretation. Moreover I tried to clone a table with and without the COPY GRANTS option : with COPY GRANTS the initial privileges of the source table appears in the clone, without COPY GRANT there are no privileges (except the OWNER). But in the 2 cases future grants on the source table never inherits the clone table. Could someone help me in the understanding ? ???? Thanks a lot, Regards, Cyril

Gokhan Atil Gokhan Atil · Accepted Answer · 2021-03-21T16:03:59

It seems you found a bug on the documents. When you use COPY GRANTS, the cloned table will inherit any explicit access privileges granted on the original table.

create role test_role;

create table test_table (v varchar);

grant update on future tables in schema public to test_role;

grant select on test_table to role test_role;

show grants on test_table;

+-----------+------------+--------------+
| privilege | granted_on | grantee_name |
+-----------+------------+--------------+
| OWNERSHIP | TABLE      | ACCOUNTADMIN |
| SELECT    | TABLE      | TEST_ROLE    |
+-----------+------------+--------------+

create table clone_table_nocp clone test_table;

create table clone_table_yescp clone test_table copy grants;

show grants on clone_table_nocp; -- has future grants of schema

+-----------+--------------+
| privilege | grantee_name |
+-----------+--------------+
| OWNERSHIP | ACCOUNTADMIN |
| UPDATE    | TEST_ROLE    |
+-----------+--------------+

show grants on clone_table_withcp; -- inherited access privileges, but does not have future grants 

+-----------+--------------+
| privilege | grantee_name |
+-----------+--------------+
| OWNERSHIP | ACCOUNTADMIN |
| SELECT    | TEST_ROLE    |
+-----------+--------------+

I will contact with the documentation team to fix it.

Question about CREATE TABLE ... CLONE {COPY GRANTS} behaviour

1 Answers