Uso dos objetos seguros para controlar o acesso aos dados

Para garantir que os dados confidenciais em um banco de dados compartilhado não sejam expostos aos usuários em contas de consumidor, o Snowflake recomenda fortemente o compartilhamento de exibições seguras e/ou UDFs seguras em vez de compartilhar tabelas diretamente.

Além disso, para um ótimo desempenho, especialmente ao compartilhar dados em tabelas extremamente grandes, recomendamos definir chaves de clustering na(s) tabela(s) base(s) para seus objetos seguros.

Este tópico descreve o uso de chaves de clustering em tabelas base para objetos seguros compartilhados e fornece instruções passo a passo para compartilhar uma exibição segura com uma conta de consumidor. Ele fornece exemplos de scripts tanto para provedores de dados quanto para consumidores.

Nota

As instruções para compartilhar um objeto seguro são essencialmente as mesmas que compartilhar uma tabela, com a adição dos seguintes objetos:

  • Um esquema “privado” contendo a tabela base e um esquema “público” contendo o objeto seguro. Somente o esquema público e o objeto seguro são compartilhados.

  • Uma “tabela de mapeamento” (também no esquema “privado”), que só é necessária se você desejar compartilhar os dados na tabela base com várias contas de consumidor e compartilhar linhas específicas na tabela com contas específicas.

Uso de chaves de clustering para dados compartilhados

Em tabelas muito grandes (ou seja, multiterabytes), as chaves de clustering fornecem benefícios significativos de desempenho de consulta. Ao definir uma ou mais chaves de clustering nas tabelas base utilizadas em exibições seguras ou UDFs seguras compartilhadas, você garante que os usuários em suas contas de consumidor não sejam afetados negativamente ao usar esses objetos.

Ao escolher as colunas a serem usadas como chave de clustering para uma tabela, note algumas considerações importantes.

Exemplo de configuração e tarefas

Estas instruções de exemplo assumem que um banco de dados chamado mydb existe na conta do provedor de dados e tem dois esquemas, private e public. Se o banco de dados e os esquemas não existirem, você deve criá-los antes de prosseguir.

Etapa 1: criar dados e tabelas de mapeamento no esquema privado

Crie as duas tabelas a seguir no esquema mydb.private e preencha-as com dados:

sensitive_data — contém os dados para compartilhar, e uma coluna access_id para controlar o acesso aos dados por conta.
sharing_access — utiliza a coluna access_id para mapear os dados compartilhados e as contas que podem acessar os dados.

Etapa 2: criar uma exibição segura em esquema público

Crie a seguinte exibição segura no esquema mydb.public:

paid_sensitive_data — exibe dados baseados em conta.

Observe que a coluna access_id da tabela base (sensitive_data) não precisa ser incluída na visualização.

Etapa 3: validar tabelas e exibição segura

Valide as tabelas e a visualização segura para garantir que os dados sejam filtrados corretamente por conta.

Para permitir a validação de exibições seguras que serão compartilhadas com outras contas, o Snowflake fornece um parâmetro de sessão, SIMULATED_DATA_SHARING_CONSUMER. Defina este parâmetro de sessão para o nome da conta do consumidor para a qual você deseja simular o acesso. Você pode então consultar a exibição e ver os resultados que um usuário na conta do consumidor verá.

