MySQL: Allow user access to database

After installing MySQL on your Linux system and creating a new database, you will need to setup a new user to access that database, granting it permissions to read and/or write data to it.

It is not recommended to use the root account, but rather create a new account and grant privileges as needed. In this tutorial, you will see how to allow a user to access a MySQL database on Linux.

In this tutorial you will learn:

• How to create a new MySQL user
• How to allow full user access to MySQL database
• How to allow certain types of access to MySQL database

Software Requirements and Linux Command Line Conventions

Category	Requirements, Conventions or Software Version Used
System	Linux system
Software	MySQL
Other	Privileged access to your Linux system as root or via the sudo command.
Conventions	# – requires given linux commands to be executed with root privileges either directly as a root user or by use of sudo command $ – requires given linux commands to be executed as a regular non-privileged user

MySQL: Allow user access to database step by step instructions

---

---

1. Start by opening up MySQL with the root account.

   $ sudo mysql
   

   Or, on some configurations you may be required to enter the following command and provide your root password:

   $ mysql -u root -p
   

2. If you have not already done so, create a new user to which we will grant database permissions in the later steps. The following example creates user linuxconfig. Fill in the placeholder values with your own information.

   mysql> CREATE USER 'linuxconfig'@'localhost' IDENTIFIED BY 'password_here';
   

   Also create your database if you have not done so already:

   mysql> CREATE DATABASE test;
   

3. Now it is time to grant permissions to our linuxconfig user on the test database. Here is how you would grant full permissions to the user, which allows them to do anything to the database.

   mysql> GRANT ALL PRIVILEGES ON test.* TO 'linuxconfig'@'localhost';
   

   Or if you only want to grant the user access to a specific table mytable inside of the test database:

   mysql> GRANT ALL PRIVILEGES ON test.mytable TO 'linuxconfig'@'localhost';
   

   If you do not want to grant all privileges to the user, there are others you can use as well.

   CREATE - allow user to create new tables in the database
   DROP - allow user to delete tables or the database itself
   DELETE - allow user to delete rows withs in the tables
   INSERT - allow user to insert new data rows into the tables
   SELECT - allow user to read entries in the database
   UPDATE - allow user to update existing database rows
   

4. The last step is to flush all privileges before exiting MySQL completely.

   mysql> FLUSH PRIVILEGES;
   mysql> exit
   

---

---

That’s all there is to it. Your user should now be able to access the database or whichever tables inside of the database that you granted access to.

Closing Thoughts

In this tutorial, we saw how to allow a user access to a MySQL database on a Linux system. This is better and more secure than using the root account to do everything, and should work the same on any Linux distro. It is now easy enough to grant further privileges or take them away as needed.