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
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 theGRANT 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 useREVOKE SYSTEM ...
for each privilege in the result. #88149
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:
cockroach sql --insecure --host localhost --port 26257
Next, create the user maxroach
:
CREATE USER IF NOT EXISTS maxroach;
From a second Terminal window, open a SQL shell as the newly created user maxroach
.
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
:
CREATE TABLE IF NOT EXISTS max_kv (k INT, v INT);
To verify that this table is owned by maxroach
, use SHOW GRANTS
:
SHOW GRANTS FOR maxroach;
database_name | schema_name | relation_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
:
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
:
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:
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
:
CREATE TABLE IF NOT EXISTS root_kv (k INT, v INT);
Next, grant all privileges on that table to user maxroach
using GRANT ALL
:
GRANT ALL on root_kv TO maxroach;
Next, confirm that the user maxroach
has all privileges on the table using SHOW GRANTS
:
SHOW GRANTS FOR maxroach;
database_name | schema_name | relation_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:
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
:
DROP OWNED BY maxroach;
Next, confirm that the user maxroach
has no grants on any objects using SHOW GRANTS
:
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:
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