Etapa 4: criar um compartilhamento

  1. Crie um compartilhamento.

    Para criar um compartilhamento, você deve usar a função ACCOUNTADMIN ou uma função com o privilégio global CREATE SHARE. A função também deve ter um dos seguintes itens para conceder objetos ao compartilhamento:

    • Uma função com o privilégio OWNERSHIP no banco de dados compartilhado.

    • Uma função com o privilégio USAGE no banco de dados WITH GRANT OPTION. Por exemplo:

      GRANT USAGE ON <database-name> TO ROLE <role-name> WITH GRANT OPTION;

  2. Adicione o banco de dados (mydb), esquema (public) e exibição segura (paid_sensitive_data) ao compartilhamento. Você pode optar por adicionar privilégios nestes objetos a um compartilhamento por uma função de banco de dados ou conceder privilégios nos objetos diretamente ao compartilhamento. Para obter mais informações sobre estas opções, consulte Como compartilhar objetos de banco de dados.

  3. Confirme o conteúdo do compartilhamento. No nível mais básico, você deve usar o comando SHOW GRANTS para confirmar que os objetos no compartilhamento têm os privilégios necessários.

    Observe que a exibição segura paid_sensitive_data é exibida na saída do comando como uma tabela.

  4. Adicione uma ou mais contas ao compartilhamento.

Exemplo de script

O seguinte script ilustra a execução de todas as tarefas descritas na seção anterior:

  1. Crie duas tabelas no esquema “privado” e preencha a primeira com dados de estoque de três empresas diferentes (Apple, Microsoft e IBM). Você então preencherá o segundo com dados que mapeiam os dados do estoque para contas individuais:

    use role sysadmin;

create or replace table mydb.private.sensitive_data (
    name string,
    date date,
    time time(9),
    bid_price float,
    ask_price float,
    bid_size int,
    ask_size int,
    access_id string /* granularity for access */ )
    cluster by (date);

insert into mydb.private.sensitive_data
    values('AAPL',dateadd(day,  -1,current_date()), '10:00:00', 116.5, 116.6, 10, 10, 'STOCK_GROUP_1'),
          ('AAPL',dateadd(month,-2,current_date()), '10:00:00', 116.5, 116.6, 10, 10, 'STOCK_GROUP_1'),
          ('MSFT',dateadd(day,  -1,current_date()), '10:00:00',  58.0,  58.9, 20, 25, 'STOCK_GROUP_1'),
          ('MSFT',dateadd(month,-2,current_date()), '10:00:00',  58.0,  58.9, 20, 25, 'STOCK_GROUP_1'),
          ('IBM', dateadd(day,  -1,current_date()), '11:00:00', 175.2, 175.4, 30, 15, 'STOCK_GROUP_2'),
          ('IBM', dateadd(month,-2,current_date()), '11:00:00', 175.2, 175.4, 30, 15, 'STOCK_GROUP_2');

create or replace table mydb.private.sharing_access (
  access_id string,
  snowflake_account string
);


/* In the first insert, CURRENT_ACCOUNT() gives your account access to the AAPL and MSFT data.       */

insert into mydb.private.sharing_access values('STOCK_GROUP_1', CURRENT_ACCOUNT());


/* In the second insert, replace <consumer_account> with an account name; this account will have     */
/* access to IBM data only. Note that account names are case-sensitive and must be in uppercase      */
/* enclosed in single-quotes, e.g.                                                                   */
/*                                                                                                   */
/*      insert into mydb.private.sharing_access values('STOCK_GROUP_2', 'ACCT1')                */
/*                                                                                                   */
/* To share the IBM data with multiple accounts, repeat the second insert for each account.          */

insert into mydb.private.sharing_access values('STOCK_GROUP_2', '<consumer_account>');

  2. Crie uma exibição segura no esquema “público”. Esta exibição filtra os dados de estoque da primeira tabela por conta, usando as informações de mapeamento na segunda tabela:

    create or replace secure view mydb.public.paid_sensitive_data as
    select name, date, time, bid_price, ask_price, bid_size, ask_size
    from mydb.private.sensitive_data sd
    join mydb.private.sharing_access sa on sd.access_id = sa.access_id
    and sa.snowflake_account = current_account();

grant select on mydb.public.paid_sensitive_data to public;


/* Test the table and secure view by first querying the data as the provider account. */

select count(*) from mydb.private.sensitive_data;

select * from mydb.private.sensitive_data;

select count(*) from mydb.public.paid_sensitive_data;

select * from mydb.public.paid_sensitive_data;

select * from mydb.public.paid_sensitive_data where name = 'AAPL';


