Remove User From MySQL

Basic database security involves having a password and user being assigned to  specific database, tables and their roles. This is also known as privileges in MySQL and Maria DB. In previous post, its shown how to list user permissions in MySQL database. But how do you remove that user?

The term MySQL in this post will refer to also MariaDb unless specifically mentioned otherwise.

The process to remove a user involves (1) to identify what privileges the user have, then (2)to revoke privileges and finally (3)remove the user from MySQL. In this tutorial, using the command line, a user with the name patrick is to be removed from the database quartz.

REVOKE statement

Firstly, here is the syntax;

REVOKE
    priv_type [(column_list)]
      [, priv_type [(column_list)]] ...
    ON [object_type] priv_level
    FROM user [, user] ...

REVOKE ALL PRIVILEGES, GRANT OPTION
    FROM user [, user] ...

REVOKE PROXY ON user
    FROM user [, user] ...

The REVOKE statement enables system administrators to revoke privileges
from MySQL accounts.

When the read_only system variable is enabled, REVOKE requires the
SUPER privilege in addition to any other required privileges described
in the following discussion.

Each account name uses the format described in
http://dev.mysql.com/doc/refman/5.7/en/account-names.html. For example:

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';

If you specify only the user name part of the account name, a host name
part of '%' is used.

For details on the levels at which privileges exist, the permissible
priv_type, priv_level, and object_type values, and the syntax for
specifying users and passwords, see [HELP GRANT]

To use the first REVOKE syntax, you must have the GRANT OPTION
privilege, and you must have the privileges that you are revoking.

To revoke all privileges, use the second syntax, which drops all global, database, table, column, and routine privileges for the named
user or users:

REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

To use this REVOKE syntax, you must have the global CREATE USER
privilege or the UPDATE privilege for the mysql database.

URL: http://dev.mysql.com/doc/refman/5.7/en/revoke.html

Step 1 : Identify existing user privileges

Before any revoke, it is a practise to verify the user privileges.

> SHOW GRANTS FOR patrick@'localhost';

If @'localhost' is not used, the default will be @'%' which will look like this;

> SHOW GRANTS FOR patrick@'localhost';
+----------------------------------------------------------------------------+
| Grants for patrick@localhost                                                            |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'patrick'@'localhost'
| GRANT ALL PRIVILEGES ON `quartz`.* TO 'patrick'@'localhost' WITH GRANT OPTION     |
+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Step 2 : Revoke(remove) user privilege

I have not mentioned use of roles, but there are options to manage large number of users by roles. Could be another topic. The REVOKE only works at the next user login. Which means, a user that is currently connected online will still have the privilege unchanged.

To revoke privileges of user for a specific task like INSERT;

> REVOKE INSERT ON  'quartz'.'*' FROM patrick@'localhost';

If there are many users to be removed at the same time, just append with a comma separator.

> REVOKE INSERT ON  'quartz'.'*' FROM patrick@'localhost', username2, username3;

All privileges of a user is removed with the REVOKE ALL PRIVILEGES, GRANT OPTION.

If the user did not have any privileges to be revoked, and ERROR 1269 is returned.

> REVOKE ALL PRIVILEGES, GRANT OPTION FROM patrick;
> SHOW GRANTS FOR patrick@'localhost';
+----------------------------------------------------------------------------+
| Grants for patrick@localhost                                |
+----------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'patrick'@'localhost'
+----------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Step 3. Remove(delete) user

The last step is to remove or delete the user.

> DROP USER patrick@'localhost';

The user is now safely removed from MySQL along with its privileges.

Practise

Here is an exercise which should reinforce the skills and knowledge from this tutorial. The user Patrick is assigned to retrieve data from a database named fruit_shop. Patrick will only be working on this database for 1 day and his user access is to be removed from the database once his task is done.

1. Login (as root) to MySQL and create a database named fruit_shop.
2. Create and apply a user named patrick who can only user the commands for SELECT and SHOW VIEW, with access from any host, using the password Banana1234 
3. Display list of user privileges on the system.
4. Remove all privileges for patrick.
5. Display list of user privileges on the system.
6. Remove user patrick.
7. Display list of user privileges on the system.