Introduction: Create and Drop a User

When we logged in in the previous chapter of our tutorial, we used the root account of MySQL. This is not recommended. We should only use the superuser account, when it is really necessary. To this purpose, we have to create a new user, which we can use for following examples.

Create a Database User

We create a user 'frank' with the password 'e2718282' with the following command:

mysql> CREATE USER 'frank'@'localhost' IDENTIFIED BY 'test123';
Query OK, 0 rows affected (0.00 sec)

mysql> 

The user frank can now login from localhost to our MySQL server. But "Frank" can't use the user "frank" to access from another host. To ensure that Frank can login from any host, we have to create another account for the user frank. The percentage sign '%' is used as a wildcard for the host part. '%' stands for any host.

mysql> CREATE USER frank@'%' IDENTIFIED BY 'e2718282';
Query OK, 0 rows affected (0.00 sec)

mysql>

We can see all the users from our mysql database with the following "Select" command:

mysql> SELECT user, host, password FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *BA65ADF2949EE18F0D3F54A88642AE9C9C7F91A0 |
| root             | venus     | *BA65ADF2949EE18F0D3F54A88642AE9C9C7F91A0 |
| root             | 127.0.0.1 | *BA65ADF2949EE18F0D3F54A88642AE9C9C7F91A0 |
| debian-sys-maint | localhost | *07EC8E910DC25B34A5409E06A2398182DD97336D |
| phpmyadmin       | localhost | *F9D3072F9DD47E6E230A11723F8886E1A77B06DA |
| frank            | localhost | *598E1E92BB96DCC92120EE0576967A0A8F000591 |
| frank            | %         | *598E1E92BB96DCC92120EE0576967A0A8F000591 |
+------------------+-----------+-------------------------------------------+
7 rows in set (0.00 sec)

mysql> 

Removing a User

Even though we have just created the user frank, we want to provide you with the command to remove a user again. This can be done with the "DROP" command:

mysql> DROP USER frank@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> DROP USER frank@'%';
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT user, host, password FROM mysql.user;
+------------------+-----------+-------------------------------------------+
| user             | host      | password                                  |
+------------------+-----------+-------------------------------------------+
| root             | localhost | *BA65ADF2949EE18F0D3F54A88642AE9C9C7F91A0 |
| root             | venus     | *BA65ADF2949EE18F0D3F54A88642AE9C9C7F91A0 |
| root             | 127.0.0.1 | *BA65ADF2949EE18F0D3F54A88642AE9C9C7F91A0 |
| debian-sys-maint | localhost | *07EC8E910DC25B34A5409E06A2398182DD97336D |
| phpmyadmin       | localhost | *F9D3072F9DD47E6E230A11723F8886E1A77B06DA |
+------------------+-----------+-------------------------------------------+
5 rows in set (0.00 sec)

mysql> 

You can see from the above session, that we have successfully removed both accounts for frank.