/* Next, test the secure view by querying the data as a simulated consumer account. You specify the  */
/* account to simulate using the SIMULATED_DATA_SHARING_CONSUMER session parameter.                  */
/*                                                                                                   */
/* In the ALTER command, replace <consumer_account> with one of the accounts you specified in the    */
/* mapping table. Note that the account name is not case-sensitive and does not need to be enclosed  */
/* in single-quotes, e.g.                                                                            */
/*                                                                                                   */
/*      alter session set simulated_data_sharing_consumer=acct1;                                     */

alter session set simulated_data_sharing_consumer=<account_name>;

select * from mydb.public.paid_sensitive_data;

  3. Crie um compartilhamento usando a função ACCOUNTADMIN.

    use role accountadmin;

create or replace share mydb_shared
  comment = 'Example of using Secure Data Sharing with secure views';

show shares;

  4. Adicione os objetos ao compartilhamento. Você pode optar por adicionar privilégios nestes objetos a um compartilhamento por uma função de banco de dados (Opção 1) ou conceder privilégios nos objetos diretamente ao compartilhamento (Opção 2):

    /* Option 1: Create a database role, grant privileges on the objects to the database role, and then grant the database role to the share */

create database role mydb.dr1;

grant usage on database mydb to database role mydb.dr1;

grant usage on schema mydb.public to database role mydb.dr1;

grant select on mydb.public.paid_sensitive_data to database role mydb.dr1;

grant usage on database mydb to share mydb_shared;

grant database role mydb.dr1 to share mydb_shared;


/* Option 2: Grant privileges on the database objects to include in the share.  */

grant usage on database mydb to share mydb_shared;

grant usage on schema mydb.public to share mydb_shared;

grant select on mydb.public.paid_sensitive_data to share mydb_shared;


/*  Confirm the contents of the share. */

show grants to share mydb_shared;

  5. Adicionar contas ao compartilhamento.

    /* In the alter statement, replace <consumer_accounts> with the  */
/* consumer account(s) you assigned to STOCK_GROUP2 earlier,     */
/* with each account name separated by commas, e.g.              */
/*                                                               */
/*    alter share mydb_shared set accounts = acct1, acct2;       */

alter share mydb_shared set accounts = <consumer_accounts>;

Exemplo de script (para consumidores)

O seguinte script pode ser usado pelos consumidores para criar um banco de dados (a partir do compartilhamento criado no script acima) e consultar a exibição segura no banco de dados resultante:

  1. Traga o banco de dados compartilhado para sua conta, criando um banco de dados a partir do compartilhamento.

    /* In the following commands, the share name must be fully qualified by replacing     */
/* <provider_account> with the name of the account that provided the share, e.g.      */
/*                                                                                    */
/*    desc prvdr1.mydb_shared;                                                        */

use role accountadmin;

show shares;

desc share <provider_account>.mydb_shared;

create database mydb_shared1 from share <provider_account>.mydb_shared;

  2. Conceda privilégios no banco de dados para outras funções em sua conta (por exemplo, CUSTOM_ROLE1). A instrução GRANT difere dependendo se os dados que o consumidor adicionou objetos à ação usando funções de banco de dados (Opção 1) ou concedendo privilégios nos objetos diretamente ao compartilhamento (Opção 2):

    /* Option 1 */
grant database role mydb_shared1.db1 to role custom_role1;

/* Option 2 */
grant imported privileges on database mydb_shared1 to custom_role1;

  3. Use a função CUSTOM_ROLE1 para consultar a exibição no banco de dados que você criou. Observe que deve haver um warehouse ativo em uso na sessão para realizar consultas. No comando USE WAREHOUSE, substitua <nome_warehouse> pelo nome de um dos warehouses em sua conta. A função CUSTOM_ROLE1 deve ter o privilégio USAGE no warehouse:

    use role custom_role1;

show views;

use warehouse <warehouse_name>;

select * from paid_sensitive_data;