Create function to encapsulate row level security using case statement

Question

I want to create a function to implement row level security in snowflake DW for reporting purpose. For this wanted to create a function keeping that logic inside and call it in select query.

But creating this function, throws error:

CREATE or replace FUNCTION derive_sbg()
returns NUMBER(1,0)
as $$ CASE
    when (select current_role()) = 'z_DEPARTMENT_A_DEVELOPER' and SBG   = 'A' then 1 
    when (select current_role()) = 'z_DEPARTMENT_B_DEVELOPER' and SBG   = 'B' then 1  
    when (select current_role()) = 'z_DEPARTMENT_C_DEVELOPER' and SBG   = 'C' then 1  
    when (select current_role()) = 'z_DEPARTMENT_D_DEVELOPER' and SBG   = 'D' then 1  
    when (select current_role()) = 'z_DEPARTMENT_E_DEVELOPER' and SBG   = 'E' then 1  
    when (select current_role()) = 'z_DEPARTMENT_ALL_DEVELOPER' and  SBG in ('A','B','C','D','E') then 1 
    ELSE 0 END $$;

Error: SQL compilation error: error line 2 at position 71 invalid identifier 'SBG'

NOTE: SBG is column in dev.corp_report.sales_info table

Though, when I run logic (function) directly in my select query as mentioned below, works PERFECT

select * from dev.corp_report.sales_info  WHERE 1 = 
CASE
when (select current_role()) = 'z_DEPARTMENT_A_DEVELOPER' and SBG   = 'A' then 1 
when (select current_role()) = 'z_DEPARTMENT_B_DEVELOPER' and SBG   = 'B' then 1  
when (select current_role()) = 'z_DEPARTMENT_C_DEVELOPER' and SBG   = 'C' then 1  
when (select current_role()) = 'z_DEPARTMENT_D_DEVELOPER' and SBG   = 'D' then 1  
when (select current_role()) = 'z_DEPARTMENT_E_DEVELOPER' and SBG   = 'E' then 1  
when (select current_role()) = 'z_DEPARTMENT_ALL_DEVELOPER' and  SBG in ('A','B','C','D','E') then 1 
ELSE 0 END; 

So I want to hide this logic inside a function and use it like this in my security views

select * 
    from dev.corp_report.sales_info 
    WHERE 1 = derive_sbg();

Your guidance would be greatly appreciated. -Regards

Answer 1

there's no table that can refer SBG on the function;

CREATE or replace FUNCTION derive_sbg() returns NUMBER(1,0) as $$ CASE when (select 
current_role()) = 'z_DEPARTMENT_A_DEVELOPER' and SBG = 'A' then 1 when (select 
current_role()) = 'z_DEPARTMENT_B_DEVELOPER' and SBG = 'B' then 1
when (select current_role()) = 'z_DEPARTMENT_C_DEVELOPER' and SBG = 'C' then 1
when (select current_role()) = 'z_DEPARTMENT_D_DEVELOPER' and SBG = 'D' then 1
when (select current_role()) = 'z_DEPARTMENT_E_DEVELOPER' and SBG = 'E' then 1
when (select current_role()) = 'z_DEPARTMENT_ALL_DEVELOPER' and SBG in 
('A','B','C','D','E') then 1 ELSE 0 END from dev.corp_report.sales_info $$