How can I force Snowflake users to filter results from a table using a column value, like a date/timestamp?

Question

When discussing how to stop Snowflake users from running select * on a table, we got a related question: "If only there was a way to do something similar to enforce a where clause on the date/timestamp column"

So how I can force Snowflake users to use a range of dates when querying a table?

Answer 1

Note: Check a cleaner solution using table UDFS

---

One way to solve this is to create a view over the table. The first row of this view should be full of invalid computations, and a date/timestamp outside the normal range of dates:

create or replace table mytable3(i number, s string, d date);

insert into mytable3 values (1, 2, '2020-01-01');

create or replace secure view mytable3_view 
as
select 'you need to add a date filter'::int i, 'you need to add a date filter'::int s, '1-1-1'::date d
union all
select * 
from mytable3
;

Now whenever someone queries that view, it will throw a descriptive error whenever they don't filter out the inappropriate date:

select i, s
from mytable3_view
--where d > '2000-01-01'
;

-- Numeric value 'you need to add a date filter' is not recognized

Note that with a secure view we can manage permissions so analysts can only get data through the view, without giving them access to the underlying table.

Extra credits:

• Thanks to Lukasz for sharing a way to get more descriptive errors.
• Thanks to Mike for sharing a way of using row-level-security to analyze queries and force policies like these.

Answer 2

A cleaner alternative (as suggested on reddit): Create a SQL table function requiring the filtering parameters, and then returns the filtered table:

create or replace secure function table_within(since date, until date )
returns table(i number, s string, d date)
as $$
select i, s, d
from mytable3
where d between since and until
$$;

Then you can use it with select * from table(function_name(since, until)):

select * 
from table(table_within('2019-01-01'::date, '2021-01-01'::date))