如何动态地将角色名称传递给IS_ROLE_IN_SESSION？

Question

我正在设置一个掩蔽策略，如果用户的当前角色继承自指定的角色，则可以绕过该策略。使用函数IS_ROLE_IN_SESSION可以很容易地做到这一点。挑战是，我希望能够更改指定的角色，而不必修改掩蔽策略。

这些示例假设用户使用的是ACCO对联than以外的角色。

我让它使用一个会话变量，但这并不安全，因为我无法控制对会话变量的访问：

create or replace table tab as select * from values('personal value') d (data);

set unmask_role = 'PUBLIC';
alter table tab modify column data unset masking policy;
create or replace masking policy hide as (d varchar) returns varchar -> 
    iff(is_role_in_session($unmask_role),d,replace(d,'personal value','hidden'));
alter table tab modify column data set masking policy hide;

set unmask_role = 'PUBLIC';
select * from tab;
-- Works as expected: shows personal value

set unmask_role = 'ACCOUNTADMIN';
select * from tab;
-- Works as expected: shows hidden

理想情况下，我可以在表中提供角色，因为我可以控制对表内容的访问，但无法通过这些错误：

create or replace table unmask_role_tab as select 'PUBLIC' role;

alter table tab modify column data unset masking policy;
create or replace masking policy hide as (d varchar) returns varchar ->
    iff(is_role_in_session((select role from unmask_role_tab)),d,replace(d,'personal value','hidden'));
alter table tab modify column data set masking policy hide;

select * from tab;
-- Fails with error:
-- SQL compilation error: error line Check Arg at position 0 invalid argument for function [IS_ROLE_IN_SESSION] unexpected argument [(SELECT UNMASK_ROLE_TAB.ROLE AS "ROLE" FROM UNMASK_ROLE_TAB AS UNMASK_ROLE_TAB)] at position 0,

alter table tab modify column data unset masking policy;
create or replace masking policy hide as (d varchar) returns varchar ->
    (select iff(is_role_in_session(role),d,replace(d,'personal value','hidden')) from unmask_role_tab);
alter table tab modify column data set masking policy hide;

select * from tab;
-- Fails with error:
-- SQL compilation error: error line Check Arg at position 0 invalid argument for function [IS_ROLE_IN_SESSION] unexpected argument [UNMASK_ROLE_TAB.ROLE] at position 0,

Answer 1

这是一个有趣的问题，因为它归结为如何传递一个需要string_literal的“非静态”值。

会议 is_role_in_session( '‘)

使用视图而不是表(如果必须添加新条目，则必须更新视图定义，而不更改掩蔽策略定义)：

create or replace table tab as select * from values('personal value') d (data);


CREATE OR REPLACE VIEW unmask_role_view
AS
SELECT 1 AS col WHERE IS_ROLE_IN_SESSION('PUBLIC')
-- UNION  SELECT 1 AS col WHERE IS_ROLE_IN_SESSION('...')  -- more entries
;

create or replace masking policy hide as (d varchar) returns varchar -> 
case when exists(SELECT 1 FROM unmask_role_view) then d
     else replace(d,'personal value','hidden')
end;

alter table tab modify column data set masking policy hide;

select * from tab;

需要定义所有应该访问数据的角色的解决方案。尽管显式地列出了角色，但它有一个优点，即。缺点之一是维护这个表。

create or replace table tab as select * from values('personal value') d (data);
create or replace table unmask_role_tab as select 'PUBLIC' role;

-- here we compare against CURRENT_ROLE 
-- so we need all roles that have access to masked data
create or replace masking policy hide as (d varchar) returns varchar -> 
case when exists(SELECT 1 FROM unmask_role_tab u WHERE u.role = CURRENT_ROLE()) then d
     else replace(d,'personal value','hidden')
end;

alter table tab modify column data set masking policy hide;

select * from tab;

创建掩蔽策略 如果不存在，则创建或替换掩蔽策略，因为(VAL )返回 -> 您可以使用：

• 条件表达式函数
• 上下文函数，
• 和UDF来编写SQL表达式。

尝试1:标准调用

SELECT IS_ROLE_IN_SESSION(u.role) FROM unmask_role_tab u;
-- SQL compilation error: error line Check Arg at position 0 invalid argument
-- for function [IS_ROLE_IN_SESSION] unexpected argument [U.ROLE] at position 0

SELECT IS_ROLE_IN_SESSION(u.role::STRING) FROM unmask_role_tab u;
-- SQL compilation error: error line Check Arg at position 0 invalid argument 
-- for function [IS_ROLE_IN_SESSION] unexpected argument [U.ROLE] at position 0

尝试2:创建UDF(执行build SQL不可用)

CREATE OR REPLACE FUNCTION role_check(role_name STRING)
RETURNS boolean
LANGUAGE JAVASCRIPT
AS
$$
   var res = snowflake.createStatement({sqlText: 'SELECT IS_ROLE_IN_SESSION(:1)'
                                      , binds:[ROLE_NAME]}).execute()
   res.next();
   return res.getColumnValue(1); 
$$;

SELECT role_check(u.role) FROM unmask_role_tab u;
-- JavaScript execution error: Uncaught ReferenceError: 
-- snowflake is not defined in ROLE_CHECK

尝试3 SQL UDF(与直接调用相同的错误)

CREATE OR REPLACE FUNCTION role_check(role_name STRING)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
   IS_ROLE_IN_SESSION(ROLE_NAME)
$$;

SELECT *, role_check(role) FROM unmask_role_tab;
-- SQL compilation error: error line Check Arg at position 0 invalid argument 
-- for function [IS_ROLE_IN_SESSION] unexpected argument [UNMASK_ROLE_TAB.ROLE] 

尝试4用户定义的存储过程：

CREATE OR REPLACE PROCEDURE role_check_proc(role_name STRING)
RETURNS boolean
LANGUAGE JAVASCRIPT
AS
$$
   var res = snowflake.createStatement({sqlText: 'SELECT IS_ROLE_IN_SESSION(:1)'
                                       ,binds:[ROLE_NAME]}).execute()
   res.next();
   return res.getColumnValue(1); 
$$;

CALL role_check_proc((SELECT role FROM unmask_role_tab));
-- TRUE
-- Works only if table contains single entry

它返回结果，但存储过程调用不能用于掩蔽策略/SQL查询调用。

用函数包装它们将无法工作，因为不可能从函数调用SP。

CREATE OR REPLACE FUNCTION role_check(role_name STRING)
RETURNS BOOLEAN
LANGUAGE SQL
AS $$
   CALL role_check_proc(ROLE_NAME::STRING)
$$;