Move Tables & Revoke All Privileges

Question

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?

Answer 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.