# ALTER SHARE


> Change which tables a Share exposes, and add, replace, or drop the secure views that filter its rows and columns.

The `ALTER SHARE` statement changes what a Share you own exposes. It does two things:

- Sets or clears the include pattern, which controls which tables and views the Share exposes. See [table-level security](/key-tasks/sharing-data/table-level-security/).
- Adds, replaces, or drops secure views, which filter the rows and columns a table returns. See [row-level and column-level security with secure views](/key-tasks/sharing-data/secure-views/).

Those are the only changes `ALTER SHARE` makes. To publish new data to a Share, use [`UPDATE SHARE`](update-share.md). To change a Share's access, visibility, or update mode, recreate it with [`CREATE OR REPLACE SHARE`](create-share.md).

:::note
Only the owner of a Share can alter it. Altering a Share owned by another account fails as though the Share doesn't exist.
:::

## Syntax

```sql
ALTER SHARE [ IF EXISTS ] <share name> SET INCLUDE_PATTERN '<pattern list>';
ALTER SHARE [ IF EXISTS ] <share name> RESET INCLUDE_PATTERN;

ALTER SHARE [ IF EXISTS ] <share name> <secure view action> [, <secure view action> ...];

<secure view action> ::=
      ADD [ OR REPLACE ] SECURE VIEW [ REPLACES ] [<schema>.]<name> AS ( <select statement> )
    | DROP SECURE VIEW [ IF EXISTS ] [<schema>.]<name>
```

An include-pattern change and secure-view actions can't be combined in one statement.

## Parameters

| Parameter | Description |
| --- | --- |
| `<share name>` | The name of a Share you own. Without `IF EXISTS`, a Share that doesn't exist raises an error |
| `IF EXISTS` | Makes the statement a no-op when the Share doesn't exist, for both `SET` and `RESET` |
| `<pattern list>` | A comma-separated list of `schema.table` patterns. See the [`INCLUDE_PATTERN` clause](include-pattern.md) |

`SET` and `RESET` produce three distinct states:

| Statement | Stored value | Effect |
| --- | --- | --- |
| `SET INCLUDE_PATTERN 'reporting.*'` | `[reporting.*]` | The Share exposes what the patterns match |
| `SET INCLUDE_PATTERN ''` | `[]` | The Share exposes nothing. Only the default schema remains |
| `RESET INCLUDE_PATTERN` | `NULL` | The Share exposes the whole database |

Read the stored value back from the `INCLUDE_PATTERN` column of [`LIST SHARES`](list-shares.md) or [`MD_INFORMATION_SCHEMA.OWNED_SHARES`](md_information_schema/owned_shares.md).

`SET INCLUDE_PATTERN` validates every pattern against the source database's **live** catalog, and the check is all-or-nothing: if one pattern in the list matches nothing, the statement fails and the stored pattern is left unchanged. See [validation](include-pattern.md#validation) for the rules that decide whether a pattern matches.

## Secure views

A secure view is a `SELECT` over the shared database that the Share returns in place of a table. Use it to filter rows with `WHERE`, hide columns by leaving them out, and mask columns with an expression.

| Element | Description |
| --- | --- |
| `ADD SECURE VIEW` | Adds a secure view. Fails if the Share already has a secure view of that name |
| `ADD OR REPLACE SECURE VIEW` | Adds a secure view, or replaces the definition of an existing one |
| `DROP SECURE VIEW [ IF EXISTS ]` | Removes a secure view. With `REPLACES`, the underlying table is exposed again |
| `REPLACES` | The secure view stands in for the existing table or view of the same name, so consumers keep querying that name. Without it, the secure view is a new table in the Share, and the name must not already exist |
| `<schema>.<name>` | The table to replace, or the name of the new table. An unqualified name uses the default schema. It can't include a database name |
| `<select statement>` | One `SELECT` over the shared database, in parentheses. See [rules for a secure view](/key-tasks/sharing-data/secure-views/#rules-for-a-secure-view) |

Each secure view needs a name that's unique in the Share. A table can be replaced by only one secure view per Share, but any number of secure views without `REPLACES` can read from it, for example `customers_eu` and `customers_us` over the same `customers` table.

Several actions in one statement apply all-or-nothing. Adding a secure view requires a Business or Enterprise plan, including free trials; dropping one is always allowed.

To list a Share's secure views, run `SHOW SECURE VIEWS ON SHARE <share name>`. It returns `schema_name`, `view_name`, `sql`, `replaces_table`, and `columns`, and only works for the Share's owner.

## Propagation

An edit to the include pattern or secure views reaches an already-attached consumer on the next update cycle, within a minute or two. Re-attaching the Share picks it up immediately. This includes your own held-open connection, since the owner gets no fast path. The Share URL doesn't change, so an edit never forces consumers to re-attach.

## Example usage

```sql
-- Restrict an existing share to one schema.
ALTER SHARE sales_share SET INCLUDE_PATTERN 'reporting.*';

-- Expose several patterns; they are stored as a list, in order.
ALTER SHARE sales_share SET INCLUDE_PATTERN 'reporting.*, finance.salaries';

-- Expose nothing.
ALTER SHARE sales_share SET INCLUDE_PATTERN '';

-- Go back to exposing the whole database.
ALTER SHARE sales_share RESET INCLUDE_PATTERN;

-- No-op when the share is absent, rather than an error.
ALTER SHARE IF EXISTS maybe_missing RESET INCLUDE_PATTERN;

-- Share only EU rows of orders.
ALTER SHARE sales_share
    ADD SECURE VIEW REPLACES orders AS (
        SELECT id, customer_id, region, amount FROM orders WHERE region = 'EU'
    );

-- Hide email and mask ssn in customers, replacing any existing secure view.
ALTER SHARE sales_share
    ADD OR REPLACE SECURE VIEW REPLACES customers AS (
        SELECT id, name, region, 'XXX-XX-' || right(ssn, 4) AS ssn FROM customers
    );

-- Several actions at once, applied all-or-nothing.
ALTER SHARE sales_share
    DROP SECURE VIEW orders,
    ADD SECURE VIEW region_totals AS (
        SELECT region, sum(amount) AS revenue FROM orders GROUP BY region
    );

-- List the secure views on a share.
SHOW SECURE VIEWS ON SHARE sales_share;
```

## Troubleshooting

| Error | Cause |
| --- | --- |
| `does not exist` | The Share doesn't exist, or it's owned by another account. Add `IF EXISTS` to make a missing Share a no-op |
| `syntax error` | An option other than `INCLUDE_PATTERN` or a secure-view action was given, or both were combined in one statement |
| `No share named "..." is owned by current user` | A secure-view action named a Share that doesn't exist or that another account owns |
| `Secure views on shares are not enabled for this account` | Your organization's plan doesn't include secure views |

For errors raised by a secure view's body, see [troubleshooting secure views](/key-tasks/sharing-data/secure-views/#troubleshooting).

For errors raised by the pattern itself, such as a pattern that matches nothing or names a missing schema, see [`INCLUDE_PATTERN` errors](include-pattern.md#errors).

## Related

- [Table-level security](/key-tasks/sharing-data/table-level-security/)
- [Row-level and column-level security with secure views](/key-tasks/sharing-data/secure-views/)
- [INCLUDE_PATTERN clause](include-pattern.md)
- [CREATE SHARE](create-share.md)
- [LIST SHARES](list-shares.md)
- [UPDATE SHARE](update-share.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=%2Fsql-reference%2Fmotherduck-sql-reference%2Falter-share%2F&page_title=ALTER%20SHARE&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.