雪花掩码策略：输入可以是常量字符串变量吗？ Snowflake掩码策略：输入是否可以为常量字符串变量？

Question

我正在尝试创建一个带有标记的遮盖策略：

CREATE OR REPLACE MASKING POLICY TAGS_MASKING
AS (val VARCHAR, col_name STRING) RETURNS VARCHAR ->
CASE        
   WHEN CURRENT_ROLE() IN ('ADMIN_ROLE') THEN val
   WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') AND (SELECT SYSTEM$GET_TAG('TAG_NAME', col_name , 'COLUMN') = 'PUBLIC') THEN val
   WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') AND (SELECT SYSTEM$GET_TAG('TAG_NAME', col_name , 'COLUMN') IN ('PROTECTED')) THEN '****MASKED****'
 END; 

这里，col_name是一个字符串（例如 'mytable.col1'），这样我就可以将此掩码策略分配给任何我想要的列。但当我使用以下查询将其分配给一个表的一列时，它失败了：

ALTER TABLE IF EXISTS db.masking.mytable MODIFY COLUMN col1
SET MASKING POLICY TAGS_MASKING using (col1, 'mytable.col1');

错误信息如下：

语法错误：意外的"mytable.col1"

请问我该如何解决这个问题呢？谢谢！

- Yanli Dong

Answer 1

我还没有找到一种将列名参数化（作为可选的第二个参数传递）的方法，因此我采用了另一种方法。

使用Snowflake脚本自动创建每个列的掩码策略。

设置：

CREATE OR REPLACE TAG TAG_NAME;

CREATE OR REPLACE TABLE mytable(col1 STRING); 
ALTER TABLE mytable SET TAG TAG_NAME='PUBLIC';

INSERT INTO mytable(col1) VALUES ('Test');

SELECT * FROM mytable;
-- Test

操作步骤：

 CREATE OR REPLACE PROCEDURE test(schema_name STRING, tab_name STRING, col_name STRING)
 RETURNS STRING
 LANGUAGE SQL
 AS
 $$
 DECLARE 
   sql_masking_policy   STRING;
   sql_alter_table      STRING;
   masking_policy_name  STRING := CONCAT_WS('_', 'TAGS_MASKING_', SCHEMA_NAME, TAB_NAME, COL_NAME);
 BEGIN
 sql_masking_policy := '
 CREATE OR REPLACE MASKING POLICY <masking_policy_name>
 AS (val VARCHAR) RETURNS VARCHAR ->
 CASE        
    WHEN CURRENT_ROLE() IN (''ADMIN_ROLE'') THEN val
    WHEN CURRENT_ROLE() IN (''ANALYST_ROLE'') AND (SYSTEM$GET_TAG(''TAG_NAME'', ''<col_name>'', ''COLUMN'') = ''PUBLIC'') THEN val
    WHEN CURRENT_ROLE() IN (''ANALYST_ROLE'') AND (SYSTEM$GET_TAG(''TAG_NAME'', ''<col_name>'', ''COLUMN'') IN (''PROTECTED'')) THEN ''****MASKED****''
 END;';
                             
sql_alter_table := 'ALTER TABLE IF EXISTS <tab_name> MODIFY COLUMN <col_name>
SET MASKING POLICY <masking_policy_name>;';

sql_masking_policy := REPLACE(sql_masking_policy, '<masking_policy_name>', :masking_policy_name);
sql_masking_policy := REPLACE(sql_masking_policy, '<col_name>', CONCAT_WS('.', schema_name, tab_name, col_name));
sql_alter_table    := REPLACE(sql_alter_table,    '<masking_policy_name>', :masking_policy_name);
sql_alter_table    := REPLACE(sql_alter_table,    '<tab_name>', CONCAT_WS('.', schema_name, tab_name));
sql_alter_table    := REPLACE(sql_alter_table,    '<col_name>', col_name);

EXECUTE IMMEDIATE :sql_masking_policy;
EXECUTE IMMEDIATE :sql_alter_table;
    
