0

We need users to move their tables from their personal schemas (user_db.username) to the managed schema (userdb.groupname) which provides a predefined set of permissions for select access. In moving the table, we need to accomplish the following:

  1. Move the table out of the old schema
  2. Remove the old select grants
  3. Apply the new grants from the managed schema

I've reviewed the Alter table .. rename to.. documentation, and while that appears to enable movement of the table, it would retain the old grants and not apply the new ones.

Is there an option or another way to do this in Snowflake SQL?

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Lee_Str
  • 3,266
  • 2
  • 21
  • 32
  • Can you do the ALTER table option and then revoke/grant permissions right afterwards? I am guessing each user owns the table and are moving it so you could probably give each user a SQL script they an execute. Let me know if I am missing something in the requirements. – Suzy Lockwood Nov 22 '19 at 18:36
  • 3
    You may also want to consider creating a clone of the original table into the new schema. This clone will then inherit the ON FUTURE grants of the managed schema. Then, you can simply drop the original table. – Mike Walton Nov 22 '19 at 21:31
  • Suzy, we have hundreds of tables to move, so we are trying to minimize the # SQL statements. – Lee_Str Nov 25 '19 at 18:47
  • Mike, thanks! that will work beautifully. – Lee_Str Nov 25 '19 at 18:47

2 Answers2

2

You can remove the old select grants with a revoke query: https://docs.snowflake.net/manuals/sql-reference/sql/revoke-privilege.html

For Example:

revoke all privileges on all tables in schema mydb.myschema from role <roletoremove>;

Then, if it's helpful, you can set up Future grants in the target schema. This will cause any new table created in that schema to have these permissions: https://docs.snowflake.net/manuals/sql-reference/sql/grant-privilege.html

For Example:

grant select,insert on future tables in schema mydb.myschema to role <roletoadd>;

Then I would clone the tables from the old schema into the new one

create or replace mydatabase.newschema.table1 clone mydatabase.oldschema.table1

That way you can verify the permissions set up on the new tables, before dropping the old ones, and optionally removing the future grants so that it doesn't affect other new tables in the future.

David Garrison
  • 2,546
  • 15
  • 25
1

You can do that by: 

ALTER TABLE db.schema.table RENAME TO other_db.other_schema.table;

or

CREATE TABLE other_db.other_schema.table AS SELECT * FROM db.schema.table;

or 

CREATE TABLE other_db.other_schema.table CLONE db.schema.table;

The CLONE copies the security and permissions but the CTAS does not.

If you have a lot of tables, you could get a list of tables (SHOW TABLES;) then copy the output into Excel, concatenate the rename string and copy the completed string into a worksheet and select the All Queries box next to the Run button.

Scott Fair
  • 26
  • 2