Filtrar tabela sensível, uso de dados, filtros de linha e máscaras de coluna

Visualização

Esse recurso está em visualização pública.

Este artigo fornece orientações e exemplos para usar filtros de linha, máscaras de coluna e tabelas de mapeamento para filtrar dados confidenciais em suas tabelas.

O que são filtros de linha?

Os filtros de linha permitem aplicar um filtro a uma tabela para que query subsequente retorne apenas linhas para as quais o predicado do filtro seja avaliado como verdadeiro. Um filtro de linha é implementado como uma função SQL definida pelo usuário (UDF).

Para criar um filtro de linha, primeiro escreva uma UDF SQL para definir a política de filtro e depois aplique-a a uma tabela com uma instrução ALTER TABLE . Como alternativa, você pode especificar um filtro de linha para uma tabela na instrução CREATE TABLE inicial. Cada tabela pode ter apenas um filtro de linha. Um filtro de linha aceita zero ou mais parâmetros de entrada, onde cada parâmetro de entrada é vinculado a uma coluna da tabela correspondente.

Qual é a diferença entre esses filtros e visualizações dinâmicas?

A visualização dinâmica é uma visualização abstrata e somente leitura de uma ou mais tabelas de origem. O usuário pode acessar a view dinâmica sem ter acesso direto às tabelas de origem. A criação de uma view dinâmica define um novo nome de tabela que não deve corresponder ao nome de nenhuma tabela de origem ou de outras tabelas e view presentes no mesmo esquema.

Por outro lado, associar um filtro de linha ou máscara de coluna a uma tabela de destino aplica a lógica correspondente diretamente à própria tabela, sem introduzir novos nomes de tabela. query subsequente pode continuar referindo-se diretamente à tabela de destino usando seu nome original.

Tanto view dinâmica quanto os filtros de linha e as máscaras de coluna permitem aplicar lógica complexa às tabelas e processar suas decisões de filtragem no tempo de execução query .

Use view dinâmica se precisar aplicar lógica de transformação, como filtros e máscaras, em tabelas somente leitura e se for aceitável que os usuários se refiram à view dinâmica usando nomes diferentes. Use filtros de linha e máscaras de coluna se quiser filtrar ou compute expressões sobre dados específicos, mas ainda fornecer aos usuários acesso às tabelas usando seus nomes originais.

Sintaxe de filtro de linha

Para criar um filtro de linha e adicioná-lo a uma tabela existente, use a seguinte sintaxe:

Crie o filtro de linha:

CREATE FUNCTION <function_name> (<parameter_name> <parameter_type>, ...)
RETURN {filter clause whose output must be a boolean};

Aplique o filtro de linha a uma tabela:

ALTER TABLE <table_name> SET ROW FILTER <function_name> ON (<column_name>, ...);

Remova um filtro de linha de uma tabela:

ALTER TABLE <table_name> DROP ROW FILTER;

Modifique um filtro de linha:

Run a DROP FUNCTION statement to drop the existing function, or use CREATE OR REPLACE FUNCTION to replace it.

Excluir um filtro de linha:

ALTER TABLE <table_name> DROP ROW FILTER;
DROP FUNCTION <function_name>;

Observação

Você deve executar o comando ALTER TABLE ... DROP ROW FILTER antes de eliminar a função ou a tabela ficará inacessível.

Se a tabela ficar inacessível dessa forma, altere a tabela e elimine a referência do filtro de linha órfã usando ALTER TABLE <table_name> DROP ROW FILTER;.

Exemplos de filtro de linha

Crie uma função SQL definida pelo usuário aplicada aos membros do grupo admin na região US.

Com esta função, os membros do grupo admin podem acessar todos os registros da tabela. Se a função for chamada por alguém que não seja administrador, a condição RETURN_IF falhará e a expressão region='US' será avaliada, filtrando a tabela para mostrar apenas registros na região US .

CREATE FUNCTION us_filter(region STRING)
RETURN IF(IS_ACCOUNT_GROUP_MEMBER('admin'), true, region='US');

Aplique a função a uma tabela como filtro de linha. query subsequente da tabela sales retorna um subconjunto de linhas.

CREATE TABLE sales (region STRING, id INT);
ALTER TABLE sales SET ROW FILTER us_filter ON (region);

Desative o filtro de linha. query futura do usuário na tabela sales retornará todas as linhas da tabela.

ALTER TABLE sales DROP ROW FILTER;

Crie uma tabela com a função aplicada como filtro de linha como parte da instrução CREATE TABLE. query futura da tabela sales, cada uma retornará um subconjunto de linhas.

CREATE TABLE sales (region STRING, id INT)
WITH ROW FILTER us_filter ON (region);

O que são máscaras de coluna?

