# Sharing data in MotherDuck


> MotherDuck data sharing model concepts including read-only shares and scope options.

MotherDuck's data sharing model has the following key characteristics:
- A share is created from a single MotherDuck database. By default it exposes the whole database, and [table-level security](./table-level-security.md) lets you scope it to specific tables and views by giving the share an include pattern. [Secure views](./secure-views.md) go further, filtering the rows and hiding or masking the columns of individual tables.
- Sharing is read-only.
- Sharing is done through **share** objects.
- You can make shares discoverable and queryable by every preset role in your [organization](../managing-organizations/managing-organizations.mdx) by granting access to the Explorer role.
- You can create restricted shares, where access is granted to specific preset roles or users.
- Alternatively, you can use hidden share URLs to limit access to specific people in your organization you share the URL with.
    - You can also configure the URL of a hidden share to be accessible by anyone with a MotherDuck account in the same cloud region as your Organization.

:::note
Shares are **region-scoped** based on your Organization's cloud region. Each MotherDuck Organization is scoped to a single cloud region that must be chosen at Org creation when signing up.

MotherDuck is available on AWS in six regions:
- **US East (N. Virginia):** `us-east-1`
- **US West (Oregon):** `us-west-2`
- **Europe (Frankfurt):** `eu-central-1`
- **Europe (Dublin):** `eu-west-1`
- **Asia Pacific (Tokyo):** `ap-northeast-1`
- **Asia Pacific (Sydney):** `ap-southeast-2`
:::

Sharing in MotherDuck works as follows:
1. The **data provider** shares their database in MotherDuck by creating a share.
2. The **data consumer** attaches said share, which creates a database clone in their workspace. The data consumer can now query this database.
3. The **data provider** periodically updates the share to push updates to the database to **data consumers**.

## Creating a share

The first step in sharing databases in MotherDuck is to create a share, which can be done in both UI and SQL. Creating a share does not incur additional costs, and no actual data is copied or transferred - creating a share is a zero-copy, metadata-only operation.

### UI

Select the trident next to the database you'd like to share, then select **Share**. On the **Create share** page:

![Create share page, with Access options and What to share set to Entire database](./img/ui-share_new.png)

1. Optionally, change the share name. The default is the database name.
2. Under **Who has access**, choose whether the share is accessible to your organization, specified roles or users, or any MotherDuck user in the same cloud region who has the share link.
3. Choose whether the share is **Discoverable** and **Auto-updating**. In the UI, auto-updating is on by default.
4. Under **What to share**, keep **Entire database**, or select **Custom** to choose tables and limit their rows and columns. See [table-level security](./table-level-security.md) and [secure views](./secure-views.md).
5. Select **Create share**.

### SQL

The following example creates a restricted share from database "birds" and grants it to the Explorer role:
- Share is also named "birds".
- Explorer, Builder, and Admin users can access the Share because preset roles are concentric.
- This Share is discoverable to users who have access.

```sql
use birds;
CREATE SHARE IF NOT EXISTS birds FROM birds
    (ACCESS RESTRICTED, VISIBILITY DISCOVERABLE, UPDATE AUTOMATIC);
GRANT READ ON SHARE birds TO ROLE explorer;
```

Learn more about the [CREATE SHARE](/sql-reference/motherduck-sql-reference/create-share.md) SQL command.

### Changing who has access

You can change a share's access and visibility after you create it. In the left sidebar, find the share under **Shares I've created**, select the trident next to it, then select **Alter**. Change **Who has access** or **Discoverable**, then save. The share URL stays the same, so consumers stay attached and Dives that use the share keep working.

When you change the access level, the list of users and roles is rebuilt from what you pick on that page. Include every user, role, and service account that still needs the share.

In SQL, [`ALTER SHARE`](/sql-reference/motherduck-sql-reference/alter-share/) changes only the include pattern and secure views. Changing access, visibility, or update mode in SQL means recreating the share with [`CREATE OR REPLACE SHARE`](/sql-reference/motherduck-sql-reference/create-share/), which gives it a new URL and drops its grants. Use the UI when consumers are attached.

### Scoping a share to specific tables

A share exposes every table and view in the source database unless you give it an include pattern. With one, consumers see only the tables and views the pattern names, and everything else is absent from their catalog:

```sql
CREATE SHARE sales_share FROM sales (
    INCLUDE_PATTERN 'reporting.*, main.orders',
    ACCESS RESTRICTED
);
```

