Sharing data with specific users

MotherDuck enables you to securely share data with specific users. Common scenarios include:

• Building data applications, in which each tenant should only have access to their own data.
• Sharing sensitive data within your Organization.
• Sharing data outside of your Organization.

Sharing data with individuals is easy. MotherDuck supports two approaches:

• Creating a share with Restricted access, limiting access to a list of specified users within your organization (known as an "ACL" or "Access Control List").
• Creating a Hidden share and providing individuals with the share URL.

Creating a share with restricted access (ACL)

Overview

1. Data provider creates a share with Restricted access.
2. Data provider (share owner) specifies which data consumers (users) can read from the share.
3. Data consumer attaches the share.
4. Data provider periodically updates the share to push new data to data consumers.

Anyone within your organization that is not included in the list will not be able to access the share, even if they have a share link.

UI

Click on the "trident" next to the database you'd like to share. Select "Share".

1. Optionally name the share.
2. Under "Who has access" choose "Specified users with the share link". Search for and add the users within your Organization that should have access to read the share.
3. Choose whether the share should be automatically updated or not. Default is MANUAL.
4. Create the share.
5. For the specified users, the share will appear in their UI under 'Shared with me' and can be attached.

SQL

use birds;
CREATE SHARE birds FROM birds
    (ACCESS RESTRICTED); -- This query creates a share accessible only by organization users specified with GRANT commands
GRANT READ ON SHARE birds TO duck1, duck2; -- Gives the users with usernames 'duck1' and 'duck2' access to the share 'birds'

Data consumer must ATTACH the restricted share before querying the share. See consuming restricted shares.

note

Restricted shares default to Discoverable visibility for users who have been granted access to the share. (Learn more about "Discoverable shares").

Consuming restricted shares

The data consumers in your Organization with access to the restricted share can use the UI or SQL to attach the share and start querying it.

UI

1. Select the restricted share you want to attach under "Shared with me"
2. Click "attach" and optionally name the resulting database.
3. You can now query the resulting database.

SQL

Run the ATTACH command to attach the share as a queryable database. This is a zero-cost metadata-only operation.

ATTACH md:_share/birds/e9ads7-dfr32-41b4-a230-bsadgfdg32tfa; -- Creates a zero-copy clone database called birds

Learn more about ATTACH SHARE.

Modifying share access

Data providers (share owners) can modify which users within your Organization have access to the share.

UI

1. Find the target share in the "Shares I've created" section of the Object Explorer, and choose the 'Alter' option from the context menu.
2. From here, you can add and remove users with access to the share.
3. You may also alter the share to use a different access scope. Learn more about share access scopes.

For more details on how to configure access controls for restricted shares, see the GRANT READ ON SHARE reference page.

SQL

GRANT READ ON SHARE birds TO duck3; -- Gives the user with username 'duck3' access to the share 'birds'

REVOKE READ ON SHARE birds FROM penguin; -- Revokes access to the share 'birds' from the user with username 'penguin'

For more details on configuring access controls for restricted shares, see the GRANT READ ON SHARE reference page.

Creating hidden shares

Overview

1. Data provider creates the share URL and passes this URL to the data consumer.
2. Data consumer attaches the share.
3. Data provider periodically updates the share to push new data to data consumers.

To share a database, first create a Hidden share. No actual data is copied and no additional costs are incurred in this process.

UI

Click on the "trident" next to the database you'd like to share. Select "share".

1. Optionally name the share.
2. To share the data with MotherDuck users inside or outside of your Organization, choose the "Anyone with the share link" option. This will enable anyone with the share link to attach and query the share, including users outside your Organization.
3. Create the share.
4. Copy the resulting ATTACH command to your clipboard and send it to your data consumers.

SQL

use birds;
CREATE SHARE birds FROM birds
    (ACCESS UNRESTRICTED , VISIBILITY HIDDEN); -- This query creates a Hidden share accessible by anyone with the share link, including users outside your Organization
> md:_share/birds/e9ads7-dfr32-41b4-a230-bsadgfdg32tfa

Save the returned share URL and pass it to data consumers.

Consuming hidden shares

The data consumer in your Organization can use SQL to attach the share and start querying it!

SQL

Run the ATTACH command to attach the share as a queryable database. This is a zero-cost metadata-only operation.

ATTACH md:_share/birds/e9ads7-dfr32-41b4-a230-bsadgfdg32tfa; -- Creates a zero-copy clone database called birds

Learn more about ATTACH SHARE.

Updating shared data

If during creation of the share, the data provider chose to have the share updated automatically, the share will be updated periodically. If the share was created with MANUAL updates, the data provider needs to manually update the share.

UPDATE SHARE birds; 

Learn more about UPDATE SHARE.