Sharing datashares - Amazon Redshift
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Sharing datashares

You only need datashares when you are sharing data between different Amazon Redshift provisioned clusters or serverless workgroups. Within the same cluster, you can query another database using simple three-part notation database.schema.table as long as you have the required permissions on the objects in the other database.

Managing permissions for datashares in Amazon Redshift

As a producer cluster administrator, you retain control for the datasets you are sharing. You can add new objects to or remove them from the datashare. You can also grant or revoke access to datashares as a whole for the consumer clusters, Amazon accounts, or Amazon Regions. When permissions are revoked, consumer clusters immediately lose access to the shared objects and stop seeing them in the list of INBOUND datashares in SVV_DATASHARES.

The following example creates the datashare salesshare, adds the schema public, and adds the table public.tickit_sales_redshift to salesshare. It also grants usage permissions on salesshare to the specified cluster namespace.

CREATE DATASHARE salesshare; ALTER DATASHARE salesshare ADD SCHEMA public; ALTER DATASHARE salesshare ADD TABLE public.tickit_sales_redshift; GRANT USAGE ON DATASHARE salesshare TO NAMESPACE '13b8833d-17c6-4f16-8fe4-1a018f5ed00d';

For CREATE DATASHARE, superusers and database owners can create datashares. For more information, see CREATE DATASHARE. For ALTER DATASHARE, the owner of the datashare with the required permissions on the datashare objects to be added or removed can alter the datashare. For information, see ALTER DATASHARE.

As a producer administrator, when you drop a datashare, it stops being listed on consumer clusters. The databases and schema references created on the consumer cluster from the dropped datashare continue to exist with no objects in them. The consumer cluster administrator must delete these databases manually.

On the consumer side, a consumer cluster administrator can determine which users and roles should get access to the shared data by creating a database from the datashare. Depending on the options you choose when creating the database, you can control access to it as follows. For more information about creating a database from a datashare, see CREATE DATABASE.

Creating the database without the WITH PERMISSIONS clause

An administrator can control access at the database or schema level. To control access at the schema level, the administrator must create an external schema from the Amazon Redshift database created from the datashare.

The following example grants permissions to access a shared table at the database level and schema level.

GRANT USAGE ON DATABASE sales_db TO Bob; CREATE EXTERNAL SCHEMA sales_schema FROM REDSHIFT DATABASE sales_db; GRANT USAGE ON SCHEMA sales_schema TO ROLE Analyst_role;

To further restrict access, you can create views on top of shared objects, exposing only the necessary data. You can then use these views to give access to the users and roles.

Once the users are granted access to the database or schema, they will have access to all shared objects in that database or schema.

Creating the database with the WITH PERMISSIONS clause

After granting usage rights on the database or schema, an administrator can further control access using the same permission granting process as they would on a local database or schema. Without individual object permissions, users can’t access any objects in the datashared database or schema even after being granted the USAGE permission.

The following example grants permissions to access a shared table at the database level.

GRANT USAGE ON DATABASE sales_db TO Bob; GRANT USAGE FOR SCHEMAS IN DATABASE sales_db TO Bob; GRANT SELECT ON sales_db.public.tickit_sales_redshift TO Bob;

After being granted access to the database or schema, users still need to be given the relevant permissions for any objects in the database or schema that you want them to access.

Working with views in Amazon Redshift data sharing

A producer cluster can share regular, late-binding, and materialized views. When sharing regular or late-binding views, you don't have to share the base tables. The following table shows how views are supported with data sharing.

View name Can this view be added to a datashare? Can a consumer create this view on datashare objects across clusters?
Regular view Yes No
Late-binding view Yes Yes
Materialized view Yes Yes, but only with a complete refresh

The following query shows the output of a regular view that is supported with data sharing. For information about regular view definition, see CREATE VIEW.

SELECT * FROM tickit_db.public.myevent_regular_vw ORDER BY eventid LIMIT 5; eventid | eventname ----------+------------- 3835 | LeAnn Rimes 3967 | LeAnn Rimes 4856 | LeAnn Rimes 4948 | LeAnn Rimes 5131 | LeAnn Rimes

