How to create a database in Mysql and grant permissions on a user

After installing MySQL or MariaDB you can enter in mysqli command line by

sudo mysql

 

This is how you create a new database:

CREATE SCHEMA mydb;

See the list of databases in mysql server:

SHOW SCHEMAS;

Now you have access in the mydb schema because you are using the root user, if you want to create a new user so you can have acces in the database from an application(not safe to use root user) you have to create a new user

CREATE USER 'myuser'@'localhost' IDENTIFIED BY 'password';

We use 'localhost' instead of the computer ip so we restrict the access in the local machine.

We can give now to the new user all the privileges to the database:

GRANT ALL PRIVILEGES ON mydb.* TO 'myuser'@'localhost';

Here we used ALL PRIVILEGES beacause we need a user to take all control of the database, if we want to make different user with different privileges

 we can use the other alternatives:

https://mariadb.com/kb/en/library/show-privileges/

https://dev.mysql.com/doc/refman/5.7/en/privileges-provided.html

We gave the privileges for all the tables of the database mydb, so myuser wont have access to ther databases, you can give him access to all databases replacing mydb with *

Finally we make changes take effect

FLUSH PRIVILEGES;

Check the user's privileges

SHOW GRANTS FOR myuser;

 

The next time you can log in to mysql with this user with

mysql -u myuser -p