MySQL user management

1.4K

MySQL is among the most popular database managers in the world. It contains many features that make it reliable, efficient, and robust. Its maximum reliability is achieved if it is rightly used. Creating users with limited permissions on the database is one of the easiest and reliable ways to increase MySQL security.

MySQL create user

When installing MySQL, the root user (MySQL administrator) is the first user to be created. The root user is allowed to do anything and everything on the MySQL database. It is convenient for other people to access your database using this account.

Malicious users might try to log in as root users to steal the hosted information or destroy the service alongside the data. Therefore, the system administrator has to create users with specific permissions on the database. This ensures that if the security of that user is compromised, the impact is minimal or manageable.

MySQL Create User statement allows you to create a new user account in the database server. It provides authentication, resource limit, role and password management properties for the new accounts. The statement also allows us to control the accounts that should be locked or unlocked.

To Create a User, it is required to have a global privilege (be a root user) of the “CREATE USER” statement or the INSERT privilege for the MySQL system schema. An error appears if you try to create a user that already exists but if you use the “IF NOT EXISTS” clause, the statement gives a warning for each named user that already exists instead of an error message.

To create a non-root user and grant them specific privileges to access and modify the database, we use the following syntax:

CREATE USER IF NOT EXISTS 'fosslinux'@'localhost' IDENTIFIED BY 'foss12345';

The account_name has two parts; the username and hostname, separated by the @ symbol. The username is the user’s name, while the hostname is the host’s name from which the user can connect with the database server.

username@hostname

The hostname is optional. If the hostname is not given, the user can connect from any host on the server. A user account name without a hostname can be written as username@%

CREATE USER IF NOT EXISTS 'fosslinux@%' IDENTIFIED BY 'foss12345';

Note that the create user statement creates a new user with full access. to give privileges to the user, use the GRANT statement

Use the following steps to create a new user in the MySQL database:

1. By using the MySQL client tool, open the MySQL server

2. Type in the password, then press Enter.

3. Create a new user using the following command

create user 'foss'@'localhost' identified by 'foss12345'; 

4. Use the following command to show users in the MySQL server

select user from mysql.user;

From the output shown, the user ‘foss’ has been created successfully.

5. Now use the IF NOT EXISTS clause with the CREATE USER statement by running the following command:

Granting privileges to the MySQL New User

Some of the most common privileges provided by the MySQL server to a new user include;

1. ALL PRIVILEGES: permits all privileges to a new user account

2. CREATE: enables the user account to create databases and tables

3. DROP: enables the user account to drop databases and tables

4. DELETE: enables the user account to delete rows from a specific table

5. INSERT: enables the user account to insert rows into a specific table

6. SELECT: enables the user account to read a database

7. UPDATE: enables a user account to update table rows

Execute the following command to grant all privileges to a user

GRANT ALL PRIVILEGES ON *.* TO 'foss'@'localhost';

Execute the following command to give specific privileges to a newly created user,

GRANT CREATE, SELECT, INSERT ON *.* TO 'foss'@'localhost';

To flush all privileges that are assigned to a user, execute the command below.

FLUSH PRIVILEGES;

To see the existing privileges assigned to a user, execute the following command.

SHOW GRANTS FOR 'foss'@'localhost';

MySQL Drop User

MySQL Drop User statement allows you to remove one or more user accounts and their privileges from the database server. If the user account does not exist in the database server, it will return an error.

To use the Drop User statement, you need to have a global privilege or the DELETE privilege for the MySQL system schema.

The syntax for deleting user accounts from the database server entirely is as follows:

DROP USER 'foss'@'localhost';

Note: The account name in the example above is identified as username@hostname. The username is the name of the account you want to delete from the database server, and the hostname is the server’s name of the user account. for example, ‘foss@localhost’. foss is the username, while localhost is the hostname.

The following are steps to follow when deleting an existing user from the MySQL server database;

1. By using the MySQL client tool, open the MySQL server

2. Type in the account password, then press Enter

3. To drop a user account, execute the following command

DROP USER 'fosslinux'@'localhost';

4. Execute the following command to show users

select user from mysql.user; 

You will get an output in which the username fosslinux will not be present, as shown below:

5. The Drop user can also be used to remove more than one user accounts at once. To do that, you separate the account_names using commas. 

Note: Since we had already dropped the users, we shall create two users using the create user concepts (fosslinuxtuts and fosslinux@%). After that, we shall run the command in step 4 to show the existing users as shown below:

Now drop the two users simultaneously using the command below: Execute the following command to drop both users:

DROP USER 'fosslinuxtuts'@'localhost', 'fosslinux@%' ;

Note that the DROP statement cannot close any open user sessions automatically. When the DROP statement is executed while a user account session is active, the command does not affect it until its session is closed. The user account is dropped only when the session is closed, and the user’s next attempt will not be able to log in again.

MySQL show users/list all users

To manage a database in MySQL, you need to see the list of all user’s accounts in a database. People assume that there is a SHOW USERS command similar to SHOW DATABASE or SHOW TABLES for displaying the list of all users available in the database server. Unfortunately, the MySQL server does not have a SHOW USERS command to display a list of all users in the MySQL server. So instead, we use the following query to see the list of all users in the database server:

Select user from mysql.user;

After executing the command, you will get the user data from the user table of the MySQL database server.

Note: In this example, we shall use the MySQL database. Select the database by running the command below, then use the select from statement to check the existing users as shown in the image below:

use mysql;
SELECT user FROM user;

If you want to see more information on the user table, execute the following command:

DESC user;

The command will give the following output that lists all the available columns of the mysql.user database:

To get selected information like hostname, password expiration status, and account locking, execute the following query:

 SELECT user, host, account_locked, password_expired FROM user;

The following output will appear after successful execution of the query:

Show current user

You can get information of the current user by using the user() or current_user() function as shown below:

Select user(); 

OR

Select current_user();

The following output will appear after successfully executing any of the above commands.

Show currently logged user

You can see a user who is currently logged in to the database server by using the following command in the MySQL server:

SELECT user, host, db, command FROM information_schema.processlist;

The above commands output a similar screen to the one shown below:

How to change MySQL user password

MySQL user records contain the login information, account privileges, and host information for MySQL account to access and manage the database. Login information includes the username and password. Therefore, there may be a need to change the user password in the MySQL database.

To change any user account password, remember to have the following information in mind:

• Details of the user account to be changed
• The application is used by the user account whose password is to be changed. If the user account password has been reset without changing an application connection string, the application cannot connect with the database server.

MySQL allows you to change the user account password in three different ways:

1. UPDATE statement
2. SET PASSWORD statement
3. ALTER USER statement

Changing user account password using the UPDATE statement

After executing the UPDATE statement, we use the FLUSH PRIVILEGE statement to reload privileges from the grant table of the MySQL database.
Suppose you want to change the password for a user account foss that connects from the localhost with the password foss12345, execute the following command:

USE mysql;

UPDATE user SET password=password('kip12345') WHERE user='foss'; 

FLUSH PRIVILEGES;

The above statement will not work on MySQL version 5.7.6 or higher since the MySQL user table contains the authentication string column that stores the password only. Higher versions have the authentication string column in the UPDATE statement as shown in the following statement:

USE mysql;

UPDATE user SET authentication_string = password('foss12345') WHERE user = 'foss';

FLUSH PRIVILEGES;

Note: This method only works with older versions of MySQL. Therefore, if you have newer or latest MySQL versions, skip to the other methods.

Changing user account password using SET PASSWORD statement

If you want to change another account password, you need to have the UPDATE privilege. The statement uses the user account in the following format: username@localhost

Reloading privileges from the rant tables of MySQL database using the FLUSH PRIVILEGES does not need to be used. To change the password of the user account (foss) by using the SET PASSWORD statement, use the following statement:

SET PASSWORD FOR 'foss'@'localhost' = PASSWORD('foss12345');

If you are using MySQL version 5.7.6 or higher, the above statement is deprecated and won’t work in future releases. Instead, use the following statement;

SET PASSWORD FOR 'foss'@'localhost' = 'foss12345';

Changing user account password using ALTER USER statement

MySQL uses ALTER USER statement with the IDENTIFIED BY clause. To do that, you use the following syntax:

ALTER USER 'foss'@'localhost' IDENTIFIED BY 'foss12345';

You might need to reset the MySQL root account password. To do this, you can force stop then restart the MySQL database server without using the grant table validation.

Conclusion

This article has comprehensively covered all the aspects regarding MySQL users management. We believe it is detailed enough to provide you with solutions to your problems. If you encounter errors or difficulties while showing users using MySQL, please reach out via the comments section for help. Thanks for reading.