In the UI, select **Custom** under **What to share** on the **Create share** page and pick the tables and views from the schema tree.

Because the pattern belongs to the share rather than to a grant, every consumer of one share sees the same tables and views. To serve two audiences different tables, create two shares over the same database with different patterns. See [table-level security](./table-level-security.md) for the pattern rules, the plan requirement, and the limitations.

### Filtering rows and masking columns

To share only some rows of a table, or to hide or mask some of its columns, add a secure view to the share. A secure view is a `SELECT` that consumers get in place of the table:

```sql
ALTER SHARE sales_share
    ADD SECURE VIEW REPLACES customers AS (
        SELECT id, name, region, 'XXX-XX-' || right(ssn, 4) AS ssn
        FROM customers
        WHERE region = 'EU'
    );
```

In the UI, select **Custom** under **What to share** on the share's create or alter page, then select **Configure** next to a table. Secure views are available on **Business** and **Enterprise** plans, including free trials. See [row-level and column-level security with secure views](./secure-views.md).

### Access types

A share's access type decides who can attach it. Its visibility decides who can find it in the UI without the share URL. The two are set separately.

| | Restricted | Organization | Unrestricted |
|---|---|---|---|
| UI label under **Who has access** | **Specified users or roles** | **Anyone in my organization** | **Anyone with the share link** |
| Who can attach | The owner, plus users and roles you `GRANT` | Every member of your organization | Any MotherDuck user whose organization is in the same cloud region |
| Effect of `GRANT` and `REVOKE` | Adds or removes access | None. Every member already has access | None. Every user already has access |
| Who can find it when **Discoverable** | Users with a grant | Every member of your organization | Every member of your organization. Users in other organizations need the URL |
| Intended for | Specific people, roles, or service accounts | Your whole organization (legacy) | Data you're willing to make public |