As máscaras de coluna permitem aplicar uma função de mascaramento a uma coluna da tabela. A função de mascaramento é avaliada no tempo de execução query , substituindo cada referência da coluna de destino pelos resultados da função de mascaramento. Na maioria dos casos de uso, as máscaras de coluna determinam se o valor original da coluna deve ser retornado ou redigido com base na identidade do usuário que fez a chamada. Máscaras de coluna são expressões escritas como UDFs SQL.

Cada coluna da tabela pode opcionalmente ter uma função de mascaramento aplicada a ela. A função de mascaramento pega o valor não mascarado da coluna como entrada e retorna o valor mascarado como resultado. O valor de retorno da função de mascaramento deve ser do mesmo tipo da coluna que está sendo mascarada. A função de mascaramento também pode usar colunas adicionais como parâmetros de entrada e usá-las em sua lógica de mascaramento.

Para aplicar máscaras de coluna, crie uma função e aplique-a a uma coluna da tabela usando uma instrução ALTER TABLE . Alternativamente, você pode aplicar a função de mascaramento ao criar a tabela.

Sintaxe da máscara de coluna

Dentro da cláusula MASK , você pode usar qualquer uma das funções de runtime integradas do Databricks ou chamar outras funções definidas pelo usuário. Casos de uso comuns incluem a inspeção da identidade do usuário invocador que executa a função usando current_user( ) ou de quais grupos ele é membro usando is_account_group_member( ).

Crie uma máscara de coluna:

CREATE FUNCTION <function_name> (<parameter_name> <parameter_type>, ...)
RETURN {expression with the same type as the first parameter};

Aplique máscara de coluna a uma coluna em uma tabela existente:

ALTER TABLE <table_name> ALTER COLUMN <col_name> SET MASK <mask_func_name> [USING COLUMNS <additional_columns>];

Remova uma máscara de coluna de uma coluna em uma tabela:

ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;

Modifique uma máscara de coluna:

DROP a função existente ou use CREATE OR REPLACE TABLE.

Excluir uma máscara de coluna:

ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;
DROP FUNCTION <function_name>;

Observação

Você deve executar o comando ALTER TABLE antes de eliminar a função ou a tabela ficará inacessível.

Se a tabela ficar inacessível dessa forma, altere a tabela e elimine a referência de referência da máscara órfã usando ALTER TABLE <table_name> ALTER COLUMN <column where mask is applied> DROP MASK;.

Exemplos de máscaras de coluna

Neste exemplo, você cria uma função definida pelo usuário que mascara a coluna ssn para que somente os usuários membros do grupo HumanResourceDept possam view os valores nessa coluna.

CREATE FUNCTION ssn_mask(ssn STRING)
  RETURN CASE WHEN is_member('HumanResourceDept') THEN ssn ELSE '***-**-****' END;

Aplique a nova função a uma tabela como máscara de coluna. Você pode adicionar a máscara de coluna ao criar a tabela ou depois.

--Create the `users` table and apply the column mask in a single step:

CREATE TABLE users (
  name STRING,
  ssn STRING MASK ssn_mask);

--Create the `users` table and apply the column mask after:

CREATE TABLE users
  (name STRING, ssn STRING);

ALTER TABLE users ALTER COLUMN ssn SET MASK ssn_mask;

query nessa tabela agora retorna valores de coluna ssn mascarados quando o usuário que faz a consulta não é membro do grupo HumanResourceDept:

SELECT * FROM users;
  James  ***-**-****

Para desativar a máscara de coluna para que query retorne os valores originais na coluna ssn:

ALTER TABLE users ALTER COLUMN ssn DROP MASK;

Use tabelas de mapeamento para criar uma lista de controle de acesso

Para obter segurança em nível de linha, considere definir uma tabela de mapeamento (ou lista de controle de acesso). Cada tabela de mapeamento é uma tabela de mapeamento abrangente que codifica quais linhas de dados na tabela original são acessíveis a determinados usuários ou grupos. As tabelas de mapeamento são úteis porque oferecem integração simples com suas tabelas de fatos por meio de join direta.

Essa metodologia se mostra benéfica ao abordar muitos casos de uso com requisitos personalizados. Exemplos incluem:

  • Imposição de restrições com base no usuário log in , ao mesmo tempo em que acomoda regras diferentes para grupos de usuários específicos.

  • Criação de hierarquias complexas, como estruturas organizacionais, que exigem diversos conjuntos de regras.

  • Replicar modelos de segurança complexos de sistemas de origem externos.

Ao adotar tabelas de mapeamento dessa forma, você pode enfrentar com eficácia esses cenários desafiadores e garantir implementações robustas de segurança em nível de linha e coluna.

Exemplos de tabelas de mapeamento

Use uma tabela de mapeamento para verificar se o usuário atual está em uma lista:

USE CATALOG main;

Crie uma nova tabela de mapeamento:

