Skip to main content
The DROP OWNED BY drops all objects owned by and any on objects not owned by a .
The DROP OWNED BY statement performs a schema change. For more information about how online schema changes work in CockroachDB, see .

​
Required privileges

The must have the DROP on the specified objects. DROP OWNED BY will result in an error if the user was granted a (i.e., using the statement). To work around this, use and then use for each system-level privilege in the result.

​
Synopsis

drop_owned_by syntax diagram

​
Parameters

​
Known limitations

  • types are not dropped.
  • drops all owned objects as well as any on objects not owned by the .
  • If the for which you are trying to DROP OWNED BY was granted a (i.e., using the statement), the following error will be signalled:
    The phrase “synthetic privileges” in the error message refers to . The workaround is to use and then use for each privilege in the result.

​
Examples

The following examples assume a . 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:
Next, create the user maxroach:
From a second Terminal window, open a SQL shell as the newly created user maxroach.

​
Drop all objects owned by a user/role

From the maxroach user’s SQL shell, create a table called max_kv:
To verify that this table is owned by maxroach, use :
To drop all of the objects owned by the user maxroach, switch to the root user’s SQL shell and use DROP OWNED BY:
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 :
From the root user’s SQL shell, use to further confirm that the maxroach user has no remaining object grants:

​
Drop all grants on objects for a user/role

From the root user’s SQL shell, create a table called root_kv:
Next, grant all privileges on that table to user maxroach using :
Next, confirm that the user maxroach has all privileges on the table using :
Next, switch to the maxroach user’s SQL shell, and insert some data into the table. It should succeed:
Next, switch to the root user’s SQL shell and use DROP OWNED BY to remove all grants on objects to the user maxroach:
Next, confirm that the user maxroach has no grants on any objects using :
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:

​
See also