Блог

У меня есть необработанная таблица, в которой есть столбец variant данных json. Есть несколько обычных представлений (не материализованных представлений), и таблицы создаются с использованием событий json из необработанной таблицы.

После применения политики маскирования с использованием UDF к столбцу variant необработанной таблицы, когда роль является bi_analyst , я обнаружил две проблемы:

1. Таблицы, производные от базовой таблицы, не маскируются с помощью bi_analyst роли;
2. Представления, полученные с использованием базовой таблицы, становятся пустыми с bi_analyst ролью;

Кто-нибудь знает, почему это произошло? эта функция динамической маскировки не поддерживает представления в базовой таблице?

Что я хотел бы сделать, так это замаскировать базовые данные, и все таблицы и представления, исходящие из них, также маскируются указанной ролью. С этими таблицами легко работать, поскольку я могу просто применить политику маскирования и к ним.

Однако я понятия не имею об этих представлениях. Как я могу по-прежнему получать доступ к представлениям с ролью, которая должна видеть данные, но не чувствительные столбцы?

UDF является:

 -- JavaScript UDF to mask pii data --
use role ACCOUNTADMIN;

CREATE OR REPLACE FUNCTION full_address_masking(V variant)
  RETURNS variant
  LANGUAGE JAVASCRIPT
  AS
  $$
    if ("detail" in V) {
        if ("latitude" in V.detail) {
            V.detail.latitude = "******";
        }
        if ("longitude" in V.detail) {
            V.detail.longitude = "******";
        }
        if ("customerAddress" in V.detail) {
            V.detail.customerAddress = "******";
        }
    }

    return V;
  $$;
  

Политика маскирования заключается:

 -- Create a masking policy using JavaScript UDF --
create or replace masking policy json_address_mask as (val variant) returns variant ->
    CASE
      WHEN current_role() IN ('ACCOUNTADMIN') THEN val
      WHEN current_role() IN ('BI_ANALYST') THEN full_address_masking(val)
    ELSE full_address_masking(val)
    END;
  

Команда sql для установки политики маскирования для необработанных данных является:

 -- Set masking policy --
use role ACCOUNTADMIN;
alter table DB.PUBLIC.RAW_DATA 
    modify column EVERYTHING 
    set masking policy json_address_mask;
  

Политика маскирования применяется к столбцу variant EVERYTHING , структура данных которого выглядит следующим образом:

   {
      "detail": {
        "customAddress": "******",
        "id": 1,
        "latitude": "******",
        "longitude": "******"
      },
      "source": "AAA"
    }
  

Производная таблица является:

 create or replace table DB.SCHEMA_A.TABLE_A
as 
select * from DB.PUBLIC.RAW_DATA 
where everything:source='AAA';

grant select on table DB.schema_A.table_A to role bi_analyst;
  

Представление является:

 create or replace  view DB.SCHEMA_A.VIEW_A  as ( 
select
everything:account::string as account,
everything:detail:latitude::float as detail_latitude,
everything:detail:longitude::float as detail_longitude,
from
DB.PUBLIC.RAW_DATA
where
everything:source::string = 'AAA'

grant select on view DB.SCHEMA_A.VIEW_A to role bi_analyst;
  

В результате RAW_DATA маскируется, TABLE_A вообще не маскируется, VIEW_A возвращает 0 строк при запросе данных с помощью BI_ANALYST роли.

# 1 — Когда вы создаете таблицу из таблицы, содержащей замаскированные данные, вы собираетесь получить данные, к которым роль, создающая новую таблицу, имеет доступ в замаскированной таблице. Итак, в вашем примере TABLE_A имеет демаскированные данные, потому что они были созданы ролью, которая имеет к ним доступ. Политика маскирования автоматически не применяется к новой таблице.

# 2 — Что касается # 2, я полагаю, ваша единственная проблема заключается в том, что JSON в вашем примере неверно сформирован, поэтому вы получаете значения NULL. Когда я исправил этот json на следующий, он отлично работает, используя ту же функцию и политику маскировки, которые вы опубликовали:

 {
"detail":{
"latitude": 132034034.00,
"longitude": 12393438583732,
"id": 1,
"customAddress" : "XXX Road, XXX city, UK"
},
"source": "AAA"
}
  

Результат маскировки:

 {
  "detail": {
    "customAddress": "XXX Road, XXX city, UK",
    "id": 1,
    "latitude": "******",
    "longitude": "******"
  },
  "source": "AAA"
}
  

Проблема с таблицами, которые не были замаскированы, хорошо объяснена @Mike в его ответе. Решением может быть просто создание производных таблиц с использованием роли, которая ограничена политикой маскирования.

Проблема представлений связана с типом маскируемого значения «******», которое является строковым типом, в то время как фактический тип полей latitude и longitude являются float . При создании представления я по-прежнему приводил поля latitude и longitude к типу float:

 create or replace  view DB.SCHEMA_A.VIEW_A  as ( 
select
everything:account::string as account,
everything:detail:latitude::float as detail_latitude,
everything:detail:longitude::float as detail_longitude,
from
DB.PUBLIC.RAW_DATA
where
everything:source::string = 'AAA'
  

Существует скрытая ошибка при приведении «******» к плавающему значению, но snowflake все равно продолжает создавать представление. Но когда я запрашиваю данные с помощью BI_ANALYST роли, она возвращает строку 0.

Итак, обходным путем является приведение этих полей к вариантному типу:

 create or replace  view DB.SCHEMA_A.VIEW_A  as ( 
select
everything:account::string as account,
everything:detail:latitude::variant as detail_latitude,
everything:detail:longitude::variant as detail_longitude,
from
DB.PUBLIC.RAW_DATA
where
everything:source::string = 'AAA'
  

Что не идеально, поскольку это полностью изменило определение представления, ни одна из ролей не может получить фактический тип данных с плавающей точкой / числом, даже включая accountadmin