DROP TABLE IF EXISTS valid_users;

CREATE TABLE valid_users(username string);
INSERT INTO valid_users
VALUES
  ('fred@databricks.com'),
  ('barney@databricks.com');

Crie um novo filtro:

Observação

Todos os filtros são executados com direitos de definidor, exceto funções que verificam o contexto do usuário (por exemplo, as funções CURRENT_USER e IS_MEMBER ) que são executadas como invocador.

Neste exemplo, a função verifica se o usuário atual está na tabela valid_users . Se o usuário for encontrado, a função retornará verdadeiro.

DROP FUNCTION IF EXISTS row_filter;

CREATE FUNCTION row_filter()
  RETURN EXISTS(
    SELECT 1 FROM valid_users v
    WHERE v.username = CURRENT_USER()
);

O exemplo abaixo aplica o filtro de linha durante a criação da tabela. Você também pode adicionar o filtro posteriormente usando uma instrução ALTER TABLE . Ao aplicar a uma tabela inteira, use a sintaxe ON () . Para uma linha específica, use ON (row);.

DROP TABLE IF EXISTS data_table;

CREATE TABLE data_table
  (x INT, y INT, z INT)
  WITH ROW FILTER row_filter ON ();

INSERT INTO data_table VALUES
  (1, 2, 3),
  (4, 5, 6),
  (7, 8, 9);

Selecione os dados da tabela. Isso só deverá retornar dados se o usuário estiver na tabela valid_users .

SELECT * FROM data_table;

Crie uma tabela de mapeamento composta por account que sempre deve ter acesso para view todas as linhas da tabela, independentemente dos valores das colunas:

CREATE TABLE valid_accounts(account string);
INSERT INTO valid_accounts
VALUES
  ('admin'),
  ('cstaff');

Agora crie uma UDF SQL que retorne true se os valores de todas as colunas na linha forem menores que cinco ou se o usuário chamador for membro da tabela de mapeamento acima.

CREATE FUNCTION row_filter_small_values (x INT, y INT, z INT)
  RETURN (x < 5 AND y < 5 AND z < 5)
  OR EXISTS(
    SELECT 1 FROM valid_accounts v
    WHERE IS_ACCOUNT_GROUP_MEMBER(v.account));

Por fim, aplique a UDF SQL à tabela como um filtro de linha:

ALTER TABLE data_table SET ROW FILTER row_filter_small_values ON (x, y, z);

Capacidade de suporte

  • Há suporte para cargas de trabalho do Databricks SQL e do Databricks Notebook para SQL.

  • Há suporte para o comando DML por usuários com privilégios MODIFY. Os filtros e as máscaras são aplicados aos dados lidos por UPDATEs e DELETEs e não são aplicados aos dados gravados (incluindo dados INSERIDOS).

  • Formatos compatíveis: Delta e Parquet. O Parquet é compatível apenas com tabelas gerenciáveis ou externas.

  • em tabelas com máscaras de coluna ou filtros de linha são suportados.

  • Os feeds de dados de alteração do Delta Lake são suportados desde que o esquema seja compatível com os filtros de linha e máscaras de coluna que podem ser aplicados à tabela de destino.

  • Tabelas estrangeiras são suportadas.

Limitações

  • As versões do Databricks Runtime abaixo da 12.2 LTS não oferecem suporte a filtros de linha ou máscaras de coluna. Esses tempos de execução falham com segurança, ou seja, se o senhor tentar acessar tabelas de versões não suportadas desses tempos de execução, nenhum dado será retornado.

  • view materializada e as tabelas de transmissão das Delta Live Tables não suportam filtros de linha ou máscaras de coluna.

  • As UDFs Python e Scala não são suportadas diretamente como funções de filtro de linha ou máscara de coluna. No entanto, é possível fazer referência a eles em UDFs SQL, desde que suas definições estejam permanentemente armazenadas no catálogo (em outras palavras, não sejam temporárias para a sessão).

  • Delta compartilhamento não funciona com segurança em nível de linha ou máscaras de coluna.

  • viagem do tempo não funciona com segurança em nível de linha ou máscaras de coluna.

  • A amostragem de tabela não funciona com segurança em nível de linha ou máscaras de coluna.

  • O acesso baseado em caminho a arquivos em tabelas com políticas não é suportado atualmente.

  • Políticas de filtro de linha ou máscara de coluna com dependências circulares em relação às políticas originais não são suportadas.

  • MERGE e clones superficiais não são suportados.

Limitação de clusters de usuário único

Não adicione filtros de linha ou máscaras de coluna a nenhuma tabela que você esteja acessando a partir de clusters de usuário único. Isto é normalmente feito no contexto de trabalhos do Databricks. Durante a visualização pública, você não poderá acessar a tabela de clusters de um único usuário depois que um filtro ou máscara for aplicado.