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 useramarok 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. mysql>
How To Grant Different User Permissions
Here is a short list of other common possible permissions that users can enjoy.
- ALL PRIVILEGES- as we saw previously, this would allow a MySQL user all access to a designated database (or if no database is selected, across the system)
- CREATE- allows them to create new tables or databases
- DROP- allows them to them to delete tables or databases
- DELETE- allows them to delete rows from tables
- INSERT- allows them to insert rows into tables
- SELECT- allows them to use the Select command to read through databases
- UPDATE- allow them to update table rows