How To Create a New User and Grant Permissions in MySQL

 MySQL is a powerful and popular open-source relational database management system (RDBMS) that serves as a robust foundation for storing, managing, and retrieving data. Its primary purpose lies in efficiently organizing and structuring data in a way that facilitates easy retrieval and manipulation. MySQL is widely used in various applications, ranging from small-scale web projects to large-scale enterprise solutions.

Creating a new user and grant permissions in MySQL is essential for ensuring secure and controlled access to the database. This process is like setting up a customized key system for your data house, allowing you to regulate who can enter and what they can do once inside. Users are only granted the specific permissions they need for their intended tasks, minimizing the risk of unauthorized access or accidental misuse.

In this tutorial, we will provide a detailed guide on creating a new user and granting permission in MySQL using Ubuntu 22.04.

How to create a New User and Grant Permissions in MySQL?

Before creating a new user, we need to ensure that MySQL is already installed and running in Ubuntu 22.04 which can be done by following the below steps:

Step 1: Installing MySQL on Ubuntu 22.04

First, we need to install MySQL using the below command:

$ sudo apt install mysql-server

Step 2: Starting MySQL Services

Next, we need to start the MySQL services on our Ubuntu 22.04 distribution:

$ sudo service mysql start

Step 3: Enabling MySQL Service on Startup

If we want to enable MySQL services every time we boot out Ubuntu distribution, we can execute the following command:

$ sudo systemctl enable mysql

Step 4: Verifying MySQL Service Status

After following the above steps, MySQL services should be up and running without any issue which we can check using:

$ sudo service mysql status

Step 5: Interacting with MySQL Database

Now we will use the root user to interact with MySQL database using the following command:

$ sudo mysql -u root -p

In the next section, we will guide you on how you can create a new user.

How to Create a New User in MySQL

Once we log in to MySQL, we can use the following command to create a new user:

$ mysql> CREATE USER 'New-UserName'@'localhost or IP' IDENTIFIED BY 'User-Password';

In this command, we need to replace ‘new_user’ and ‘password’ with the actual name and password which we want to set.

If you want to allow connections from any host, you can use ‘%’ instead of ‘localhost’:

$ CREATE USER 'new_user'@'%' IDENTIFIED BY 'password';

How to Grant Permission to the User in MySQL

After creating the user, you need to grant the necessary permissions. For example, to grant all privileges on a specific database, you can use:

$ GRANT ALL PRIVILEGES ON database_name.* TO 'new_user'@'localhost';

Replace database_name with the name of the database to which you want to grant access. If you want to grant all privileges on all databases, you can use the wildcard *:

$ GRANT ALL PRIVILEGES ON *.* TO 'new_user'@'localhost';

Flush Privileges

After granting privileges, you need to flush the privileges to apply the changes immediately:

FLUSH PRIVILEGES;

How to Exit MySQL

Once you have created the user and granted privileges, exit the MySQL prompt:

$ exit;

Now, you’ve successfully created a new user in MySQL with the specified permissions. Adjust the commands based on your specific requirements and security considerations.

Advantages of MySQL

One of the significant advantages of MySQL is its scalability, enabling it to handle an extensive range of data and user loads. It seamlessly accommodates the growth of data and users, making it suitable for projects of different sizes and complexities. This scalability ensures that MySQL can adapt to the evolving needs of applications over time.

Another key advantage is its reliability and performance. MySQL is designed to deliver high-performance data retrieval and management, ensuring quick and efficient access to information. Its reliability is crucial for applications that require consistent and stable data handling, such as e-commerce websites, content management systems, and financial applications.

Furthermore, MySQL supports multiple programming languages, making it versatile and compatible with various application development frameworks. This flexibility allows developers to choose the programming language that best suits their project requirements while still benefiting from the robust capabilities of MySQL.

Conclusion

MySQL serves as a reliable, scalable, and high-performance database management system that caters to diverse data storage needs. Its open-source nature and compatibility with multiple programming languages contribute to its widespread adoption and continued relevance in the ever-evolving landscape of data management. In this guide, we provide in-depth details on how to create a new user and grant permissions in MySQL on Ubuntu 22.04.

FAQ