Skip to main content
The DROP OWNED BY statement drops all objects owned by and any grants on objects not owned by a role. The DROP OWNED BY 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 <role> and then use REVOKE SYSTEM... for each system-level privilege in the result.

​
Synopsis

drop_owned_by syntax diagram

​
Parameters

​
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:
    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:
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 SHOW GRANTS:
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 SHOW TABLES:
From the root user’s SQL shell, use SHOW GRANTS 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 GRANT ALL:
Next, confirm that the user maxroach has all privileges on the table using SHOW GRANTS:
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 SHOW GRANTS:
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