How do I prevent people from running SELECT * on Snowflake tables?

user_132438 0
Viewed 543

For compliance reasons we want to block SQL analysts from running SELECT * on a table. Instead, we want to force them to explicitly ask for the columns they want to select. How can I enforce this with Snowflake?

I saw a tip for SQL server using a calculated column, does Snowflake have an equivalent?

enter image description here

snowflake-cloud-data-platform sql
4 Answers

Sure, you can create tables with derived/computed columns in Snowflake:

create or replace table mytable (
    i number, s string
    , no_select_star number as (1/0));

Once that table has data, you won't be able to run select * on it, as the division by 0 is an invalid number:

enter image description here

You can also append a computed column to an existing table for the same effects:

alter table mytable2
add column no_select_star number as (1/0);

In action:

enter image description here

user_1324380

Why not use a row access policy, instead? It might take some tweaking, but you could create a row access policy similar to:

create or replace row access policy test_policy as (val varchar) returns boolean ->
  case
    when lower(current_statement()) like '%select%*%' 
  then false else true end;

Applying this policy to a table would not return any records if a select * was present in the query. You could apply this policy to every table and it wouldn't affect your schema in any way.

user_121789800

it's a workaround and by definition workarounds are not ideal solutions. What I'm wondering is if there's some way to get a message to the user that it's the select * that's causing the error. I tried a JS UDF that throws an error, but that can't be used as a default for a column.

It is possible to use truncation error to display custom message:

create or replace table mytable (
    i number, s string
    , no_select_star string as ('Code smell: SELECT * '::CHAR(1))
);
    
INSERT INTO mytable(i, s)  VALUES (1, 'a');

Query:

SELECT * FROM  mytable;

Output:

enter image description here

user_50708790

Here's an alternative approach.

  1. No dodgy columns

  2. No schema pollution

  3. Education message to user customisable

  4. Native Snowflake functionality

  5. Handles '*' in any position (select col , * ...)

  6. Does take more thought to set up -> must be applied to each column

  7. Can update ALL your tables with new error messages in one fell swoop

  8. Extendable/adaptable to include any other dodgy SQL your lovely users dream up

  9. You get to learn about Dynamic Masking which is super cool!

  10. To use in production you'd need to handle ALL datatypes -> would be nice if there was a Dynamic Mask for the entire table.

  11. Still runs the SQL in background but hopefully after the users get sick of seeing 'bad bad bad' they'll change their ways.

  12. Allows you to exempt SOME users (e.g. exempt yourself for select top 10 *)

    create or replace masking policy select_star_bad_bad_bad as (val string) 
returns string ->
case
   when REGEXP_COUNT(current_statement(),$$\*+$$) >0 then'select * = bad bad bad'
  else val
end;

alter table if exists mytable1 modify column s set masking policy select_star_bad_bad_bad;
user_13524900
Related