# Row-level and column-level security with secure views


> Filter rows, hide columns, and mask values in a Share with secure views. Available on Business and Enterprise plans, including free trials.

A secure view controls which rows and columns of a table a Share exposes. You attach the secure view to the Share as a `SELECT` over the source table, and consumers who query that table get the result of your `SELECT` instead of the raw table. One secure view can apply all three policies:

- **Row-level security:** a `WHERE` clause limits which rows consumers see.
- **Column-level security:** a column you leave out of the `SELECT` doesn't exist for consumers.
- **Column masking:** an expression in place of a column, such as `'XXX-XX-' || right(ssn, 4) AS ssn`, replaces its values.

Use this when you need to:

- Share one region's or one customer's rows from a table that holds everyone's.
- Keep personal data such as emails or ID numbers out of a Share while sharing the rest of the table.
- Show a partial value, such as the last four digits of an account number, instead of the full value.

Every secure view has a name, and that name is what consumers query. Name it after an existing table and add `REPLACES`, and the secure view stands in for that table inside the Share, so queries, dashboards, and BI tools built against the table keep working. Give it a new name instead, and it appears as an extra table in the Share. Secure views and tables aren't one-to-one: a table can have only one secure view that replaces it per Share, but any number of other secure views can read from it, such as `customers_eu` and `customers_us` built over the same `customers` table. Policies are enforced on MotherDuck's servers.

:::note
MotherDuck secure views aren't DuckDB's `CREATE SECURE VIEW`. DuckDB's secure view is an optimizer barrier on a view in the database: it doesn't restrict access, and anyone who can read the base table can bypass it. MotherDuck secure views are access-control policies attached to a Share and enforced on MotherDuck's servers.
:::

You'll know it worked when a consumer who attaches the Share sees only the rows and columns you allowed. See [Verify what a Share exposes](#verify-what-a-share-exposes) for the steps.

Secure views are available on **Business** and **Enterprise** plans, including free trials.

## Before you start

