# Provision user databases and shares
> Create an advanced admin Flight that reads a users table, creates one database per user, grants share access, and revokes access for inactive users.
You run an application where each user should get a small, isolated MotherDuck database and a restricted share, and you want access removed when a user is marked inactive. In this guide, a Flight reads `docs_playground.flights_demo.flight_users`, creates one database and restricted share per active user, grants read access, revokes read access for inactive users, and writes the provisioning result to `docs_playground.flights_demo.user_database_map`.

```mermaid
flowchart LR
    Users[("docs_playground.flights_demo.flight_users")]:::yellow --> Flight["Provisioning Flight"]:::yellow
    Flight --> DB1[("user_dw_* databases")]:::yellow
    Flight --> Share["restricted shares"]:::green
    Users --> Revoke["inactive users<br/>REVOKE READ"]:::watermelon
    Flight --> Ledger[("docs_playground.flights_demo.user_database_map")]:::yellow
```

The control table and provisioning ledger live in `docs_playground`, but the created databases and shares are account-level resources. Treat this as an admin workflow, not a disposable demo.

## Before you start

:::warning[Advanced admin workflow]
This guide creates account-level databases and shares. The Flight runs with the identity that creates it, and that identity must be allowed to create databases, create shares, and grant or revoke share access. The email values in the users table must be valid MotherDuck usernames in the same sharing scope. For scheduled use, prefer a [service account](/key-tasks/flights/flights-authentication-config-and-secrets) that owns the created resources and has only the permissions this workflow needs: the created resources should not depend on a person's account lifecycle.
:::

## Create the users table

Replace the example email addresses with real MotherDuck usernames before you run the provisioning Flight.

<MotherDuckSQLEditor
  database="docs_playground"
  title="Create the Flight users table"
  query={`CREATE SCHEMA IF NOT EXISTS docs_playground.flights_demo;

CREATE OR REPLACE TABLE docs_playground.flights_demo.flight_users AS
SELECT *
FROM (
    VALUES
        ('analyst_one@example.com', 'starter', true),
        ('analyst_two@example.com', 'growth', true),
        ('former_user@example.com', 'starter', false)
) AS users(email, segment, active);`} />

Check the control table:

<MotherDuckSQLEditor
  database="docs_playground"
  title="Review users to provision"
  query={`SELECT email, segment, active
FROM docs_playground.flights_demo.flight_users
ORDER BY email;`} />

## Create the Flight

Run the next SQL in the MotherDuck UI SQL editor, DuckDB CLI, or an AI agent connected as the admin or service account that should own the created resources. It is intentionally not runnable from the docs SQL editor because it creates account-level resources outside `docs_playground`.

```sql
SELECT flight_id, flight_name, current_version
FROM MD_CREATE_FLIGHT(
    name := 'docs_user_database_provisioning',
    source_code := $flight$
import re
import duckdb

def ident(value):
    return '"' + value.replace('"', '""') + '"'

def slug(email):
    value = re.sub(r"[^a-zA-Z0-9_]+", "_", email.split("@")[0].lower()).strip("_")
    return value[:40] or "user"

def main():
    con = duckdb.connect("md:")
    con.execute("CREATE SCHEMA IF NOT EXISTS docs_playground.flights_demo")
    con.execute("""
        CREATE TABLE IF NOT EXISTS docs_playground.flights_demo.user_database_map (
            email VARCHAR,
            database_name VARCHAR,
            share_name VARCHAR,
            active BOOLEAN,
            processed_at TIMESTAMPTZ
        )
    """)
    users = con.execute("""
        SELECT email, segment, active
        FROM docs_playground.flights_demo.flight_users
        ORDER BY email
    """).fetchall()

    for email, segment, active in users:
        database_name = "user_dw_" + slug(email)
        share_name = database_name + "_share"

        if active:
            con.execute(f"CREATE DATABASE IF NOT EXISTS {ident(database_name)}")
            con.execute(f"CREATE SCHEMA IF NOT EXISTS {ident(database_name)}.app")
            con.execute(f"""
                CREATE OR REPLACE TABLE {ident(database_name)}.app.profile AS
                SELECT
                    ? AS email,
                    ? AS segment,
                    current_timestamp AS provisioned_at
            """, [email, segment])
            con.execute(f"""
                CREATE SHARE IF NOT EXISTS {ident(share_name)}
                FROM {ident(database_name)} (
                    ACCESS RESTRICTED,
                    VISIBILITY HIDDEN,
                    UPDATE AUTOMATIC
                )
            """)
            try:
                con.execute(f"GRANT READ ON SHARE {ident(share_name)} TO {ident(email)}")
                print(f"granted {email} access to {share_name}")
            except Exception as exc:
                print(f"skipped grant for {email}: {exc}")
        else:
            try:
                con.execute(f"REVOKE READ ON SHARE {ident(share_name)} FROM {ident(email)}")
                print(f"revoked {email} from {share_name}")
            except Exception as exc:
                print(f"skipped revoke for {email}: {exc}")

        con.execute(
            """
            INSERT INTO docs_playground.flights_demo.user_database_map
            VALUES (?, ?, ?, ?, current_timestamp)
            """,
            [email, database_name, share_name, active]
        )

if __name__ == "__main__":
    main()
$flight$,
    requirements_txt := array_to_string([
        'duckdb==1.5.3'
    ], chr(10))
);
```

