> ## Documentation Index
> Fetch the complete documentation index at: https://cockroachlabs.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
# DROP OWNED BY
export const InternalLink = ({version, path = "", children, ...props}) => {
let detectedVersion = version || "stable";
if (typeof window !== 'undefined' && !version) {
const match = window.location.pathname.match(/\/docs\/([^/]+)/);
if (match) {
detectedVersion = match[1];
}
}
const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
return
{children}
;
};
The `DROP OWNED BY` statement drops all objects owned by and any grants on objects not owned by a role.
The \`\` statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
## Required privileges
The role must have the `DROP` privilege on the specified objects.
`DROP OWNED BY` will result in an error if the user was granted a system-level privilege (i.e., using the `GRANT SYSTEM ...` statement). To work around this, use `SHOW SYSTEM GRANTS FOR ` and then use `REVOKE SYSTEM ...` for each system-level privilege in the result.
## Synopsis
## Parameters
| Parameter | Description |
| ---------------- | ----------------------------------------------------------------------------------------------------------------------------------------- |
| `role_spec_list` | The source role, or a comma-separated list of source roles. |
| `RESTRICT` | *(Default)* Do not drop ownership if any objects (such as constraints and tables) use it. |
| `CASCADE` | Not implemented. |
## Known limitations
* `ENUM` types are not dropped.
* `DROP OWNED BY` drops all owned objects as well as any grants on objects not owned by the role.
* If the role for which you are trying to `DROP OWNED BY` was granted a system-level privilege (i.e., using the `GRANT SYSTEM ...` 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.
The workaround is to use `SHOW SYSTEM GRANTS FOR {role}` and then use `REVOKE SYSTEM ...` for each privilege in the result.
## Examples
The following examples assume a local cluster is running. 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`:
```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`:
```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
SHOW TABLES;
```
```
SHOW TABLES 0
```
From the `root` user's SQL shell, use `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`:
```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`:
```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`:
```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
* `GRANT`
* `SHOW GRANTS`
* `REVOKE`
* `REASSIGN OWNED`
* `DROP ROLE`
* `SHOW TABLES`
* SQL Statements
* Online Schema Changes