Understanding MySQL Users

The Default Account

When MySQL is installed the default user is created, root. This account is the server administrator with full rights to the server. All user accounts to MySQL are stored in the system database mysql. We can query the table user:

SELECT User FROM mysql.User;

We may see more than one entry for root on my system we see three entries. The user accounts exist for system or hosts that the user is permitted to access the MySQL server from. For root I have configured the account to be able to be used for local access only. If we extend our query to include the Host column then as in the following;

SELECT User, Host FROM mysql.user;

We will see that the 3 entries are for:

• localhost
• ::1
• 127.0.0.1

Being the host name for the local server and the IPv6 and IPv4 addresses; this is shown in the following screen shot:

Creating Users

We may need additional accounts and we certainly need to consider creating more accounts than just root for both security and auditing purposes. The root user can create accounts as can other users with the CREATE_USER privilege.

CREATE USER bob;

This creates and account for the user bob without a password and the ability to connect from any host. Using the user name on its own will equate to a host name of % the wild card character that means any character and any amount of characters. The full code for this would be:

CREATE USER ‘bob’@’%’;

If we want to control access to just the local machine, in the case of application access from the server we could create the user as :

CREATE USER ‘bob’@’localhost’;

We can use host names or IP addresses in the user name so if a user needed to connect from the local network we may add an entry such as:

CREATE USER ‘bob’@’196.168.1.%’;

Setting the Password

So far we have created users without a password MySQL allows this but you may not wish it. Using the SQL code:

CREATE USER bob IDENTIFIED by ‘Password’;

This will create the user that will be able to log in from any host using the specified password.

We can also set a users password at a later date or change the password with the code:

SET PASSWORD for ‘bob’@’%’ PASSWORD(‘Password’);

The password will be stored encrypted in the mysql.user table as we can see with the following code:

SELECT USER, Host, Password FROM mysql.user;

Deleting Users

Of course id we have the correct privileges we can both create and delete users. The same privileges, CREATE_USER, allow for the holding account to both create and drop accounts. Drop is the mysql term for deleting objects on the server rather than delete which refer to removal of rows.

DROP USER ‘bob’;

Would delete bob@% who could log on from any host

DROP USER ‘bob’@’localhost’;

Would delete the user bob who can log in from the named host localhost.

The use of the single quotes in the user and host names are only required if special characters are used so we could write bob@localhost or ‘bob’@’localhost’