Complete DROP statement reference for MariaDB. Complete guide for safely removing database objects with CASCADE options with comprehensive examples and best.
The DROP USER statement removes one or more MariaDB accounts. It removes privilege rows for the account from all grant tables. To use this statement, you must have the global privilege or the privilege for the mysql database. Each account is named using the same format as for the CREATE USER statement; for example, 'jeffrey'@'localhost'. If you specify only the user name part of the account name, a host name part of '%' is used. For additional information about specifying account names, see .
If you specify an account that is currently connected, it is deleted, but the statement completes with a warning:
This means that the user account with all its privileges is still active until the connection is terminated. As the warning implies, use a KILL CONNECTION statement to terminate a connection, or (more proactively) use the FORCE clause to forcibly close connections of the users named in the DROP USER statement. This ends connections, and immediately deletes the users.
In , if a user is connected, the DROP USER statement fails with an error:
Again, use the FORCE clause to prevent that error.
If any of the specified user accounts do not exist, ERROR 1396 (HY000) results. If an error occurs, DROP USER still drops the accounts that do not result in an error. Only one error is produced for all users which have not been dropped:
Failed CREATE or DROP operations, for both users and roles, produce the same error.
IF EXISTS
If the IF EXISTS clause is used, MariaDB returns a note instead of an error if the user does not exist.
The CREATE USER statement creates new MariaDB accounts. To use it, you must have the global privilege or the privilege for the database.
If the IF EXISTS clause is used, MariaDB returns a note instead of an error if the user does not exist.
Examples
IF EXISTS:
See Also
This page is licensed: GPLv2, originally from
If you specify an account that is currently connected, it is not deleted until the connection is closed. The connection is not automatically closed.
However, a deleted user cannot initiate new connections any more.
Dropped users 'user'@'host[,...]' have active connections. UseKILLCONNECTIONif they should not be used anymore.
Operation DROP USER failed for'foo'@'localhost'.
ERROR 1396 (HY000): Operation DROP USER failed for 'u1'@'%','u2'@'%'
DROP USER bob;
DROP USER foo2@localhost,foo2@'127.%';
DROP USER bob;
ERROR 1396 (HY000): Operation DROP USER failed for 'bob'@'%'
DROP USER IF EXISTS bob;
Query OK, 0 rows affected, 1 warning (0.00 sec)
SHOW WARNINGS;
+-------+------+---------------------------------------------+
| Level | Code | Message |
+-------+------+---------------------------------------------+
| Note | 1974 | Can't drop user 'bob'@'%'; it doesn't exist |
+-------+------+---------------------------------------------+