How to Create a User for a Specific Database

It is a common operation to create a user and giant all the permission to a specific database.

Step 1: Create a database

MariaDB [(none)]> create database dbname;
Query OK, 1 row affected (0.00 sec)

Step 2: Grant permissions to operate the database for a new user

MariaDB [(none)]> grant all on dbname.* to 'user'@'localhost' identified by 'pass';
Query OK, 0 rows affected (0.01 sec)

Note:

grant “all” means this new user can do everything in this database. Please visit this page, update the keyword “all” and using appropriate keywords instead if you wanna to create a restricted user.

dbname.* means this new user will able to operate all the tables in this database.

‘user’@’localhost’ means this user can only visit the page from local. If you wish this new user able to visit from remote, you should use “0.0.0.0” or your host, ip instead of ‘localhost’

‘pass’ is your password

Step 3: Refresh Privileges

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)

This command will reload the privileges, and then you can login using your new user and password