RETURN sql_masking_policy || CHR(10) || sql_alter_table;
END;
$$;

呼叫：

CALL test('public', 'mytable', 'col1');

输出：

CREATE OR REPLACE MASKING POLICY TAGS_MASKING__public_mytable_col1
AS (val VARCHAR) RETURNS VARCHAR ->                 
CASE                         
WHEN CURRENT_ROLE() IN ('ADMIN_ROLE') THEN val                 
WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') AND (SYSTEM$GET_TAG('TAG_NAME', 'public.mytable.col1', 'COLUMN') = 'PUBLIC') THEN val
WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') AND (SYSTEM$GET_TAG('TAG_NAME', 'public.mytable.col1', 'COLUMN') IN ('PROTECTED')) THEN '****MASKED****'
END; 
  
ALTER TABLE IF EXISTS public.mytable MODIFY COLUMN col1 SET MASKING POLICY TAGS_MASKING__public_mytable_col1; 

检查：

SHOW MASKING POLICIES;

输出:

使用POLICY_CONTEXT测试select语句:

execute using policy_context(current_role => 'PUBLIC')
AS
SELECT * FROM public.mytable;
-- NULL

execute using policy_context(current_role => 'ADMIN_ROLE')
AS
SELECT * FROM public.mytable;
-- Test

execute using policy_context(current_role => 'ANALYST_ROLE')
AS
SELECT * FROM public.mytable;
-- Test

ALTER TABLE mytable SET TAG TAG_NAME='PROTECTED';

execute using policy_context(current_role => 'ANALYST_ROLE')
AS
SELECT * FROM public.mytable;
-- ****MASKED****

- Lukasz Szozda

---

谢谢Lukasz。你的回答非常有帮助，解答了我的问题。 - Yanli Dong

Answer 2

使用新功能标签掩码策略可以改进之前的回答：

标签掩码策略结合了对象标记和掩码策略功能，允许使用ALTER TAG命令在标记上设置掩码策略。当掩码策略签名中的数据类型与列的数据类型匹配时，标记的列会自动受到掩码策略中的条件保护。这简化了数据保护工作，因为不再需要手动为应该受到保护的列应用掩码策略来保护数据。列可以通过直接分配给列的掩码策略和基于标记的掩码策略来保护。

SYSTEM$GET_TAG_ON_CURRENT_COLUMN：

SYSTEM$GET_TAG_ON_CURRENT_COLUMN( '<tag_name>' )

Returns the tag string value assigned to the column based upon the specified tag or NULL if a tag is not assigned to the specified column.

---

针对这种情况：

CREATE OR REPLACE TAG TAG_NAME;

CREATE OR REPLACE MASKING POLICY TAGS_MASKING
AS (val VARCHAR) RETURNS VARCHAR ->
CASE        
   WHEN CURRENT_ROLE() IN ('ADMIN_ROLE') THEN val
   WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') 
    AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('TAG_NAME') = 'PUBLIC' THEN val
   WHEN CURRENT_ROLE() IN ('ANALYST_ROLE') 
    AND SYSTEM$GET_TAG_ON_CURRENT_COLUMN('TAG_NAME') IN ('PROTECTED') THEN '****MASKED****'
 END; 

ALTER TAG TAG_NAME SET MASKING POLICY TAGS_MASKING;

表格：

CREATE OR REPLACE TABLE mytable(col1 STRING); 
INSERT INTO mytable(col1) VALUES ('Test');
SELECT * FROM mytable;
-- COL1
-- Test

标签的分配：

ALTER TABLE mytable ALTER COLUMN col1 SET TAG TAG_NAME='PROTECTED';

ADMIN_ROLE/ANALYST_ROLE之外的角色：

SELECT * FROM mytable;
-- COL1
-- null

切换到 ANALYST_ROLE（列带有PROTECTED值）：

USE ROLE ANALYST_ROLE;

SELECT * FROM mytable;
-- COL1
-- ****MASKED****

- Lukasz Szozda

---

谢谢Lukasz。您的答案更为简明扼要，我喜欢它。 - Yanli Dong