Creating a DATABASE and USER for MariaDB
This is a quick and simple tutorial on how to create a database in MariaDB and as well creating and adding a
user to use that DB.
Moreover we are going to see how to allow said user to connect from any server in the local network.
All text like this are the commands we are going to use.
Things written like this is what you should expect from your command or see in the terminal.
I am assuming you have a fresh MariaDB install. For example what you get after running apt install mariadb-server on Debian based systems.
Creating a database in MariaDB
First run mysql as root from the server terminal to interact with MariaDB.
You should now see something like this.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 49
Server version: 10.3.18-MariaDB-0+deb10u1 Debian 10
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
We are no going to create a database called this_database with the command CREATE
DATABASE `this_database`;
Please notice the quote style " ` " and the " ; " at the end.
You should see something like this.
Query OK, 1 row affected (0.001 sec)
Creating a User in MariaDB
Ok, you have a database, now let's create a user so you or your application can actually
make use of it
We are going to create a user called my_self and it's password is going to be my_password,
of course you can change the password, user name and database name to anything you like.
The MySQL command for this is CREATE USER 'my_user' IDENTIFIED BY 'my_password';.
You should see an output like this.
Query OK, 0 rows affected (0.001 sec)
Granting Privileges to User in a Database in MariaDB
Fantastic! Now we have a database called this_database and a user named
my_user authenticated with the password my_password.
However! Just because both exist it doesn't mean the user can use the
database!
Now we are going to allow my_user to access the database this_database from localhost, that
is, the same server, the same place MariaDB is running from.
Let's use the command GRANT USAGE ON *.* TO 'my_user'@localhost IDENTIFIED BY 'my_password';
The output should be something like this.
Query OK, 0 rows affected (0.000 sec)
Now, if you want to allow user to connect from any other server on the network, just
replace localhost with '%'.
The command and output will be. GRANT USAGE ON *.* TO 'my_user'@'%' IDENTIFIED BY
'my_password';
Query OK, 0 rows affected (0.001 sec)
And finally lets give all privileges to my_user so it can modify this_database. As before " '@' " for
network access and " localhost " for server access.
GRANT ALL privileges ON `my_database`.* TO 'my_user'@'%' IDENTIFIED BY 'my_password';
Query OK, 0 rows affected (0.001 sec)
Lets finish up with a FLUSH PRIVILEGES;.
You can see if this worked by using the command SHOW GRANTS FOR 'my_user'@localhost;
SHOW GRANTS FOR 'my_user'@localhost;
+--------------------------------------------------------------------+
| Grants for my_user@localhost |
+--------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'my_user'@'localhost' |
| GRANT ALL PRIVILEGES ON `this_database`.* TO 'my_user'@'localhost' |
+--------------------------------------------------------------------+
2 rows in set (0,00 sec)