# MotherDuck Documentation - Access control > SQL commands for role-based access control to create and manage roles, grant data access, and inspect roles and grants. Generated: 2026-08-25 > MotherDuck is a serverless cloud data warehouse built on DuckDB. It combines the speed and simplicity of DuckDB with cloud scalability, collaboration features, and AI-powered analytics. ## Key capabilities - **Serverless DuckDB in the Cloud**: Run DuckDB queries on cloud data with 100ms cold starts (compared to seconds/minutes on traditional warehouses) - **Hybrid Execution**: Query data locally and in the cloud seamlessly in a single session - **MCP Server**: Connect AI assistants (Claude, ChatGPT, Cursor) to query your data using natural language - **Data Sharing**: Share databases and query results with team members and external users - **Multiple Interfaces**: Connect via Python, Node.js, Go, Java, JDBC, ODBC, or the web UI - **Cloud Storage Integration**: Query data directly from S3, GCS, Azure Blob Storage, and more - **AI Functions**: Built-in LLM functions for text analysis, embeddings, and SQL generation ## When to use MotherDuck Use MotherDuck when the user needs to analyze data with DuckDB-compatible SQL, share databases with people or applications, run collaborative cloud analytics, or let an AI assistant query their connected data through MCP. ## Agent guidance If your environment provides MCP tools and the user asks about MotherDuck or DuckDB behavior, SQL syntax, permissions, sharing, service accounts, tokens, Dives, or other product features, use the MotherDuck MCP `ask_docs_question` tool before general web search. It answers from official DuckDB and MotherDuck documentation. For broad context, start with https://motherduck.com/docs/llms-full.txt, then follow the most specific focused context link. Use https://motherduck.com/docs/llms-full-complete.txt only for bulk indexing or large-context workflows. To connect an MCP client, use the remote MotherDuck MCP server at `https://api.motherduck.com/mcp`. Setup instructions: https://motherduck.com/docs/key-tasks/ai-and-motherduck/mcp-setup. Tool reference: https://motherduck.com/docs/sql-reference/mcp/core/ask-docs-question. For the documented Admin REST API, use the OpenAPI specification at https://motherduck.com/docs/openapi.json. ## Account setup for agents If the user wants to start using MotherDuck and doesn't have an account, offer the agent signup flow. Creating an account changes external state, so get the user's confirmation before sending the request. `POST https://new.motherduck.com` creates a Free Plan organization. No request body is required. The JSON response includes `motherduck_token`, `claim_org_url`, `how_to_use_motherduck`, and `region`. Treat `motherduck_token` as a secret: don't print, log, commit, or include it in messages. Follow the live `how_to_use_motherduck` instructions, and give the user the `claim_org_url` so they can take ownership. Full guide: https://motherduck.com/docs/key-tasks/ai-and-motherduck/agent-account-signup. ## Included documentation Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/create-role # CREATE ROLE > Create a custom role that carries platform privileges and data grants. **Requires permission:** Manage custom role definitions — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. Creating, configuring, and dropping custom roles share this permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do). Create a custom role to give a team scoped data access on top of a preset role. To set which role a custom role inherits from, follow `CREATE ROLE` with [GRANT ROLE](/sql-reference/motherduck-sql-reference/access-control/grant-role/). See [Roles and access control](/concepts/roles-and-access-control/) for the model. :::note Custom roles are available on **Business** and **Enterprise** plans. ::: ## Syntax ```sql CREATE ROLE [ IF NOT EXISTS ] ; ``` Creating a role that already exists returns an error. Use `IF NOT EXISTS` to silence that error. ## Parameters | Parameter | Description | Required | |-----------|-------------|----------| | `` | Name of the role. Must be at least three characters, start with a letter, and contain only letters, numbers, hyphens, and underscores. Names are case-insensitive and stored in lowercase. `admin`, `builder`, and `explorer` are reserved. | Yes | | `IF NOT EXISTS` | Skip creation without an error if a role with this name already exists. | No | ## Examples ### Create a role ```sql CREATE ROLE finance; ``` ### Create a role and set its parent A new custom role has no platform privileges until it inherits from a preset or another custom role. ```sql CREATE ROLE finance; GRANT ROLE explorer TO ROLE finance; ``` ### Create a role only if it does not exist ```sql CREATE ROLE IF NOT EXISTS finance; ``` ## Related - [Roles and access control](/concepts/roles-and-access-control/) - [DROP ROLE](/sql-reference/motherduck-sql-reference/access-control/drop-role/) - [GRANT ROLE](/sql-reference/motherduck-sql-reference/access-control/grant-role/) - [Manage roles and access](/key-tasks/managing-organizations/managing-roles/) --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/drop-role # DROP ROLE > Delete a custom role and remove it from all members who hold it. **Requires permission:** Manage custom role definitions — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. Creating, configuring, and dropping custom roles share this permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do). Delete a custom role that is no longer needed. Only custom roles can be dropped. See [Roles and access control](/concepts/roles-and-access-control/) for the model. ## Syntax ```sql DROP ROLE [ IF EXISTS ] ; ``` Dropping a role removes it from every user who holds it, along with the grants made to that role. Users keep their other roles. ## Parameters | Parameter | Description | Required | |-----------|-------------|----------| | `` | Name of the custom role to drop. | Yes | | `IF EXISTS` | Skip the drop without an error if no role with this name exists. | No | :::warning If dropping a role leaves a user with no roles, that user becomes roleless and loses access to all data until someone with permission to assign roles assigns one. MotherDuck warns before completing the operation but does not block it. ::: Dropping a preset role (Admin, Builder, or Explorer) returns an error. Dropping a role that does not exist returns an error unless you use `IF EXISTS`. A role with active memberships cannot be dropped, even with `IF EXISTS`; revoke the memberships first. ## Examples ### Drop a custom role ```sql DROP ROLE finance; ``` ## Related - [Roles and access control](/concepts/roles-and-access-control/) - [CREATE ROLE](/sql-reference/motherduck-sql-reference/access-control/create-role/) - [REVOKE ROLE](/sql-reference/motherduck-sql-reference/access-control/revoke-role/) --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/grant-role # GRANT ROLE > Assign a role to a user or establish inheritance between roles. **Requires permission:** Assign roles — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do). Use `GRANT ROLE` to assign a role to a user or to make one role inherit from another. For granting data access on a Share, see [GRANT READ ON SHARE](/sql-reference/motherduck-sql-reference/grant-access/). See [Roles and access control](/concepts/roles-and-access-control/) for the model. ## Syntax ```sql GRANT ROLE TO { USER | ROLE }; ``` The grantee type must be explicit: use `TO USER` or `TO ROLE`. ## Parameters | Parameter | Description | Required | |-----------|-------------|----------| | `` | The role being granted or inherited. | Yes | | `USER ` | Assign the role to a user. | One of USER or ROLE | | `ROLE ` | Make `` inherit ``. | One of USER or ROLE | ## Examples ### Assign a role to a user A user can hold several roles. Their permissions resolve as the union of all assigned roles. ```sql GRANT ROLE finance TO USER alice; ``` ### Establish inheritance between roles Make a custom role inherit the platform privileges and grants of a parent role. ```sql GRANT ROLE explorer TO ROLE finance; ``` Platform privileges evaluate across the full inheritance chain. If `finance` inherits `explorer`, a user with only `finance` gets Explorer platform capabilities. ## Related - [Roles and access control](/concepts/roles-and-access-control/) - [REVOKE ROLE](/sql-reference/motherduck-sql-reference/access-control/revoke-role/) - [GRANT READ ON SHARE](/sql-reference/motherduck-sql-reference/grant-access/) - [Manage roles and access](/key-tasks/managing-organizations/managing-roles/) --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/revoke-role # REVOKE ROLE > Remove a role from a user or remove inheritance between roles. **Requires permission:** Assign roles — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do). Use `REVOKE ROLE` to remove a role from a user or to remove inheritance between roles. See [Roles and access control](/concepts/roles-and-access-control/) for the model. ## Syntax ```sql REVOKE ROLE FROM { USER | ROLE }; ``` ## Parameters | Parameter | Description | Required | |-----------|-------------|----------| | `` | The role being removed. | Yes | | `USER ` | Remove the role from a user. | One of USER or ROLE | | `ROLE ` | Remove inheritance so `` no longer inherits ``. | One of USER or ROLE | Revoking a role removes only the access that role provided. If a user has the same access through another role, they keep it. Revoking a role from a user who does not hold it returns an error. :::warning If revoking a role leaves a user with no roles, that user becomes roleless and loses access to all data until someone with permission to assign roles assigns one. MotherDuck warns before completing the operation but does not block it. ::: ## Examples ### Remove a role from a user ```sql REVOKE ROLE finance FROM USER alice; ``` ## Related - [Roles and access control](/concepts/roles-and-access-control/) - [GRANT ROLE](/sql-reference/motherduck-sql-reference/access-control/grant-role/) - [DROP ROLE](/sql-reference/motherduck-sql-reference/access-control/drop-role/) --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/show-roles # SHOW roles and membership > Inspect roles, role membership, and role assignments with SHOW commands. **Requires permission:** View organization-wide role membership — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. Applies to inspecting other members and roles; without it, users can inspect only roles they belong to and their own assignments. SHOW ALL ROLES needs no permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do). These read-only commands report the state of roles and membership. They do not change any access. Listing a role name does not grant access to it. See [Roles and access control](/concepts/roles-and-access-control/) for the model. The `OF` and `TO` keywords set the direction of the lookup, matching Snowflake's `SHOW GRANTS` semantics: - **`OF ROLE`** walks down: the users and roles that hold the role. - **`TO ROLE`** and **`TO USER`** walk up: the roles a role or user inherits, transitively. ## SHOW ALL ROLES List every role in the organization, both preset and custom. Any member can run this command and sees role names only, with no membership details. ```sql SHOW ALL ROLES; ``` Output columns: | Column | Description | |--------|-------------| | `role_name` | Name of the role. | | `role_type` | `preset` or `custom`. | | `included_roles` | The roles this role inherits directly. | | `created_at` | When the role was created. | ## SHOW USERS OF ROLE List the users and service accounts that hold a role directly. ```sql SHOW USERS OF ROLE finance; ``` Output columns: | Column | Description | |--------|-------------| | `username` | Member or service account name. | | `email` | Member email. | | `is_service_account` | `true` for a service account. | | `granted_at` | When the role was assigned. | ## SHOW ROLES OF ROLE List the roles that hold a role directly, that is, the roles that inherit from it. ```sql SHOW ROLES OF ROLE finance; ``` Output columns: | Column | Description | |--------|-------------| | `role_name` | Name of a role that inherits this role. | | `role_type` | `preset` or `custom`. | | `granted_at` | When the inheritance was established. | ## SHOW ROLES TO ROLE List the roles a role inherits, walking up the hierarchy transitively. ```sql SHOW ROLES TO ROLE finance; ``` Output columns: | Column | Description | |--------|-------------| | `role_name` | Name of an inherited role. | | `role_type` | `preset` or `custom`. | | `is_direct` | `true` when the role is inherited directly, `false` when inherited through another role. | | `granted_at` | When a direct inheritance was established. `NULL` for a transitively inherited role. | ## SHOW ROLES TO USER List every role assigned to a user, walking up the hierarchy transitively. Useful for auditing what a user can access. ```sql SHOW ROLES TO USER alice; ``` Output columns: | Column | Description | |--------|-------------| | `role_name` | Name of an assigned role. | | `role_type` | `preset` or `custom`. | | `is_direct` | `true` when the role is assigned directly, `false` when inherited through another role. | | `granted_at` | When a direct assignment was made. `NULL` for a transitively inherited role. | ## Related - [Roles and access control](/concepts/roles-and-access-control/) - [SHOW grants](/sql-reference/motherduck-sql-reference/access-control/show-grants/) - [Manage roles and access](/key-tasks/managing-organizations/managing-roles/) --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/show-grants # SHOW grants > Inspect which roles and users can read a Share. **Requires permission:** View all organization Shares — included by default in the Admin preset role. A custom role qualifies when it inherits a role that includes the permission. Share owners can inspect grants on Shares they own without this permission. See [Roles and access control](/concepts/roles-and-access-control/#what-each-role-can-do). This read-only command reports who can read a Share. Visibility does not guarantee access. See [Roles and access control](/concepts/roles-and-access-control/) for the model. ## SHOW GRANTS ON SHARE List everyone with READ access to a Share: direct role and user grants, plus the automatic grant that an organization-wide or public Share carries. ```sql SHOW GRANTS ON SHARE core_metrics; ``` Output columns: | Column | Description | |--------|-------------| | `share_owner` | The user who owns the Share. | | `grantee_name` | The role name or username for a direct grant, or a fixed keyword for a whole-organization (`ENTIRE_ORGANIZATION`) or public (`ALL_USERS`) grant. | | `grantee_type` | The kind of grantee. See the values below. | | `privilege` | `read`. | | `granted_at` | When the grant was made. | `grantee_type` is one of: | `grantee_type` | `grantee_name` | Produced by | |----------------|----------------|-------------| | `role` | the role name | `GRANT READ ON SHARE ... TO ROLE` | | `user` | the username | `GRANT READ ON SHARE ... TO USER` | | `organization` | `ENTIRE_ORGANIZATION` | a Share created with `ACCESS ORGANIZATION`, readable by everyone in the owner's organization | | `domain` | `ALL_USERS` | a Share created with `ACCESS UNRESTRICTED`, readable by anyone with the Share URL | A Share created with `ACCESS ORGANIZATION` or `ACCESS UNRESTRICTED` carries a single automatic grant (`ENTIRE_ORGANIZATION` or `ALL_USERS`) rather than individual role or user grants. `GRANT` and `REVOKE` apply only to `RESTRICTED` Shares. :::note Shares are region-scoped based on your Organization's cloud region. A grant appearing here does not extend across regions: an `ACCESS UNRESTRICTED` (public) Share is only accessible to MotherDuck users in the same [cloud region](/about-motherduck/cloud-regions/) as the underlying data, while an `ACCESS ORGANIZATION` Share is only accessible to your Organization's users. ::: ## Related - [Roles and access control](/concepts/roles-and-access-control/) - [SHOW roles and membership](/sql-reference/motherduck-sql-reference/access-control/show-roles/) - [GRANT READ ON SHARE](/sql-reference/motherduck-sql-reference/grant-access/) --- Source: https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/access-control # Access control > SQL commands for role-based access control to create and manage roles, grant data access, and inspect roles and grants. Role-based access control (RBAC) commands let you manage roles and data access from SQL. Use them to create custom roles, assign roles to users, grant READ on Shares, and audit who has access. For the model behind these commands, see [Roles and access control](/concepts/roles-and-access-control/). For task walkthroughs, see [Manage roles and access](/key-tasks/managing-organizations/managing-roles/). **Command groups:** - **Roles**: [CREATE ROLE](/sql-reference/motherduck-sql-reference/access-control/create-role/), [DROP ROLE](/sql-reference/motherduck-sql-reference/access-control/drop-role/), [GRANT ROLE](/sql-reference/motherduck-sql-reference/access-control/grant-role/), [REVOKE ROLE](/sql-reference/motherduck-sql-reference/access-control/revoke-role/) - **Data grants**: [GRANT READ ON SHARE](/sql-reference/motherduck-sql-reference/grant-access/), [REVOKE READ ON SHARE](/sql-reference/motherduck-sql-reference/revoke-access/) - **Introspection**: [SHOW roles and membership](/sql-reference/motherduck-sql-reference/access-control/show-roles/), [SHOW grants](/sql-reference/motherduck-sql-reference/access-control/show-grants/) ## Included pages - [CREATE ROLE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/create-role): Create a custom role that carries platform privileges and data grants. - [DROP ROLE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/drop-role): Delete a custom role and remove it from all members who hold it. - [GRANT ROLE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/grant-role): Assign a role to a user or establish inheritance between roles. - [REVOKE ROLE](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/revoke-role): Remove a role from a user or remove inheritance between roles. - [SHOW roles and membership](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/show-roles): Inspect roles, role membership, and role assignments with SHOW commands. - [SHOW grants](https://motherduck.com/docs/sql-reference/motherduck-sql-reference/access-control/show-grants): Inspect which roles and users can read a Share. --- ## 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%2Faccess-control%2F&page_title=MotherDuck%20Documentation%20-%20Access%20control&text= ``` Optionally append `&source=` such as `claude.ai` or `chatgpt`. `page_path` and `text` are required; `page_title` and `source` are optional. Responses: `200 {"feedback_id": ""}`, `400` for malformed query parameters, and `429` when rate-limited.