Hide tables from users upon creation in snowflake

Question

Can we hide tables from users upon creation in Snowflake?

Can we setup an access control rule using wildcards on table names? Ex: revoke access to users if table name like '%ETL_TRANSIT%'?

OR

When creating a table/view, do we have an option to choose "not visible to users"?

Only by putting the tables in databases/schemas that the users don’t have access to i.e. hiding the databases — NickW, Oct 11 '22 at 22:09
Who is creating the tables? Who are the users? Probably you can make this work, for the right definition of "users" and "creators" - using roles. — Felipe Hoffa, Oct 12 '22 at 03:26
creator is a service account, users are all the generic snowflake users through UI. In other words, can we able to hide certain tables/views not visible in the database object browser from the UI? — Alee, Oct 12 '22 at 13:10

score 0 · Answer 1 · answered Oct 12 '22 at 16:46

You will first have to be using a different role to create the tables than the role that will be viewing the tables. I would recommend reviewing the Role-based Access Controls in the documentation: Role Hierarchy and Privilege Inheritance

I'm going to use SYSADMIN as the role that creates objects, and a create a new role for viewing called VIEWER just as an example. If you have SYSADMIN access you can test this out yourself:

USE ROLE sysadmin;
CREATE OR REPLACE DATABASE demo_db; -- creating a new db just for demo purposes
CREATE OR REPLACE SCHEMA demo_schema;

USE ROLE securityadmin; -- Use the securityadmin or useradmin to create and manage roles
CREATE ROLE viewer;
GRANT ROLE viewer TO ROLE sysadmin; 

-- Now go check out the Roles page in Snowsight under Admin --> Users & Roles 
-- You should see viewer underneath sysadmin

USE ROLE viewer; -- You will see nothing because you don't have access to anything

USE ROLE securityadmin;
GRANT USAGE ON WAREHOUSE compute_wh TO ROLE viewer; -- viewer needs a WH to access data
GRANT USAGE ON DATABASE demo_db TO ROLE viewer;
GRANT USAGE ON SCHEMA demo_db.demo_schema TO ROLE viewer;

USE ROLE viewer; -- You will see the db and the schema now

-- Follow this for each new table you create:
USE ROLE sysadmin;
CREATE OR REPLACE TABLE demo_db.demo_schema.demo_tbl (col INT);

USE ROLE viewer; -- You will not see the new table

USE ROLE securityadmin;
GRANT SELECT ON demo_db.demo_schema.demo_tbl TO ROLE viewer;

USE ROLE viewer; -- You will now see the new table and be able to query it

Do NOT grant SELECT on FUTURE TABLES or FUTURE VIEWS in a schema though. IF you do that, the Viewer role will automatically see new tables as you create them. You will want to grant SELECT one at a time based on your requirements.

This is an answer to your second requirement. You could build out a Stored Procedure that could GRANT based on a like, but it would be a highly custom option that would need to run on a schedule or triggered manually.

score 0 · Answer 2 · answered Oct 12 '22 at 16:48

Unless you have future grants in place or a role is inheriting the role used to create the table, most roles do not automatically gain access to tables created by other roles. You would need to explicitly grant them permissions on the new table (and the table's associated schema/database) in order for the table to be "visible" to the role.

Hide tables from users upon creation in snowflake

2 Answers2