## Run the provisioning Flight

Trigger the Flight on demand after you review the users table.

Run this from the same admin or service-account execution surface. The `MD_*` Flight table functions only accept literal parameters, not subqueries or lateral join columns, so store the Flight ID in a SQL variable first:

```sql
SET VARIABLE provisioning_flight_id = (
    SELECT flight_id
    FROM MD_LIST_FLIGHTS()
    WHERE flight_name = 'docs_user_database_provisioning'
    ORDER BY created_at DESC
    LIMIT 1
);

SELECT *
FROM MD_RUN_FLIGHT(
    flight_id := getvariable('provisioning_flight_id')
);
```

Poll the run history:

```sql
SELECT run_number, status, flight_version, created_at
FROM MD_LIST_FLIGHT_RUNS(
    flight_id := getvariable('provisioning_flight_id')
)
ORDER BY run_number DESC
LIMIT 5;
```

## Inspect the result

The map table shows what the Flight attempted for each user.

<MotherDuckSQLEditor
  database="docs_playground"
  title="Read the provisioning map"
  query={`SELECT email, database_name, share_name, active, processed_at
FROM docs_playground.flights_demo.user_database_map
ORDER BY processed_at DESC
LIMIT 20;`} />

Use the run log to audit grant and revoke operations:

```sql
SET VARIABLE provisioning_run_number = (
    SELECT max(run_number)
    FROM MD_LIST_FLIGHT_RUNS(
        flight_id := getvariable('provisioning_flight_id')
    )
);

SELECT logs
FROM MD_GET_FLIGHT_LOGS(
    flight_id := getvariable('provisioning_flight_id'),
    run_number := getvariable('provisioning_run_number')
);
```

## Adapt the pattern

- Add a `plan`, `region`, or `dataset_version` column to the users table and write it into each user's database.
- Replace the profile table with the per-user tables your application needs.
- Keep deprovisioning explicit. This example revokes share access; dropping user databases is a separate policy decision.
- Add a `DRY_RUN` key to the Flight's `config`, read it with `os.environ`, and print planned grants and revokes instead of applying them when it is set. You can then [override it for a single run](/key-tasks/flights/scheduling-and-runs#override-config-for-a-single-run) to preview changes before applying them.
- Run this as an on-demand Flight first, then add `schedule_cron` after you trust the control table.

## Related resources

- [Sharing with users](/key-tasks/sharing-data/sharing-with-users)
- [CREATE SHARE](/sql-reference/motherduck-sql-reference/create-share)
- [GRANT READ ON SHARE](/sql-reference/motherduck-sql-reference/grant-access)
- [REVOKE READ ON SHARE](/sql-reference/motherduck-sql-reference/revoke-access)
- [Service accounts](/key-tasks/service-accounts-guide/)


---

## Docs feedback

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

Payload:

```json
{
  "page_path": "/key-tasks/flights/provision-user-databases-and-shares/",
  "page_title": "Provision user databases and shares",
  "text": "<the user's feedback, max 2000 characters>",
  "source": "<optional identifier for your interface, for example 'claude.ai' or 'chatgpt'>"
}
```

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