The Organization type is a legacy scope that is planned for deprecation. For organization-wide sharing, use a Restricted share with a role grant. Grant READ to Explorer for the whole organization, Builder for Builders and Admins, or Admin for Admins only. See [Roles and access control](/concepts/security-and-access/roles-and-access-control/#how-data-access-grants-flow).

`GRANT` and `REVOKE` don't return an error on Organization and Unrestricted shares, but they don't change who has access. To limit an Unrestricted share to specific users, recreate it as Restricted and grant access.

### Discoverable shares

A **Discoverable** share appears under **Shared with me** for users who can find it, as listed in the table above. A **Hidden** share doesn't appear anywhere, and users attach it with the share URL. Any access type can be Discoverable or Hidden.

:::caution
"Anyone with the share link" doesn't mean the share is hidden. In SQL, `VISIBILITY` defaults to `DISCOVERABLE` for every access type. The **Create share** page shows the **Discoverable** toggle only for **Anyone in my organization**. Shares you create with **Anyone with the share link** or **Specified users or roles** are saved as Discoverable. An Unrestricted share is therefore listed for every member of your organization, who can attach it without receiving the link.

To create an Unrestricted share that only people with the link can find, create it in SQL with `VISIBILITY HIDDEN`:

```sql
CREATE SHARE public_birds FROM birds (ACCESS UNRESTRICTED, VISIBILITY HIDDEN);
```

:::

### Share URLs

When you create a share, a URL for this share is generated:
- If the share is **Discoverable**, members of your Organization can find this share without the share URL. Alternatively, they can use the URL directly.
- If the share is **Hidden** (e.g. not Discoverable), other users will not be able to find the share URL. You will need to send this URL directly to the users with whom you want to share this data.

### When access is removed

A consumer loses access when you revoke their grant, drop the share, change its access type so it no longer covers them, or recreate it with `CREATE OR REPLACE SHARE`. Access isn't checked on every query. MotherDuck checks it when the consumer attaches the share and again each time their Duckling refreshes the share, which happens about once a minute.

Until that refresh, a consumer who already attached the share can keep querying it, including from a new connection. After the refresh, MotherDuck detaches the share from the consumer's account, and queries against it fail with a catalog error:

```text
Catalog Error: Table with name "orders" does not exist because schema "sales" does not exist.
```

Attaching the share URL again fails with the same error as a share that doesn't exist:

```text
Catalog Error: no database/share named "_share/sales/<share_id>" found
```

If the data must stop being readable at a precise moment, remove access, wait for the refresh, and confirm that the consumer's queries fail before you treat the data as protected. `REFRESH DATABASES` run by the consumer triggers the check immediately, but the owner can't force a consumer's refresh.

## Consuming shared data

The **data consumer** needs to attach the share to their workspace, thereby creating a read-only zero-copy clone of the source database. This is a free, metadata-only operation.

When you attach a share, it gets an alias that defaults to the source database name. If you already have a database with that name, the attach fails. Use `AS` to pick a different alias, or [detach](/key-tasks/database-operations/detach-and-reattach-motherduck-database/) the conflicting database first. See [share alias conflicts](/sql-reference/motherduck-sql-reference/attach/#share-alias-conflicts) for details.

### Views and fully-qualified table references

If the shared database contains views, those views may reference tables using fully-qualified paths that include the original database name. For example, a view in a database called `org_dwh` might reference `org_dwh.main.sales`.

When you attach the share, make sure the database alias matches the original database name. Otherwise, the views fail because they can't resolve the original database name in your namespace.

```sql
-- The share was created from a database called "org_dwh".
-- Views inside reference the tables as "org_dwh.main.<table_name>".

-- This will cause view errors because the alias doesn't match:
ATTACH 'md:_share/org_dwh/id_abc123' AS dwh;

-- Use the original database name as the alias:
ATTACH 'md:_share/org_dwh/id_abc123' AS org_dwh;
```

This applies to any object in the shared database that uses fully-qualified references, including views, macros, and stored procedures.

### Views that read cloud storage

A shared view runs with the consumer's identity and the consumer's secrets, not the owner's. A share never carries the owner's secrets. If a view in the shared database reads files directly, such as `read_parquet('s3://...')` or `read_parquet('gcs://...')`, each consumer needs their own [secret](/sql-reference/motherduck-sql-reference/create-secret/) with access to that bucket. Without one, the view works only when the bucket is publicly readable.

To share data that lives in object storage without giving consumers bucket credentials, load it into a table in the shared database, for example with `CREATE TABLE ... AS SELECT * FROM read_parquet(...)`. Consumers then read the table from the share and need no secret.

[Secure views](./secure-views.md) can't read files at all. A secure view that calls a table function such as `read_parquet` is rejected when you create it.

### Consuming discoverable shares

If the **data provider** created a Discoverable share you have access to, you should be able to find this share in the UI.

### UI

1. Select the share you want under "Shared with me".
2. Optionally roll over the share to see the tooltip that tells you the share owner, when it was last updated, and share access scope.
2. Click "attach".
3. You can query the resulting database.

### Consuming hidden shares

If the **data provider** created a Hidden (e.g. non-Discoverable) share, they need to pass the share URL to the **data consumer**. The **data consumer**, in turn, needs to attach the share URL.

```sql
ATTACH 'md:_share/ducks/0a9a026ec5a55946a9de39851087ed81' AS birds;   # attaches the share as database `birds`
```

## Updating shared data

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

```sql
UPDATE SHARE birds;
```

Learn more about [UPDATE SHARE](/sql-reference/motherduck-sql-reference/update-share.md) and [data replication timing and checkpoints](./updating-shares.md).

## Consuming updated data

By default, shares automatically update every minute. However, if you need the most up-to-date data sooner, the consumer can manually refresh the share after the producer executes UPDATE SHARE.

To manually refresh the data:

```sql
REFRESH DATABASES;       -- Refreshes all connected databases and shares
REFRESH DATABASE my_share;  -- Alternatively, refresh a specific database/share
```

Lean more about [REFRESH DATABASES](/sql-reference/motherduck-sql-reference/refresh-database.md).


---

## Docs feedback

MotherDuck accepts optional user-submitted feedback about this page at `GET https://motherduck.com/docs/api/feedback/agent`.
For agents and automated tools, feedback submission should be user-confirmed before sending.

URL-encode query parameter values and send a GET request:

```text
GET https://motherduck.com/docs/api/feedback/agent?page_path=%2Fkey-tasks%2Fsharing-data%2Fsharing-overview%2F&page_title=Sharing%20concepts%20and%20overview&text=<url-encoded user feedback, max 2000 characters>
```

Optionally append `&source=<url-encoded interface identifier>` such as `claude.ai` or `chatgpt`.

`page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": "<uuid>"}`, `400` for malformed query parameters, and `429` when rate-limited.
