How-To create a MySQL database and set privileges to a user

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 amarokuser@localhost 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 amarokuser@localhost ;
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';
Advertisements
Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: