Managing MySQL users through the command line

In our previous publication, we covered the process of managing MySQL databases through the use of the command line. However, it is important to note that database administration in MySQL encompasses more than just the management of databases. This is due to the fact that databases are individual entities that cannot be managed without the creation of MySQL users who possess the necessary privileges to execute actions on these databases.

Consequently, the management of MySQL users and their privileges represents an equally important aspect of MySQL database administration. In this guide, I will demonstrate how to manage MySQL users and their privileges. Specifically, I will illustrate how to create a MySQL User, extract a listing of MySQL users, establish a database and grant complete privileges on said database to a designated MySQL User, and ultimately delete a specific MySQL user. Additionally, we will explore the process of creating a MySQL user with similar privileges to the root user, who possesses the authority to generate new users and assign privileges to other users.

It is advisable to follow this guide and actively practice MySQL user management in order to acquire a comprehensive understanding of the topic. To facilitate this, you will require a Virtual Private Server (VPS). You can obtain a brand new VPS from WebHostingPeople by clicking here. Once this has been accomplished, proceed to install MySQL on your VPS by utilizing the provided commands.

$ sudo apt-get install mysql-server mysql-client -y
$ sudo mysql_secure_installation

Once MySQL has been installed on your server, execute the subsequent command to initiate a login session in your MySQL server as a root user. The root user possesses complete privileges, enabling them to create new users, assign privileges, generate databases, and execute numerous other actions. Consequently, the root user is not subject to any limitations.

$ mysql -uroot -p;

At this point, you will be prompted to enter your MySQL password. Proceed to enter the correct password that was established during the installation of MySQL on your server. Once you have successfully logged in, you will be prepared to create your first MySQL user.

Creating a MySQL User

Before executing the initial MySQL query to create a MySQL user, it is essential to familiarize oneself with the processes of user management and authentication within MySQL. It is important to recognize that MySQL authenticates a user based on their Username, Password, and Host. Consequently, it is necessary to define a user’s Username, Password, and Host within MySQL, rather than just their username and password.

For instance, let us imagine that we seek to establish a user with the username “test,” the password “qawsedrf,” and the host “localhost.” To create a MySQL user with this information, you must execute the subsequent query within either the MySQL console or CLI.

mysql> CREATE USER 'test'@'localhost' IDENTIFIED BY 'qawsedrf';

In the aforementioned query, you are already familiar with the username and password. However, what is the significance of the host in MySQL? The host refers to the hostname/IP address from which a MySQL user can access the MySQL server. Consequently, if you wish to access the MySQL server from the same computer, it is necessary to employ localhost as the host. However, if you intend to access the server remotely from another machine, you may enter the Hostname or IP address of that machine as the host.

In the event that you desire to remotely access the MySQL server from any machine across the globe, it is possible to designate the host as %. In this scenario, the query to create a MySQL user will assume the following form:

mysql> CREATE USER 'test'@'%' IDENTIFIED BY 'qswaefrd';

Thus, you can observe the procedure for creating a user account in MySQL. Nevertheless, it is essential to acknowledge that at the present moment, our user does not possess any form of privileges on any database(s). As a result, our user is incapable of managing databases hosted on the recently installed MySQL server. To enable our user to manage database(s), it is necessary to grant them privileges. We shall delve deeper into this facet later on within this guide.

Listing MySQL Users

Generating a list of MySQL users represents one of the simplest tasks within MySQL. Merely a single query is required to obtain a roster of all the active user accounts on our MySQL server. All pertinent information regarding these users is stored within the mysql.user table, with mysql denoting the database and user signifying the table.

To obtain a comprehensive list of all available MySQL users, it is only necessary to execute a standard MySQL query that retrieves data from the aforementioned table. Employ the following query to obtain a roster of all MySQL users:

mysql> SELECT User,Host from mysql.user;

If you are actively following this guide, executing the above query will yield the subsequent output:

+------------------+-----------+
| User             | Host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| test             | localhost |
+------------------+-----------+
5 rows in set (0.00 sec)

It is feasible to obtain additional information regarding MySQL users by incorporating the desired columns into the SELECT query. Consequently, this represents the mechanism for securing a list of MySQL users.

Creating a Database and User in MySQL

This particular task embodies the most prevalent and resourceful approach. It is possible to establish both a database and user, concurrently conferring complete privileges on the database to the designated user. This can be accomplished for all databases that currently exist on your MySQL server or that you intend to establish.

This method offers a seamless and secure procedure, whereby a specific user can only execute actions on a particular database. Consequently, it is possible to have multiple user-database couplings on your MySQL server.

From a MySQL perspective, the process involves creating both a user and a database, subsequently granting the user comprehensive privileges on that database. Thus, the following steps denote the sequence of events necessary to generate a MySQL user:

mysql> CREATE USER 'testOneUser'@'localhost' IDENTIFIED BY 'qawsedrf';

Subsequently, proceed to create a MySQL database:

mysql> CREATE DATABASE 'testOne';

Following the database creation, it is now time to confer all privileges on the testOne database to the testOneUser through the usage of the subsequent query:

mysql> GRANT ALL PRIVILEGES ON testOne.* to 'testOneUser'@'localhost';

Consequently, it is now possible for our user testOneUser to administer all tables within the testOne database. Should you desire to confer all privileges on a specific table within a particular database, it is merely necessary to replace testOne.* with testOne.TABLE_NAME within the query.

Such is the process for creating a database and user within MySQL. The final topic to cover pertains to removing or deleting a user within MySQL. This is an effortlessly executable task, equivalent to launching a drop query.

Deleting/Dropping a User in MySQL

MySQL employs a distinct terminology for deletion, known as “dropping.” Consequently, it is possible to drop both databases and users, with the associated query initiating with “DROP”. In order to delete a user account within MySQL, you must be acquainted with the user’s Username and Host. Our user testOneUser possesses a host of localhost. So, should you wish to delete a user with the account of 'testOneUser'@'localhost', it necessitates executing the subsequent query within your MySQL server.

mysql> DROP USER 'testOneUser'@'localhost';

Consequently, this denotes the procedure for deleting MySQL user accounts. By following the guidelines outlined within this guide, you will become proficient in managing users on your MySQL server.

Conclusion: Once you have developed a comprehensive understanding of how Databases, Users, and Privileges are managed within MySQL, the effective management of databases and users on your MySQL server becomes a far more straightforward endeavor. In essence, databases represent distinct entities that can be managed by MySQL users based on the privileges they possess pertaining to specific databases or groups of databases.

Please do not hesitate to notify us should you desire the addition of a particular section within this guide. Furthermore, feel free to utilize the comment section below to share your thoughts and inquiries. We are devoted to providing assistance!+