In order to be able to use a database, one needs to create: a new database, give access permission to the database server to a database user and finally grant all right to that specific database to this user.
For the purpose of this tutorial, I will demonstrate how to create a database for a company called MWired and user to access the database.
Now you should create the database with the root user:
1. Open mysql from terminal:
root@gs2:~# mysql -u root -p
2. Enter the password created before.
Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 49 Server version: 5.5.44-0ubuntu0.14.04.1 (Ubuntu) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
3. Enter the following line:
If you enter SHOW DATABASES; you should see it in the list. If so, you have a database ready to use!
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | +--------------------+ 8 rows in set (0.16 sec)
4. CREATE DATABASE piERP;
mysql> create database piERP; Query OK, 1 row affected (0.10 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | phpmyadmin | | piERP | +--------------------+ 9 rows in set (0.02 sec)
5. CREATE USER called gs and GRANT privileges to gs user.
From MySQL command line:
mysql> create user 'gs'@'localhost' IDENTIFIED BY 'MyGSL1nks15100s'; Query OK, 0 rows affected (0.04 sec)
As it stands at this point 'gs' user has no permissions to do anything with the databases. In fact, if 'gs' user even tries to login (with the password, password), they will not be able to reach the MySQL shell.
Therefore, the first thing to do is to provide the user with access to the information they will need.
mysql> grant all on piERP.* to 'gs'@'localhost' identified by 'MyGSL1nks15100s'; Query OK, 0 rows affected (0.04 sec)
The asterisks in this command refer to the database and table (respectively) that they can access—this specific command allows to the user to read, edit, execute and perform all tasks across all the databases and tables.
Once you have finalised the permissions that you want to set up for your new users, always be sure to reload all the privileges.
mysql> flush privileges; Query OK, 0 rows affected (0.24 sec)
Your changes will now be in effect.
MySQL Administrator makes creating users, dropping users, granting privileges, and seeing privileges quite easy.
That's it for now!
In the next post I will further explore MySQL where I will outline a practical business case so that you can relate to real-world scenario.