使用安全对象控制数据访问

To ensure sensitive data in a shared database is not exposed to users in consumer accounts, Snowflake strongly recommends sharing secure views and/or secure UDFs instead of directly sharing tables.

In addition, for optimal performance, especially when sharing data in extremely large tables, we recommend defining clustering keys on the base table(s) for your secure objects.

本主题介绍在基表中为共享安全对象使用群集密钥，并提供了与使用者账户共享安全视图的详细步骤说明。此外还提供了针对数据提供商和使用者的示例脚本。

Note

共享安全对象的说明与共享表的说明基本相同，只是增加了以下对象：

  • A “private” schema containing the base table and a “public” schema containing the secure object. Only the public schema and secure object are shared.
  • A “mapping table” (also in the “private” schema), which is only required if you wish to share the data in the base table with multiple consumer accounts and share specific rows in the table with specific accounts.

为共享数据使用群集密钥

在超大（即大小为数 TB）的表上，群集密钥可提供显著的查询性能优势。通过在共享安全视图或安全 UDFs 中使用的基表上定义一个或多个群集密钥，即可确保使用者账户中的用户在使用这些对象时不会受到负面影响。

When choosing the columns to use as the clustering key for a table, please note some important considerations.

设置和任务示例

These sample instructions assume a database named mydb exists in the data provider account and has two schemas, private and public. If the database and schemas do not exist, you should create them before proceeding.

第 1 步：在私有架构中创建数据和映射表

Create the following two tables in the mydb.private schema and populate them with data:

sensitive_data — contains the data to share, and an access_id column for controlling data access by account. sharing_access — uses the access_id column to map the shared data and the accounts that can access the data.

第 2 步：在公共架构中创建安全视图

Create the following secure view in the mydb.public schema:

paid_sensitive_data — displays data based on account.

Note that the access_id column from the base table (sensitive_data) does not need to be included in the view.

第 3 步：验证表和安全视图

验证表和安全视图，以确保按账户正确筛选数据。

To enable validating secure views that will be shared with other accounts, Snowflake provides a session parameter, SIMULATED_DATA_SHARING_CONSUMER. Set this session parameter to the name of the consumer account you wish to simulate access for. You can then query the view and see the results that a user in the consumer account will see.

第 4 步：创建共享

  1. Create a share.

要创建共享，必须使用 ACCOUNTADMIN 角色，或者获授全局 CREATE SHARE 权限的角色。要向共享授予对象，该角色还必须符合以下条件之一：

  • 具有共享数据库 OWNERSHIP 权限的角色。
  • A role with the USAGE privilege on the database WITH GRANT OPTION. For example: 
    GRANT USAGE ON <database-name> TO ROLE <role-name> WITH GRANT OPTION;

  1. Add the database (mydb), schema (public), and secure view (paid_sensitive_data) to the share. You can choose to either add privileges on these objects to a share via a database role, or grant privileges on the objects directly to the share. For more information on these options, see How to share database objects.

  2. Confirm the contents of the share. At the most basic level, you should use the SHOW GRANTS command to confirm the objects in the share have the necessary privileges.

    Note that the secure view paid_sensitive_data is displayed in the command output as a table.

  3. 向共享添加一个或多个账户。

脚本示例

以下脚本说明了如何执行上一节中介绍的所有任务：

  1. Create two tables in the ‘private’ schema and populate the first one with stock data from three different companies (Apple, Microsoft, and IBM). You will then populate the second one with data that maps the stock data to individual accounts:

    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. Create a secure view in the ‘public’ schema. This view filters the stock data from the first table by account, using the mapping information in the second table:

    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. 使用 ACCOUNTADMIN 角色创建共享。

    use role accountadmin;

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

show shares;

  4. Add the objects to the share. You can choose to either add privileges on these objects to a share via a database role (Option 1), or grant privileges on the objects directly to the share (Option 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. 将账户添加到共享。

    /* 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>;

脚本示例（面向使用者）

使用者可使用以下脚本来创建数据库（从上述脚本中创建的共享），并在所获得的数据库中查询安全视图：

  1. 通过从共享创建数据库，将共享数据库引入您的账户。

    /* 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. Grant privileges on the database to other roles in your account (e.g. CUSTOM_ROLE1). The GRANT statement differs depending on whether the data consumer added objects to the share using database roles (Option 1) or by granting privileges on the objects directly to the share (Option 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 the CUSTOM_ROLE1 role to query the view in the database you created. Note that there must be an active warehouse in use in the session to perform queries. In the USE WAREHOUSE command, replace <warehouse_name> with the name of one of the warehouses in your account. The CUSTOM_ROLE1 role must have the USAGE privilege on the warehouse:

    use role custom_role1;

show views;

use warehouse <warehouse_name>;

select * from paid_sensitive_data;