The following query shows the output of a late-binding view that is supported with data sharing. For information about late-binding view definition, see CREATE VIEW.

SELECT * FROM tickit_db.public.event_lbv ORDER BY eventid LIMIT 5; eventid | venueid | catid | dateid | eventname | starttime --------+---------+-------+--------+------------------------------+--------------------- 1 | 305 | 8 | 1851 | Gotterdammerung | 2008-01-25 14:30:00 2 | 306 | 8 | 2114 | Boris Godunov | 2008-10-15 20:00:00 3 | 302 | 8 | 1935 | Salome | 2008-04-19 14:30:00 4 | 309 | 8 | 2090 | La Cenerentola (Cinderella) | 2008-09-21 14:30:00 5 | 302 | 8 | 1982 | Il Trovatore | 2008-06-05 19:00:00

The following query shows the output of a materialized view that is supported with data sharing. For information about materialized view definition, see CREATE MATERIALIZED VIEW.

SELECT * FROM tickit_db.public.tickets_mv; catgroup | qtysold ----------+--------- Concerts | 195444 Shows | 149905

You can maintain common tables across all tenants in a producer cluster. You can also share subsets of data filtered by dimension columns, such as tenant_id (account_id or namespace_id), to consumer clusters. To do this, you can define a view on the base table with a filter on these ID columns, for example current_aws_account = tenant_id. On the consumer side, when you query the view, you see only the rows that qualify for your account. To do this, you can use the Amazon Redshift context functions current_aws_account and current_namespace.

The following query returns the account ID in which the current Amazon Redshift cluster resides. You can run this query if you are connected to Amazon Redshift.

select current_user, current_aws_account; current_user | current_aws_account -------------+-------------------- dwuser | 111111111111 (1row)

The following query returns the namespace of the current Amazon Redshift cluster. You can run this query if you are connected to the database.

select current_user, current_namespace; current_user | current_namespace -------------+-------------------------------------- dwuser | 86b5169f-01dc-4a6f-9fbb-e2e24359e9a8 (1 row)

Incremental refresh for materialized views in a datashare

Amazon Redshift supports incremental refresh for materialized views in a consumer datashare when the base tables are shared. Incremental refresh is an operation where Amazon Redshift identifies changes in the base table or tables that happened after the previous refresh and updates only the corresponding records in the materialized view. For more information about this behavior, see CREATE MATERIALIZED VIEW.

Managing access to data sharing API operations with IAM policies

To control the access to the data sharing API operations, use IAM action-based policies. For information about how to manage IAM policies, see Managing IAM policies in the IAM User Guide.

For information on the permissions required to use the data sharing API operations, see Permissions required to use the data sharing API operations in the Amazon Redshift Management Guide.

To make cross-account data sharing more secure, you can use a conditional key ConsumerIdentifier for the AuthorizeDataShare and DeauthorizeDataShare API operations. By doing this, you can explicitly control which Amazon Web Services accounts can make calls to the two API operations.

You can deny authorizing or deauthorizing data sharing for any consumer that isn't your own account. To do so, specify the Amazon Web Services account number in the IAM policy.

{ "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Deny", "Action": [ "redshift:AuthorizeDataShare", "redshift:DeauthorizeDataShare" ], "Resource": "*", "Condition": { "StringNotEquals": { "redshift:ConsumerIdentifier": "555555555555" } } } ] }

You can allow a producer with a DataShareArn testshare2 to explicitly share with a consumer with an Amazon Web Services account of 111122223333 in the IAM policy.

{ "Version": "2012-10-17", "Statement": [ { "Sid": "VisualEditor0", "Effect": "Allow", "Action": [ "redshift:AuthorizeDataShare", "redshift:DeauthorizeDataShare" ], "Resource": "arn:aws:redshift:us-east-1:666666666666:datashare:af06285e-8a45-4ee9-b598-648c218c8ff1/testshare2", "Condition": { "StringEquals": { "redshift:ConsumerIdentifier": "111122223333" } } } ] }