Creating a New User and Granting Permissions
Step 1 : We’ll need to enter the MariaDB shell, which again is simply a matter of executing the mariadb command with sudo.
Step 2 : Now, we can create our new administrative user. Here’s the command to create a new user in MariaDB (replace the username and password in the command with your desired credentials):
CREATE USER 'admin'@'localhost' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;
Step 3 : We can create a set of permissions (also known as Grants) with the Grant command.
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost'; FLUSH PRIVILEGES;
Step 4 : Now, we have an administrative user we can use to manage our server’s databases. We can use this account for managing our server instead of the root account.
To test out your new user, log out by typing:
and log back in with this command in terminal:
mariadb -u admin -p
Step 5 : After entering the password, you’ll be logged in to MariaDB as admin.
Creating a database
Step 6 : Let’s create a database. At the MariaDB prompt, execute:
CREATE DATABASE new_database;
Step 7 : To list all databases on our server (and confirm our database was created properly), we can execute the following command:
Step 8 : We can also list users just as easily:
SELECT HOST, USER, PLUGIN, PASSWORD FROM mysql.user;
Step 9 : The USE command allows us to select a database we want to work with.
Remove user access
Step 10 : If you’d like to remove user access, you can use the following command to do so (as root)
DELETE FROM mysql.user WHERE user='myuser' AND host='localhost';