MySQL is a widely spread SQL database management system mainly used on LAMP (Linux/Apache/MySQL/PHP) projects.
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.
This tutorial will explain how to create a new database and give a user the appropriate grant permissions.
For the purpose of this tutorial, I will explain how to create a database and user for the music player Amarok. In order to index its music collection, Amarok quand use a mysql backend.
The requirement for this set up is to have access to a database. We are going to create a database called amarok which will be accessible from localhost to user amarok idetified by the password amarok….
Obviously, we need to to have a mysql server installed as well as amarok:
$ sudo apt-get install mysql-server amarok
On a default settings, mysql root user do not need a password to authenticate from localhost. In this case, ou can login as root on your mysql server using:
$ mysql -u root
If a password is required, use the extra switch -p:
$ mysql -u root -p
Enter password:
Now that you are logged in, we create a database:
mysql> create database amarokdb;
Query OK, 1 row affected (0.00 sec)
We allow user amarokuser to connect to the server from localhost using the password amarokpasswd:
mysql> grant usage on *.* to [email protected] identified by ‘amarokpasswd’;
Query OK, 0 rows affected (0.00 sec)
And finally we grant all privileges on the amarok database to this user:
mysql> grant all privileges on amarokdb.* to [email protected] ;
Query OK, 0 rows affected (0.00 sec)
And that’s it. You can now check that you can connect to the MySQL server using this command:
$ mysql -u amarokuser -p’amarokpasswd’ amarokdb
Your MySQL connection id is 12
Server version: 5.0.38-Ubuntu_0ubuntu1-log Ubuntu 7.04 distribution
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
You can check the privileges of the current user by running
SHOW GRANTS;Or the privileges of ANY user by running
SHOW GRANTS FOR 'user'@'host'————————————————————————————————————
MySQL – Creating and editing users
Creating users for MySQL runs along the same lines as adding users on the Cloud Server itself – you create a user with a password and assign permissions to different databases as needed.
As with the other MySQL commands, you will see it is actually quite easy to do this from the command line.
Login
First thing is to log into your Cloud Server via the terminal or PuTTY, etc and then log into MySQL:
mysql -u root -p
You will be prompted for your MySQL root password (note this is not the same as the Cloud Server root password).
New user
Let’s jump straight in and create a new user. In this example the username will be ‘test’. We’ll also set a password for the new user:
CREATE USER 'test'@'localhost' IDENTIFIED BY 'newpassword';
Next we need to flush the privileges which reloads the ‘user’ table in MySQL – do this each time you add or edit users.
FLUSH PRIVILEGES;
Done.
Permissions – Select
At this stage, our new user (‘test’) can’t do anything as he has no permissions set.
We can start by assigning ‘select’ permissions on all the available databases. This will allow him to read them but not edit or delete.
Of course, this is just an example of how to set permissions – you may not want a user to have select permissions on all the databases. Please adjust for your needs.
GRANT SELECT ON * . * TO 'test'@'localhost';
Permissions – All
Let’s create a new database and allow ‘test’ to have full access to it. When done he will be able to create, read, update and delete records as needed.
This is the type of permission set that could be used when setting a user and database for a web application. There would be no need to have the user access any other database.
CREATE DATABASE mytestdb;
Now we have the database and the user, we can assign the privileges:
GRANT ALL PRIVILEGES ON `mytestdb` . * TO 'test'@'localhost';
Note the backticks (`) surrounding the database name.
Flush the privileges:
FLUSH PRIVILEGES;
Log in as the new user
Logging into MySQL as the new user takes exactly the same format as when we logged in earlier:
mysql -u test -p
You will be prompted for the ‘test’ user password.
Once logged in, we can try to create a new database:
CREATE DATABASE mytestdb2;
You will get an error like this:
ERROR 1044 (42000): Access denied for user 'test'@'localhost' to database 'mytestdb2'
Which is good news as we granted ‘select’ privileges to everything and ‘all’ privileges on the ‘mytestdb’ database only.
Looks like everything is working very well.
Dropping a user
There may come a point where we have to part ways with ‘test’. In a similar manner to dropping databases, we can simply ‘drop’ the user.
You will need to be logged into MySQL as the root user for this:
DROP USER 'test'@'localhost';
