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.
Considerations
- The
adminuser/role cannot be dropped, androotmust always be a member ofadmin. - A user/role cannot be dropped if it has privileges. Use
REVOKEto remove privileges. - Users/roles that own objects (such as databases, tables, schemas, and types) cannot be dropped until the ownership is transferred to another user/role.
- If a user/role is logged in while a different session drops that user, CockroachDB checks that the user exists before allowing it to inherit privileges from the
publicrole. In addition, any active web sessions are revoked when a user is dropped.
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
Remove privileges
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.
> SHOW GRANTS ON test.customers FOR mroach;
+-----------+--------+------------+
| Table | User | Privileges |
+-----------+--------+------------+
| customers | mroach | CREATE |
| customers | mroach | INSERT |
| customers | mroach | UPDATE |
+-----------+--------+------------+
(3 rows)
> REVOKE CREATE,INSERT,UPDATE ON test.customers FROM mroach;
> DROP USER mroach;
Remove default privileges
In addition to removing a user's privileges, a user's default privileges must be removed prior to dropping the user. If you attempt to drop a user with modified default privileges, you will encounter an error like the following:
ERROR: role mroach cannot be dropped because some objects depend on it
privileges for default privileges on new relations belonging to role demo in database movr
SQLSTATE: 2BP01
HINT: USE test; ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM mroach;
Run the HINT SQL prior to dropping the user.
USE test; ALTER DEFAULT PRIVILEGES REVOKE ALL ON TABLES FROM mroach;
> DROP USER mroach;
See also
Was this helpful?
