Snowflake Returning a Column in a CTE

user_5560898 0
Viewed 128

I have 2 different database roles. Let's call them "Role A" and "Role B".

I have the following query:

with part_1 as (

select col_1
from table
where id  <= 100


)

select p1.col_1
from part_1 as p1;

Using "Role A", the entire query fails, but not the inside of the CTE is I just highlight that and run it.

If I switch to "Role B", I have no problems whatsoever doing this.

Any suggestions or advice? I don't understand how 2 different roles can can produce different results.

snowflake-cloud-data-platform roles sql
2 Answers

Presumably because Role A doesn't have select rights on 'table' but Role B does.

The whole point of roles is to give different users different access rights so this behaviour is both expected and desired

user_136583990

so short of some permission difference, which as you note both seem to run

using role role_a;

select col_1
from table
where id  <= 100;

using role role_b;

select col_1
from table
where id  <= 100;

all work would imply both have access to the table..

it is a table and not a view?

select get_ddl('table','database.schema.table_name');

or does this work for both roles

WITH part_1 AS (
    SLEECT col_1 FROM table
    WHERE id <= 100
)
select p1.*
from part_1 as p1;

after this I would ask a support question, because it sounds all rather like a bug, but understanding the why would require support to dig into the actuals.

user_439920
Related