> ## Documentation Index > Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt > Use this file to discover all available pages before exploring further. # DROP OWNED BY The `DROP OWNED BY` [statement](https://docs.cockroachlabs.com/docs/stable/sql-statements.md) drops all objects owned by and any [grants](https://docs.cockroachlabs.com/docs/stable/grant.md) on objects not owned by a [role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#roles). > [!NOTE] > > The `DROP OWNED BY` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see [Online Schema Changes](https://docs.cockroachlabs.com/docs/stable/online-schema-changes.md). ## Required privileges The [role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#roles) must have the `DROP` [privilege](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#managing-privileges) on the specified objects. `DROP OWNED BY` will result in an error if the user was granted a [system-level privilege](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#supported-privileges) (i.e., using the [`GRANT SYSTEM ...`](https://docs.cockroachlabs.com/docs/stable/grant#grant-system-level-privileges-on-the-entire-cluster) statement). To work around this, use [`SHOW SYSTEM GRANTS FOR `](https://docs.cockroachlabs.com/docs/stable/show-system-grants) and then use [`REVOKE SYSTEM ...`](https://docs.cockroachlabs.com/docs/stable/revoke#revoke-system-level-privileges-on-the-entire-cluster) for each system-level privilege in the result. ## Synopsis ![drop\_owned\_by syntax diagram](https://mintcdn.com/cockroachlabs/UZNSFbmU2EwEm6G5/images/sql-diagrams/common/drop_owned_by.svg?fit=max\&auto=format\&n=UZNSFbmU2EwEm6G5\&q=85\&s=d70c15d9bb657954d669b479e11842ea) ## Parameters | Parameter | Description | | - | - | | `role_spec_list` | The source [role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#roles), or a comma-separated list of source roles. | | `RESTRICT` | *(Default)* Do not drop ownership if any objects (such as [constraints](https://docs.cockroachlabs.com/docs/stable/constraints.md) and tables) use it. | | `CASCADE` | Not implemented. | ## Known limitations * [`ENUM`](https://docs.cockroachlabs.com/docs/stable/enum) types are not dropped. * [`DROP OWNED BY`](https://docs.cockroachlabs.com/docs/stable/drop-owned-by) drops all owned objects as well as any [grants](https://docs.cockroachlabs.com/docs/stable/grant.md) on objects not owned by the [role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#roles). * If the [role](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#roles) for which you are trying to `DROP OWNED BY` was granted a [system-level privilege](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#supported-privileges) (i.e., using the [`GRANT SYSTEM ...`](https://docs.cockroachlabs.com/docs/stable/grant#grant-system-level-privileges-on-the-entire-cluster) statement), the following error will be signalled: ``` ERROR: cannot perform drop owned by if role has synthetic privileges; foo has entries in system.privileges SQLSTATE: 0A000 HINT: perform REVOKE SYSTEM ... for the relevant privileges foo has in system.privileges ``` The phrase "synthetic privileges" in the error message refers to [system-level privileges](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md#supported-privileges). The workaround is to use [`SHOW SYSTEM GRANTS FOR {role}`](https://docs.cockroachlabs.com/docs/stable/show-system-grants) and then use [`REVOKE SYSTEM ...`](https://docs.cockroachlabs.com/docs/stable/revoke#revoke-system-level-privileges-on-the-entire-cluster) for each privilege in the result. ## Examples The following examples assume a [local cluster is running](https://docs.cockroachlabs.com/docs/stable/start-a-local-cluster.md). They involve a user we will create called `maxroach` and several tables. The setup is shown below. From a Terminal window, open a SQL shell as the `root` user: ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach sql --insecure --host localhost --port 26257 ``` Next, create the user `maxroach`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE USER IF NOT EXISTS maxroach; ``` From a second Terminal window, open a SQL shell as the newly created user `maxroach`. ```shell theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} cockroach sql --insecure --host localhost --port 26257 --user maxroach ``` ### Drop all objects owned by a user/role From the `maxroach` user's SQL shell, create a table called `max_kv`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE IF NOT EXISTS max_kv (k INT, v INT); ``` To verify that this table is owned by `maxroach`, use [`SHOW GRANTS`](https://docs.cockroachlabs.com/docs/stable/show-grants): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS FOR maxroach; ``` ``` database_name | schema_name | object_name | grantee | privilege_type | is_grantable ----------------+-------------+---------------+----------+----------------+--------------- defaultdb | public | max_kv | maxroach | ALL | t (1 row) ``` To drop all of the objects owned by the user `maxroach`, switch to the `root` user's SQL shell and use `DROP OWNED BY`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} DROP OWNED BY maxroach; ``` In this case, `maxroach` only owns the `max_kv` table, so this will drop that table from the database completely. To confirm that the table has been dropped, run [`SHOW TABLES`](https://docs.cockroachlabs.com/docs/stable/show-tables): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW TABLES; ``` ``` SHOW TABLES 0 ``` From the `root` user's SQL shell, use [`SHOW GRANTS`](https://docs.cockroachlabs.com/docs/stable/show-grants) to further confirm that the `maxroach` user has no remaining object grants: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS FOR maxroach; ``` ``` SHOW GRANTS 0 ``` ### Drop all grants on objects for a user/role From the `root` user's SQL shell, create a table called `root_kv`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} CREATE TABLE IF NOT EXISTS root_kv (k INT, v INT); ``` Next, grant all privileges on that table to user `maxroach` using [`GRANT ALL`](https://docs.cockroachlabs.com/docs/stable/grant): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} GRANT ALL on root_kv TO maxroach; ``` Next, confirm that the user `maxroach` has all privileges on the table using [`SHOW GRANTS`](https://docs.cockroachlabs.com/docs/stable/show-grants): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS FOR maxroach; ``` ``` database_name | schema_name | object_name | grantee | privilege_type | is_grantable ----------------+-------------+---------------+----------+----------------+--------------- defaultdb | public | root_kv | maxroach | ALL | f (1 row) ``` Next, switch to the `maxroach` user's SQL shell, and insert some data into the table. It should succeed: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} INSERT INTO root_kv(k) select i from generate_series(1,10) as i; ``` ``` INSERT 0 10 ``` Next, switch to the `root` user's SQL shell and use `DROP OWNED BY` to remove all grants on objects to the user `maxroach`: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} DROP OWNED BY maxroach; ``` Next, confirm that the user `maxroach` has no grants on any objects using [`SHOW GRANTS`](https://docs.cockroachlabs.com/docs/stable/show-grants): ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} SHOW GRANTS FOR maxroach; ``` ``` SHOW GRANTS 0 ``` Finally, switch back to the `maxroach` user's SQL shell and try to insert data into the `root_kv` table. This should signal an error: ```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}} INSERT INTO root_kv(k) select i from generate_series(1,10) as i; ``` ``` ERROR: user maxroach does not have INSERT privilege on relation root_kv SQLSTATE: 42501 ``` ## See also * [Authorization in CockroachDB](https://docs.cockroachlabs.com/docs/stable/security-reference/authorization.md) * [`GRANT`](https://docs.cockroachlabs.com/docs/stable/grant) * [`SHOW GRANTS`](https://docs.cockroachlabs.com/docs/stable/show-grants) * [`REVOKE`](https://docs.cockroachlabs.com/docs/stable/revoke) * [`REASSIGN OWNED`](https://docs.cockroachlabs.com/docs/stable/reassign-owned) * [`DROP ROLE`](https://docs.cockroachlabs.com/docs/stable/drop-role) * [`SHOW TABLES`](https://docs.cockroachlabs.com/docs/stable/show-tables) * [SQL Statements](https://docs.cockroachlabs.com/docs/stable/sql-statements.md) * [Online Schema Changes](https://docs.cockroachlabs.com/docs/stable/online-schema-changes.md)