The DROP USER statement removes one or more SQL users. You can use the keywords ROLE and USER interchangeably. DROP USER is an alias for DROP ROLE.
The DROP USER statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.
Consideration
Users that own objects (such as databases, tables, schemas, and types) cannot be dropped until the ownership is transferred to another user.
Required privileges
Non-admin users cannot drop admin users. To drop non-admin users, the user must be a member of the admin role or have the CREATEROLE parameter set.
Synopsis
See DROP ROLE: Synopsis.
Parameters
| Parameter | Description |
|---|---|
user_name |
The name of the user to remove. To remove multiple users, use a comma-separate list of usernames. You can use SHOW USERS to find usernames. |
Example
All of a user's privileges must be revoked before the user can be dropped.
In this example, first check a user's privileges. Then, revoke the user's privileges before removing the user.
CREATE DATABASE test;
CREATE TABLE customers (k int, v int);
CREATE USER max;
GRANT ALL ON TABLE customers TO max;
SHOW GRANTS ON customers FOR max;
database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
test | public | customers | max | ALL | f
(1 row)
You can test that dropping the user will fail unless all privileges are revoked (including default privileges). Issue the following statement to revoke a subset of the user's privileges:
REVOKE CREATE,INSERT,UPDATE ON customers FROM max;
If you attempt to drop a user with privileges remaining, you will encounter an error like the following:
DROP USER max;
ERROR: cannot drop role/user max: grants still exist on test.public.customers
SQLSTATE: 2BP01
To see what privileges the user still has remaining on the table, issue the following statement:
SHOW GRANTS ON TABLE test.customers FOR max;
database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
test | public | customers | max | BACKUP | f
test | public | customers | max | CHANGEFEED | f
test | public | customers | max | DELETE | f
test | public | customers | max | DROP | f
test | public | customers | max | SELECT | f
test | public | customers | max | ZONECONFIG | f
(6 rows)
To drop the user you must revoke all of the user's remaining privileges:
REVOKE ALL ON TABLE public.customers FROM max;
Now dropping the user should succeed:
DROP USER max;
See also
Was this helpful?
