Step 4: Change or Revoke the Access Rights of a MySQL User
If you want to change or revoke the permissions of a MySQL user, you can use the REVOKE command. The general format of this command is as follows:
REVOKE privileges ON database.table FROM 'username''host';
Here, you should replace the privileges, database, table, username, and host with the corresponding values. privileges is the access right that you want to change or revoke from the user. You can use specific or global access rights as described earlier. database, table, username, and host are the names of databases, tables, users, and hosts that you have previously granted access rights to.
For example, if you want to revoke DELETE permissions from a user1 user on all tables in db1 database, you can use the following command:
REVOKE DELETE ON db1.* FROM 'user1''%';
If the command is successful, you’ll see a message like this:
Query OK, 0 rows affected (0.01 sec)
You can check if the access rights have been revoked correctly by using the SHOW GRANTS command:
SHOW GRANTS FOR 'user1''%';
This command will display all the access rights owned by the user1 user. You should see results like this:
+--------------------------------------------------------------+ | Grants for user1% | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO `user1``%` | | GRANT SELECT, INSERT, UPDATE ON `db1`.* TO `user1``%` | +--------------------------------------------------------------+ 2 rows in set (0.00 sec)
Here, you can see that DELETE access rights have been revoked from user1 users on all tables in db1 database.
Step 5: Delete MySQL Users
If you want to delete a MySQL user, you can use the DROP USER command. The general format of this command is as follows:
DROP USER 'username''host';
Here, you’ll need to replace the username and host with the username and host you want to remove. This command deletes the user and all permissions it has.
For example, if you want to delete a user1 user, you can use the following command:
DROP USER 'user1''%';
If the command is successful, you’ll see a message like this:
Query OK, 0 rows affected (0.01 sec)
You can check if a user has been deleted correctly by using the SELECT command in the table mysql.user:
SELECT User, Host FROM mysql.user;
This command will display all existing MySQL users and their hosts. You should see results like this:
+------------------+-----------+ | User | Host | +------------------+-----------+ | root | localhost | | mysql.session | localhost | | mysql.sys | localhost | +------------------+-----------+ 3 rows in set (0.00 sec)
Here, you can see that user1 user has been removed from the MySQL user list.
Conclusion
MySQL has several useful commands for managing users and access rights. Using these commands, you can organize your MySQL database better and more securely.
Hope you find this article useful. Thanks for reading.