To create a user in MySQL or MariaDB and granting it permissions to access a database is a basic task when deploying a new website on a LAMP stack server. To read and write data on a database is a very common and essential task that is necessary to perform each time you install a new application such as WordPress website.
These following commands are valid to be executed both from a generic MySQL command line interface and from any other tool that allows to execute SQL commands against a MySQL instance such as phpMyAdmin, MySQL Workbench or SSH access. These can also be run in MariaDB with identical result.
In the given tutorial I am going to show it via using SSH access to server. I assume that you have a valid SSH access to your server and you are logged in as a root or a user with root privileges.
Let’s start!
First of all make a root login to your MySQL or MariaDB server. I am using MariDB in this example.
mysql -u root -p
Enter MySQL or MariaDB root password when asked to enter. When logged in you should be shown like the following message:
Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 202881 Server version: 10.3.17-MariaDB MariaDB Server Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
Following are the steps in sequence to create a user and grant permissions on database.
1. Create new database
MariaDB [(none)]> CREATE DATABASE `mydb`;
Hint: Replace mydb with your new database name
2. Create a user
MariaDB [(none)]> CREATE USER 'myuser' IDENTIFIED BY 'mypassword';
Hint: Replace myuser and mypassword with your new database user and it’s password respectively.
3. Grant permissions to access and use the MySQL server
Allow access only from localhost. This is the most secure and common configuration you may have for a web application
MariaDB [(none)]> GRANT USAGE ON *.* TO 'myuser'@localhost IDENTIFIED BY 'mypassword';
Hint: Replace myuser with your new database user and mypassword with it’s password
Following step is optional and not recommended.
To allow access to MySQL server from any other computer on the network:
MariaDB [(none)]> GRANT USAGE ON *.* TO 'myuser'@'%' IDENTIFIED BY 'mypassword';
4. Grant all privileges to new created user on new created database
MariaDB [(none)]> GRANT ALL privileges ON `mydb`.* TO 'myuser'@localhost;
As in the previous command, if you want the user to work with the database from any location you will have to replace localhost with ‘%’. Not recommended!
5. Apply changes made
To be effective the new assigned permissions you must finish with the following command:
MariaDB [(none)]> FLUSH PRIVILEGES;
6. Verify that new user has required right permissions
MariaDB [(none)]> SHOW GRANTS FOR 'myuser'@localhost; +--------------------------------------------------------------+ | Grants for myuser@localhost | +--------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'myuser'@'localhost' | | GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'localhost' | +--------------------------------------------------------------+ 2 rows in set (0,00 sec)
If you made a mistake in the process you can undo all the steps above by executing the following commands. If you used % instead of localhost in the previous commands use it in the place of location in the following as well:
DROP USER myuser@localhost; DROP DATABASE mydb;
Here is a very simple and small bash script that may help you to do all this in a much faster way. Simply change your user and database names, and that’s it:
#! /bin/bash
newUser='testuser'
newDbPassword='testpwd'
newDb='testdb'
host=localhost
#host='%'
commands="CREATE DATABASE \`${newDb}\`;CREATE USER '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';GRANT USAGE ON *.* TO '${newUser}'@'${host}' IDENTIFIED BY '${newDbPassword}';GRANT ALL privileges ON \`${newDb}\`.*
TO '${newUser}'@'${host}';FLUSH PRIVILEGES;"
echo "${commands}" | /usr/bin/mysql -u root -p