- You own the Share. Only a Share's creator can add, change, or remove its secure views, and that includes organization Admins: an Admin can't edit another user's Share.
- You're signed in with a read-write token. A [read scaling token](/key-tasks/authenticating-and-connecting-to-motherduck/read-scaling/) is read-only and can't create or alter a Share.
- To manage secure views with SQL, you're on DuckDB 1.5.6 or later. Older clients don't recognize `SECURE VIEW` in `CREATE SHARE`, `ALTER SHARE`, or `SHOW`. Consumers can query secure views from older clients, with some limits; see [Client versions](#client-versions).
- The source is a native MotherDuck database or a fully managed DuckLake database. See [Limitations](#limitations) for the other storage types.
- To edit secure views in the UI, the source database is attached to your session.

## Set row and column visibility in the UI

The UI edits secure views from the **Alter share** page. The same controls appear on the **Create share** page, which you open by selecting the trident next to a database and then **Share**.

1. In the left sidebar, find the Share under **Shares I've created**, select the trident next to it, then select **Alter**.

   ![Share menu in the sidebar, showing Alter, Attach, Copy share link, and Drop](img/secure-views-alter-menu.png)

2. Under **What to share**, select **Custom**. Row and column settings are only available in **Custom** mode, which also lets you choose the tables the Share includes. See [table-level security](./table-level-security.md) for how that selection works.

   <TerminalChrome chrome="browser" url="https://app.motherduck.com/shares/<share_id>/alter" urlLink={false} ariaLabel="Alter share page with Custom selected">

   ![Alter share page with Custom selected, showing the customers table with a summary of its row and column settings](img/secure-views-alter-page.png)

3. Hover over a table in the schema tree and select **Configure**. The table's settings open on the right.
4. Under **Rows**, keep **All rows** or select **Only rows matching** and enter a filter, for example `region = 'EU'`. The filter is the body of a SQL `WHERE` clause.
5. Under **Columns**, set each column to one of:

   | Setting | What consumers see |
   | --- | --- |
   | **Visible** | The column's values, unchanged |
   | **Hidden** | No column. Querying it fails as though it doesn't exist |
   | **Masked as NULL** | The column, with `NULL` in every row |
   | **Custom masking** | The result of a SQL expression you enter, such as `'XXX-XX-' \|\| right(ssn, 4)` |

   ![Row and column settings for main.customers, with a row filter on region, email hidden, and ssn masked](img/secure-views-table-editor.png)

   The table's row in the schema tree shows a summary of its settings, such as `1 column hidden, 1 column masked, row filter`.

6. Check the **Visibility summary** and the **Preview** below the settings. The preview runs your settings against up to 500 rows of the table and shows up to 100 of the rows a consumer would see. It runs as you, so a filter that depends on who's querying, such as `owner = current_email()`, previews the rows you'd see.

   ![Visibility summary and preview, showing two EU customers with masked ssn values and no email column](img/secure-views-preview.png)

7. Select **Done**, repeat for any other table, then select **Save changes**.

Settings apply as you type, so there's no separate save for each table. To clear a table's settings, select **Reset** in its settings panel.

### What to enter

The **Only rows matching** box takes the body of a `WHERE` clause, without the word `WHERE`. A row is shared when the filter is true for it.

A static filter returns the same rows to every consumer:

| To share | Enter |
| --- | --- |
| One region's rows | `region = 'EU'` |
| Several regions' rows | `region IN ('EU', 'US')` |
| Rows from a date onward | `order_date >= DATE '2026-01-01'` |
| All rows except one customer's | `customer_id <> 42` |
| Rows that meet several conditions | `region = 'EU' AND lifetime_value > 500` |

A dynamic filter uses the [identity functions](#show-each-consumer-different-rows) to return different rows depending on who's querying:

| To share | Enter |
| --- | --- |
| The rows each consumer owns | `account_owner = current_email()` |
| The rows matching each consumer's email address | `email = current_email()` |
| The rows for each consumer's company, by email domain | `customer_domain = split_part(current_email(), '@', 2)` |
| All rows, but only to named people | `current_email() IN ('maria@acme.com', 'finn@acme.com')` |
| The rows a service account owns, by its name | `loaded_by = current_user` |
| All rows, but only to holders of a role | `is_role_in_session('finance')` |
| All rows, but only to holders of any of several roles | `is_role_in_session(['finance', 'admin'])` |
| Each region's rows to the matching role, such as `sales_eu` for `EU` | `is_role_in_session('sales_' \|\| lower(region))` |
| All rows to Admins, and only their own rows to everyone else | `is_role_in_session('admin') OR account_owner = current_email()` |
| The rows a mapping table grants each consumer | `region IN (SELECT region FROM region_access WHERE member = current_email())` |

To match people, use `current_email()` rather than `current_user`. Usernames are unique only within an organization; see [Show each consumer different rows](#show-each-consumer-different-rows).

The **Custom masking** box takes one SQL expression. It can reference any column of the table, and its result replaces the column's value:

| To show | Enter | Example result |
| --- | --- | --- |
| A fixed placeholder | `'***'` | `***` |
| The last four characters only | `'XXX-XX-' \|\| right(ssn, 4)` | `XXX-XX-6789` |
| An email's first letter and domain | `left(email, 1) \|\| '***@' \|\| split_part(email, '@', 2)` | `m***@example.com` |
| A consistent hash, for joining without the raw value | `md5(email)` | `3f2b8c...` |
| A rounded number | `round(lifetime_value, -2)` | `1200` |
| A month instead of a full date | `date_trunc('month', order_date)` | `2026-09-01` |
| The full value to one role, masked for everyone else | `CASE WHEN is_role_in_session('hr') THEN ssn ELSE 'XXX-XX-' \|\| right(ssn, 4) END` | `123-45-6789` or `XXX-XX-6789` |
| The full value to the person the row is about, masked for everyone else | `CASE WHEN email = current_email() THEN ssn ELSE '***' END` | `123-45-6789` or `***` |
| The value to several roles, `NULL` for everyone else | `CASE WHEN is_role_in_session(['hr', 'admin']) THEN lifetime_value END` | `1200.50` or `NULL` |

Both boxes take a single SQL fragment: no comments, no `;`, and no `$$`, with balanced brackets and quotes. A masking expression can't contain a comma outside brackets, so `round(amount, -2)` works but `amount, region` doesn't. Functions that return a different value on every run, such as `random()`, aren't allowed. See [Rules for a secure view](#rules-for-a-secure-view).

If an entry isn't valid, the box shows an error, such as **Enter a row filter** for an empty filter, and the preview waits until every setting is valid.

A table that already has a secure view written in SQL, in a form the editor can't read back, shows a **custom SQL** badge. You can still include or exclude that table in the editor, but changes to its secure view need SQL.

## Set row and column visibility with SQL

A secure view's body is a single `SELECT` in parentheses. `REPLACES` makes the secure view stand in for the table of the same name, so consumers keep querying `orders` rather than a new name. It also catches typos: with `REPLACES`, the statement fails if the table or view doesn't exist, and without it, the statement fails if it does.

### Add secure views when you create a Share

Add a `WITH SECURE VIEW` clause after the Share's options. Separate several secure views with commas:

```sql
CREATE SHARE eu_share FROM acme_sales (ACCESS RESTRICTED)
WITH
    -- Row-level security: only EU orders.
    SECURE VIEW REPLACES orders AS (
        SELECT id, customer_id, region, amount, order_date
        FROM orders
        WHERE region = 'EU'
    ),
    -- Row-level security, column-level security, and masking together:
    -- EU customers only, no email column, and the ssn masked.
    SECURE VIEW REPLACES customers AS (
        SELECT id, name, region, 'XXX-XX-' || right(ssn, 4) AS ssn
        FROM customers
        WHERE region = 'EU'
    );

GRANT READ ON SHARE eu_share TO ROLE eu_analysts;
```

A consumer who queries `customers` in this Share gets two rows, no `email` column, and an `ssn` column that reads `XXX-XX-6789`.

### Add, change, or remove secure views on an existing Share

Use [`ALTER SHARE`](/sql-reference/motherduck-sql-reference/alter-share/). Editing secure views leaves the Share URL unchanged, so consumers stay attached.

```sql
-- Add a secure view.
ALTER SHARE eu_share
    ADD SECURE VIEW REPLACES invoices AS (
        SELECT id, customer_id, amount FROM invoices WHERE region = 'EU'
    );

-- Replace an existing secure view with a new definition.
ALTER SHARE eu_share
    ADD OR REPLACE SECURE VIEW REPLACES orders AS (
        SELECT id, customer_id, region, amount, order_date
        FROM orders
        WHERE region = 'EU' AND order_date >= DATE '2026-01-01'
    );

-- Remove a secure view, exposing the underlying table again.
ALTER SHARE eu_share DROP SECURE VIEW invoices;
```

One `ALTER SHARE` can carry several comma-separated actions, and they apply all-or-nothing:

```sql
ALTER SHARE eu_share
    DROP SECURE VIEW orders,
    ADD SECURE VIEW REPLACES customers AS (SELECT id, name FROM customers);
```

### Mask columns

A masking expression can be any deterministic SQL expression, and it can change the column's type:

```sql
ALTER SHARE eu_share
    ADD OR REPLACE SECURE VIEW REPLACES customers AS (
        SELECT
            id,
            name,
            region,
            'XXX-XX-' || right(ssn, 4) AS ssn,        -- partial mask
            NULL::VARCHAR AS email,                    -- keep the column, drop its values
            round(lifetime_value, -2) AS lifetime_value -- reduce precision
        FROM customers
    );
```

To remove a column entirely, leave it out of the `SELECT`. Masking with `NULL` keeps the column in the consumer's schema, which helps when a dashboard or query expects it to exist.

### Reuse a masking rule across tables

To mask the same kind of value the same way everywhere, define the rule once as a scalar macro in the source database and call it from each secure view. This works like a masking policy: change the macro, and every secure view that calls it masks the new way.

```sql
-- In the source database, in a schema the Share doesn't expose.
CREATE SCHEMA acme_hr.policy;
CREATE MACRO acme_hr.policy.mask_ssn(x) AS 'XXX-XX-' || right(x, 4);
CREATE MACRO acme_hr.policy.mask_email(e) AS left(e, 1) || '***@' || split_part(e, '@', 2);

-- Every secure view calls the same rules.
CREATE SHARE people_share FROM acme_hr (
    INCLUDE_PATTERN 'main.*',
    ACCESS RESTRICTED
)
WITH
    SECURE VIEW REPLACES employees AS (
        SELECT id, name, policy.mask_ssn(ssn) AS ssn, policy.mask_email(email) AS email
        FROM employees
    ),
    SECURE VIEW REPLACES contractors AS (
        SELECT id, name, policy.mask_ssn(ssn) AS ssn, policy.mask_email(email) AS email
        FROM contractors
    );
```

A macro can call the [identity functions](#show-each-consumer-different-rows), and they evaluate as the consumer, so one rule can mask differently per role:

```sql
CREATE OR REPLACE MACRO acme_hr.policy.mask_ssn(x) AS
    CASE WHEN is_role_in_session('hr') THEN x ELSE 'XXX-XX-' || right(x, 4) END;
```

- **Keep policy macros out of the Share.** Consumers can read the definition of any macro in a schema the Share exposes, and the `main` schema is always exposed. A macro in a schema the [include pattern](./table-level-security.md) leaves out, such as `policy` above, still works inside secure views and stays hidden from consumers.
- **A macro edit is a policy edit.** After `CREATE OR REPLACE MACRO`, every secure view that calls the macro uses the new definition once the Share publishes the change, without an `ALTER SHARE`. On an `UPDATE MANUAL` Share, that's after the next [`UPDATE SHARE`](/sql-reference/motherduck-sql-reference/update-share/). Either way it takes a minute or two, and re-attaching doesn't speed it up.
- **Breaking the macro breaks its secure views, closed.** If you drop the macro, or redefine it to break a [rule for a secure view](#rules-for-a-secure-view), such as by calling `getvariable()`, consumers get `can no longer be evaluated` from every secure view that calls it, rather than unmasked data.
- **Scalar macros in the source database only.** Table macros aren't allowed, and a secure view can't call a macro from another database or Share, because a consumer of the Share may not have access to it.

### Replace a view

`REPLACES` works on views as well as tables. Consumers query the view's name as before, but get a table that returns what your secure view returns, and they can no longer see the view's definition:

```sql
ALTER SHARE eu_share
    ADD SECURE VIEW REPLACES orders_summary AS (
        SELECT id, region FROM orders_summary WHERE region = 'EU'
    );
```

Replacing a view restricts only that view. The tables it reads stay in the Share as they are, so leave them out with an [include pattern](./table-level-security.md) or replace them too.

Other views in the Share that query a replaced table or view read the secure view, not the original. They see only the rows it returns, and fail if they use a column it leaves out.

### Add more secure views over the same table

Without `REPLACES`, a secure view adds a new table to the Share under the name you give it. The name must not match an existing table or view; if it does, add `REPLACES` to confirm you mean to replace it. This lets one Share expose several filtered or aggregated versions of the same table:

```sql
ALTER SHARE regional_share
    ADD SECURE VIEW customers_eu AS (
        SELECT id, name, region FROM customers WHERE region = 'EU'
    ),
    ADD SECURE VIEW customers_us AS (
        SELECT id, name, region FROM customers WHERE region = 'US'
    ),
    ADD SECURE VIEW orders_by_region AS (
        SELECT region, count(*) AS order_count, sum(amount) AS revenue
        FROM orders
        GROUP BY region
    );
```

Consumers of `regional_share` see `customers_eu`, `customers_us`, and `orders_by_region` alongside the Share's other tables. An aggregate like `orders_by_region` publishes totals without exposing the rows behind them.

Each of these secure views reads the source tables directly, not through other secure views in the Share. If the Share also includes `customers` itself, consumers can read it in full unless you replace it with its own secure view or leave it out with an [include pattern](./table-level-security.md). Secure views without `REPLACES` don't appear in the UI editor, so manage them with SQL.

### Combine with table-level security

A Share can carry an [include pattern](./table-level-security.md) and secure views at the same time. The include pattern decides which tables are in the Share, and the secure views filter what those tables return:

```sql
CREATE SHARE partner_share FROM acme_sales (
    INCLUDE_PATTERN 'main.customers, main.orders',
    ACCESS RESTRICTED
)
WITH SECURE VIEW REPLACES customers AS (
    SELECT id, name, region FROM customers WHERE region = 'EU'
);
```

Unlike `CREATE SHARE`, `ALTER SHARE` can't change the include pattern and the secure views in one statement. Use a separate `ALTER SHARE` for each.

A secure view is visible to consumers even when the include pattern leaves out its table. Tables that are neither matched by the pattern nor covered by a secure view stay hidden.

### Show each consumer different rows

A secure view is evaluated when a consumer queries it, as that consumer. These functions return the identity of whoever is running the query, so one Share can return different rows or values to different people:

| Function | Returns |
| --- | --- |
| `current_user`, `session_user`, `user` | The consumer's MotherDuck username, or their email address if they have no username. For a service account, its name |
| `current_email()` | The consumer's email address. `NULL` for a service account |
| `session_roles()` | A list of the roles the consumer holds, including preset roles they inherit. An Admin holds `['admin', 'builder', 'explorer']` |
| `is_role_in_session(<role>)` | `true` when the consumer holds `<role>`. Pass a list, such as `['finance', 'admin']`, to check for any of several roles. The role can come from a column or an expression. A role that doesn't exist returns `false` |

Match people with `current_email()`. Usernames are unique only within an organization, so on a Share that users outside your organization can attach, a `current_user` filter can match someone else with the same username. Use `current_user` to match service accounts, which have no email address.

`current_role` isn't available in a secure view. A MotherDuck session holds several roles at once rather than one current role, so check for the roles a policy allows with `is_role_in_session()` or `session_roles()`.

#### Filter by user

Match a column that records who may see each row against `current_email()`. This suits data keyed by person, such as accounts each manager owns or a customer portal where each person sees their own records. Match on the email domain to give everyone at a partner company that company's rows:

```sql
-- Each account manager sees only the accounts they own.
ALTER SHARE crm_share
    ADD SECURE VIEW REPLACES accounts AS (
        SELECT id, name, region, account_owner, arr
        FROM accounts
        WHERE account_owner = current_email()
    );

-- Everyone at a partner company sees that company's orders.
ALTER SHARE partner_share
    ADD SECURE VIEW REPLACES orders AS (
        SELECT id, partner_domain, amount, order_date
        FROM orders
        WHERE partner_domain = split_part(current_email(), '@', 2)
    );
```

`current_email()` is `NULL` for service accounts, so these filters return no rows to a service account. To match a service account, compare its name with `current_user`, for example `WHERE loaded_by = current_user`.

#### Filter by role

Grant whole tables, or slices of them, to the holders of a role:

```sql
-- Finance and Admins see every invoice; nobody else sees any.
ALTER SHARE billing_share
    ADD SECURE VIEW REPLACES invoices AS (
        SELECT id, customer_id, amount, due_date
        FROM invoices
        WHERE is_role_in_session(['finance', 'admin'])
    );

-- Each regional sales role sees its own region: sales_eu sees EU, sales_us sees US.
ALTER SHARE sales_share
    ADD SECURE VIEW REPLACES opportunities AS (
        SELECT id, account, region, stage, amount
        FROM opportunities
        WHERE is_role_in_session('sales_' || lower(region))
    );

-- Admins see everything; everyone else sees only the rows they own.
ALTER SHARE crm_share
    ADD OR REPLACE SECURE VIEW REPLACES accounts AS (
        SELECT id, name, region, account_owner, arr
        FROM accounts
        WHERE is_role_in_session('admin') OR account_owner = current_email()
    );
```

`session_roles()` returns the same roles as a list, for list functions. Unlike `is_role_in_session()`, which ignores case, list functions compare role names exactly, so `list_has_any(session_roles(), ['Finance'])` doesn't match the `finance` role. Prefer `is_role_in_session()` in a policy.

#### Combine roles, users, and masking

Branch on the consumer in both the `WHERE` clause and the column list:

```sql
-- Finance and Admins see every row; everyone else sees only their region's rows.
-- HR sees full ID numbers; everyone else sees the last four digits.
ALTER SHARE people_share
    ADD SECURE VIEW REPLACES employees AS (
        SELECT
            id,
            name,
            region,
            CASE WHEN is_role_in_session('hr') THEN ssn
                 ELSE 'XXX-XX-' || right(ssn, 4) END AS ssn
        FROM employees
        WHERE is_role_in_session(['finance', 'admin'])
           OR region IN (SELECT region FROM region_access WHERE member = current_email())
    );
```

The same works for a single person's own data: `CASE WHEN email = current_email() THEN ssn ELSE '***' END` shows each employee their own full ID number and masks everyone else's.

Preset roles are nested, so `is_role_in_session('builder')` is also true for Admins. Test for the role you mean in its positive form: `WHERE is_role_in_session(...)` rather than `WHERE NOT is_role_in_session(...)`, because a `NULL` argument makes the negated form unpredictable.

A mapping table such as `region_access` above is read from the Share's published data like any other table, so a mapping table you create after the Share isn't visible to the secure view until the Share publishes it. Until then, consumers get a `can no longer be evaluated` error. On an `UPDATE AUTOMATIC` Share, this clears on its own within a minute or two. On an `UPDATE MANUAL` Share, run [`UPDATE SHARE`](/sql-reference/motherduck-sql-reference/update-share/) before consumers query the secure view. Keep it out of the Share with an [include pattern](./table-level-security.md), or consumers can read who has access to what.

## Verify what a Share exposes

To see the secure views stored on a Share you own, run `SHOW SECURE VIEWS`:

```sql
SHOW SECURE VIEWS ON SHARE eu_share;
```

| schema_name | view_name | sql | replaces_table | columns |
| --- | --- | --- | --- | --- |
| main | customers | `SELECT id, name, region, 'XXX-XX-' \|\| right(ssn, 4) AS ssn FROM customers WHERE region = 'EU'` | true | `[id, name, region, ssn]` |
| main | orders | `SELECT id, customer_id, region, amount, order_date FROM orders WHERE region = 'EU'` | true | `[id, customer_id, region, amount, order_date]` |

To check the result as a consumer would, attach your own Share under a different alias and query it. You see the Share through its secure views, while your source database stays unfiltered. Identity functions evaluate as you, so a secure view that filters on `current_email()` or `is_role_in_session()` returns the rows your own email and roles allow, not what another consumer sees:

```sql
ATTACH 'md:_share/eu_share/<share_token>' AS eu_check;

SELECT * FROM eu_check.customers;
```

| id | name | region | ssn |
| --- | --- | --- | --- |
| 1 | Maria Lopez | EU | XXX-XX-6789 |
| 2 | Finn Weber | EU | XXX-XX-7890 |

A change to a secure view reaches attached consumers within a minute or two, and never needs `UPDATE SHARE`, even on an `UPDATE MANUAL` Share. Detach and re-attach to see it immediately. Changes in the source database, such as new rows or an edited macro, don't arrive faster on re-attach.

## What a consumer sees

- The secure view in place of the table. A consumer can't read rows or columns the secure view leaves out, and their own `WHERE` clauses, joins, and aggregates only work on the rows it returns.
- Hidden columns don't exist. `SELECT ssn FROM customers` fails with a missing-column error rather than returning `NULL`.
- No policy definitions. The secure view appears in the consumer's catalog as an ordinary table, `SHOW SECURE VIEWS` fails for anyone but the owner, and `EXPLAIN` shows the secure view as a single opaque step without its filter or row counts.
- Copies stay filtered. `CREATE TABLE ... AS SELECT`, `COPY FROM DATABASE`, and `EXPORT DATABASE` from the Share only copy what the secure views return. If one secure view [can no longer be evaluated](#rules-for-a-secure-view), `COPY FROM DATABASE` fails for the whole Share with the `must be updated by the share owner` error and copies nothing, even though the Share's other secure views still answer queries.
- No `rowid`. A consumer can't use `rowid` through a secure view, whether in `SELECT`, `WHERE`, `ORDER BY`, or a join between two secure views. A secure view that keeps rows one-for-one with its table fails with `its row identity would have to leave the server`. One that aggregates, joins, or deduplicates fails with `Referenced column "rowid" not found`.
- Rarely, DuckDB's optimizer rewrites a consumer's query into a form a secure view refuses, for example by adding `rowid`, so a query can fail with one of these errors even though it doesn't use `rowid` as written.

### Client versions

Secure views are enforced on MotherDuck's servers, but the consumer's client still plans the query, so older clients support fewer query shapes:

| Consumer's DuckDB version | What works |
| --- | --- |
| 1.5.5 or later | All queries |
| 1.4.0 to 1.5.4 | Most queries, including `SELECT *`, filters, joins, and aggregates. `ORDER BY ... LIMIT`, `LIMIT ... OFFSET`, and `USING SAMPLE` on a secure view fail with `This query shape is not supported on secure view "..." by this client; please upgrade the client version` |

Clients older than 1.4.0 aren't supported.

### Different results for different consumers

A secure view returns the same result to every consumer unless it uses the identity functions in [Show each consumer different rows](#show-each-consumer-different-rows). You can also give two audiences different rows or columns by creating a Share per audience, each with its own secure views, and granting each Share to the right users or roles.

## Rules for a secure view

MotherDuck validates each secure view against the source database when you save it:

- The body is exactly one `SELECT`, written in parentheses: `AS (SELECT ...)`. Joins, `GROUP BY`, `DISTINCT`, `UNION`, window functions, CTEs, and scalar subqueries are allowed.
- It reads only the shared database. It can't name another database, call table functions such as `read_csv` or `range`, call table macros, or take parameters. Scalar macros are allowed.
- It's deterministic. Volatile functions such as `random()` aren't allowed. Time functions such as `now()` are.
- It doesn't use `SAMPLE`, `LIMIT`, or `OFFSET`.
- It doesn't call session or configuration functions such as `current_setting()`, `getvariable()`, `current_database()`, `current_schema()`, `current_role`, or `pg_has_role()`. These aren't supported in a secure view. To check who's querying, use the [identity functions](#show-each-consumer-different-rows).
- With `REPLACES`, a table or view of that name must exist. Without it, the name must not already exist, and its schema must exist in the source database: a secure view named `reporting.eu_orders` needs a `reporting` schema.

A rule can also fail on a query you didn't write that way. DuckDB's optimizer sometimes rewrites a secure view's body, for example by adding a `LIMIT`, and the rewritten form can break a rule, so a secure view can be refused for something its SQL doesn't contain.

A secure view stores an explicit query against the source, so a later schema change can break it, for example dropping a column it selects. When that happens, consumers get an error that the secure view must be updated by the Share owner. Fix it with `ALTER SHARE ... ADD OR REPLACE SECURE VIEW`. The UI always writes an explicit column list for this reason; prefer the same in SQL over `SELECT *`.

## Limitations

- **Up to 100 secure views per Share.** Each secure view's SQL can be up to 256 KiB, and a Share's secure views up to 4 MiB in total.
- **Storage types.** Secure views work on native MotherDuck databases and on fully managed DuckLake databases. DuckLake databases with their own `DATA_PATH` aren't supported, and a DuckLake Share with secure views must use `UPDATE AUTOMATIC`.
- **No time travel or DuckLake metadata functions on a DuckLake Share with secure views.** Consumers can't time travel or call DuckLake's metadata functions, such as `ducklake_snapshots`, `ducklake_table_info`, `ducklake_list_files`, `ducklake_table_insertions`, and `ducklake_table_changes`.
- **No zero-copy clone.** `CREATE DATABASE ... FROM <share>` is refused for a Share with secure views, because the clone would carry the unfiltered data. To copy what the Share exposes, use `COPY FROM DATABASE <share> TO <your_database>`.
- **`CREATE OR REPLACE SHARE` drops secure views it doesn't restate.** To change a Share's secure views without changing its URL, use `ALTER SHARE`.
- **Removing secure views always works.** If your organization's plan no longer includes secure views, you can still drop them, and existing secure views keep being enforced, but you can't add new ones.

## Troubleshooting

| Error | Cause and fix |
| --- | --- |
| `Secure views on shares are not enabled for this account` | Your organization isn't on a plan that includes secure views. See [pricing](/about-motherduck/billing/pricing/) |
| `No share named "..." is owned by current user` | The Share doesn't exist, or another user owns it. Only the owner can manage its secure views |
| `The body of a SECURE VIEW is written in parentheses: AS (SELECT ...)` | Wrap the `SELECT` in parentheses |
| `... already exists in "..."; add REPLACES to state that the secure view shadows it` | A table or view has that name. Add `REPLACES` to replace it in the Share, or pick another name for a derived secure view |
| `A secure view named ... already exists on this share` | The Share already has a secure view with that name, for example a second `REPLACES` for the same table. Use `ADD OR REPLACE` to change its definition, or give the new secure view a different name |
| `... declared with REPLACES, but no table or view of that name exists` | Check the table name and schema, or drop `REPLACES` to create a derived secure view |
| `Secure views are not supported for shares of DUCKLAKE databases with their own DATA_PATH` | The source is a DuckLake database that stores its data in your own bucket. Only fully managed DuckLake databases support secure views. See [Limitations](#limitations) |
| `Secure view ... does not exist on this share` | `DROP SECURE VIEW` named a secure view the Share doesn't have. Check the name and schema with `SHOW SECURE VIEWS`, or add `IF EXISTS` |
| `Secure view "..." may not use ...: ...` | The body breaks one of the [rules for a secure view](#rules-for-a-secure-view). The message names what's not allowed and why |
| `its row identity would have to leave the server` or `Referenced column "rowid" not found` | The query uses `rowid`, which isn't available through a secure view. Remove it from the query. See [What a consumer sees](#what-a-consumer-sees) |
| `This query shape is not supported on secure view "..." by this client; please upgrade the client version` | The consumer's DuckDB version is older than 1.5.5 and the query uses `ORDER BY ... LIMIT`, `LIMIT ... OFFSET`, or `USING SAMPLE`. Upgrade the client. See [Client versions](#client-versions) |
| `Secure view "..." cannot be evaluated against the shared database: Catalog "..." does not exist` | The secure view refers to another database or Share, for example by calling a macro defined there. A secure view can only read from the shared database, so move the macro or table into the source database |
| `Secure view "..." can no longer be evaluated against the shared database` | The secure view doesn't match the Share's published data. Either the source table changed after the secure view was saved, so update the secure view with `ADD OR REPLACE`, or the secure view reads a table the Share hasn't published yet, so run [`UPDATE SHARE`](/sql-reference/motherduck-sql-reference/update-share/) or wait for the next automatic update |

## Related

- [Table-level security](./table-level-security.md)
- [`CREATE SHARE`](/sql-reference/motherduck-sql-reference/create-share/) and [`ALTER SHARE`](/sql-reference/motherduck-sql-reference/alter-share/)
- [Sharing concepts and overview](./sharing-overview.md)
- [Roles and access control](/concepts/roles-and-access-control/)


---

## 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%2Fsecure-views%2F&page_title=Row-level%20and%20column-level%20security%20with%20secure%20views&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.